-- Observation station daily survey data (raw retention for future analysis).
-- Run against the traffic_audit database.
CREATE TABLE IF NOT EXISTS observation_station_flow (
  id BIGINT NOT NULL AUTO_INCREMENT,
  obs_date DATE NOT NULL COMMENT 'Observation date',
  station_code VARCHAR(64) NOT NULL COMMENT 'Station code',
  station_name VARCHAR(128) DEFAULT NULL COMMENT 'Station name',
  mileage DECIMAL(30,15) DEFAULT NULL COMMENT 'Observed mileage (km)',
  city_name VARCHAR(64) DEFAULT NULL COMMENT 'City name',
  county_name VARCHAR(64) DEFAULT NULL COMMENT 'County name',
  direction VARCHAR(32) NOT NULL DEFAULT '' COMMENT 'Travel direction',
  small_medium_passenger DECIMAL(30,15) DEFAULT NULL COMMENT 'Small/medium passenger cars',
  large_passenger DECIMAL(30,15) DEFAULT NULL COMMENT 'Large passenger cars',
  small_truck DECIMAL(30,15) DEFAULT NULL COMMENT 'Small trucks',
  medium_truck DECIMAL(30,15) DEFAULT NULL COMMENT 'Medium trucks',
  large_truck DECIMAL(30,15) DEFAULT NULL COMMENT 'Large trucks',
  extra_large_truck DECIMAL(30,15) DEFAULT NULL COMMENT 'Extra large trucks',
  container_truck DECIMAL(30,15) DEFAULT NULL COMMENT 'Container trucks',
  motorcycle DECIMAL(30,15) DEFAULT NULL COMMENT 'Motorcycles',
  tractor DECIMAL(30,15) DEFAULT NULL COMMENT 'Tractors',
  passenger_total DECIMAL(30,15) DEFAULT NULL COMMENT 'Total passenger vehicles',
  truck_total DECIMAL(30,15) DEFAULT NULL COMMENT 'Total trucks',
  car_total DECIMAL(30,15) DEFAULT NULL COMMENT 'Total automobiles',
  motor_total DECIMAL(30,15) DEFAULT NULL COMMENT 'Total motor vehicles',
  passenger_equivalent DECIMAL(30,15) DEFAULT NULL COMMENT 'Passenger car equivalent',
  truck_equivalent DECIMAL(30,15) DEFAULT NULL COMMENT 'Truck equivalent',
  car_equivalent DECIMAL(30,15) DEFAULT NULL COMMENT 'Automobile equivalent',
  motor_equivalent DECIMAL(30,15) DEFAULT NULL COMMENT 'Motor vehicle equivalent',
  vehicle_speed DECIMAL(30,15) DEFAULT NULL COMMENT 'Motor vehicle speed',
  congestion_degree DECIMAL(30,15) DEFAULT NULL COMMENT 'Congestion degree',
  source_file VARCHAR(255) DEFAULT NULL COMMENT 'Source file name',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_observation_station (obs_date, station_code, direction),
  KEY idx_observation_station_date (obs_date),
  KEY idx_observation_station_city (city_name),
  KEY idx_observation_station_code (station_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;