-- Migracja: unifikacja modelu danych produktow
-- Cel:
-- 1) przeniesienie danych produktowych do tabeli products (kolumny dotad trzymane glownie w products_data)
-- 2) dodanie tabeli agregatow products_aggregate (30 dni + all-time)

-- ===========================
-- products: nowe kolumny danych produktu
-- ===========================

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

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

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

SET @sql = IF(
  EXISTS (
    SELECT 1
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'products'
      AND COLUMN_NAME = 'description'
  ),
  'DO 1',
  'ALTER TABLE `products` ADD COLUMN `description` TEXT NULL AFTER `title`'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = IF(
  EXISTS (
    SELECT 1
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'products'
      AND COLUMN_NAME = 'google_product_category'
  ),
  'DO 1',
  'ALTER TABLE `products` ADD COLUMN `google_product_category` TEXT NULL AFTER `description`'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

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

-- ===========================
-- Backfill products <- products_data
-- (zostawiamy products_data na razie dla kompatybilnosci)
-- ===========================

UPDATE `products` p
INNER JOIN `products_data` pd ON pd.product_id = p.id
SET
  p.custom_label_4 = CASE
    WHEN ( p.custom_label_4 IS NULL OR TRIM( p.custom_label_4 ) = '' ) AND pd.custom_label_4 IS NOT NULL AND TRIM( pd.custom_label_4 ) <> '' THEN pd.custom_label_4
    ELSE p.custom_label_4
  END,
  p.custom_label_3 = CASE
    WHEN ( p.custom_label_3 IS NULL OR TRIM( p.custom_label_3 ) = '' ) AND pd.custom_label_3 IS NOT NULL AND TRIM( pd.custom_label_3 ) <> '' THEN pd.custom_label_3
    ELSE p.custom_label_3
  END,
  p.title = CASE
    WHEN ( p.title IS NULL OR TRIM( p.title ) = '' ) AND pd.title IS NOT NULL AND TRIM( pd.title ) <> '' THEN pd.title
    ELSE p.title
  END,
  p.description = CASE
    WHEN ( p.description IS NULL OR TRIM( p.description ) = '' ) AND pd.description IS NOT NULL AND TRIM( pd.description ) <> '' THEN pd.description
    ELSE p.description
  END,
  p.google_product_category = CASE
    WHEN ( p.google_product_category IS NULL OR TRIM( p.google_product_category ) = '' ) AND pd.google_product_category IS NOT NULL AND TRIM( pd.google_product_category ) <> '' THEN pd.google_product_category
    ELSE p.google_product_category
  END,
  p.product_url = CASE
    WHEN ( p.product_url IS NULL OR TRIM( p.product_url ) = '' ) AND pd.product_url IS NOT NULL AND TRIM( pd.product_url ) <> '' THEN pd.product_url
    ELSE p.product_url
  END;

-- ===========================
-- Tabela agregatow produktow
-- ===========================

CREATE TABLE IF NOT EXISTS `products_aggregate` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `product_id` INT(11) NOT NULL,
  `campaign_id` INT(11) NOT NULL DEFAULT 0,
  `ad_group_id` INT(11) NOT NULL DEFAULT 0,
  `impressions_30` INT(11) NOT NULL DEFAULT 0,
  `clicks_30` INT(11) NOT NULL DEFAULT 0,
  `ctr_30` DECIMAL(20,6) NOT NULL DEFAULT 0.000000,
  `cost_30` DECIMAL(20,6) NOT NULL DEFAULT 0.000000,
  `conversions_30` DECIMAL(20,6) NOT NULL DEFAULT 0.000000,
  `conversion_value_30` DECIMAL(20,6) NOT NULL DEFAULT 0.000000,
  `roas_30` DECIMAL(20,6) NOT NULL DEFAULT 0.000000,
  `impressions_all_time` INT(11) NOT NULL DEFAULT 0,
  `clicks_all_time` INT(11) NOT NULL DEFAULT 0,
  `ctr_all_time` DECIMAL(20,6) NOT NULL DEFAULT 0.000000,
  `cost_all_time` DECIMAL(20,6) NOT NULL DEFAULT 0.000000,
  `conversions_all_time` DECIMAL(20,6) NOT NULL DEFAULT 0.000000,
  `conversion_value_all_time` DECIMAL(20,6) NOT NULL DEFAULT 0.000000,
  `roas_all_time` DECIMAL(20,6) NOT NULL DEFAULT 0.000000,
  `date_sync` DATE NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_products_aggregate_scope` (`product_id`,`campaign_id`,`ad_group_id`),
  KEY `idx_products_aggregate_campaign_id` (`campaign_id`),
  KEY `idx_products_aggregate_ad_group_id` (`ad_group_id`),
  KEY `idx_products_aggregate_date_sync` (`date_sync`),
  CONSTRAINT `FK_products_aggregate_products`
    FOREIGN KEY (`product_id`) REFERENCES `products` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;