multi-platform-media-manage/database/migrations/restore_user_day_statistics_full.sql

-- Restore full schema for user_day_statistics (idempotent-ish)
-- Date: 2026-01-28
--
-- Notes:
-- - This script avoids TypeORM "synchronize" surprises by ensuring columns exist.
-- - MySQL 8.0 does NOT support ADD COLUMN IF NOT EXISTS reliably across all syntaxes,
--   so we use INFORMATION_SCHEMA checks + dynamic SQL.
-- - It will try to add an FK to platform_accounts(id). If there are orphan rows, it will NOT add FK.

USE media_manager;

SET @db = DATABASE();
SET @tbl = 'user_day_statistics';

-- Helper: add column if missing
-- play_count
SET @col = 'play_count';
SET @sql = (
  SELECT IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME=@tbl AND COLUMN_NAME=@col) > 0,
    'SELECT 1',
    'ALTER TABLE user_day_statistics ADD COLUMN play_count INT NOT NULL DEFAULT 0 AFTER works_count'
  )
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- comment_count
SET @col = 'comment_count';
SET @sql = (
  SELECT IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME=@tbl AND COLUMN_NAME=@col) > 0,
    'SELECT 1',
    'ALTER TABLE user_day_statistics ADD COLUMN comment_count INT NOT NULL DEFAULT 0 AFTER play_count'
  )
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- fans_increase
SET @col = 'fans_increase';
SET @sql = (
  SELECT IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME=@tbl AND COLUMN_NAME=@col) > 0,
    'SELECT 1',
    'ALTER TABLE user_day_statistics ADD COLUMN fans_increase INT NOT NULL DEFAULT 0 AFTER comment_count'
  )
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- like_count
SET @col = 'like_count';
SET @sql = (
  SELECT IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME=@tbl AND COLUMN_NAME=@col) > 0,
    'SELECT 1',
    'ALTER TABLE user_day_statistics ADD COLUMN like_count INT NOT NULL DEFAULT 0 AFTER fans_increase'
  )
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- share_count
SET @col = 'share_count';
SET @sql = (
  SELECT IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME=@tbl AND COLUMN_NAME=@col) > 0,
    'SELECT 1',
    'ALTER TABLE user_day_statistics ADD COLUMN share_count INT NOT NULL DEFAULT 0 AFTER like_count'
  )
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- collect_count
SET @col = 'collect_count';
SET @sql = (
  SELECT IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME=@tbl AND COLUMN_NAME=@col) > 0,
    'SELECT 1',
    'ALTER TABLE user_day_statistics ADD COLUMN collect_count INT NOT NULL DEFAULT 0 AFTER share_count'
  )
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- cover_click_rate (string)
SET @col = 'cover_click_rate';
SET @sql = (
  SELECT IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME=@tbl AND COLUMN_NAME=@col) > 0,
    'ALTER TABLE user_day_statistics MODIFY COLUMN cover_click_rate VARCHAR(50) NOT NULL DEFAULT ''0''',
    'ALTER TABLE user_day_statistics ADD COLUMN cover_click_rate VARCHAR(50) NOT NULL DEFAULT ''0'' AFTER collect_count'
  )
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- avg_watch_duration (string)
SET @col = 'avg_watch_duration';
SET @sql = (
  SELECT IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME=@tbl AND COLUMN_NAME=@col) > 0,
    'ALTER TABLE user_day_statistics MODIFY COLUMN avg_watch_duration VARCHAR(50) NOT NULL DEFAULT ''0''',
    'ALTER TABLE user_day_statistics ADD COLUMN avg_watch_duration VARCHAR(50) NOT NULL DEFAULT ''0'' AFTER cover_click_rate'
  )
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- total_watch_duration (string)
SET @col = 'total_watch_duration';
SET @sql = (
  SELECT IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME=@tbl AND COLUMN_NAME=@col) > 0,
    'ALTER TABLE user_day_statistics MODIFY COLUMN total_watch_duration VARCHAR(50) NOT NULL DEFAULT ''0''',
    'ALTER TABLE user_day_statistics ADD COLUMN total_watch_duration VARCHAR(50) NOT NULL DEFAULT ''0'' AFTER avg_watch_duration'
  )
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- completion_rate (string)
SET @col = 'completion_rate';
SET @sql = (
  SELECT IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME=@tbl AND COLUMN_NAME=@col) > 0,
    'ALTER TABLE user_day_statistics MODIFY COLUMN completion_rate VARCHAR(50) NOT NULL DEFAULT ''0''',
    'ALTER TABLE user_day_statistics ADD COLUMN completion_rate VARCHAR(50) NOT NULL DEFAULT ''0'' AFTER total_watch_duration'
  )
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Index on record_date
SET @idx = 'idx_record_date';
SET @sql = (
  SELECT IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA=@db AND TABLE_NAME=@tbl AND INDEX_NAME=@idx) > 0,
    'SELECT 1',
    'ALTER TABLE user_day_statistics ADD INDEX idx_record_date (record_date)'
  )
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Foreign key to platform_accounts(id) if possible (no orphans)
SET @fk = 'fk_user_day_statistics_account';
SET @orphans = (
  SELECT COUNT(*)
  FROM user_day_statistics uds
  LEFT JOIN platform_accounts pa ON pa.id = uds.account_id
  WHERE pa.id IS NULL
);

SET @sql = (
  SELECT IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_SCHEMA=@db AND TABLE_NAME=@tbl AND CONSTRAINT_NAME=@fk) > 0,
    'SELECT 1',
    IF(@orphans > 0,
      'SELECT ''Skip adding FK fk_user_day_statistics_account: orphan rows exist'' AS warning',
      'ALTER TABLE user_day_statistics ADD CONSTRAINT fk_user_day_statistics_account FOREIGN KEY (account_id) REFERENCES platform_accounts(id) ON DELETE CASCADE'
    )
  )
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;