CREATE TABLE IF NOT EXISTS province_independent_sharing_policy (
 id TINYINT UNSIGNED NOT NULL PRIMARY KEY, enabled TINYINT(1) NOT NULL DEFAULT 0, approved_by BIGINT UNSIGNED NULL, approved_at DATETIME NULL, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 CONSTRAINT fk_pisp_actor FOREIGN KEY(approved_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT IGNORE INTO province_independent_sharing_policy(id,enabled) VALUES(1,0);
-- Existing rows predate the independent-Unit consent contract.  They cannot
-- become active merely because the platform approves the product direction.
UPDATE province_unit_data_sharing SET active=0,revoked_at=COALESCE(revoked_at,NOW()) WHERE active=1;
CREATE TABLE IF NOT EXISTS province_unit_sharing_approval_tokens (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, province_id BIGINT UNSIGNED NOT NULL, unit_id BIGINT UNSIGNED NOT NULL,
 categories_json JSON NOT NULL, token_hash CHAR(64) NOT NULL, expires_at DATETIME NULL, consumed_at DATETIME NULL, consumed_by BIGINT UNSIGNED NULL, revoked_at DATETIME NULL, created_by BIGINT UNSIGNED NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_pusat_token(token_hash), KEY idx_pusat_unit(unit_id,revoked_at,expires_at),
 FOREIGN KEY(province_id) REFERENCES provinces(id) ON DELETE CASCADE, FOREIGN KEY(unit_id) REFERENCES units(id) ON DELETE CASCADE,
 FOREIGN KEY(consumed_by) REFERENCES users(id) ON DELETE SET NULL, FOREIGN KEY(created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS province_unit_write_approvals (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, province_id BIGINT UNSIGNED NOT NULL, unit_id BIGINT UNSIGNED NOT NULL,
 write_kind ENUM('task','document','template') NOT NULL, token_hash CHAR(64) NOT NULL, expires_at DATETIME NULL, consumed_at DATETIME NULL, consumed_by BIGINT UNSIGNED NULL, revoked_at DATETIME NULL, created_by BIGINT UNSIGNED NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_puwa_token(token_hash), KEY idx_puwa_unit(unit_id,write_kind,revoked_at,expires_at),
 FOREIGN KEY(province_id) REFERENCES provinces(id) ON DELETE CASCADE, FOREIGN KEY(unit_id) REFERENCES units(id) ON DELETE CASCADE,
 FOREIGN KEY(consumed_by) REFERENCES users(id) ON DELETE SET NULL, FOREIGN KEY(created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;