-- Holiday statistics module. Run against the traffic_audit database.
CREATE TABLE IF NOT EXISTS holiday_calendar (
  id BIGINT NOT NULL AUTO_INCREMENT,
  year INT NOT NULL,
  holiday_type VARCHAR(32) NOT NULL,
  holiday_date DATE NOT NULL,
  day_index INT NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_holiday_calendar_seq (year, holiday_type, day_index),
  UNIQUE KEY uk_holiday_calendar_date (year, holiday_type, holiday_date),
  KEY idx_holiday_calendar_date (holiday_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS holiday_passenger_flow (
  id BIGINT NOT NULL AUTO_INCREMENT,
  year INT NOT NULL,
  flow_date DATE NOT NULL,
  transport_mode VARCHAR(32) NOT NULL,
  passenger_count DECIMAL(30,15) NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_holiday_passenger (year, flow_date, transport_mode),
  KEY idx_holiday_passenger_date (flow_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS holiday_vehicle_flow (
  id BIGINT NOT NULL AUTO_INCREMENT,
  year INT NOT NULL,
  flow_date DATE NOT NULL,
  road_category VARCHAR(32) NOT NULL,
  vehicle_type VARCHAR(32) NOT NULL,
  vehicle_count DECIMAL(30,15) NOT NULL,
  comparison_base DECIMAL(30,15) NULL COMMENT '普通国省道环比基期（万辆次，仅当年使用）',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_holiday_vehicle (year, flow_date, road_category, vehicle_type),
  KEY idx_holiday_vehicle_date (flow_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS holiday_passenger_coefficient (
  id BIGINT NOT NULL AUTO_INCREMENT,
  year INT NOT NULL,
  date_category VARCHAR(32) NOT NULL,
  coefficient DECIMAL(30,15) NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_holiday_coefficient (year, date_category)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;