-- Real schema captured from tvviewers @ 62.138.14.194 (read-only SHOW CREATE TABLE)
-- Captured: 2026-08-16 08:12 UTC by Claude Code, Task 1 of CLAUDE_CODE_HANDOFF.md

-- ================================================================
-- global_sports
-- ================================================================
CREATE TABLE `global_sports` (
  `global_id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `prog_type` varchar(50) DEFAULT NULL,
  `source_id` bigint unsigned DEFAULT NULL,
  `source_table` varchar(60) DEFAULT NULL,
  `information_source` varchar(100) DEFAULT NULL,
  `country` varchar(60) DEFAULT NULL,
  `channel` varchar(120) NOT NULL,
  `content_type` varchar(50) DEFAULT NULL,
  `week_day` varchar(10) DEFAULT NULL,
  `prog_date` date DEFAULT NULL,
  `program` varchar(255) DEFAULT NULL,
  `ama_000` decimal(12,4) DEFAULT NULL,
  `program_en` varchar(255) DEFAULT NULL,
  `alternative` varchar(255) DEFAULT NULL,
  `alternative_en` varchar(255) DEFAULT NULL,
  `comment` varchar(255) DEFAULT NULL,
  `nota` varchar(255) DEFAULT NULL,
  `secondary` varchar(255) DEFAULT NULL,
  `secondary_en` varchar(255) DEFAULT NULL,
  `program_secondary` varchar(512) DEFAULT NULL,
  `start_time` time DEFAULT NULL,
  `end_time` time DEFAULT NULL,
  `duration` time DEFAULT NULL,
  `viewership_minutes_000` decimal(16,3) DEFAULT NULL,
  `start_time_str` varchar(10) DEFAULT NULL,
  `end_time_str` varchar(10) DEFAULT NULL,
  `duration_str` varchar(10) DEFAULT NULL,
  `duration_minutes` int DEFAULT NULL,
  `sub_genre` varchar(100) DEFAULT NULL,
  `sub_genre_id` int DEFAULT NULL,
  `event_name` varchar(255) DEFAULT NULL,
  `sub_event` varchar(255) DEFAULT NULL,
  `parent_event` varchar(255) DEFAULT NULL,
  `event_context` varchar(255) DEFAULT NULL,
  `temp_event_name` varchar(255) DEFAULT NULL,
  `event_id` int DEFAULT NULL,
  `match_level` varchar(50) DEFAULT NULL,
  `sub_match_level` varchar(50) DEFAULT NULL,
  `match_level_ai` varchar(100) DEFAULT NULL,
  `telecast_type` varchar(50) DEFAULT NULL,
  `telecast_platform` varchar(100) DEFAULT NULL,
  `telecast_platform2` varchar(100) DEFAULT NULL,
  `pan_market` varchar(100) DEFAULT NULL,
  `metered_estimated` varchar(100) DEFAULT NULL,
  `broadcaster` varchar(150) DEFAULT NULL,
  `telecast_type_backup` varchar(100) DEFAULT NULL,
  `telecast_type_source` varchar(70) DEFAULT NULL,
  `event_gender` varchar(20) DEFAULT NULL,
  `event_agegroup` varchar(20) DEFAULT NULL,
  `sports_team1` varchar(100) DEFAULT NULL,
  `sports_team2` varchar(100) DEFAULT NULL,
  `sports_teams` varchar(255) DEFAULT NULL,
  `sports_teams_ai` varchar(255) DEFAULT NULL,
  `broadcast_day_date` date DEFAULT NULL,
  `broadcast_start_time` time DEFAULT NULL,
  `broadcast_end_time` time DEFAULT NULL,
  `level` int DEFAULT NULL,
  `genre_code` varchar(20) DEFAULT NULL,
  `genre_1` varchar(100) DEFAULT NULL,
  `genre_2` varchar(100) DEFAULT NULL,
  `genre_3` varchar(100) DEFAULT NULL,
  `agb_type` varchar(30) DEFAULT NULL,
  `ama_pct` decimal(6,2) DEFAULT NULL,
  `shr_pct` decimal(6,2) DEFAULT NULL,
  `ttv_ama_pct` decimal(6,2) DEFAULT NULL,
  `ttv_ama_000` decimal(12,4) DEFAULT NULL,
  `year_num` smallint unsigned DEFAULT NULL,
  `month_num` tinyint unsigned DEFAULT NULL,
  `week_num` tinyint unsigned DEFAULT NULL,
  `lower_start_time` tinyint unsigned DEFAULT NULL,
  `daypart` varchar(25) GENERATED ALWAYS AS ((case when (`lower_start_time` is null) then NULL when ((`lower_start_time` % 24) between 0 and 5) then _utf8mb4'OVERNIGHT' when ((`lower_start_time` % 24) between 6 and 11) then _utf8mb4'MORNING' when ((`lower_start_time` % 24) between 12 and 16) then _utf8mb4'AFTERNOON' when ((`lower_start_time` % 24) between 17 and 22) then _utf8mb4'PRIME' else _utf8mb4'LATE_NIGHT' end)) STORED,
  `lower_end_time` tinyint unsigned DEFAULT NULL,
  `event_name_2` varchar(200) DEFAULT NULL,
  `sub_genre_2` varchar(100) DEFAULT NULL,
  `telecast_type_2` varchar(50) DEFAULT NULL,
  `information_source_details` varchar(255) DEFAULT NULL,
  `info_1` varchar(200) DEFAULT NULL,
  `info_2` varchar(200) DEFAULT NULL,
  `info_3` varchar(200) DEFAULT NULL,
  `notes` varchar(500) DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`global_id`),
  KEY `idx_gs_channel` (`channel`),
  KEY `idx_gs_prog_date` (`prog_date`),
  KEY `idx_gs_program` (`program`),
  KEY `idx_gs_event_name` (`event_name`),
  KEY `idx_gs_sub_genre` (`sub_genre`),
  KEY `idx_gs_start_time` (`start_time`),
  KEY `idx_gs_country_date` (`country`,`prog_date`),
  KEY `idx_gs_country_channel_date` (`country`,`channel`,`prog_date`),
  KEY `idx_gs_country_event_date` (`country`,`event_name`,`prog_date`),
  KEY `idx_global_sports_source_table` (`source_table`),
  KEY `idx_global_sports_source_eventname` (`source_table`,`event_name`),
  KEY `idx_gs_telecast_type` (`telecast_type`)
) ENGINE=InnoDB AUTO_INCREMENT=12433233 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
;

-- ================================================================
-- global_sports_stats
-- ================================================================
CREATE TABLE `global_sports_stats` (
  `country` varchar(70) NOT NULL,
  `channel` varchar(120) NOT NULL,
  `sub_genre` varchar(70) DEFAULT NULL,
  `event_name` varchar(120) DEFAULT NULL,
  `telecast_type` varchar(40) DEFAULT NULL,
  `week_day` varchar(15) DEFAULT NULL,
  `lower_start_time` tinyint unsigned DEFAULT NULL,
  `avg_ama_000` decimal(12,4) DEFAULT NULL,
  `max_ama_000` decimal(12,4) DEFAULT NULL,
  `median_ama_000` decimal(12,4) DEFAULT NULL,
  `p95_ama_000` decimal(12,4) DEFAULT NULL,
  `low_ama_000` decimal(12,4) DEFAULT NULL,
  `shr_pct` decimal(8,3) DEFAULT NULL,
  `ama_pct` decimal(8,3) DEFAULT NULL,
  `ttv_ama_pct` decimal(8,3) DEFAULT NULL,
  `ttv_ama_000` decimal(12,4) DEFAULT NULL,
  `avg_daily_rch_000` decimal(12,4) DEFAULT NULL,
  `max_daily_rch_000` decimal(12,4) DEFAULT NULL,
  `median_daily_rch_000` decimal(12,4) DEFAULT NULL,
  `low_daily_rch_000` decimal(12,4) DEFAULT NULL,
  `row_count` int DEFAULT NULL,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `uq_country_stats` (`country`,`channel`,`sub_genre`,`event_name`,`telecast_type`,`week_day`,`lower_start_time`),
  KEY `idx_country_chan` (`country`,`channel`),
  KEY `idx_country_sg` (`country`,`sub_genre`),
  KEY `idx_main_lookup` (`country`,`sub_genre`,`event_name`,`telecast_type`,`week_day`,`lower_start_time`,`channel`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
;

-- ================================================================
-- gl_ratings
-- ================================================================
CREATE TABLE `gl_ratings` (
  `gl_id` int NOT NULL AUTO_INCREMENT,
  `gsiq_country` varchar(100) DEFAULT NULL,
  `gsiq_channel` varchar(100) DEFAULT NULL,
  `channel_group` varchar(150) DEFAULT NULL COMMENT 'Normalized channel group used for estimation, e.g. CBS.us, ParamountPlus.us, TUDN.us',
  `is_english` varchar(50) DEFAULT NULL,
  `standard_channel_name` varchar(100) DEFAULT NULL,
  `broadcaster` varchar(150) DEFAULT NULL,
  `telecast_platform` varchar(30) DEFAULT NULL,
  `telecast_platform_2` varchar(255) DEFAULT NULL,
  `gl_territory` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `gl_channel` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `epg_id` int DEFAULT NULL,
  `ti_total_day_share_bk` decimal(10,3) DEFAULT NULL,
  `ti_total_day_share` decimal(12,6) DEFAULT NULL,
  `channel_rating` decimal(12,6) DEFAULT NULL,
  `channel_rating_bk` decimal(12,6) DEFAULT NULL,
  `tvuniverse` decimal(12,2) DEFAULT NULL,
  `ti_prime_day_share` decimal(10,3) DEFAULT NULL,
  `low_ama_000` decimal(12,3) DEFAULT NULL,
  `mean_ama_000` decimal(12,3) DEFAULT NULL,
  `median_ama_000` decimal(12,3) DEFAULT NULL,
  `max_ama_000` decimal(12,3) DEFAULT NULL,
  `football_ama_000` decimal(12,3) DEFAULT NULL,
  `tennis_ama_000` decimal(12,3) DEFAULT NULL,
  `rugby_ama_000` decimal(12,3) DEFAULT NULL,
  `cricket_ama_000` decimal(12,3) DEFAULT NULL,
  `basketball_ama_000` decimal(12,3) DEFAULT NULL,
  `motorsports_ama_000` decimal(12,3) DEFAULT NULL,
  `athletics_ama_000` decimal(12,3) DEFAULT NULL,
  `america_football_ama_000` decimal(12,3) DEFAULT NULL,
  `gg_nonprime_mean_ama_000` decimal(12,3) DEFAULT NULL,
  `gg_nonprime_median_ama_000` decimal(12,3) DEFAULT NULL,
  `gg_nonprime_max_ama_000` decimal(12,3) DEFAULT NULL,
  `gg_prime_mean_ama_000` decimal(12,3) DEFAULT NULL,
  `gg_prime_median_ama_000` decimal(12,3) DEFAULT NULL,
  `gg_prime_max_ama_000` decimal(12,3) DEFAULT NULL,
  `is_hd_channel` int DEFAULT NULL,
  `hd_confidence` int DEFAULT NULL,
  `hd_info_source` varchar(100) DEFAULT NULL,
  `sd_share` decimal(10,5) DEFAULT NULL,
  `hd_share` decimal(10,5) DEFAULT NULL,
  `confidence_share` decimal(10,5) DEFAULT NULL,
  `prefer_gl_ratings_ama` varchar(30) DEFAULT NULL,
  `channel_genre` varchar(100) DEFAULT NULL,
  `channel_sub_genre` varchar(100) DEFAULT NULL,
  `channel_level` varchar(100) DEFAULT NULL,
  `real_id` varchar(50) DEFAULT NULL,
  `real_country` varchar(100) DEFAULT NULL,
  `real_channel` varchar(100) DEFAULT NULL,
  `parent_real_channel` varchar(255) DEFAULT NULL,
  `gs_map_country` varchar(100) DEFAULT NULL,
  `gs_map_channel` varchar(100) DEFAULT NULL,
  `gs_map_rating` decimal(12,6) DEFAULT NULL,
  `input_table_name` varchar(100) DEFAULT NULL,
  `gsiq_table` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `gsiq_duplicate` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `national_tv_viewers` int DEFAULT NULL,
  `cumulative_tv_viewers` int DEFAULT NULL,
  `ti_prime_day_viewers` int DEFAULT NULL,
  `ti_total_day_share_clone` decimal(10,3) DEFAULT NULL,
  `channel_weight` decimal(10,3) DEFAULT NULL,
  `ya_total_day_share` decimal(10,3) DEFAULT NULL,
  `ya_prime_time_share` decimal(10,3) DEFAULT NULL,
  `daily_reach_percent` decimal(10,3) DEFAULT NULL,
  `cumulative_reach_percent` decimal(10,3) DEFAULT NULL,
  `cumulative_definition` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `data_availability` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `status` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `genre` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `access_type` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `ti_td_viewers` int DEFAULT NULL,
  `daily_tv_viewers` int DEFAULT NULL,
  `channel_avghourly_reach` int DEFAULT NULL,
  `source_data` varchar(255) DEFAULT NULL,
  `ref_channel_id` int DEFAULT NULL,
  `notes` varchar(400) DEFAULT NULL,
  `delay_status` varchar(50) DEFAULT NULL,
  `delay_from_channel` varchar(150) DEFAULT NULL,
  `base_channel` varchar(200) DEFAULT NULL,
  `is_duplicate` varchar(50) DEFAULT NULL,
  `primary_duplicate_gl_id` int DEFAULT NULL,
  `country_01` varchar(100) DEFAULT NULL,
  `country_02` varchar(100) DEFAULT NULL,
  `country_03` varchar(100) DEFAULT NULL,
  `country_04` varchar(100) DEFAULT NULL,
  `country_05` varchar(100) DEFAULT NULL,
  `country_06` varchar(100) DEFAULT NULL,
  `country_07` varchar(100) DEFAULT NULL,
  `country_08` varchar(100) DEFAULT NULL,
  `country_09` varchar(100) DEFAULT NULL,
  `country_10` varchar(100) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`gl_id`),
  KEY `idx_gr_gsiq_country_channel` (`gsiq_country`,`gsiq_channel`),
  KEY `idx_gl_ratings_territory_channel` (`gl_territory`,`gl_channel`),
  KEY `idx_gl_ratings_map_country_genre` (`gs_map_country`,`genre`),
  KEY `idx_gl_ratings_genre` (`genre`),
  KEY `idx_gl_ratings_channel_level` (`channel_level`),
  KEY `idx_gl_ratings_access_type` (`access_type`)
) ENGINE=InnoDB AUTO_INCREMENT=60557 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
;

-- ================================================================
-- india_channel_subgenre_stats
-- ================================================================
CREATE TABLE `india_channel_subgenre_stats` (
  `unique_id` bigint NOT NULL AUTO_INCREMENT,
  `channel` varchar(255) NOT NULL,
  `sub_genre` varchar(255) NOT NULL,
  `max_avg_daily_rch_000` decimal(12,3) DEFAULT NULL,
  `low_avg_daily_rch_000` decimal(12,3) DEFAULT NULL,
  `avg_daily_rch_000` decimal(12,3) DEFAULT NULL,
  `max_avg_ama_000` decimal(12,3) DEFAULT NULL,
  `low_avg_ama_000` decimal(12,3) DEFAULT NULL,
  `avg_ama_000` decimal(12,3) DEFAULT NULL,
  PRIMARY KEY (`unique_id`),
  KEY `idx_channel_subgenre` (`channel`,`sub_genre`)
) ENGINE=InnoDB AUTO_INCREMENT=1641 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
;

-- ================================================================
-- uk_channel_subgenre_stats
-- ================================================================
CREATE TABLE `uk_channel_subgenre_stats` (
  `unique_id` bigint NOT NULL AUTO_INCREMENT,
  `channel` varchar(255) NOT NULL,
  `sub_genre` varchar(255) NOT NULL,
  `max_avg_daily_rch_000` decimal(12,3) DEFAULT NULL,
  `low_avg_daily_rch_000` decimal(12,3) DEFAULT NULL,
  `avg_daily_rch_000` decimal(12,3) DEFAULT NULL,
  `max_avg_ama_000` decimal(12,3) DEFAULT NULL,
  `low_avg_ama_000` decimal(12,3) DEFAULT NULL,
  `avg_ama_000` decimal(12,3) DEFAULT NULL,
  PRIMARY KEY (`unique_id`),
  KEY `idx_channel_subgenre` (`channel`,`sub_genre`)
) ENGINE=InnoDB AUTO_INCREMENT=1024 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
;

-- ================================================================
-- viewership_by_hour
-- ================================================================
CREATE TABLE `viewership_by_hour` (
  `hour_num` tinyint unsigned NOT NULL,
  `hour_general_percentage` decimal(10,2) DEFAULT NULL,
  `hour_sports_percentage` decimal(10,2) DEFAULT NULL,
  `notes` varchar(200) NOT NULL,
  PRIMARY KEY (`hour_num`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
;

-- ================================================================
-- tvuniverse
-- ================================================================
CREATE TABLE `tvuniverse` (
  `territory_id` int NOT NULL AUTO_INCREMENT,
  `territory` varchar(100) DEFAULT NULL,
  `territory_type` varchar(30) DEFAULT NULL,
  `region` varchar(255) DEFAULT NULL,
  `continent` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `world_region` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `world_sub_region` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `gdp_billion_usd` decimal(14,2) DEFAULT NULL,
  `priority_id` int unsigned DEFAULT NULL,
  `map_country` varchar(100) DEFAULT NULL,
  `map_country_nat` varchar(100) DEFAULT NULL,
  `map_country_tvuniverse` bigint DEFAULT NULL,
  `population` decimal(15,4) DEFAULT NULL,
  `total_individuals_2025` decimal(15,4) DEFAULT NULL,
  `tvuniverse_percentage` decimal(15,3) GENERATED ALWAYS AS ((case when ((`population` is not null) and (`population` <> 0) and (`total_individuals_2025` is not null)) then ((`total_individuals_2025` / `population`) * 100) else NULL end)) STORED,
  `missing_channel_weight` decimal(8,5) DEFAULT NULL COMMENT 'Fallback channel weight used when ti_total_day_share is unavailable',
  `country_session_hard_max_pct` decimal(8,5) DEFAULT NULL COMMENT 'Absolute country/session maximum as percentage of TV Universe',
  `country_day_hard_max_pct` decimal(8,5) DEFAULT NULL COMMENT 'Absolute country/day maximum as percentage of TV Universe',
  `total_individuals_2024` int DEFAULT NULL,
  `cricket` float DEFAULT NULL,
  `football` float DEFAULT NULL,
  `tennis` float DEFAULT NULL,
  `formula_1` float DEFAULT NULL,
  `athletics` varchar(255) DEFAULT NULL,
  `hockey` float DEFAULT NULL,
  `horse_racing` float DEFAULT NULL,
  `equestrian` float DEFAULT NULL,
  `combat_sports` float DEFAULT NULL,
  `rugby` float DEFAULT NULL,
  `cycling` float DEFAULT NULL,
  `baseball` float DEFAULT NULL,
  `boxing` float DEFAULT NULL,
  `wrestling` float DEFAULT NULL,
  `bull_fighting` float DEFAULT NULL,
  `kabaddi` float DEFAULT NULL,
  `esports` float DEFAULT NULL,
  `notes` varchar(400) DEFAULT NULL,
  `map_country_old` varchar(100) DEFAULT NULL,
  `total_individuals_agegroup` varchar(50) DEFAULT NULL,
  `total_individuals_old` int DEFAULT NULL,
  `total_individuals_2024_ratio` decimal(15,5) DEFAULT NULL,
  `young_adults_agegroup` varchar(50) DEFAULT NULL,
  `youngadults` int DEFAULT NULL,
  `total_day` varchar(100) DEFAULT NULL,
  `prime_time` varchar(100) DEFAULT NULL,
  `time_shifted_viewing` varchar(200) DEFAULT NULL,
  `sources` varchar(200) DEFAULT NULL,
  `population_ratio` decimal(15,5) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`territory_id`),
  UNIQUE KEY `unique_id` (`territory_id`)
) ENGINE=InnoDB AUTO_INCREMENT=290 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
;

-- ================================================================
-- map_country_map_channel
-- ================================================================
CREATE TABLE `map_country_map_channel` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `map_country` varchar(100) NOT NULL,
  `sub_genre` varchar(100) NOT NULL,
  `map_channel` varchar(150) NOT NULL,
  `map_priority` int NOT NULL DEFAULT '1',
  `map_rating` decimal(12,4) DEFAULT NULL,
  `source_type` varchar(40) NOT NULL DEFAULT 'auto' COMMENT 'auto/manual/verified',
  `source_note` varchar(255) DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `map_confidence_score` decimal(8,4) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_map_country_subgenre_priority` (`map_country`,`sub_genre`,`map_priority`),
  KEY `idx_country_sg_active` (`map_country`,`sub_genre`,`is_active`),
  KEY `idx_country_channel` (`map_country`,`map_channel`)
) ENGINE=InnoDB AUTO_INCREMENT=444 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
;

