-- Explicit, revocable Unit-to-Province data sharing.  A Province role alone
-- never grants access to a Unit's data: the Unit category grant and Province
-- feature switch are both required at request time.
CREATE TABLE IF NOT EXISTS province_data_features (
  province_id BIGINT UNSIGNED NOT NULL,
  category_key VARCHAR(40) NOT NULL,
  enabled TINYINT(1) NOT NULL DEFAULT 0,
  updated_by BIGINT UNSIGNED NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (province_id,category_key),
  CONSTRAINT fk_pdf_province FOREIGN KEY (province_id) REFERENCES provinces(id) ON DELETE CASCADE,
  CONSTRAINT fk_pdf_actor FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS province_unit_data_sharing (
  province_id BIGINT UNSIGNED NOT NULL,
  unit_id BIGINT UNSIGNED NOT NULL,
  category_key VARCHAR(40) NOT NULL,
  active TINYINT(1) NOT NULL DEFAULT 0,
  granted_by BIGINT UNSIGNED NULL,
  granted_at DATETIME NULL,
  expires_at DATETIME NULL,
  revoked_by BIGINT UNSIGNED NULL,
  revoked_at DATETIME NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (province_id,unit_id,category_key),
  KEY idx_puds_unit_category (unit_id,category_key,active,expires_at),
  CONSTRAINT fk_puds_province FOREIGN KEY (province_id) REFERENCES provinces(id) ON DELETE CASCADE,
  CONSTRAINT fk_puds_unit FOREIGN KEY (unit_id) REFERENCES units(id) ON DELETE CASCADE,
  CONSTRAINT fk_puds_granter FOREIGN KEY (granted_by) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_puds_revoker FOREIGN KEY (revoked_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS province_data_access_log (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  province_id BIGINT UNSIGNED NOT NULL,
  unit_id BIGINT UNSIGNED NOT NULL,
  actor_id BIGINT UNSIGNED NOT NULL,
  category_key VARCHAR(40) NOT NULL,
  purpose_key VARCHAR(40) NOT NULL,
  source_workspace VARCHAR(80) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_pdal_review (province_id,unit_id,category_key,created_at),
  KEY idx_pdal_actor (actor_id,created_at),
  CONSTRAINT fk_pdal_province FOREIGN KEY (province_id) REFERENCES provinces(id) ON DELETE CASCADE,
  CONSTRAINT fk_pdal_unit FOREIGN KEY (unit_id) REFERENCES units(id) ON DELETE CASCADE,
  CONSTRAINT fk_pdal_actor FOREIGN KEY (actor_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Preserve the existing Province operational dashboard only for non-sensitive,
-- aggregate event and attendance information.  All personal, finance, welfare,
-- document and email categories remain disabled until explicitly enabled.
INSERT IGNORE INTO province_data_features(province_id,category_key,enabled)
SELECT id,'events',1 FROM provinces WHERE active=1;
INSERT IGNORE INTO province_data_features(province_id,category_key,enabled)
SELECT id,'attendance',1 FROM provinces WHERE active=1;
INSERT IGNORE INTO province_unit_data_sharing(province_id,unit_id,category_key,active,granted_at)
SELECT province_id,id,'events',1,NOW() FROM units WHERE province_id IS NOT NULL AND deleted_at IS NULL;
INSERT IGNORE INTO province_unit_data_sharing(province_id,unit_id,category_key,active,granted_at)
SELECT province_id,id,'attendance',1,NOW() FROM units WHERE province_id IS NOT NULL AND deleted_at IS NULL;