-- Explicit migration only; API reads and module imports never create storage.
CREATE TABLE IF NOT EXISTS bi_asset_history_settings (
  id TINYINT NOT NULL PRIMARY KEY,
  enabled_at DATETIME(3) NOT NULL,
  CONSTRAINT chk_asset_history_singleton CHECK (id = 1)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS bi_asset_history_batches (
  id VARCHAR(36) NOT NULL PRIMARY KEY,
  period_month CHAR(7) NOT NULL,
  definition_version VARCHAR(32) NOT NULL,
  scheduled_for DATETIME(3) NOT NULL,
  captured_at DATETIME(3) NULL,
  completed_at DATETIME(3) NULL,
  timezone VARCHAR(32) NOT NULL DEFAULT 'Asia/Shanghai',
  source ENUM('AUTO','IMPORT') NOT NULL,
  status ENUM('COMPLETE','FAILED','MISSED') NOT NULL,
  failure_code VARCHAR(64) NULL,
  total_count INT UNSIGNED NOT NULL DEFAULT 0,
  operating_count INT UNSIGNED NOT NULL DEFAULT 0,
  inventory_count INT UNSIGNED NOT NULL DEFAULT 0,
  import_provenance JSON NULL,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  UNIQUE KEY uk_asset_history_period (period_month, definition_version),
  KEY idx_asset_history_status (status, period_month),
  CONSTRAINT chk_asset_history_counts CHECK (total_count = operating_count + inventory_count)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS bi_asset_history_vehicles (
  batch_id VARCHAR(36) NOT NULL,
  vehicle_id VARCHAR(64) NOT NULL,
  operation_status VARCHAR(50) NOT NULL,
  vehicle_status VARCHAR(50) NULL,
  disposition ENUM('operating','inventory') NOT NULL,
  subject_org VARCHAR(500) NULL,
  department_name VARCHAR(500) NULL,
  manager_id VARCHAR(64) NULL,
  frozen_vehicle JSON NOT NULL,
  PRIMARY KEY (batch_id, vehicle_id),
  KEY idx_asset_history_subject (batch_id, subject_org(128)),
  KEY idx_asset_history_manager (batch_id, manager_id),
  CONSTRAINT fk_asset_history_batch FOREIGN KEY (batch_id) REFERENCES bi_asset_history_batches (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;