-- ================================================================
-- channel_match_ratings
-- ================================================================
CREATE TABLE `channel_match_ratings` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `country` varchar(100) NOT NULL,
  `channel` varchar(255) NOT NULL,
  `channel_weight` decimal(10,3) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_country_channel` (`country`,`channel`)
) ENGINE=InnoDB AUTO_INCREMENT=639 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
;

-- ================================================================
-- country_alias
-- ================================================================
CREATE TABLE `country_alias` (
  `country_alias_id` int unsigned NOT NULL AUTO_INCREMENT,
  `country` varchar(100) NOT NULL,
  `alias_country` varchar(100) NOT NULL,
  `confidence_score` decimal(5,2) NOT NULL DEFAULT '100.00',
  `notes` varchar(500) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`country_alias_id`),
  UNIQUE KEY `uq_country_alias_alias_country` (`alias_country`),
  KEY `idx_country_alias_country` (`country`)
) ENGINE=InnoDB AUTO_INCREMENT=321 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
;

-- ================================================================
-- pan_mapping
-- ================================================================
CREATE TABLE `pan_mapping` (
  `id` int NOT NULL AUTO_INCREMENT,
  `country_name` varchar(100) NOT NULL,
  `channel_name` varchar(150) NOT NULL,
  `input_table_name` varchar(150) NOT NULL,
  `continent` varchar(50) DEFAULT NULL,
  `world_region` varchar(50) DEFAULT NULL,
  `pan_channel_id` int DEFAULT NULL,
  `pan_channel_name` varchar(150) DEFAULT NULL,
  `pan_region` varchar(50) DEFAULT NULL,
  `verified` varchar(50) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_country_channel` (`country_name`,`channel_name`),
  KEY `idx_pan_channel` (`pan_channel_id`),
  KEY `idx_pan_region` (`pan_region`)
) ENGINE=InnoDB AUTO_INCREMENT=65536 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
;

