-- Scoped planning calendars. Bookable meetings remain canonical in meetings.
CREATE TABLE calendar_venues (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, province_id BIGINT UNSIGNED NOT NULL, unit_id BIGINT UNSIGNED NULL,
 name VARCHAR(200) NOT NULL, address VARCHAR(500) NULL, timezone VARCHAR(64) NOT NULL DEFAULT 'Europe/London', active TINYINT(1) NOT NULL DEFAULT 1,
 created_by BIGINT UNSIGNED NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_calendar_venue(unit_id,name), KEY idx_calendar_venue_province(province_id,active),
 FOREIGN KEY(province_id) REFERENCES provinces(id) ON DELETE CASCADE, FOREIGN KEY(unit_id) REFERENCES units(id) ON DELETE CASCADE,
 FOREIGN KEY(created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE calendar_recurring_templates (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, scope_type ENUM('province','unit') NOT NULL,
 province_id BIGINT UNSIGNED NOT NULL, unit_id BIGINT UNSIGNED NULL,
 entry_kind ENUM('rehearsal','installation','provincial_visit','special_event','reporting_date','officer_task') NOT NULL,
 title VARCHAR(200) NOT NULL, description VARCHAR(500) NULL, order_key VARCHAR(40) NULL,
 venue_id BIGINT UNSIGNED NULL, timezone VARCHAR(64) NOT NULL DEFAULT 'Europe/London',
 local_start_time TIME NOT NULL, duration_minutes SMALLINT UNSIGNED NOT NULL DEFAULT 60,
 frequency ENUM('weekly','monthly','yearly') NOT NULL, recurrence_interval SMALLINT UNSIGNED NOT NULL DEFAULT 1,
 start_date DATE NOT NULL, until_date DATE NULL, occurrence_count SMALLINT UNSIGNED NULL,
 owner_user_id BIGINT UNSIGNED NULL, visibility ENUM('members','officers','private') NOT NULL DEFAULT 'officers', active TINYINT(1) NOT NULL DEFAULT 1,
 created_by BIGINT UNSIGNED NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 KEY idx_calendar_template_scope(scope_type,province_id,unit_id,active),
 FOREIGN KEY(province_id) REFERENCES provinces(id) ON DELETE CASCADE, FOREIGN KEY(unit_id) REFERENCES units(id) ON DELETE CASCADE,
 FOREIGN KEY(venue_id) REFERENCES calendar_venues(id) ON DELETE SET NULL, FOREIGN KEY(owner_user_id) REFERENCES users(id) ON DELETE SET NULL,
 FOREIGN KEY(created_by) REFERENCES users(id),
 CONSTRAINT chk_calendar_template_scope CHECK((scope_type='province' AND unit_id IS NULL) OR (scope_type='unit' AND unit_id IS NOT NULL))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE calendar_planning_entries (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, scope_type ENUM('province','unit') NOT NULL,
 province_id BIGINT UNSIGNED NOT NULL, unit_id BIGINT UNSIGNED NULL, template_id BIGINT UNSIGNED NULL,
 entry_kind ENUM('rehearsal','installation','provincial_visit','special_event','summons_deadline','dining_deadline','reporting_date','officer_task') NOT NULL,
 title VARCHAR(200) NOT NULL, description VARCHAR(500) NULL, order_key VARCHAR(40) NULL,
 venue_id BIGINT UNSIGNED NULL, venue_text VARCHAR(300) NULL, timezone VARCHAR(64) NOT NULL DEFAULT 'Europe/London',
 starts_at DATETIME NOT NULL, ends_at DATETIME NULL, task_due_at DATETIME NULL,
 owner_user_id BIGINT UNSIGNED NULL, visibility ENUM('members','officers','private') NOT NULL DEFAULT 'officers',
 status ENUM('draft','confirmed','cancelled','completed') NOT NULL DEFAULT 'draft', override_conflict TINYINT(1) NOT NULL DEFAULT 0,
 created_by BIGINT UNSIGNED NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 KEY idx_calendar_entry_scope(scope_type,province_id,unit_id,starts_at,status), KEY idx_calendar_owner(owner_user_id,task_due_at,status),
 FOREIGN KEY(province_id) REFERENCES provinces(id) ON DELETE CASCADE, FOREIGN KEY(unit_id) REFERENCES units(id) ON DELETE CASCADE,
 FOREIGN KEY(template_id) REFERENCES calendar_recurring_templates(id) ON DELETE SET NULL,
 FOREIGN KEY(venue_id) REFERENCES calendar_venues(id) ON DELETE SET NULL, FOREIGN KEY(owner_user_id) REFERENCES users(id) ON DELETE SET NULL,
 FOREIGN KEY(created_by) REFERENCES users(id),
 CONSTRAINT chk_calendar_entry_scope CHECK((scope_type='province' AND unit_id IS NULL) OR (scope_type='unit' AND unit_id IS NOT NULL))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE calendar_recurrence_exceptions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, template_id BIGINT UNSIGNED NOT NULL, occurrence_date DATE NOT NULL,
 exception_kind ENUM('cancel','replace') NOT NULL, replacement_entry_id BIGINT UNSIGNED NULL, note VARCHAR(300) NULL,
 created_by BIGINT UNSIGNED NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_calendar_exception(template_id,occurrence_date),
 FOREIGN KEY(template_id) REFERENCES calendar_recurring_templates(id) ON DELETE CASCADE,
 FOREIGN KEY(replacement_entry_id) REFERENCES calendar_planning_entries(id) ON DELETE SET NULL, FOREIGN KEY(created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE calendar_feed_tokens (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL,
 scope_type ENUM('my','province','unit') NOT NULL, province_id BIGINT UNSIGNED NULL, unit_id BIGINT UNSIGNED NULL,
 token_hash CHAR(64) NOT NULL, label VARCHAR(100) NOT NULL, include_private TINYINT(1) NOT NULL DEFAULT 0,
 expires_at DATETIME NULL, revoked_at DATETIME NULL, last_used_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_calendar_feed_token(token_hash), KEY idx_calendar_feed_user(user_id,revoked_at),
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE, FOREIGN KEY(province_id) REFERENCES provinces(id) ON DELETE CASCADE,
 FOREIGN KEY(unit_id) REFERENCES units(id) ON DELETE CASCADE,
 CONSTRAINT chk_calendar_feed_scope CHECK(
  (scope_type='my' AND province_id IS NULL AND unit_id IS NULL) OR
  (scope_type='province' AND province_id IS NOT NULL AND unit_id IS NULL) OR
  (scope_type='unit' AND unit_id IS NOT NULL)
 )
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE calendar_reminder_rules (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, entry_id BIGINT UNSIGNED NOT NULL, minutes_before INT UNSIGNED NOT NULL,
 recipient_user_id BIGINT UNSIGNED NOT NULL, active TINYINT(1) NOT NULL DEFAULT 1, last_queued_for DATETIME NULL,
 created_by BIGINT UNSIGNED NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_calendar_reminder(entry_id,minutes_before,recipient_user_id),
 FOREIGN KEY(entry_id) REFERENCES calendar_planning_entries(id) ON DELETE CASCADE,
 FOREIGN KEY(recipient_user_id) REFERENCES users(id) ON DELETE CASCADE, FOREIGN KEY(created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO migration_history(version,name,checksum,execution_ms)
VALUES('202609270028','Scoped unit Province and member calendars',SHA2('202609270028_scoped_calendars_v1',256),0)
ON DUPLICATE KEY UPDATE name=VALUES(name),checksum=VALUES(checksum);