-- Add the historical national/provincial road adjustment table.
-- Run against the traffic_audit database on upgrade.
CREATE TABLE IF NOT EXISTS holiday_vehicle_adjustment (
  id BIGINT NOT NULL AUTO_INCREMENT,
  year INT NOT NULL COMMENT 'year',
  flow_date DATE NOT NULL COMMENT 'holiday date',
  adjusted_value DECIMAL(30,15) NOT NULL COMMENT 'adjusted national/provincial vehicle flow (10k vehicle trips)',
  reason VARCHAR(500) NOT NULL COMMENT 'adjustment reason',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_holiday_vehicle_adjustment (year, flow_date),
  KEY idx_holiday_vehicle_adjustment_date (flow_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='historical national/provincial vehicle flow adjustments';