-- Dewx Local — SQLite master schema v1
-- =====================================
-- Mirrors the synced SUBSET of the cloud Postgres schema (packages/database).
-- Every synced table carries the four sync-contract columns:
--   id TEXT (UUID)  ·  updated_at  ·  deleted_at (tombstone)  ·  workspace_id (tenant)
-- Local user = exactly one workspace. Last-write-wins on updated_at.
--
-- SCHEMA_VERSION must match the cloud master-schema version. Sync refuses to run
-- if local and cloud versions differ (prevents silent drift).
PRAGMA journal_mode = WAL;
PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS _schema_meta (
  key   TEXT PRIMARY KEY,
  value TEXT NOT NULL
);
INSERT OR REPLACE INTO _schema_meta(key, value) VALUES ('schema_version', '1');

-- ── Channel-agnostic inbox ────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS unified_conversation (
  id            TEXT PRIMARY KEY,            -- UUID (matches cloud)
  workspace_id  TEXT NOT NULL,
  channel       TEXT NOT NULL,               -- WHATSAPP | LINKEDIN | GMAIL | ...
  account_id    TEXT,                         -- which local adapter account
  external_id   TEXT,                         -- provider chat id (Unipile chatId)
  title         TEXT,
  contact_id    TEXT,                         -- → contact.id (CRM link)
  last_ts       INTEGER,                      -- epoch ms of last message
  unread        INTEGER NOT NULL DEFAULT 0,
  archived_at   INTEGER,                      -- non-null = archived (hidden from All, shown in Archived)
  muted         INTEGER NOT NULL DEFAULT 0,   -- 1 = no notifications (still ingested + visible)
  updated_at    INTEGER NOT NULL,
  deleted_at    INTEGER
);
CREATE INDEX IF NOT EXISTS ix_conv_ws_last   ON unified_conversation(workspace_id, last_ts DESC);
CREATE INDEX IF NOT EXISTS ix_conv_contact   ON unified_conversation(contact_id);

CREATE TABLE IF NOT EXISTS unified_message (
  id              TEXT PRIMARY KEY,           -- UUID
  workspace_id    TEXT NOT NULL,
  conversation_id TEXT NOT NULL,
  direction       TEXT NOT NULL,              -- IN | OUT
  body            TEXT,
  sender          TEXT,
  ts              INTEGER NOT NULL,           -- epoch ms
  provider_msg_id TEXT,                       -- provider message id (idempotency)
  source          TEXT,                       -- local | web (origin tag for echo/push guards)
  attachments     TEXT,                       -- JSON
  media_type      TEXT,                        -- audio | image | video | document | sticker
  media_path      TEXT,                        -- local path to downloaded media
  reaction        TEXT,                         -- the user's emoji reaction on this message (empty = none)
  reactions       TEXT,                         -- JSON array of provider reactions when web sends full fidelity
  status          INTEGER,                      -- sent/delivered/read/failed receipt level
  updated_at      INTEGER NOT NULL,
  deleted_at      INTEGER,                    -- (cloud gap: add to UnifiedMessage too)
  FOREIGN KEY(conversation_id) REFERENCES unified_conversation(id)
);
CREATE INDEX IF NOT EXISTS ix_msg_conv_ts ON unified_message(conversation_id, ts);
CREATE UNIQUE INDEX IF NOT EXISTS ux_msg_provider ON unified_message(provider_msg_id)
  WHERE provider_msg_id IS NOT NULL;

-- ── CRM ───────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS contact (
  id           TEXT PRIMARY KEY,             -- UUID
  workspace_id TEXT NOT NULL,
  display_name TEXT,
  avatar_url   TEXT,
  avatar_path  TEXT,                         -- local path to a downloaded/address-book profile picture
  updated_at   INTEGER NOT NULL,
  deleted_at   INTEGER
);

-- one person, many channel handles (cross-channel identity)
CREATE TABLE IF NOT EXISTS contact_identity (
  id           TEXT PRIMARY KEY,             -- UUID
  workspace_id TEXT NOT NULL,
  contact_id   TEXT NOT NULL,
  channel      TEXT NOT NULL,                -- WHATSAPP | LINKEDIN | GMAIL ...
  handle       TEXT NOT NULL,                -- jid / email / linkedin urn
  email        TEXT,                         -- normalized email (matcher signal across channels)
  phone        TEXT,                         -- normalized digits (matcher signal across channels)
  updated_at   INTEGER NOT NULL,
  deleted_at   INTEGER,                      -- (cloud gap: add to ContactIdentity)
  FOREIGN KEY(contact_id) REFERENCES contact(id)
);
CREATE UNIQUE INDEX IF NOT EXISTS ux_identity ON contact_identity(workspace_id, channel, handle)
  WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS ix_identity_email ON contact_identity(email) WHERE email IS NOT NULL;