-- ================================================================
-- event_country_interest_factor
-- ================================================================
CREATE TABLE `event_country_interest_factor` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `event_name` varchar(150) NOT NULL,
  `event_id` int unsigned DEFAULT NULL,
  `event_season` varchar(100) DEFAULT NULL,
  `country` varchar(100) NOT NULL,
  `event_country_interest_factor` decimal(4,2) NOT NULL DEFAULT '1.00' COMMENT 'Multiplier applied on top of previously computed value (1.20 = +20%)',
  `notes` varchar(255) DEFAULT NULL COMMENT 'Reason / context for applying this interest factor',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_event_country` (`event_name`,`country`)
) ENGINE=InnoDB AUTO_INCREMENT=23070 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
;

-- ================================================================
-- event_interest_factor
-- ================================================================
-- !! NOT AVAILABLE: ERROR 1146 (42S02) at line 1: Table 'tvviewers.event_interest_factor' doesn't exist

-- ================================================================
-- event_country_tvuniverse_factor
-- ================================================================
CREATE TABLE `event_country_tvuniverse_factor` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `event_name` varchar(150) NOT NULL,
  `event_id` int unsigned DEFAULT NULL,
  `event_season` varchar(100) DEFAULT NULL,
  `country` varchar(100) NOT NULL,
  `tv_universe_000` decimal(14,3) DEFAULT NULL COMMENT 'Country TV Universe in 000s. NULL means not available / not provided',
  `event_ama_000` decimal(14,3) DEFAULT NULL COMMENT 'Event country-level AMA in 000s. NULL means not available / not estimated',
  `event_country_tvuniverse_percentage` decimal(10,5) DEFAULT NULL COMMENT 'Event AMA_000 as percentage of country TV Universe. Example: 2.50000 means 2.5%. NULL means not calculated',
  `global_event_ama_000` decimal(14,3) DEFAULT NULL COMMENT 'Global event AMA in 000s across all countries. NULL means not available / not estimated',
  `country_perc_of_global_event_ama` decimal(10,5) DEFAULT NULL COMMENT 'Country AMA_000 as percentage of global event AMA_000. Example: 5.25000 means country contributes 5.25%. NULL means not calculated',
  `channel_1` varchar(150) DEFAULT NULL COMMENT 'Main / first channel or platform for this event-country',
  `channel_1_ama_000` decimal(14,3) DEFAULT NULL COMMENT 'AMA_000 for channel_1',
  `channel_2` varchar(150) DEFAULT NULL COMMENT 'Second channel or platform for this event-country',
  `channel_2_ama_000` decimal(14,3) DEFAULT NULL COMMENT 'AMA_000 for channel_2',
  `ott_1` varchar(150) DEFAULT NULL COMMENT 'Main / first OTT platform for this event-country',
  `ott_1_ama_000` decimal(14,3) DEFAULT NULL COMMENT 'AMA_000 for ott_1',
  `ott_2` varchar(150) DEFAULT NULL COMMENT 'Second OTT platform for this event-country',
  `ott_2_ama_000` decimal(14,3) DEFAULT NULL COMMENT 'AMA_000 for ott_2',
  `channel_3` varchar(150) DEFAULT NULL COMMENT 'Third channel or platform for this event-country',
  `channel_3_ama_000` decimal(14,3) DEFAULT NULL COMMENT 'AMA_000 for channel_3',
  `channel_4` varchar(150) DEFAULT NULL COMMENT 'Fourth channel or platform for this event-country',
  `channel_4_ama_000` decimal(14,3) DEFAULT NULL COMMENT 'AMA_000 for channel_4',
  `channel_5` varchar(150) DEFAULT NULL COMMENT 'Fifth channel or platform for this event-country',
  `channel_5_ama_000` decimal(14,3) DEFAULT NULL COMMENT 'AMA_000 for channel_5',
  `notes` varchar(255) DEFAULT NULL COMMENT 'Reason / source / context for this country-event value',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_event_country_season` (`event_name`,`event_season`,`country`),
  KEY `idx_event_name` (`event_name`),
  KEY `idx_event_id` (`event_id`),
  KEY `idx_country` (`country`),
  KEY `idx_event_country_tvuniverse_percentage` (`event_country_tvuniverse_percentage`),
  KEY `idx_country_perc_of_global_event_ama` (`country_perc_of_global_event_ama`)
) ENGINE=InnoDB AUTO_INCREMENT=172440 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
;

