CREATE TYPE "platform_meeting_attendance"."OutlookBindingSyncMode" AS ENUM ('AUTO', 'LOCKED_BY_LOCAL_EDIT');

ALTER TABLE "platform_meeting_attendance"."outlook_sync_bindings"
ADD COLUMN "sync_mode" "platform_meeting_attendance"."OutlookBindingSyncMode" NOT NULL DEFAULT 'AUTO',
ADD COLUMN "local_override_at" TIMESTAMPTZ(3),
ADD COLUMN "local_override_by_user_id" UUID,
ADD COLUMN "local_override_by_email" VARCHAR(255),
ADD COLUMN "local_override_reason" VARCHAR(64),
ADD COLUMN "local_override_fields" JSONB;

CREATE INDEX "outlook_sync_bindings_manage_status_sync_mode_idx"
ON "platform_meeting_attendance"."outlook_sync_bindings"("manage_status", "sync_mode");

CREATE TABLE "platform_meeting_attendance"."outlook_event_source_versions" (
  "id" VARCHAR(32) NOT NULL,
  "binding_id" VARCHAR(32) NOT NULL,
  "mailbox_id" VARCHAR(32) NOT NULL,
  "graph_event_id" VARCHAR(255) NOT NULL,
  "graph_series_master_id" VARCHAR(255),
  "graph_event_type" VARCHAR(32) NOT NULL,
  "version_source" VARCHAR(32) NOT NULL,
  "graph_last_modified_at" TIMESTAMPTZ(3),
  "fetched_at" TIMESTAMPTZ(3) NOT NULL,
  "etag" VARCHAR(255),
  "payload_hash" VARCHAR(128) NOT NULL,
  "attendees_count" INTEGER NOT NULL,
  "attendees_required_count" INTEGER NOT NULL,
  "attendees_optional_count" INTEGER NOT NULL,
  "attendees_resource_count" INTEGER NOT NULL,
  "organizer_email" VARCHAR(255),
  "start_time" TIMESTAMPTZ(3),
  "end_time" TIMESTAMPTZ(3),
  "is_cancelled" BOOLEAN NOT NULL DEFAULT false,
  "raw_payload" JSONB NOT NULL,
  "normalized_payload" JSONB NOT NULL,
  "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT "outlook_event_source_versions_pkey" PRIMARY KEY ("id")
);

CREATE TABLE "platform_meeting_attendance"."outlook_event_sync_diffs" (
  "id" VARCHAR(32) NOT NULL,
  "binding_id" VARCHAR(32) NOT NULL,
  "source_version_id" VARCHAR(32) NOT NULL,
  "previous_source_version_id" VARCHAR(32),
  "detected_at" TIMESTAMPTZ(3) NOT NULL,
  "diff_type" VARCHAR(32) NOT NULL,
  "changed_fields" JSONB NOT NULL,
  "summary_json" JSONB NOT NULL,
  "attendees_added" JSONB,
  "attendees_removed" JSONB,
  "attendees_response_changed" JSONB,
  "graph_attendees_count_before" INTEGER,
  "graph_attendees_count_after" INTEGER,
  "internal_matched_count_before" INTEGER,
  "internal_matched_count_after" INTEGER,
  "meeting_required_count_before" INTEGER,
  "meeting_required_count_after" INTEGER,
  "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT "outlook_event_sync_diffs_pkey" PRIMARY KEY ("id")
);

CREATE UNIQUE INDEX "outlook_event_source_versions_binding_id_payload_hash_key"
ON "platform_meeting_attendance"."outlook_event_source_versions"("binding_id", "payload_hash");

CREATE INDEX "outlook_event_source_versions_binding_id_created_at_idx"
ON "platform_meeting_attendance"."outlook_event_source_versions"("binding_id", "created_at");

CREATE INDEX "outlook_event_source_versions_mailbox_id_graph_event_id_created_at_idx"
ON "platform_meeting_attendance"."outlook_event_source_versions"("mailbox_id", "graph_event_id", "created_at");

CREATE INDEX "outlook_event_sync_diffs_binding_id_detected_at_idx"
ON "platform_meeting_attendance"."outlook_event_sync_diffs"("binding_id", "detected_at");

ALTER TABLE "platform_meeting_attendance"."outlook_event_source_versions"
ADD CONSTRAINT "outlook_event_source_versions_binding_id_fkey"
FOREIGN KEY ("binding_id") REFERENCES "platform_meeting_attendance"."outlook_sync_bindings"("id")
ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE "platform_meeting_attendance"."outlook_event_source_versions"
ADD CONSTRAINT "outlook_event_source_versions_mailbox_id_fkey"
FOREIGN KEY ("mailbox_id") REFERENCES "platform_meeting_attendance"."outlook_sync_mailboxes"("id")
ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE "platform_meeting_attendance"."outlook_event_sync_diffs"
ADD CONSTRAINT "outlook_event_sync_diffs_binding_id_fkey"
FOREIGN KEY ("binding_id") REFERENCES "platform_meeting_attendance"."outlook_sync_bindings"("id")
ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE "platform_meeting_attendance"."outlook_event_sync_diffs"
ADD CONSTRAINT "outlook_event_sync_diffs_source_version_id_fkey"
FOREIGN KEY ("source_version_id") REFERENCES "platform_meeting_attendance"."outlook_event_source_versions"("id")
ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE "platform_meeting_attendance"."outlook_event_sync_diffs"
ADD CONSTRAINT "outlook_event_sync_diffs_previous_source_version_id_fkey"
FOREIGN KEY ("previous_source_version_id") REFERENCES "platform_meeting_attendance"."outlook_event_source_versions"("id")
ON DELETE SET NULL ON UPDATE CASCADE;