CREATE INDEX IF NOT EXISTS ix_identity_phone ON contact_identity(phone) WHERE phone IS NOT NULL;

-- Reversible record of a manual cross-channel merge: the surviving 'primary' contact absorbs the
-- 'merged' contact's conversations (their contact_id is repointed). conv_ids_json lists exactly which
-- conversations moved so unmerge can put them back. undone_at != NULL = the merge was reverted.
CREATE TABLE IF NOT EXISTS contact_merge (
  id                 TEXT PRIMARY KEY,
  workspace_id       TEXT NOT NULL,
  primary_contact_id TEXT NOT NULL,
  merged_contact_id  TEXT NOT NULL,
  signal             TEXT,                  -- email | phone | name | manual
  conv_ids_json      TEXT NOT NULL,         -- JSON array of unified_conversation.id moved by this merge
  created_at         INTEGER NOT NULL,
  undone_at          INTEGER
);
CREATE INDEX IF NOT EXISTS ix_merge_primary ON contact_merge(primary_contact_id, undone_at);

CREATE TABLE IF NOT EXISTS contact_tag (
  id           TEXT PRIMARY KEY,             -- UUID
  workspace_id TEXT NOT NULL,                -- (cloud gap: add ws scope to ContactTag)
  name         TEXT NOT NULL,
  color        TEXT,
  updated_at   INTEGER NOT NULL,             -- (cloud gap: add updatedAt)
  deleted_at   INTEGER
);

CREATE TABLE IF NOT EXISTS contact_tag_link (
  contact_id TEXT NOT NULL,
  tag_id     TEXT NOT NULL,
  updated_at INTEGER NOT NULL,
  deleted_at INTEGER,
  PRIMARY KEY(contact_id, tag_id)
);

CREATE TABLE IF NOT EXISTS contact_note (
  id           TEXT PRIMARY KEY,             -- UUID
  workspace_id TEXT NOT NULL,
  contact_id   TEXT NOT NULL,
  body         TEXT NOT NULL,
  ts           INTEGER NOT NULL,
  updated_at   INTEGER NOT NULL,
  deleted_at   INTEGER,
  FOREIGN KEY(contact_id) REFERENCES contact(id)
);

CREATE TABLE IF NOT EXISTS pipeline (
  id           TEXT PRIMARY KEY,
  workspace_id TEXT NOT NULL,
  name         TEXT NOT NULL,
  updated_at   INTEGER NOT NULL,
  deleted_at   INTEGER
);

CREATE TABLE IF NOT EXISTS deal (
  id           TEXT PRIMARY KEY,             -- UUID
  workspace_id TEXT NOT NULL,
  contact_id   TEXT,
  pipeline_id  TEXT,
  title        TEXT,
  stage        TEXT,
  value        REAL,
  updated_at   INTEGER NOT NULL,
  deleted_at   INTEGER,
  FOREIGN KEY(contact_id)  REFERENCES contact(id),
  FOREIGN KEY(pipeline_id) REFERENCES pipeline(id)
);

-- ── Sync bookkeeping (local-only, never synced) ────────────────────────────
CREATE TABLE IF NOT EXISTS _sync_state (
  table_name  TEXT PRIMARY KEY,
  last_pulled  INTEGER NOT NULL DEFAULT 0,   -- cloud updated_at watermark
  last_pushed  INTEGER NOT NULL DEFAULT 0
);

-- Web→local sync loop health, written by the MCP background worker and read by Settings.
CREATE TABLE IF NOT EXISTS sync_health (
  source           TEXT PRIMARY KEY,          -- bootstrap | inbox | crm
  status           TEXT NOT NULL,
  last_started_at  INTEGER,
  last_finished_at INTEGER,
  cursor           INTEGER NOT NULL DEFAULT 0,
  counts_json      TEXT NOT NULL DEFAULT '{}',
  last_error       TEXT,
  updated_at       INTEGER NOT NULL
);

