SET @sql = IF(
  EXISTS (
    SELECT 1
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'clients'
      AND COLUMN_NAME = 'facebook_ads_account_id'
  ),
  'DO 1',
  'ALTER TABLE `clients` ADD COLUMN `facebook_ads_account_id` VARCHAR(40) NULL DEFAULT NULL AFTER `google_ads_customer_id`'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

CREATE TABLE IF NOT EXISTS `facebook_campaigns` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `client_id` INT(11) NOT NULL,
  `account_id` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
  `campaign_id` BIGINT(20) UNSIGNED NOT NULL,
  `campaign_name` VARCHAR(255) NOT NULL DEFAULT '',
  `status` VARCHAR(40) NULL DEFAULT NULL,
  `effective_status` VARCHAR(40) NULL DEFAULT NULL,
  `date_sync` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_facebook_campaigns_client_campaign` (`client_id`, `campaign_id`),
  KEY `idx_facebook_campaigns_client_id` (`client_id`),
  KEY `idx_facebook_campaigns_campaign_id` (`campaign_id`),
  CONSTRAINT `FK_facebook_campaigns_clients`
    FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `facebook_campaigns_history` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `facebook_campaign_id` INT(11) NOT NULL,
  `spend` DECIMAL(20,6) NOT NULL DEFAULT 0.000000,
  `impressions` INT(11) NOT NULL DEFAULT 0,
  `clicks` INT(11) NOT NULL DEFAULT 0,
  `ctr` DECIMAL(20,6) NOT NULL DEFAULT 0.000000,
  `cpc` DECIMAL(20,6) NOT NULL DEFAULT 0.000000,
  `date_add` DATE NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_facebook_campaigns_history_day` (`facebook_campaign_id`, `date_add`),
  KEY `idx_facebook_campaigns_history_date_add` (`date_add`),
  CONSTRAINT `FK_facebook_campaigns_history_campaigns`
    FOREIGN KEY (`facebook_campaign_id`) REFERENCES `facebook_campaigns` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `facebook_ad_sets` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `client_id` INT(11) NOT NULL,
  `facebook_campaign_id` INT(11) NULL DEFAULT NULL,
  `campaign_id` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
  `ad_set_id` BIGINT(20) UNSIGNED NOT NULL,
  `ad_set_name` VARCHAR(255) NOT NULL DEFAULT '',
  `status` VARCHAR(40) NULL DEFAULT NULL,
  `effective_status` VARCHAR(40) NULL DEFAULT NULL,
  `date_sync` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_facebook_ad_sets_client_ad_set` (`client_id`, `ad_set_id`),
  KEY `idx_facebook_ad_sets_client_id` (`client_id`),
  KEY `idx_facebook_ad_sets_facebook_campaign_id` (`facebook_campaign_id`),
  KEY `idx_facebook_ad_sets_campaign_id` (`campaign_id`),
  CONSTRAINT `FK_facebook_ad_sets_clients`
    FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_facebook_ad_sets_campaigns`
    FOREIGN KEY (`facebook_campaign_id`) REFERENCES `facebook_campaigns` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `facebook_ad_sets_history` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `facebook_ad_set_id` INT(11) NOT NULL,
  `spend` DECIMAL(20,6) NOT NULL DEFAULT 0.000000,
  `impressions` INT(11) NOT NULL DEFAULT 0,
  `clicks` INT(11) NOT NULL DEFAULT 0,
  `ctr` DECIMAL(20,6) NOT NULL DEFAULT 0.000000,
  `cpc` DECIMAL(20,6) NOT NULL DEFAULT 0.000000,
  `date_add` DATE NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_facebook_ad_sets_history_day` (`facebook_ad_set_id`, `date_add`),
  KEY `idx_facebook_ad_sets_history_date_add` (`date_add`),
  CONSTRAINT `FK_facebook_ad_sets_history_ad_sets`
    FOREIGN KEY (`facebook_ad_set_id`) REFERENCES `facebook_ad_sets` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `facebook_ads` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `client_id` INT(11) NOT NULL,
  `facebook_campaign_id` INT(11) NULL DEFAULT NULL,
  `facebook_ad_set_id` INT(11) NULL DEFAULT NULL,
  `campaign_id` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
  `ad_set_id` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
  `ad_id` BIGINT(20) UNSIGNED NOT NULL,
  `ad_name` VARCHAR(255) NOT NULL DEFAULT '',
  `status` VARCHAR(40) NULL DEFAULT NULL,
  `effective_status` VARCHAR(40) NULL DEFAULT NULL,
  `date_sync` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_facebook_ads_client_ad` (`client_id`, `ad_id`),
  KEY `idx_facebook_ads_client_id` (`client_id`),
  KEY `idx_facebook_ads_facebook_campaign_id` (`facebook_campaign_id`),
  KEY `idx_facebook_ads_facebook_ad_set_id` (`facebook_ad_set_id`),
  KEY `idx_facebook_ads_campaign_id` (`campaign_id`),
  KEY `idx_facebook_ads_ad_set_id` (`ad_set_id`),
  CONSTRAINT `FK_facebook_ads_clients`
    FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_facebook_ads_campaigns`
    FOREIGN KEY (`facebook_campaign_id`) REFERENCES `facebook_campaigns` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_facebook_ads_ad_sets`
    FOREIGN KEY (`facebook_ad_set_id`) REFERENCES `facebook_ad_sets` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `facebook_ads_history` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `facebook_ad_id` INT(11) NOT NULL,
  `spend` DECIMAL(20,6) NOT NULL DEFAULT 0.000000,
  `impressions` INT(11) NOT NULL DEFAULT 0,
  `clicks` INT(11) NOT NULL DEFAULT 0,
  `ctr` DECIMAL(20,6) NOT NULL DEFAULT 0.000000,
  `cpc` DECIMAL(20,6) NOT NULL DEFAULT 0.000000,
  `date_add` DATE NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_facebook_ads_history_day` (`facebook_ad_id`, `date_add`),
  KEY `idx_facebook_ads_history_date_add` (`date_add`),
  CONSTRAINT `FK_facebook_ads_history_ads`
    FOREIGN KEY (`facebook_ad_id`) REFERENCES `facebook_ads` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;