ALTER TABLE projects
  ADD COLUMN default_municipality_id INT NULL;

CREATE TABLE municipalities (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  slug VARCHAR(160) NOT NULL,
  name VARCHAR(160) NOT NULL,
  status VARCHAR(16) NOT NULL DEFAULT 'active',
  notes TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE UNIQUE INDEX municipalities_slug_uq ON municipalities (slug);
CREATE UNIQUE INDEX municipalities_name_uq ON municipalities (name);
CREATE INDEX municipalities_status_idx ON municipalities (status);

CREATE TABLE municipality_rule_sets (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  municipality_id INT NOT NULL,
  name VARCHAR(160) NOT NULL,
  effective_from DATETIME NOT NULL,
  effective_to DATETIME NOT NULL,
  verification_status VARCHAR(16) NOT NULL DEFAULT 'draft',
  source_tariff_schedule_id INT NULL,
  high_demand_months_json TEXT NULL,
  low_demand_months_json TEXT NULL,
  weekday_holiday_treatment VARCHAR(64) NULL,
  unexpected_holiday_treatment VARCHAR(64) NULL,
  off_peak_demand_excluded INT NOT NULL DEFAULT 0,
  demand_charge_basis_text TEXT NULL,
  network_access_basis_text TEXT 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 municipality_rule_sets_municipality_idx
  ON municipality_rule_sets (municipality_id);
CREATE INDEX municipality_rule_sets_status_idx
  ON municipality_rule_sets (verification_status);
CREATE INDEX municipality_rule_sets_effective_idx
  ON municipality_rule_sets (municipality_id, effective_from, effective_to);
CREATE UNIQUE INDEX municipality_rule_sets_source_tariff_uq
  ON municipality_rule_sets (source_tariff_schedule_id);

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

CREATE INDEX municipality_tou_windows_rule_set_idx
  ON municipality_tou_windows (rule_set_id);
CREATE INDEX municipality_tou_windows_lookup_idx
  ON municipality_tou_windows (rule_set_id, season, day_type, band);

ALTER TABLE project_bills
  ADD COLUMN linked_municipality_rule_set_id INT NULL;

CREATE INDEX project_bills_rule_set_idx
  ON project_bills (linked_municipality_rule_set_id);