CREATE TABLE tariff_schedules (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  municipality VARCHAR(160) NOT NULL,
  tariff_year VARCHAR(32) NOT NULL,
  tariff_code VARCHAR(160) NOT NULL,
  season VARCHAR(64) NULL,
  verification_status VARCHAR(16) NOT NULL DEFAULT 'draft',
  verified_at DATETIME NULL,
  verified_by VARCHAR(160) NULL,
  source_file_name VARCHAR(255) NULL,
  stored_file_path VARCHAR(512) NULL,
  source_url VARCHAR(512) NULL,
  notes TEXT NULL,
  raw_extracted_json LONGTEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX tariff_schedules_municipality_idx
  ON tariff_schedules (municipality);
CREATE INDEX tariff_schedules_status_idx
  ON tariff_schedules (verification_status);
CREATE INDEX tariff_schedules_year_idx
  ON tariff_schedules (tariff_year);

CREATE TABLE tariff_time_windows (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  schedule_id INT NOT NULL,
  day_type VARCHAR(32) NOT NULL,
  band VARCHAR(32) NOT NULL,
  start_time VARCHAR(5) NOT NULL,
  end_time VARCHAR(5) NOT NULL,
  season VARCHAR(64) NULL,
  sort_order INT NOT NULL DEFAULT 0
);

CREATE INDEX tariff_time_windows_schedule_idx
  ON tariff_time_windows (schedule_id);
CREATE INDEX tariff_time_windows_lookup_idx
  ON tariff_time_windows (schedule_id, day_type, band);

CREATE TABLE tariff_rates (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  schedule_id INT NOT NULL,
  category VARCHAR(32) NOT NULL,
  band VARCHAR(32) NULL,
  unit VARCHAR(64) NOT NULL,
  description VARCHAR(255) NULL,
  rate DOUBLE NOT NULL,
  season VARCHAR(64) NULL,
  sort_order INT NOT NULL DEFAULT 0
);

CREATE INDEX tariff_rates_schedule_idx
  ON tariff_rates (schedule_id);
CREATE INDEX tariff_rates_lookup_idx
  ON tariff_rates (schedule_id, category, band);

CREATE TABLE project_bills (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  project_id INT NOT NULL,
  linked_tariff_schedule_id INT NULL,
  file_name VARCHAR(255) NOT NULL,
  stored_file_path VARCHAR(512) NULL,
  supplier_name VARCHAR(160) NULL,
  account_reference VARCHAR(160) NULL,
  period_start DATETIME NULL,
  period_end DATETIME NULL,
  issue_date DATETIME NULL,
  read_date DATETIME NULL,
  verification_status VARCHAR(16) NOT NULL DEFAULT 'draft',
  verified_at DATETIME NULL,
  verified_by VARCHAR(160) NULL,
  notes TEXT NULL,
  raw_extracted_json LONGTEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX project_bills_project_idx
  ON project_bills (project_id);
CREATE INDEX project_bills_status_idx
  ON project_bills (verification_status);
CREATE INDEX project_bills_period_idx
  ON project_bills (project_id, period_start, period_end);

CREATE TABLE project_bill_lines (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  bill_id INT NOT NULL,
  category VARCHAR(32) NOT NULL,
  band VARCHAR(32) NULL,
  description TEXT NOT NULL,
  quantity DOUBLE NULL,
  unit VARCHAR(64) NULL,
  rate DOUBLE NULL,
  amount DOUBLE NULL,
  confidence DOUBLE NULL,
  sort_order INT NOT NULL DEFAULT 0
);

CREATE INDEX project_bill_lines_bill_idx
  ON project_bill_lines (bill_id);
CREATE INDEX project_bill_lines_lookup_idx
  ON project_bill_lines (bill_id, category, band);