-- ================================================================
-- event_host_country_factor
-- ================================================================
CREATE TABLE `event_host_country_factor` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `event_id` int unsigned DEFAULT NULL,
  `event_name` varchar(255) NOT NULL,
  `season_year` int unsigned DEFAULT NULL,
  `host_country_name` varchar(100) NOT NULL,
  `match_date` date DEFAULT NULL,
  `host_country_factor` decimal(8,4) NOT NULL DEFAULT '1.1000',
  `confidence_score` decimal(5,4) NOT NULL DEFAULT '0.8000',
  `active_flag` tinyint(1) NOT NULL DEFAULT '1',
  `notes` varchar(500) DEFAULT NULL,
  `created_by` varchar(100) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_event_name` (`event_name`),
  KEY `idx_event_season` (`event_name`,`season_year`),
  KEY `idx_host_country` (`host_country_name`),
  KEY `idx_match_date` (`match_date`),
  KEY `idx_active_flag` (`active_flag`)
) ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
;

-- ================================================================
-- sports_team_weight
-- ================================================================
CREATE TABLE `sports_team_weight` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `event_name` varchar(255) NOT NULL,
  `event_id` int unsigned DEFAULT NULL,
  `event_season` varchar(100) DEFAULT NULL,
  `event_season_key` varchar(100) GENERATED ALWAYS AS (coalesce(nullif(trim(`event_season`),_utf8mb4''),_utf8mb4'NO_SEASON')) STORED,
  `team_name` varchar(150) NOT NULL,
  `team_country` varchar(150) DEFAULT NULL,
  `team_rank` int unsigned DEFAULT NULL,
  `ama_team_weight` decimal(5,2) DEFAULT NULL,
  `weight_band` varchar(50) DEFAULT NULL,
  `city` varchar(100) DEFAULT NULL,
  `state` varchar(100) DEFAULT NULL,
  `alias` varchar(500) DEFAULT NULL,
  `total_matches_played` int unsigned DEFAULT NULL,
  `total_won_matches` int unsigned DEFAULT NULL,
  `total_lost_matches` int unsigned DEFAULT NULL,
  `notes` text,
  `team_alias_1` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `team_alias_2` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `team_alias_3` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `team_alias_4` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `team_alias_5` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `team_alias_6` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `team_alias_7` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `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_sports_team_weight_event_team` (`event_id`,`team_name`),
  UNIQUE KEY `uq_sports_team_weight_event_season_team` (`event_name`,`event_season_key`,`team_name`),
  KEY `idx_event_id` (`event_id`),
  KEY `idx_event_name` (`event_name`),
  KEY `idx_team_name` (`team_name`),
  KEY `idx_team_rank` (`team_rank`)
) ENGINE=InnoDB AUTO_INCREMENT=30353 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
;

-- ================================================================
-- match_level_weights
-- ================================================================
CREATE TABLE `match_level_weights` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `match_level` varchar(50) NOT NULL,
  `weight` decimal(6,3) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_match_level` (`match_level`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
;

-- ================================================================
-- match_level_weight_by_event
-- ================================================================
CREATE TABLE `match_level_weight_by_event` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `event_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `season_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `sport` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `match_level` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `normalized_match_level` varchar(100) DEFAULT NULL,
  `match_level_weight` decimal(10,4) NOT NULL,
  `notes` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `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_event_season_sport_match_level` (`event_name`,`season_name`,`sport`,`match_level`),
  KEY `idx_event_name` (`event_name`),
  KEY `idx_match_level` (`match_level`),
  KEY `idx_sport` (`sport`)
) ENGINE=InnoDB AUTO_INCREMENT=2444 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
;

