-- sort MySQL migration v47: central hourly statistics history
-- Non-destructive one-time migration. Do not re-run after schema_check reports the columns/table present.

ALTER TABLE `ig_media`
    ADD COLUMN `views_available` tinyint(1) NOT NULL DEFAULT 0 COMMENT '1=조회수 제공, 0=비공개/미제공' AFTER `views`,
    ADD COLUMN `stat_sort_by` varchar(32) NOT NULL DEFAULT '' AFTER `reposts`,
    ADD COLUMN `stat_sort_value` bigint(20) unsigned NOT NULL DEFAULT 0 AFTER `stat_sort_by`,
    ADD COLUMN `stat_observed_at_utc` datetime DEFAULT NULL AFTER `stat_sort_value`,
    ADD COLUMN `stat_hour_bucket_utc` datetime DEFAULT NULL AFTER `stat_observed_at_utc`,
    ADD INDEX `idx_stat_account_hour` (`ig_account_id`, `stat_hour_bucket_utc`, `id`);

CREATE TABLE IF NOT EXISTS `ig_media_stat_hour` (
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `collector_id` varchar(80) NOT NULL DEFAULT '',
    `shortcode` varchar(64) NOT NULL,
    `account_name` varchar(255) NOT NULL DEFAULT '',
    `ig_account_id` varchar(120) NOT NULL DEFAULT '',
    `hour_bucket_utc` datetime NOT NULL,
    `observed_at_utc` datetime NOT NULL,
    `views` bigint(20) unsigned NOT NULL DEFAULT 0,
    `views_available` tinyint(1) NOT NULL DEFAULT 0,
    `likes` bigint(20) unsigned NOT NULL DEFAULT 0,
    `comments` bigint(20) unsigned NOT NULL DEFAULT 0,
    `reposts` bigint(20) unsigned NOT NULL DEFAULT 0,
    `sort_by` varchar(32) NOT NULL DEFAULT '',
    `sort_value` bigint(20) unsigned NOT NULL DEFAULT 0,
    `sample_count` int(10) unsigned NOT NULL DEFAULT 1,
    `created_at` datetime NOT NULL DEFAULT current_timestamp(),
    `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_collector_shortcode_hour` (`collector_id`, `shortcode`, `hour_bucket_utc`),
    KEY `idx_account_hour` (`ig_account_id`, `hour_bucket_utc`, `shortcode`),
    KEY `idx_shortcode_hour` (`shortcode`, `hour_bucket_utc`),
    KEY `idx_hour_views` (`hour_bucket_utc`, `views`, `id`),
    KEY `idx_hour_likes` (`hour_bucket_utc`, `likes`, `id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