-- Manual web→local pull requests queued by the Mac app; the MCP worker drains this table.
CREATE TABLE IF NOT EXISTS sync_command (
  id           TEXT PRIMARY KEY,
  workspace_id TEXT NOT NULL DEFAULT 'local',
  kind         TEXT NOT NULL DEFAULT 'web_pull',
  status       TEXT NOT NULL DEFAULT 'pending',
  source       TEXT NOT NULL DEFAULT 'ui',
  requested_at INTEGER NOT NULL,
  started_at   INTEGER,
  finished_at  INTEGER,
  error        TEXT
);
CREATE INDEX IF NOT EXISTS idx_sync_command_status ON sync_command(status, requested_at);

-- Signed-in web org/workspace tree and the active local partition.
CREATE TABLE IF NOT EXISTS sync_workspace (
  workspace_id   TEXT PRIMARY KEY,
  workspace_name TEXT NOT NULL,
  workspace_slug TEXT,
  org_id         TEXT NOT NULL,
  org_name       TEXT NOT NULL,
  org_slug       TEXT,
  is_default     INTEGER NOT NULL DEFAULT 0,
  updated_at     INTEGER NOT NULL
);
CREATE TABLE IF NOT EXISTS sync_workspace_selection (
  key          TEXT PRIMARY KEY,
  workspace_id TEXT NOT NULL,
  org_id       TEXT,
  updated_at   INTEGER NOT NULL
);

-- ── CRM extra tables (companies, deals, tasks) ─────────────────────────────
CREATE TABLE IF NOT EXISTS company (
  id           TEXT PRIMARY KEY,
  workspace_id TEXT NOT NULL,
  name         TEXT NOT NULL,
  industry     TEXT,
  domain       TEXT,
  updated_at   INTEGER NOT NULL,
  deleted_at   INTEGER
);
CREATE TABLE IF NOT EXISTS crm_deal (
  id           TEXT PRIMARY KEY,
  workspace_id TEXT NOT NULL,
  name         TEXT NOT NULL,
  company      TEXT,
  stage        TEXT,
  value        TEXT,
  close_date   TEXT,
  updated_at   INTEGER NOT NULL,
  deleted_at   INTEGER
);
CREATE TABLE IF NOT EXISTS crm_task (
  id           TEXT PRIMARY KEY,
  workspace_id TEXT NOT NULL,
  title        TEXT NOT NULL,
  due          TEXT,
  priority     TEXT,
  status       TEXT NOT NULL DEFAULT 'To do',
  updated_at   INTEGER NOT NULL,
  deleted_at   INTEGER
);

-- Local CRM edits queued for future web ingest. No web drain exists in this local-side item.
CREATE TABLE IF NOT EXISTS crm_mutation_queue (
  id              TEXT PRIMARY KEY,
  workspace_id    TEXT NOT NULL,
  entity_type     TEXT NOT NULL,             -- contact | company | deal
  entity_id       TEXT NOT NULL,
  action          TEXT NOT NULL,             -- create | update
  origin          TEXT NOT NULL,             -- swift-ui | mcp | import | ...
  payload_json    TEXT NOT NULL,
  idempotency_key TEXT NOT NULL,
  status          TEXT NOT NULL DEFAULT 'pending',
  attempts        INTEGER NOT NULL DEFAULT 0,
  error           TEXT,
  created_at      INTEGER NOT NULL,
  updated_at      INTEGER NOT NULL
);
CREATE UNIQUE INDEX IF NOT EXISTS ux_crm_mutation_pending_key
  ON crm_mutation_queue(idempotency_key) WHERE status='pending';
CREATE INDEX IF NOT EXISTS idx_crm_mutation_status
  ON crm_mutation_queue(workspace_id, status, created_at);