-- ================================================================
-- event_fallback_mapping
-- ================================================================
CREATE TABLE `event_fallback_mapping` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `primary_event_id` int unsigned DEFAULT NULL,
  `primary_event_name` varchar(255) NOT NULL,
  `primary_event_multiplier` decimal(8,4) NOT NULL DEFAULT '1.0000',
  `fallback_event_id_1` int unsigned DEFAULT NULL,
  `fallback_event_name_1` varchar(255) DEFAULT NULL,
  `fallback_event_multiplier_1` decimal(8,4) DEFAULT '1.0000',
  `fallback_event_id_2` int unsigned DEFAULT NULL,
  `fallback_event_name_2` varchar(255) DEFAULT NULL,
  `fallback_event_multiplier_2` decimal(8,4) DEFAULT '1.0000',
  `fallback_event_id_3` int unsigned DEFAULT NULL,
  `fallback_event_name_3` varchar(255) DEFAULT NULL,
  `fallback_event_multiplier_3` decimal(8,4) DEFAULT '1.0000',
  `fallback_event_id_4` int unsigned DEFAULT NULL,
  `fallback_event_name_4` varchar(255) DEFAULT NULL,
  `fallback_event_multiplier_4` decimal(8,4) DEFAULT '1.0000',
  `notes` varchar(500) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_event_fallback_primary_event_id` (`primary_event_id`),
  KEY `idx_event_fallback_primary_event_name` (`primary_event_name`),
  KEY `idx_event_fallback_event_id_1` (`fallback_event_id_1`),
  KEY `idx_event_fallback_event_id_2` (`fallback_event_id_2`),
  KEY `idx_event_fallback_event_id_3` (`fallback_event_id_3`),
  KEY `idx_event_fallback_event_id_4` (`fallback_event_id_4`),
  KEY `idx_event_fallback_name_1` (`fallback_event_name_1`),
  KEY `idx_event_fallback_name_2` (`fallback_event_name_2`),
  KEY `idx_event_fallback_name_3` (`fallback_event_name_3`),
  KEY `idx_event_fallback_name_4` (`fallback_event_name_4`)
) ENGINE=InnoDB AUTO_INCREMENT=2138 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
;

-- ================================================================
-- event_category_map
-- ================================================================
CREATE TABLE `event_category_map` (
  `id` int NOT NULL AUTO_INCREMENT,
  `priority_id` int DEFAULT NULL,
  `event_name` varchar(255) DEFAULT NULL,
  `standard_event_name` varchar(150) DEFAULT NULL,
  `parent_event_name` varchar(150) DEFAULT NULL,
  `sub_genre` varchar(100) DEFAULT NULL,
  `genre_id` int DEFAULT NULL,
  `weight` decimal(5,2) DEFAULT NULL,
  `global_linear_mean_ama_000` decimal(12,3) DEFAULT NULL,
  `global_linear_median_ama_000` decimal(12,3) DEFAULT NULL,
  `global_linear_max_ama_000` decimal(12,3) DEFAULT NULL,
  `global_ott_mean_ama_000` decimal(12,3) DEFAULT NULL,
  `global_ott_median_ama_000` decimal(12,3) DEFAULT NULL,
  `global_ott_max_ama_000` decimal(12,3) DEFAULT NULL,
  `duration_minutes` int DEFAULT NULL,
  `uk_event_name` varchar(255) DEFAULT NULL,
  `uk_sub_genre` varchar(255) DEFAULT NULL,
  `india_event_name` varchar(255) DEFAULT NULL,
  `india_sub_genre` varchar(255) DEFAULT NULL,
  `country` varchar(100) DEFAULT NULL,
  `match_format` varchar(100) DEFAULT NULL,
  `matches_count` varchar(250) DEFAULT NULL,
  `teams_count` varchar(250) DEFAULT NULL,
  `traditional_tv_viewership` int DEFAULT NULL,
  `ott_viewership` int DEFAULT NULL,
  `organizer` varchar(255) DEFAULT NULL,
  `frequency` varchar(50) DEFAULT NULL,
  `event_level` varchar(255) DEFAULT NULL,
  `duplicate_status` varchar(30) DEFAULT NULL,
  `duplicate_id` int unsigned DEFAULT NULL,
  `key_interest_countries` varchar(255) DEFAULT NULL,
  `China` decimal(12,2) DEFAULT NULL,
  `India` decimal(12,2) DEFAULT NULL,
  `United States` decimal(12,2) DEFAULT NULL,
  `Indonesia` decimal(12,2) DEFAULT NULL,
  `Pakistan` decimal(12,2) DEFAULT NULL,
  `Brazil` decimal(12,2) DEFAULT NULL,
  `Nigeria` decimal(12,2) DEFAULT NULL,
  `Bangladesh` decimal(12,2) DEFAULT NULL,
  `Russia` decimal(12,2) DEFAULT NULL,
  `Mexico` decimal(12,2) DEFAULT NULL,
  `Japan` decimal(12,2) DEFAULT NULL,
  `Egypt` decimal(12,2) DEFAULT NULL,
  `Philippines` decimal(12,2) DEFAULT NULL,
  `DR Congo` decimal(12,2) DEFAULT NULL,
  `Vietnam` decimal(12,2) DEFAULT NULL,
  `Iran` decimal(12,2) DEFAULT NULL,
  `Turkey` decimal(12,2) DEFAULT NULL,
  `Germany` decimal(12,2) DEFAULT NULL,
  `Thailand` decimal(12,2) DEFAULT NULL,
  `United Kingdom` decimal(12,2) DEFAULT NULL,
  `France` decimal(12,2) DEFAULT NULL,
  `Tanzania` decimal(12,2) DEFAULT NULL,
  `South Africa` decimal(12,2) DEFAULT NULL,
  `Italy` decimal(12,2) DEFAULT NULL,
  `Myanmar` decimal(12,2) DEFAULT NULL,
  `Kenya` decimal(12,2) DEFAULT NULL,
  `South Korea` decimal(12,2) DEFAULT NULL,
  `Colombia` decimal(12,2) DEFAULT NULL,
  `Spain` decimal(12,2) DEFAULT NULL,
  `Uganda` decimal(12,2) DEFAULT NULL,
  `Argentina` decimal(12,2) DEFAULT NULL,
  `Algeria` decimal(12,2) DEFAULT NULL,
  `Sudan` decimal(12,2) DEFAULT NULL,
  `Ukraine` decimal(12,2) DEFAULT NULL,
  `Iraq` decimal(12,2) DEFAULT NULL,
  `Afghanistan` decimal(12,2) DEFAULT NULL,
  `Poland` decimal(12,2) DEFAULT NULL,
  `Canada` decimal(12,2) DEFAULT NULL,
  `Morocco` decimal(12,2) DEFAULT NULL,
  `Uzbekistan` decimal(12,2) DEFAULT NULL,
  `Malaysia` decimal(12,2) DEFAULT NULL,
  `Mozambique` decimal(12,2) DEFAULT NULL,
  `Ghana` decimal(12,2) DEFAULT NULL,
  `Peru` decimal(12,2) DEFAULT NULL,
  `Saudi Arabia` decimal(12,2) DEFAULT NULL,
  `Madagascar` decimal(12,2) DEFAULT NULL,
  `Côte d'Ivoire` decimal(12,2) DEFAULT NULL,
  `Cameroon` decimal(12,2) DEFAULT NULL,
  `Nepal` decimal(12,2) DEFAULT NULL,
  `Venezuela` decimal(12,2) DEFAULT NULL,
  `Australia` decimal(12,2) DEFAULT NULL,
  `North Korea` decimal(12,2) DEFAULT NULL,
  `Niger` decimal(12,2) DEFAULT NULL,
  `Mali` decimal(12,2) DEFAULT NULL,
  `Syria` decimal(12,2) DEFAULT NULL,
  `Taiwan` decimal(12,2) DEFAULT NULL,
  `Burkina Faso` decimal(12,2) DEFAULT NULL,
  `Sri Lanka` decimal(12,2) DEFAULT NULL,
  `Malawi` decimal(12,2) DEFAULT NULL,
  `Zambia` decimal(12,2) DEFAULT NULL,
  `Kazakhstan` decimal(12,2) DEFAULT NULL,
  `Chile` decimal(12,2) DEFAULT NULL,
  `Chad` decimal(12,2) DEFAULT NULL,
  `Romania` decimal(12,2) DEFAULT NULL,
  `Somalia` decimal(12,2) DEFAULT NULL,
  `Guatemala` decimal(12,2) DEFAULT NULL,
  `Netherlands` decimal(12,2) DEFAULT NULL,
  `Ecuador` decimal(12,2) DEFAULT NULL,
  `Cambodia` decimal(12,2) DEFAULT NULL,
  `Senegal` decimal(12,2) DEFAULT NULL,
  `Zimbabwe` decimal(12,2) DEFAULT NULL,
  `Guinea` decimal(12,2) DEFAULT NULL,
  `Rwanda` decimal(12,2) DEFAULT NULL,
  `Benin` decimal(12,2) DEFAULT NULL,
  `Burundi` decimal(12,2) DEFAULT NULL,
  `Tunisia` decimal(12,2) DEFAULT NULL,
  `Bolivia` decimal(12,2) DEFAULT NULL,
  `Belgium` decimal(12,2) DEFAULT NULL,
  `Haiti` decimal(12,2) DEFAULT NULL,
  `Cuba` decimal(12,2) DEFAULT NULL,
  `South Sudan` decimal(12,2) DEFAULT NULL,
  `Dominican Republic` decimal(12,2) DEFAULT NULL,
  `Czech Republic` decimal(12,2) DEFAULT NULL,
  `Greece` decimal(12,2) DEFAULT NULL,
  `Portugal` decimal(12,2) DEFAULT NULL,
  `Jordan` decimal(12,2) DEFAULT NULL,
  `Azerbaijan` decimal(12,2) DEFAULT NULL,
  `Sweden` decimal(12,2) DEFAULT NULL,
  `Honduras` decimal(12,2) DEFAULT NULL,
  `United Arab Emirates` decimal(12,2) DEFAULT NULL,
  `Hungary` decimal(12,2) DEFAULT NULL,
  `Belarus` decimal(12,2) DEFAULT NULL,
  `Austria` decimal(12,2) DEFAULT NULL,
  `Switzerland` decimal(12,2) DEFAULT NULL,
  `Croatia` decimal(12,2) DEFAULT NULL,
  `Oman` decimal(12,2) DEFAULT NULL,
  `Liberia` decimal(12,2) DEFAULT NULL,
  `Panama` decimal(12,2) DEFAULT NULL,
  `Kuwait` decimal(12,2) DEFAULT NULL,
  `Mauritania` decimal(12,2) DEFAULT NULL,
  `Georgia` decimal(12,2) DEFAULT NULL,
  `Moldova` decimal(12,2) DEFAULT NULL,
  `Uruguay` decimal(12,2) DEFAULT NULL,
  `Bosnia and Herzegovina` decimal(12,2) DEFAULT NULL,
  `Mongolia` decimal(12,2) DEFAULT NULL,
  `Armenia` decimal(12,2) DEFAULT NULL,
  `Albania` decimal(12,2) DEFAULT NULL,
  `Jamaica` decimal(12,2) DEFAULT NULL,
  `Lithuania` decimal(12,2) DEFAULT NULL,
  `Namibia` decimal(12,2) DEFAULT NULL,
  `Gambia` decimal(12,2) DEFAULT NULL,
  `Botswana` decimal(12,2) DEFAULT NULL,
  `Lesotho` decimal(12,2) DEFAULT NULL,
  `Qatar` decimal(12,2) DEFAULT NULL,
  `Slovenia` decimal(12,2) DEFAULT NULL,
  `North Macedonia` decimal(12,2) DEFAULT NULL,
  `Latvia` decimal(12,2) DEFAULT NULL,
  `Kosovo` decimal(12,2) DEFAULT NULL,
  `Bahrain` decimal(12,2) DEFAULT NULL,
  `Trinidad and Tobago` decimal(12,2) DEFAULT NULL,
  `Estonia` decimal(12,2) DEFAULT NULL,
  `Timor-Leste` decimal(12,2) DEFAULT NULL,
  `Mauritius` decimal(12,2) DEFAULT NULL,
  `Cyprus` decimal(12,2) DEFAULT NULL,
  `Republic of Moldova` decimal(12,2) DEFAULT NULL,
  `Guinea-Bissau` decimal(12,2) DEFAULT NULL,
  `Gabon` decimal(12,2) DEFAULT NULL,
  `Comoros` decimal(12,2) DEFAULT NULL,
  `Solomon Islands` decimal(12,2) DEFAULT NULL,
  `Eswatini` decimal(12,2) DEFAULT NULL,
  `Equatorial Guinea` decimal(12,2) DEFAULT NULL,
  `Djibouti` decimal(12,2) DEFAULT NULL,
  `Réunion` decimal(12,2) DEFAULT NULL,
  `Fiji` decimal(12,2) DEFAULT NULL,
  `Maldives` decimal(12,2) DEFAULT NULL,
  `Cape Verde` decimal(12,2) DEFAULT NULL,
  `description` text,
  PRIMARY KEY (`id`),
  KEY `fk_ecm_genre` (`genre_id`),
  KEY `idx_event_category_map_event_sports` (`event_name`,`sub_genre`),
  CONSTRAINT `fk_ecm_genre` FOREIGN KEY (`genre_id`) REFERENCES `global_genre` (`global_genre_id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2385 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
;

-- ================================================================
-- fixture_interest
-- ================================================================
CREATE TABLE `fixture_interest` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `event_name` varchar(150) NOT NULL,
  `season_name` varchar(100) DEFAULT NULL,
  `country` varchar(100) DEFAULT NULL,
  `home_team` varchar(150) DEFAULT NULL,
  `away_team` varchar(150) DEFAULT NULL,
  `fixture_key` varchar(320) DEFAULT NULL,
  `match_stage` varchar(100) DEFAULT NULL,
  `fixture_interest_factor` decimal(8,4) NOT NULL DEFAULT '1.0000',
  `source_type` varchar(50) DEFAULT NULL,
  `notes` varchar(500) DEFAULT NULL,
  `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_fixture_interest` (`event_name`,`season_name`,`country`,`fixture_key`),
  KEY `idx_event_name` (`event_name`),
  KEY `idx_country` (`country`),
  KEY `idx_fixture_key` (`fixture_key`),
  KEY `idx_home_team` (`home_team`),
  KEY `idx_away_team` (`away_team`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
;

-- ================================================================
-- ott_multipliers
-- ================================================================
CREATE TABLE `ott_multipliers` (
  `ott_id` int NOT NULL AUTO_INCREMENT,
  `country` varchar(100) NOT NULL,
  `tv_to_ott_multiplier` decimal(10,4) NOT NULL,
  `tv_to_ott_multiplier_backup` decimal(10,4) DEFAULT NULL,
  `ooh_multiplier` decimal(8,4) DEFAULT NULL,
  `ooh_multiplier_backup` decimal(10,4) DEFAULT NULL,
  `household_min_size` decimal(5,2) DEFAULT NULL,
  `household_max_size` decimal(5,2) DEFAULT NULL,
  PRIMARY KEY (`ott_id`)
) ENGINE=InnoDB AUTO_INCREMENT=280 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
;

-- ================================================================
-- global_sports_ama_caps
-- ================================================================
CREATE TABLE `global_sports_ama_caps` (
  `country` varchar(70) NOT NULL,
  `channel` varchar(120) NOT NULL,
  `sub_genre` varchar(70) DEFAULT NULL,
  `event_name` varchar(120) DEFAULT NULL,
  `telecast_type` varchar(40) DEFAULT NULL,
  `max_ama_000` decimal(12,4) DEFAULT NULL,
  `median_ama_000` decimal(12,4) DEFAULT NULL,
  `avg_ama_000` decimal(12,4) DEFAULT NULL,
  `ttv_ama_000` decimal(12,4) DEFAULT NULL,
  `row_count` int DEFAULT NULL,
  `cap_multiplier` decimal(6,3) NOT NULL,
  `cap_ama_000` decimal(12,4) DEFAULT NULL,
  `cap_rule` varchar(120) DEFAULT NULL,
  `source_grain` varchar(80) NOT NULL,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `uq_ama_caps` (`country`,`channel`,`sub_genre`,`event_name`,`telecast_type`,`source_grain`),
  KEY `idx_caps_lookup_1` (`country`,`channel`,`event_name`,`telecast_type`),
  KEY `idx_caps_lookup_2` (`country`,`channel`,`sub_genre`,`telecast_type`),
  KEY `idx_caps_lookup_3` (`country`,`channel`,`telecast_type`),
  KEY `idx_caps_lookup_4` (`country`,`channel`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
;

-- ================================================================
-- country_genre_demographics
-- ================================================================
CREATE TABLE `country_genre_demographics` (
  `demographics_id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `country` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `genre_id` int DEFAULT NULL,
  `genre` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `ama_male_percentage` decimal(10,5) DEFAULT NULL,
  `ama_female_percentage` decimal(10,5) DEFAULT NULL,
  `ama_a4to15_percentage` decimal(10,5) DEFAULT NULL,
  `ama_a16to24_percentage` decimal(10,5) DEFAULT NULL,
  `ama_a25to34_percentage` decimal(10,5) DEFAULT NULL,
  `ama_a35to44_percentage` decimal(10,5) DEFAULT NULL,
  `ama_a45to54_percentage` decimal(10,5) DEFAULT NULL,
  `ama_a55to64_percentage` decimal(10,5) DEFAULT NULL,
  `ama_65_plus_percentage` decimal(10,5) DEFAULT NULL,
  `reach_male_percentage` decimal(10,5) DEFAULT NULL,
  `reach_female_percentage` decimal(10,5) DEFAULT NULL,
  `reach_a4to15_percentage` decimal(10,5) DEFAULT NULL,
  `reach_a16to24_percentage` decimal(10,5) DEFAULT NULL,
  `reach_a25to34_percentage` decimal(10,5) DEFAULT NULL,
  `reach_a35to44_percentage` decimal(10,5) DEFAULT NULL,
  `reach_a45to54_percentage` decimal(10,5) DEFAULT NULL,
  `reach_a55to64_percentage` decimal(10,5) DEFAULT NULL,
  `reach_65plus_percentage` decimal(10,5) DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `source_info` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`demographics_id`),
  KEY `idx_country` (`country`),
  KEY `idx_genre` (`genre`),
  KEY `idx_genre_id` (`genre_id`)
) ENGINE=InnoDB AUTO_INCREMENT=13998 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
;

