-- Migration: single-ward schema  ->  multi-ward + roles.
-- Only run this if you already loaded the ORIGINAL schema.sql (with users.is_admin).
-- Fresh installs should just use the current schema.sql and skip this file.
--
--   mysql -u <user> -p <dbname> < migration_multiward.sql

-- 1. Wards table + a default ward for existing data (page_versions used ward_id = 1).
CREATE TABLE IF NOT EXISTS wards (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  name       VARCHAR(120) NOT NULL,
  slug       VARCHAR(80)  NOT NULL UNIQUE,
  created_at TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO wards (id, name, slug)
SELECT 1, 'Village Ward', 'village'
WHERE NOT EXISTS (SELECT 1 FROM wards WHERE id = 1);

-- 2. Users: add role + ward_id, convert is_admin, drop it.
ALTER TABLE users
  ADD COLUMN role ENUM('super_admin','admin','viewer') NOT NULL DEFAULT 'viewer' AFTER password_hash,
  ADD COLUMN ward_id INT DEFAULT NULL AFTER role;

-- Existing admins become super_admins (adjust by hand afterward if you prefer);
-- existing non-admins become viewers in ward 1.
UPDATE users SET role = 'super_admin', ward_id = NULL WHERE is_admin = 1;
UPDATE users SET role = 'viewer',       ward_id = 1    WHERE is_admin = 0;

ALTER TABLE users DROP COLUMN is_admin;
ALTER TABLE users
  ADD CONSTRAINT fk_user_ward FOREIGN KEY (ward_id) REFERENCES wards(id) ON DELETE SET NULL;

-- 3. page_versions: enforce the ward FK (data already uses ward_id = 1).
ALTER TABLE page_versions
  ADD CONSTRAINT fk_version_ward FOREIGN KEY (ward_id) REFERENCES wards(id) ON DELETE CASCADE;