-- Aggie-server counterpart of add_calendar_sync.sql.
-- Idempotent — safe to re-run.
-- Deploy: mysql -u <user> -p 'stacie_Aggie_v1.0' < db/aggie_add_calendar_sync.sql

USE `stacie_Aggie_v1.0`;

CREATE TABLE IF NOT EXISTS fact_calendar_events (
    id                 BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id          INT NOT NULL,
    business_id        INT NULL,
    mail_account_id    BIGINT NOT NULL,
    google_event_id    VARCHAR(255) NULL,
    title              VARCHAR(500) NOT NULL,
    description        TEXT NULL,
    location           VARCHAR(500) NULL,
    start_at           TIMESTAMP NOT NULL,
    end_at             TIMESTAMP NOT NULL,
    attendees          TEXT NULL,
    hangout_link       VARCHAR(500) NULL,
    status             VARCHAR(20) NOT NULL DEFAULT 'confirmed',
    linked_entity_type VARCHAR(20) NULL,
    linked_entity_id   BIGINT NULL,
    source             VARCHAR(10) NOT NULL,
    created_at         TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    created_by         INT NULL,
    updated_at         TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_by         INT NULL,
    is_deleted         TINYINT(1) NOT NULL DEFAULT 0,
    KEY idx_calendar_events_account (tenant_id, mail_account_id, is_deleted),
    KEY idx_calendar_events_google_id (tenant_id, mail_account_id, google_event_id),
    KEY idx_calendar_events_linked (tenant_id, linked_entity_type, linked_entity_id, is_deleted),
    KEY idx_calendar_events_start (tenant_id, start_at)
);

CREATE TABLE IF NOT EXISTS dim_calendar_sync_state (
    id              BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id       INT NOT NULL,
    mail_account_id BIGINT NOT NULL,
    sync_token      VARCHAR(1024) NULL,
    last_synced_at  TIMESTAMP NULL,
    UNIQUE KEY uq_calendar_sync_state (tenant_id, mail_account_id)
);