CREATE TABLE IF NOT EXISTS dashboard_cdc_checkpoint (
  service_name varchar(64) NOT NULL,
  binlog_file varchar(255) NOT NULL,
  binlog_pos int unsigned NOT NULL,
  updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (service_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS dashboard_cdc_event_dedup (
  event_key varchar(255) NOT NULL,
  source varchar(128) NOT NULL,
  created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (event_key),
  KEY idx_dashboard_cdc_event_dedup_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS dashboard_cdc_user_country (
  user_id bigint NOT NULL,
  sys_origin varchar(50) NOT NULL,
  country_code varchar(30) NOT NULL,
  country_name varchar(100) NOT NULL,
  is_deleted tinyint(1) NOT NULL DEFAULT 0,
  user_created_at datetime NULL,
  user_updated_at datetime NULL,
  updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (user_id),
  KEY idx_dashboard_cdc_user_country_origin_country (sys_origin, country_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS country_dashboard_daily_user_metric (
  id bigint NOT NULL AUTO_INCREMENT,
  stat_date date NOT NULL,
  sys_origin varchar(50) NOT NULL,
  country_code varchar(64) NOT NULL,
  country_name varchar(128) NOT NULL,
  user_id bigint NOT NULL,
  metric_type varchar(32) NOT NULL,
  created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_daily_user_metric (stat_date, sys_origin, country_code, user_id, metric_type),
  KEY idx_daily_user_metric (sys_origin, user_id, metric_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS country_dashboard_recharge_detail (
  id bigint NOT NULL AUTO_INCREMENT,
  source_type varchar(32) NOT NULL,
  record_id varchar(128) NOT NULL,
  source_channel varchar(64) NOT NULL DEFAULT '',
  sys_origin varchar(50) NOT NULL,
  user_id bigint NOT NULL,
  country_code varchar(64) NOT NULL DEFAULT 'UNKNOWN',
  country_name varchar(128) NOT NULL DEFAULT 'UNKNOWN',
  amount decimal(20,2) NOT NULL DEFAULT 0,
  coin_quantity decimal(20,2) NULL,
  remark varchar(512) NOT NULL DEFAULT '',
  create_time datetime NOT NULL,
  update_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_recharge_source_record (source_type, record_id),
  KEY idx_recharge_query (sys_origin, create_time, country_code),
  KEY idx_recharge_user (user_id, create_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;