CREATE TABLE IF NOT EXISTS province_team_contacts (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,province_id BIGINT UNSIGNED NOT NULL,person_id BIGINT UNSIGNED NULL,office_name VARCHAR(120) NOT NULL,contact_method ENUM('email','phone','post','none') NOT NULL DEFAULT 'email',published TINYINT(1) NOT NULL DEFAULT 0,receives_summons TINYINT(1) NOT NULL DEFAULT 0,created_by BIGINT UNSIGNED NOT NULL,revoked_at DATETIME NULL,created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,KEY idx_ptc_public(province_id,published,receives_summons),FOREIGN KEY(province_id) REFERENCES provinces(id) ON DELETE CASCADE,FOREIGN KEY(person_id) REFERENCES people(id) ON DELETE SET NULL,FOREIGN KEY(created_by) REFERENCES users(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS province_publications (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,province_id BIGINT UNSIGNED NOT NULL,publication_kind ENUM('summons_layout','email_template','meeting_planner') NOT NULL,title VARCHAR(180) NOT NULL,master_version INT UNSIGNED NOT NULL DEFAULT 1,payload_json JSON NOT NULL,approved_by BIGINT UNSIGNED NOT NULL,approved_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,revoked_at DATETIME NULL,created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(province_id) REFERENCES provinces(id) ON DELETE CASCADE,FOREIGN KEY(approved_by) REFERENCES users(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS province_publication_copies (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,publication_id BIGINT UNSIGNED NOT NULL,unit_id BIGINT UNSIGNED NOT NULL,master_version INT UNSIGNED NOT NULL,local_version INT UNSIGNED NOT NULL DEFAULT 1,acceptance_state ENUM('offered','accepted','replaced','revoked','rolled_back') NOT NULL DEFAULT 'offered',local_override TINYINT(1) NOT NULL DEFAULT 0,accepted_by BIGINT UNSIGNED NULL,accepted_at DATETIME NULL,replaced_by_copy_id BIGINT UNSIGNED NULL,province_actor_id BIGINT UNSIGNED NOT NULL,created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,UNIQUE KEY uq_ppc_publication_unit_version(publication_id,unit_id,master_version),KEY idx_ppc_unit(unit_id,acceptance_state),FOREIGN KEY(publication_id) REFERENCES province_publications(id) ON DELETE RESTRICT,FOREIGN KEY(unit_id) REFERENCES units(id) ON DELETE RESTRICT,FOREIGN KEY(accepted_by) REFERENCES users(id) ON DELETE SET NULL,FOREIGN KEY(province_actor_id) REFERENCES users(id),FOREIGN KEY(replaced_by_copy_id) REFERENCES province_publication_copies(id) ON DELETE SET NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS province_publication_audit (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,copy_id BIGINT UNSIGNED NULL,actor_id BIGINT UNSIGNED NULL,event_key ENUM('published','accepted','rejected_override','replaced','revoked','rolled_back') NOT NULL,created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(copy_id) REFERENCES province_publication_copies(id) ON DELETE SET NULL,FOREIGN KEY(actor_id) REFERENCES users(id) ON DELETE SET NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;