-- Apply after migrations 001-004. Additive; no existing rows are changed.
CREATE TABLE province_settings (
 province_id BIGINT UNSIGNED PRIMARY KEY,
 logo_url VARCHAR(500) NULL,
 primary_colour CHAR(7) NOT NULL DEFAULT '#990033',
 secondary_colour CHAR(7) NOT NULL DEFAULT '#122849',
 accent_colour CHAR(7) NOT NULL DEFAULT '#c7a646',
 contact_name VARCHAR(150) NULL,
 contact_email VARCHAR(254) NULL,
 contact_phone VARCHAR(50) NULL,
 website_url VARCHAR(500) NULL,
 postal_address VARCHAR(500) NULL,
 updated_by BIGINT UNSIGNED NULL,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 FOREIGN KEY(province_id) REFERENCES provinces(id) ON DELETE CASCADE,
 FOREIGN KEY(updated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE province_audit_log (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 province_id BIGINT UNSIGNED NOT NULL,
 actor_id BIGINT UNSIGNED NULL,
 action VARCHAR(80) NOT NULL,
 unit_id BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 KEY idx_province_audit(province_id,created_at,id),
 FOREIGN KEY(province_id) REFERENCES provinces(id) ON DELETE CASCADE,
 FOREIGN KEY(actor_id) REFERENCES users(id) ON DELETE SET NULL,
 FOREIGN KEY(unit_id) REFERENCES units(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE province_unit_invites (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 province_id BIGINT UNSIGNED NOT NULL,
 unit_id BIGINT UNSIGNED NOT NULL,
 email VARCHAR(254) NOT NULL,
 token_hash CHAR(64) NOT NULL,
 invited_by BIGINT UNSIGNED NULL,
 expires_at DATETIME NOT NULL,
 accepted_at DATETIME NULL,
 revoked_at DATETIME NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_province_invite_token(token_hash),
 KEY idx_province_invites(province_id,unit_id,created_at),
 FOREIGN KEY(province_id) REFERENCES provinces(id) ON DELETE CASCADE,
 FOREIGN KEY(unit_id) REFERENCES units(id) ON DELETE CASCADE,
 FOREIGN KEY(invited_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;