dotnet/track_report_schema_fix.sql

-- Fix track report dimensions:
-- 1. track_links stores platform and typename separately.
-- 2. track_daily_report uniqueness is based on the monitored link + metric scene + account.

SET @sql = (
  SELECT IF(
    COUNT(*) = 0,
    'ALTER TABLE `track_links` ADD COLUMN `typename` varchar(64) NOT NULL DEFAULT '''' AFTER `platform`',
    'SELECT 1'
  )
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'track_links'
    AND COLUMN_NAME = 'typename'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = (
  SELECT IF(
    COUNT(*) = 0,
    'ALTER TABLE `track_daily_report` ADD COLUMN `typename` varchar(64) NOT NULL DEFAULT '''' AFTER `platform`',
    'SELECT 1'
  )
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'track_daily_report'
    AND COLUMN_NAME = 'typename'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = (
  SELECT IF(
    COUNT(*) = 0,
    'ALTER TABLE `track_daily_report` ADD COLUMN `account_id` int NOT NULL DEFAULT 0 AFTER `track_link_id`',
    'SELECT 1'
  )
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'track_daily_report'
    AND COLUMN_NAME = 'account_id'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = (
  SELECT IF(
    (SELECT COUNT(*)
     FROM information_schema.TABLES
     WHERE TABLE_SCHEMA = DATABASE()
       AND TABLE_NAME = 'track_request_logs') > 0
    AND COUNT(*) = 0,
    'ALTER TABLE `track_request_logs` ADD COLUMN `account_id` int NOT NULL DEFAULT 0 AFTER `track_id`',
    'SELECT 1'
  )
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'track_request_logs'
    AND COLUMN_NAME = 'account_id'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

UPDATE `track_links`
SET `typename` = CASE
  WHEN `typename` <> '' THEN `typename`
  WHEN `platform` IN ('淘宝', '京东', '拼多多', 'tb', 'jd', 'pdd', '1', '9', '13') THEN ''
  ELSE `platform`
END;

UPDATE `track_links` SET `platform` = '淘宝' WHERE `id` IN (1, 2);
UPDATE `track_links` SET `platform` = '京东' WHERE `id` IN (19, 20);
UPDATE `track_links` SET `platform` = '拼多多' WHERE `id` IN (21, 22);

SET @sql = (
  SELECT IF(
    COUNT(*) > 0,
    'ALTER TABLE `track_daily_report` DROP INDEX `uk_track_daily`',
    'SELECT 1'
  )
  FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'track_daily_report'
    AND INDEX_NAME = 'uk_track_daily'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

UPDATE `track_daily_report` d
JOIN `track_links` l ON d.`track_link_id` = l.`id`
SET d.`platform` = l.`platform`,
    d.`typename` = l.`typename`,
    d.`scene` = CASE WHEN d.`scene` <> '' THEN d.`scene` ELSE l.`scene` END,
    d.`unique_id` = l.`unique_id`;

DROP TEMPORARY TABLE IF EXISTS `tmp_track_daily_keep`;
DROP TEMPORARY TABLE IF EXISTS `tmp_track_daily_merge`;

CREATE TEMPORARY TABLE `tmp_track_daily_keep` AS
SELECT MIN(`id`) AS `id`
FROM `track_daily_report`
GROUP BY `report_date`, `event_type`, `track_link_id`, `scene`, `account_id`;

CREATE TEMPORARY TABLE `tmp_track_daily_merge` AS
  SELECT
    `report_date`,
    `event_type`,
    `track_link_id`,
    `scene`,
    `account_id`,
    MAX(`event_count`) AS `event_count`
  FROM `track_daily_report`
  GROUP BY `report_date`, `event_type`, `track_link_id`, `scene`, `account_id`;

UPDATE `track_daily_report` d
JOIN `tmp_track_daily_merge` m
  ON d.`report_date` = m.`report_date`
   AND d.`event_type` = m.`event_type`
   AND d.`track_link_id` = m.`track_link_id`
   AND d.`scene` = m.`scene`
   AND d.`account_id` = m.`account_id`
SET d.`event_count` = m.`event_count`
WHERE d.`id` IN (SELECT `id` FROM `tmp_track_daily_keep`);

DELETE d
FROM `track_daily_report` d
LEFT JOIN `tmp_track_daily_keep` k ON d.`id` = k.`id`
WHERE k.`id` IS NULL;

DROP TEMPORARY TABLE `tmp_track_daily_keep`;
DROP TEMPORARY TABLE `tmp_track_daily_merge`;

ALTER TABLE `track_daily_report`
  ADD UNIQUE KEY `uk_track_daily` (`report_date`, `event_type`, `track_link_id`, `scene`, `account_id`);

SET @sql = (
  SELECT IF(
    COUNT(*) = 0,
    'ALTER TABLE `track_daily_report` ADD KEY `idx_track_daily_account` (`report_date`, `account_id`, `track_link_id`)',
    'SELECT 1'
  )
  FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'track_daily_report'
    AND INDEX_NAME = 'idx_track_daily_account'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;