-- Outbound messages an MCP-connected agent wants to send, held for human approval.
-- The agent (via send_message) inserts status='pending'; the app surfaces it for approve/discard,
-- sends on approve, and marks status='sent'/'rejected'. Nothing leaves without explicit approval.
CREATE TABLE IF NOT EXISTS outbound_draft (
  id              TEXT PRIMARY KEY,
  workspace_id    TEXT NOT NULL DEFAULT 'local',
  conversation_id TEXT NOT NULL,
  channel         TEXT NOT NULL,
  account_id      TEXT NOT NULL,   -- engine account slug (e.g. WhatsApp 'wp')
  external_id     TEXT NOT NULL,   -- provider chat id / jid to send to
  title           TEXT,            -- contact/conversation label for the approval UI
  body            TEXT NOT NULL,
  status          TEXT NOT NULL DEFAULT 'pending',  -- pending | sent | rejected
  source          TEXT DEFAULT 'mcp',
  created_at      INTEGER NOT NULL,
  updated_at      INTEGER NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_outbound_draft_status ON outbound_draft(status, created_at);

-- Local AI draft metadata. This stores references/audit fields for composer drafts only.
-- It never queues delivery and is not read by any send path.
CREATE TABLE IF NOT EXISTS draft_metadata (
  id               TEXT PRIMARY KEY,
  workspace_id     TEXT NOT NULL DEFAULT 'local',
  contact_id       TEXT,
  conversation_id  TEXT,
  intent           TEXT NOT NULL,
  model            TEXT,
  prompt_ref       TEXT,
  result_ref       TEXT,
  used_message_ids TEXT,
  warnings         TEXT,
  created_at       INTEGER NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_draft_metadata_contact ON draft_metadata(contact_id, created_at);

-- Local audio transcripts generated by on-device WhisperKit. Message id is the cache key so
-- repeated "Transcribe" actions are idempotent. No cloud STT and no provider send path reads this.
CREATE TABLE IF NOT EXISTS audio_transcript (
  message_id   TEXT PRIMARY KEY,
  workspace_id TEXT NOT NULL DEFAULT 'local',
  audio_path   TEXT,
  transcript   TEXT NOT NULL,
  model        TEXT,
  created_at   INTEGER NOT NULL,
  updated_at   INTEGER NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_audio_transcript_updated ON audio_transcript(updated_at);

-- ── Call / meeting intelligence ───────────────────────────────────────────
CREATE TABLE IF NOT EXISTS call_recording (
  id                  TEXT PRIMARY KEY,
  workspace_id        TEXT NOT NULL DEFAULT 'local',
  calendar_event_id   TEXT,
  contact_id          TEXT,
  conversation_id     TEXT,
  title               TEXT,
  source_app          TEXT,
  source_bundle_id    TEXT,
  capture_backend     TEXT NOT NULL DEFAULT 'avaudiorecorder',
  capture_mode        TEXT NOT NULL DEFAULT 'mic_only',
  started_at          INTEGER NOT NULL,
  ended_at            INTEGER,
  duration_ms         INTEGER,
  audio_path          TEXT,
  mic_audio_path      TEXT,
  system_audio_path   TEXT,
  mixed_audio_path    TEXT,
  consent_required    INTEGER NOT NULL DEFAULT 1,
  consent_granted     INTEGER NOT NULL DEFAULT 0,
  consent_note        TEXT,
  disclosure_mode     TEXT,
  processing_status   TEXT NOT NULL DEFAULT 'queued',
  processing_error    TEXT,
  created_at          INTEGER NOT NULL,
  updated_at          INTEGER NOT NULL,
  deleted_at          INTEGER,
  FOREIGN KEY(contact_id) REFERENCES contact(id),
  FOREIGN KEY(conversation_id) REFERENCES unified_conversation(id)
);
CREATE INDEX IF NOT EXISTS idx_call_calendar_event ON call_recording(workspace_id, calendar_event_id);
CREATE INDEX IF NOT EXISTS idx_call_contact_started ON call_recording(contact_id, started_at DESC);
CREATE INDEX IF NOT EXISTS idx_call_conversation_started ON call_recording(conversation_id, started_at DESC);
CREATE INDEX IF NOT EXISTS idx_call_status ON call_recording(workspace_id, processing_status, updated_at);

CREATE TABLE IF NOT EXISTS call_recording_rule (
  id                    TEXT PRIMARY KEY,
  workspace_id          TEXT NOT NULL DEFAULT 'local',
  scope                 TEXT NOT NULL DEFAULT 'calendar_meetings',
  enabled               INTEGER NOT NULL DEFAULT 0,
  start_lead_seconds    INTEGER NOT NULL DEFAULT 60,
  stop_grace_seconds    INTEGER NOT NULL DEFAULT 120,
  require_meeting_app   INTEGER NOT NULL DEFAULT 0,
  consent_required      INTEGER NOT NULL DEFAULT 1,
  disclosure_mode       TEXT NOT NULL DEFAULT 'manual',
  created_at            INTEGER NOT NULL,
  updated_at            INTEGER NOT NULL
);

CREATE TABLE IF NOT EXISTS call_auto_record_state (
  id              TEXT PRIMARY KEY,
  workspace_id    TEXT NOT NULL DEFAULT 'local',
  calendar_event_id TEXT NOT NULL,
  title           TEXT NOT NULL,
  status          TEXT NOT NULL,
  call_id         TEXT,
  source_app      TEXT,
  reason          TEXT,
  started_at      INTEGER,
  stopped_at      INTEGER,
  updated_at      INTEGER NOT NULL
);
CREATE UNIQUE INDEX IF NOT EXISTS ux_call_auto_state_event
  ON call_auto_record_state(workspace_id, calendar_event_id);
CREATE INDEX IF NOT EXISTS idx_call_auto_state_status
  ON call_auto_record_state(workspace_id, status, updated_at);

CREATE TABLE IF NOT EXISTS call_transcript (
  id             TEXT PRIMARY KEY,
  workspace_id   TEXT NOT NULL DEFAULT 'local',
  call_id        TEXT NOT NULL,
  segment_index  INTEGER NOT NULL,
  speaker_label  TEXT,
  speaker_kind   TEXT NOT NULL DEFAULT 'unknown',
  track          TEXT NOT NULL DEFAULT 'mixed',
  start_ms       INTEGER,
  end_ms         INTEGER,
  text           TEXT NOT NULL,
  confidence     REAL,
  model          TEXT,
  created_at     INTEGER NOT NULL,
  updated_at     INTEGER NOT NULL,
  FOREIGN KEY(call_id) REFERENCES call_recording(id)
);
CREATE UNIQUE INDEX IF NOT EXISTS ux_call_transcript_segment ON call_transcript(call_id, segment_index);
CREATE INDEX IF NOT EXISTS idx_call_transcript_call_time ON call_transcript(call_id, start_ms);
CREATE INDEX IF NOT EXISTS idx_call_transcript_text ON call_transcript(text);

CREATE TABLE IF NOT EXISTS call_summary (
  id                 TEXT PRIMARY KEY,
  workspace_id       TEXT NOT NULL DEFAULT 'local',
  call_id            TEXT NOT NULL,
  summary            TEXT NOT NULL,
  key_points_json    TEXT NOT NULL DEFAULT '[]',
  decisions_json     TEXT NOT NULL DEFAULT '[]',
  action_items_json  TEXT NOT NULL DEFAULT '[]',
  followups_json     TEXT NOT NULL DEFAULT '[]',
  who_said_what_json TEXT NOT NULL DEFAULT '[]',
  warnings_json      TEXT NOT NULL DEFAULT '[]',
  model              TEXT,
  prompt_ref         TEXT,
  transcript_hash    TEXT,
  created_at         INTEGER NOT NULL,
  updated_at         INTEGER NOT NULL,
  FOREIGN KEY(call_id) REFERENCES call_recording(id)
);
CREATE UNIQUE INDEX IF NOT EXISTS ux_call_summary_call ON call_summary(call_id);

CREATE TABLE IF NOT EXISTS crm_update_proposal (
  id           TEXT PRIMARY KEY,
  workspace_id TEXT NOT NULL DEFAULT 'local',
  call_id      TEXT,
  contact_id   TEXT,
  deal_id      TEXT,
  kind         TEXT NOT NULL,
  title        TEXT NOT NULL,
  payload_json TEXT NOT NULL,
  confidence   REAL,
  status       TEXT NOT NULL DEFAULT 'pending',
  created_at   INTEGER NOT NULL,
  updated_at   INTEGER NOT NULL,
  applied_at   INTEGER,
  rejected_at  INTEGER,
  error        TEXT,
  FOREIGN KEY(call_id) REFERENCES call_recording(id),
  FOREIGN KEY(contact_id) REFERENCES contact(id),
  FOREIGN KEY(deal_id) REFERENCES deal(id)
);
CREATE INDEX IF NOT EXISTS ix_crm_update_proposal_status
  ON crm_update_proposal(workspace_id, status, created_at);
CREATE INDEX IF NOT EXISTS ix_crm_update_proposal_call
  ON crm_update_proposal(call_id, status);
