CREATE TABLE IF NOT EXISTS schema_migrations (
    version VARCHAR(191) NOT NULL,
    applied_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (version)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS users (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    email VARCHAR(255) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    last_login_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY users_email_unique (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS settings (
    setting_key VARCHAR(191) NOT NULL,
    setting_value LONGTEXT NULL,
    is_encrypted TINYINT(1) NOT NULL DEFAULT 0,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (setting_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS calendar_sources (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(191) NOT NULL,
    category VARCHAR(100) NOT NULL DEFAULT 'Other',
    source_type VARCHAR(30) NOT NULL DEFAULT 'ics',
    identity_email VARCHAR(255) NULL,
    feed_url_encrypted LONGTEXT NOT NULL,
    feed_fingerprint CHAR(64) NOT NULL,
    enabled TINYINT(1) NOT NULL DEFAULT 1,
    include_in_agenda TINYINT(1) NOT NULL DEFAULT 1,
    include_in_conflicts TINYINT(1) NOT NULL DEFAULT 1,
    warn_incomplete_events TINYINT(1) NOT NULL DEFAULT 0,
    privacy_mode VARCHAR(20) NOT NULL DEFAULT 'details',
    all_day_policy VARCHAR(30) NOT NULL DEFAULT 'ignore',
    tentative_policy VARCHAR(20) NOT NULL DEFAULT 'block',
    pending_policy VARCHAR(20) NOT NULL DEFAULT 'warn',
    virtual_buffer_before SMALLINT UNSIGNED NOT NULL DEFAULT 15,
    virtual_buffer_after SMALLINT UNSIGNED NOT NULL DEFAULT 15,
    in_person_buffer_before SMALLINT UNSIGNED NOT NULL DEFAULT 30,
    in_person_buffer_after SMALLINT UNSIGNED NOT NULL DEFAULT 30,
    travel_buffer_minutes SMALLINT UNSIGNED NOT NULL DEFAULT 30,
    refresh_interval_minutes SMALLINT UNSIGNED NOT NULL DEFAULT 5,
    source_timezone VARCHAR(100) NULL,
    notes TEXT NULL,
    etag VARCHAR(500) NULL,
    last_modified VARCHAR(500) NULL,
    content_hash CHAR(64) NULL,
    consecutive_failures SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    last_attempt_at DATETIME NULL,
    last_success_at DATETIME NULL,
    last_http_status SMALLINT NULL,
    last_error TEXT NULL,
    last_content_sync_at DATETIME NULL,
    last_sync_started_at DATETIME NULL,
    last_sync_finished_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY calendar_sources_feed_fingerprint_unique (feed_fingerprint),
    KEY calendar_sources_enabled_due (enabled, last_attempt_at),
    KEY calendar_sources_category (category)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sync_runs (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    run_uuid CHAR(36) NOT NULL,
    calendar_source_id BIGINT UNSIGNED NOT NULL,
    status VARCHAR(30) NOT NULL,
    http_status SMALLINT NULL,
    events_seen INT UNSIGNED NOT NULL DEFAULT 0,
    events_created INT UNSIGNED NOT NULL DEFAULT 0,
    events_updated INT UNSIGNED NOT NULL DEFAULT 0,
    events_deleted INT UNSIGNED NOT NULL DEFAULT 0,
    events_rejected INT UNSIGNED NOT NULL DEFAULT 0,
    message TEXT NULL,
    started_at DATETIME NOT NULL,
    finished_at DATETIME NULL,
    PRIMARY KEY (id),
    UNIQUE KEY sync_runs_uuid_unique (run_uuid),
    KEY sync_runs_source_started (calendar_source_id, started_at),
    CONSTRAINT sync_runs_source_fk FOREIGN KEY (calendar_source_id) REFERENCES calendar_sources (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS events (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    calendar_source_id BIGINT UNSIGNED NOT NULL,
    instance_key CHAR(64) NOT NULL,
    uid TEXT NOT NULL,
    uid_hash CHAR(64) NOT NULL,
    recurrence_id VARCHAR(255) NULL,
    title VARCHAR(500) NOT NULL,
    description MEDIUMTEXT NULL,
    location VARCHAR(1000) NULL,
    conference_url TEXT NULL,
    source_url TEXT NULL,
    start_at DATETIME NOT NULL,
    end_at DATETIME NOT NULL,
    timezone VARCHAR(100) NOT NULL DEFAULT 'America/New_York',
    all_day TINYINT(1) NOT NULL DEFAULT 0,
    status VARCHAR(30) NOT NULL DEFAULT 'CONFIRMED',
    transparency VARCHAR(30) NOT NULL DEFAULT 'OPAQUE',
    participation_status VARCHAR(30) NOT NULL DEFAULT 'ACCEPTED',
    organizer_email VARCHAR(255) NULL,
    source_created_at DATETIME NULL,
    source_updated_at DATETIME NULL,
    raw_hash CHAR(64) NOT NULL,
    canonical_hash CHAR(64) NOT NULL,
    last_seen_run CHAR(36) NOT NULL,
    is_deleted TINYINT(1) NOT NULL DEFAULT 0,
    deleted_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY events_source_instance_unique (calendar_source_id, instance_key),
    KEY events_active_window (is_deleted, start_at, end_at),
    KEY events_source_window (calendar_source_id, start_at, end_at),
    KEY events_uid_hash (uid_hash),
    KEY events_canonical_hash (canonical_hash),
    CONSTRAINT events_source_fk FOREIGN KEY (calendar_source_id) REFERENCES calendar_sources (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS event_changes (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    event_id BIGINT UNSIGNED NULL,
    calendar_source_id BIGINT UNSIGNED NOT NULL,
    instance_key CHAR(64) NOT NULL,
    change_type VARCHAR(30) NOT NULL,
    event_start_at DATETIME NULL,
    old_payload JSON NULL,
    new_payload JSON NULL,
    detected_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    queued_at DATETIME NULL,
    notified_at DATETIME NULL,
    PRIMARY KEY (id),
    KEY event_changes_pending (queued_at, detected_at),
    KEY event_changes_source (calendar_source_id, detected_at),
    CONSTRAINT event_changes_event_fk FOREIGN KEY (event_id) REFERENCES events (id) ON DELETE SET NULL,
    CONSTRAINT event_changes_source_fk FOREIGN KEY (calendar_source_id) REFERENCES calendar_sources (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS conflicts (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    fingerprint CHAR(64) NOT NULL,
    event_a_id BIGINT UNSIGNED NOT NULL,
    event_b_id BIGINT UNSIGNED NOT NULL,
    conflict_type VARCHAR(30) NOT NULL,
    severity VARCHAR(20) NOT NULL DEFAULT 'warning',
    overlap_start_at DATETIME NULL,
    overlap_end_at DATETIME NULL,
    gap_minutes SMALLINT NULL,
    required_buffer_minutes SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    status VARCHAR(30) NOT NULL DEFAULT 'open',
    note TEXT NULL,
    suppressed_until DATETIME NULL,
    first_detected_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_detected_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_seen_scan CHAR(36) NOT NULL,
    notified_at DATETIME NULL,
    daily_notified_on DATE NULL,
    resolved_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY conflicts_fingerprint_unique (fingerprint),
    KEY conflicts_status_window (status, overlap_start_at),
    KEY conflicts_scan (last_seen_scan),
    CONSTRAINT conflicts_event_a_fk FOREIGN KEY (event_a_id) REFERENCES events (id) ON DELETE CASCADE,
    CONSTRAINT conflicts_event_b_fk FOREIGN KEY (event_b_id) REFERENCES events (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS mirror_targets (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(191) NOT NULL,
    target_calendar_id VARCHAR(255) NOT NULL DEFAULT 'primary',
    enabled TINYINT(1) NOT NULL DEFAULT 1,
    api_token_hash CHAR(64) NOT NULL,
    api_token_encrypted LONGTEXT NOT NULL,
    exclude_source_ids JSON NULL,
    include_categories JSON NULL,
    title_template VARCHAR(191) NOT NULL DEFAULT 'Busy — {category}',
    include_all_day TINYINT(1) NOT NULL DEFAULT 1,
    lookback_days SMALLINT UNSIGNED NOT NULL DEFAULT 7,
    lookahead_days SMALLINT UNSIGNED NOT NULL DEFAULT 365,
    last_polled_at DATETIME NULL,
    last_user_agent VARCHAR(500) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY mirror_targets_token_hash_unique (api_token_hash)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS notification_queue (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    notification_type VARCHAR(50) NOT NULL,
    dedupe_key VARCHAR(191) NOT NULL,
    recipient VARCHAR(500) NULL,
    subject VARCHAR(500) NOT NULL,
    body_text MEDIUMTEXT NOT NULL,
    body_html MEDIUMTEXT NULL,
    channels VARCHAR(255) NOT NULL DEFAULT 'email',
    priority VARCHAR(20) NOT NULL DEFAULT 'normal',
    bypass_quiet_hours TINYINT(1) NOT NULL DEFAULT 0,
    scheduled_at DATETIME NOT NULL,
    status VARCHAR(30) NOT NULL DEFAULT 'pending',
    attempts SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    last_error TEXT NULL,
    sent_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY notification_queue_due (status, scheduled_at),
    KEY notification_queue_dedupe (dedupe_key, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS notification_log (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    notification_type VARCHAR(50) NOT NULL,
    recipient VARCHAR(500) NULL,
    subject VARCHAR(500) NULL,
    channel VARCHAR(50) NOT NULL DEFAULT 'email',
    status VARCHAR(30) NOT NULL,
    error_message TEXT NULL,
    sent_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY notification_log_sent (sent_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS scheduled_runs (
    run_key VARCHAR(191) NOT NULL,
    run_date DATE NOT NULL,
    completed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (run_key, run_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS login_attempts (
    identity_hash CHAR(64) NOT NULL,
    attempts SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    window_started_at DATETIME NOT NULL,
    blocked_until DATETIME NULL,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (identity_hash)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS audit_log (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id BIGINT UNSIGNED NULL,
    action VARCHAR(100) NOT NULL,
    entity_type VARCHAR(100) NOT NULL,
    entity_id BIGINT UNSIGNED NULL,
    details JSON NULL,
    ip_hash CHAR(64) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY audit_log_created (created_at),
    KEY audit_log_entity (entity_type, entity_id),
    CONSTRAINT audit_log_user_fk FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS system_log (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    level VARCHAR(20) NOT NULL,
    context VARCHAR(100) NOT NULL,
    message TEXT NOT NULL,
    metadata JSON NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY system_log_created (created_at),
    KEY system_log_level (level, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE calendar_sources
    ADD COLUMN IF NOT EXISTS last_content_sync_at DATETIME NULL AFTER last_error;

ALTER TABLE calendar_sources
    ADD COLUMN IF NOT EXISTS warn_incomplete_events TINYINT(1) NOT NULL DEFAULT 0 AFTER include_in_conflicts;

INSERT IGNORE INTO schema_migrations (version, applied_at) VALUES ('001_initial', UTC_TIMESTAMP());
INSERT IGNORE INTO schema_migrations (version, applied_at) VALUES ('002_periodic_full_ics_refresh', UTC_TIMESTAMP());
INSERT IGNORE INTO schema_migrations (version, applied_at) VALUES ('003_incomplete_event_warnings', UTC_TIMESTAMP());