-- Two-way Google Calendar sync: fact_calendar_events (the events themselves, synced both
-- directions) and dim_calendar_sync_state (one row per tenant+mail_account holding the
-- incremental Google syncToken — see GoogleCalendarSyncScheduler / CalendarEventService).
-- Idempotent — safe to re-run.
-- Deploy: mysql -u root -p db_abos_v0.1 < db/add_calendar_sync.sql

USE db_abos_v0.1;

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)
);