DROP TABLE if exists `viko-lab.aquila.amz_keywords`;
CREATE TABLE `viko-lab.aquila.amz_keywords` (
  `id` INT64 NOT NULL,
  `keyword_phrase` STRING DEFAULT NULL,
  -- `total_click_share` INT64 DEFAULT NULL,
  -- `total_conv_share` INT64 DEFAULT NULL,
  `iq_score` INT64 DEFAULT NULL,
  `search_volume` INT64 DEFAULT NULL,
  `search_volume_trend` INT64 DEFAULT NULL,
  `sponsored_asin` INT64 DEFAULT NULL,
  `competing_products` INT64 DEFAULT NULL,
  `CPR` INT64 DEFAULT NULL,
  `title_density` INT64 DEFAULT NULL,
  `amazon_rec_rank` INT64 DEFAULT NULL,
  `sponsored_rank` INT64 DEFAULT NULL,
  `createdAt` TIMESTAMP,
  PRIMARY KEY (`id`)  NOT ENFORCED
);

DROP TABLE if exists `viko-lab.aquila.amz_keyword_monthly_search_volumes`;
CREATE TABLE `viko-lab.aquila.amz_keyword_monthly_search_volumes`  (
    `keyword_id` INT64 NOT NULL,
    `year` INT64,
    `month` INT64,
    `monthlySearches` INT64,
    `createdAt` TIMESTAMP,
    FOREIGN KEY (`keyword_id`) REFERENCES `viko-lab.aquila.amz_keywords`(`id`) NOT ENFORCED
);

DROP TABLE if exists `viko-lab.aquila.amz_product_keywords`;
CREATE TABLE `viko-lab.aquila.amz_product_keywords` (
  `product_id` INT64 NOT NULL,
  `asin` STRING NOT NULL,
  `category_id` INT64 NOT NULL,
  `domain_id` STRING NOT NULL,
  `global_keyword_id` INT64 DEFAULT NULL,
  `keyword_id` INT64 NOT NULL,
  PRIMARY KEY (`product_id`,`keyword_id`)  NOT ENFORCED,
  FOREIGN KEY (`keyword_id`) REFERENCES `viko-lab.aquila.amz_keywords`(`id`) NOT ENFORCED
);

ALTER TABLE viko-lab.aquila.amz_keywords
ADD COLUMN gads BOOLEAN;


DROP TABLE if exists `viko-lab.aquila.keyword_metadata`;
CREATE TABLE `viko-lab.aquila.keyword_metadata` (
  `keyword_phrase` STRING DEFAULT NULL,
  `domain_id` STRING DEFAULT NULL,
  `brand` STRING DEFAULT NULL,
  `retailer` STRING DEFAULT NULL,
  `intencionality` STRING DEFAULT NULL,
  `updatedAt` TIMESTAMP,
  `createdAt` TIMESTAMP,
  PRIMARY KEY (`keyword_phrase`)  NOT ENFORCED
);
