--insert into viko-lab.aquila.languages(id, name, code)
--SELECT id, name, code FROM viko-lab.aquila.gads_languages;
CREATE TABLE `viko-lab.aquila.gads_languages`  (
    id INT64,
    name STRING,
    code STRING,
    PRIMARY KEY(id) NOT ENFORCED
);

-- insert into viko-lab.aquila.locations(id, name, canonical_name, parent_id, country_code,target_type )
-- SELECT id, name, canonical_name, parent_id, country_code,target_type FROM viko-lab.aquila.gads_locations;
CREATE TABLE `viko-lab.aquila.gads_locations`  (
    id INT64,
    name STRING,
    canonical_name STRING,
    parent_id INT64,
    country_code STRING,
    target_type STRING,
    status STRING,
    PRIMARY KEY(id) NOT ENFORCED
);

CREATE TABLE `viko-lab.aquila.gads_keywordIdeaMetrics`  (
    seed STRING,
    keyword STRING,
    client STRING,
    languageId INT64 REFERENCES `viko-lab.aquila.gads_languages`(id) NOT ENFORCED,
    locationId INT64 REFERENCES `viko-lab.aquila.gads_locations`(id) NOT ENFORCED,
    averageCpcMicros INT64,
    avgMonthlySearches INT64,
    competition INT64,
    competitionIndex INT64,
    highTopOfPageBidMicros INT64,
    lowTopOfPageBidMicros INT64,
    createdAt TIMESTAMP,
    PRIMARY KEY(keyword, languageId, locationId, createdAt) NOT ENFORCED
);

CREATE TABLE `viko-lab.aquila.gads_keywordIdeaMonthlySearchVolumes`  (
    keyword STRING,
    languageId INT64,
    locationId INT64,
    year INT64,
    month INT64,
    monthlySearches INT64,
    createdAt TIMESTAMP,
    PRIMARY KEY(keyword, languageId, locationId) NOT ENFORCED,
    -- FOREIGN KEY (keyword, languageId, locationId) REFERENCES `viko-lab.aquila.gads_keywordIdeaMetrics`(keyword, languageId, locationId) NOT ENFORCED
);


CREATE TABLE `viko-lab.aquila.gads_requests`  (
    id STRING,
    keywords ARRAY<STRING>,
    languageId INT64,
    locationId INT64,
    client STRING,
    createdAt TIMESTAMP,
    PRIMARY KEY(id) NOT ENFORCED,
);

ALTER TABLE `viko-lab.aquila.gads_locations` ADD COLUMN available BOOL;

