-- Xpace Chat schema v1. SQLite, WAL. Every table carries created_at where it matters. Times are ISO-8601 UTC strings.
PRAGMA journal_mode = WAL;
PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS meta (key TEXT PRIMARY KEY, value TEXT NOT NULL);

CREATE TABLE IF NOT EXISTS staff (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  email TEXT UNIQUE,
  phone TEXT,
  role TEXT NOT NULL DEFAULT 'salesperson',           -- salesperson | manager | admin
  rights TEXT NOT NULL DEFAULT '[]',                   -- json array of right names
  lang TEXT NOT NULL DEFAULT 'en',                     -- en | ms | zh
  password_hash TEXT,
  crm_staff_id INTEGER,
  on_leave_until TEXT,
  cover_staff_id INTEGER,
  gets_leads INTEGER NOT NULL DEFAULT 1,
  areas TEXT NOT NULL DEFAULT '[]',
  tone_set TEXT NOT NULL DEFAULT '',
  active INTEGER NOT NULL DEFAULT 1,
  service INTEGER NOT NULL DEFAULT 0,                  -- 1 = the stand-in user behind a link to another app (no login)
  created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now'))
);

CREATE TABLE IF NOT EXISTS channel_account (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  kind TEXT NOT NULL,                                  -- whatsapp_link | meta_cloud | wecom
  label TEXT NOT NULL DEFAULT '',
  phone TEXT NOT NULL DEFAULT '',
  owner_kind TEXT NOT NULL DEFAULT 'company',          -- company | own
  staff_id INTEGER,                                    -- responsible salesperson
  approvers TEXT NOT NULL DEFAULT '[]',                -- json staff ids
  state TEXT NOT NULL DEFAULT 'waiting',               -- waiting | linked | disconnected | error
  own_agreed_by INTEGER,
  own_agreed_at TEXT,
  meta TEXT NOT NULL DEFAULT '{}',                     -- json: phone_number_id, waba_id, quality, last_check, wecom ids...
  created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now'))
);

CREATE TABLE IF NOT EXISTS contact (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  kind TEXT NOT NULL DEFAULT 'customer',               -- customer | staff | external
  name TEXT NOT NULL DEFAULT '',
  phone_canon TEXT,                                    -- 60123456789
  phone_hash TEXT,
  wechat_id TEXT,
  company TEXT NOT NULL DEFAULT '',
  area TEXT NOT NULL DEFAULT '',
  source TEXT NOT NULL DEFAULT '',
  stage TEXT NOT NULL DEFAULT 'enquiry',
  consent_offers INTEGER NOT NULL DEFAULT 0,
  optout_at TEXT,
  blocked INTEGER NOT NULL DEFAULT 0,
  crm_lead_id INTEGER,
  crm_customer_id INTEGER,
  salesperson_id INTEGER,
  notes TEXT NOT NULL DEFAULT '',
  last_in_at TEXT,
  last_out_at TEXT,
  last_bulk_at TEXT,
  details TEXT NOT NULL DEFAULT '{}',                   -- what the customer told us (json): property, wants, sizes, budget, timeline, notes
  details_locked TEXT NOT NULL DEFAULT '[]',           -- keys a person typed: the AI never overwrites them
  ai_told_at TEXT,                                     -- when this customer was last told an AI assistant may reply
  created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now'))
);
CREATE INDEX IF NOT EXISTS contact_phone ON contact(phone_canon);

CREATE TABLE IF NOT EXISTS room (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  kind TEXT NOT NULL DEFAULT 'customer',               -- customer | project | team | dm
  title TEXT NOT NULL DEFAULT '',
  contact_id INTEGER,                                  -- customer rooms
  channel_account_id INTEGER,                          -- the account the customer wrote to (customer rooms)
  external_id TEXT,                                    -- WA group jid / WeCom chat id for team rooms
  crm_project_id INTEGER,
  taken_over_by INTEGER,
  created_by INTEGER,
  hidden INTEGER NOT NULL DEFAULT 0,                    -- a WhatsApp group from a personal phone that its owner has not picked
  settled_at TEXT,                                     -- group waiting list: a person marked the open point as settled at this time
  wait_alerted TEXT,                                   -- group waiting list: the message time an alert was already sent for
  assigned_agent TEXT,                                 -- 0.10.0: which AI agent looks after this chat (internal label, never shown to the customer)
  last_question_field TEXT,
  already_asked_fields TEXT NOT NULL DEFAULT '[]',
  ai_hold_until TEXT,
  last_at TEXT,
  created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now'))
);
CREATE INDEX IF NOT EXISTS room_contact ON room(contact_id);

CREATE TABLE IF NOT EXISTS room_member (
  room_id INTEGER NOT NULL,
  contact_id INTEGER,
  staff_id INTEGER,
  channel_account_id INTEGER,
  role TEXT NOT NULL DEFAULT 'member',
  PRIMARY KEY (room_id, contact_id, staff_id)
);

CREATE TABLE IF NOT EXISTS message (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  room_id INTEGER NOT NULL,
  channel_account_id INTEGER,
  dir TEXT NOT NULL,                                   -- in | out | system
  from_contact_id INTEGER,
  from_staff_id INTEGER,
  via TEXT NOT NULL DEFAULT 'crm',                     -- crm | phone | bot | bulk | followup | api
  text TEXT NOT NULL DEFAULT '',
  media TEXT,                                          -- json {type, url, caption}
  msg_type TEXT NOT NULL DEFAULT 'text',
  lang TEXT,
  translation TEXT,
  private INTEGER NOT NULL DEFAULT 0,                  -- own-number tray rows
  staff_only_id INTEGER,                               -- tray owner when private=1
  provider_msg_id TEXT,
  at TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now'))
);
CREATE INDEX IF NOT EXISTS message_room ON message(room_id, at);
CREATE UNIQUE INDEX IF NOT EXISTS message_provider ON message(channel_account_id, provider_msg_id) WHERE provider_msg_id IS NOT NULL;

CREATE TABLE IF NOT EXISTS draft (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  room_id INTEGER NOT NULL,
  to_contact_id INTEGER NOT NULL,
  channel_account_id INTEGER,
  text TEXT NOT NULL,
  original_text TEXT,
  intent TEXT NOT NULL DEFAULT 'other',
  drafter TEXT NOT NULL DEFAULT 'ai',                  -- ai | ai_check | ai_failed | ai_budget | mock | human | followup | autoreply | claude
  note TEXT NOT NULL DEFAULT '',
  status TEXT NOT NULL DEFAULT 'pending',              -- pending | approved | sent | would_send | rejected | scheduled | cancelled | blocked
  template_name TEXT,
  scheduled_at TEXT,
  created_by INTEGER,
  decided_by INTEGER,
  decided_at TEXT,
  reason TEXT,
  send_log_id INTEGER,
  media TEXT,                                          -- 0.9.9: JSON list of pictures / files that go out after the text
  created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now'))
);
CREATE INDEX IF NOT EXISTS draft_room ON draft(room_id, status);

CREATE TABLE IF NOT EXISTS send_log (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  draft_id INTEGER,
  bulk_id INTEGER,
  channel_account_id INTEGER,
  to_phone TEXT NOT NULL,
  body TEXT NOT NULL,
  mode TEXT NOT NULL,                                  -- live | dryrun
  status TEXT NOT NULL,                                -- sent | would_send | failed | blocked
  reason TEXT,
  category TEXT NOT NULL DEFAULT 'free',               -- free | marketing | utility | service
  est_cost REAL NOT NULL DEFAULT 0,
  would_cost REAL NOT NULL DEFAULT 0,
  provider_msg_id TEXT,
  at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now'))
);

CREATE TABLE IF NOT EXISTS followup_rule (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  stage TEXT NOT NULL,                                 -- enquiry | quotation | measurement | handover | any
  step INTEGER NOT NULL,
  days INTEGER NOT NULL,
  template_id INTEGER,
  active INTEGER NOT NULL DEFAULT 1
);

CREATE TABLE IF NOT EXISTS followup (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  contact_id INTEGER NOT NULL,
  room_id INTEGER NOT NULL,
  step INTEGER NOT NULL,
  due_at TEXT NOT NULL,
  draft_id INTEGER,
  state TEXT NOT NULL DEFAULT 'due',                   -- due | prepared | sent | skipped | stopped
  UNIQUE (contact_id, step)
);

CREATE TABLE IF NOT EXISTS message_template (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  kind TEXT NOT NULL DEFAULT 'plain',                  -- plain | template
  category TEXT NOT NULL DEFAULT 'utility',
  language TEXT NOT NULL DEFAULT 'en',
  body TEXT NOT NULL DEFAULT '',
  meta_name TEXT,
  status TEXT NOT NULL DEFAULT 'draft',                -- draft | sent_to_meta | approved | rejected | removed
  created_by INTEGER,
  created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now'))
);

CREATE TABLE IF NOT EXISTS bulk (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  filters TEXT NOT NULL DEFAULT '{}',
  template_id INTEGER,
  route TEXT NOT NULL DEFAULT 'auto',                  -- auto | meta | link
  when_json TEXT NOT NULL DEFAULT '{"mode":"on_approval"}',
  state TEXT NOT NULL DEFAULT 'draft',                 -- draft | pending_approval | approved | sending | paused | done | cancelled | rejected
  prepared_by INTEGER,
  approved_by INTEGER,
  est_cost REAL NOT NULL DEFAULT 0,
  batch_size INTEGER NOT NULL DEFAULT 20,
  batch_gap_min INTEGER NOT NULL DEFAULT 5,
  next_run_at TEXT,
  created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now'))
);

CREATE TABLE IF NOT EXISTS bulk_recipient (
  bulk_id INTEGER NOT NULL,
  contact_id INTEGER NOT NULL,
  state TEXT NOT NULL DEFAULT 'queued',                -- queued | sent | skipped | failed
  skip_reason TEXT,
  send_log_id INTEGER,
  PRIMARY KEY (bulk_id, contact_id)
);

CREATE TABLE IF NOT EXISTS task (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  room_id INTEGER,
  title TEXT NOT NULL,
  assignee_staff_id INTEGER,
  due_at TEXT,
  state TEXT NOT NULL DEFAULT 'open',                  -- open | done | cancelled
  source_message_id INTEGER,
  crm_task_id INTEGER,
  created_by INTEGER,
  done_at TEXT,
  created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now'))
);

CREATE TABLE IF NOT EXISTS approval (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  kind TEXT NOT NULL,                                  -- price | campaign | unknown_sender | task | other
  ref_table TEXT,
  ref_id INTEGER,
  text TEXT NOT NULL DEFAULT '',
  state TEXT NOT NULL DEFAULT 'pending',               -- pending | approved | rejected
  requested_by INTEGER,
  decided_by INTEGER,
  decided_at TEXT,
  created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now'))
);

CREATE TABLE IF NOT EXISTS ai_usage (
  month TEXT PRIMARY KEY,                              -- YYYY-MM
  calls INTEGER NOT NULL DEFAULT 0,
  fails INTEGER NOT NULL DEFAULT 0,
  tokens_in INTEGER NOT NULL DEFAULT 0,
  tokens_out INTEGER NOT NULL DEFAULT 0,
  est_rm REAL NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS setting (key TEXT PRIMARY KEY, value TEXT NOT NULL);

CREATE TABLE IF NOT EXISTS ignore_contact (
  phone_hash TEXT NOT NULL,
  channel_account_id INTEGER NOT NULL,
  PRIMARY KEY (phone_hash, channel_account_id)
);

CREATE TABLE IF NOT EXISTS audit (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  actor_staff_id INTEGER,
  action TEXT NOT NULL,
  ref TEXT NOT NULL DEFAULT '',
  before TEXT,
  after TEXT,
  at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now'))
);

CREATE TABLE IF NOT EXISTS session (
  token TEXT PRIMARY KEY,
  staff_id INTEGER NOT NULL,
  expires_at TEXT NOT NULL
);

-- Import from WA Internal AI Agent 1.6.0 (Perfex): source id -> new id, so re-running the import updates instead of duplicating.
CREATE TABLE IF NOT EXISTS import_map (
  src_table TEXT NOT NULL,
  src_id TEXT NOT NULL,
  new_id INTEGER NOT NULL,
  PRIMARY KEY (src_table, src_id)
);

-- Hosted mode (cPanel/LiteSpeed): jobs for the one copy that owns the WhatsApp links, and events for polling.
CREATE TABLE IF NOT EXISTS chan_cmd (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  op TEXT NOT NULL,                                    -- start | send | stop
  account_id INTEGER NOT NULL,
  payload TEXT NOT NULL DEFAULT '{}',
  state TEXT NOT NULL DEFAULT 'pending',               -- pending | running | done | failed
  result TEXT,
  created_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS chan_cmd_state ON chan_cmd(state, id);

CREATE TABLE IF NOT EXISTS event_log (
  seq INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  data TEXT NOT NULL,
  for_staff TEXT,
  at TEXT NOT NULL
);

-- Connect apps (pair-1): links to other apps, one-time pairing codes, and approve-screen requests.
-- Only hashes of keys we check, and encrypted copies of keys we present. Never a plain key.
CREATE TABLE IF NOT EXISTS links (
  id TEXT PRIMARY KEY,
  peer_kind TEXT NOT NULL,
  peer TEXT NOT NULL,
  peer_name TEXT NOT NULL DEFAULT '',
  peer_base_url TEXT NOT NULL DEFAULT '',
  peer_tenant_id TEXT NOT NULL DEFAULT '',
  peer_link_id TEXT NOT NULL DEFAULT '',
  direction TEXT NOT NULL DEFAULT 'both',
  access TEXT NOT NULL DEFAULT 'look',
  state TEXT NOT NULL DEFAULT 'pending',
  origin TEXT NOT NULL DEFAULT 'paired',
  via TEXT NOT NULL DEFAULT 'connect_page',
  inbound_key_hash TEXT NOT NULL DEFAULT '',
  outbound_key_enc TEXT NOT NULL DEFAULT '',
  event_secret_enc TEXT NOT NULL DEFAULT '',
  share_updates INTEGER NOT NULL DEFAULT 1,
  service_staff_id INTEGER,
  created_by INTEGER,
  created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
  last_tested_at TEXT,
  last_test_ok INTEGER,
  last_test_message TEXT,
  last_test_json TEXT,
  fail_count INTEGER NOT NULL DEFAULT 0,
  last_used_at TEXT,
  removed_at TEXT,
  removed_by TEXT,
  removed_note TEXT,
  peer_access TEXT
);
CREATE INDEX IF NOT EXISTS links_key ON links(inbound_key_hash);

CREATE TABLE IF NOT EXISTS pair_code (
  hash TEXT PRIMARY KEY,
  tenant_id TEXT NOT NULL DEFAULT '',
  approved_base_url TEXT,
  peer_app TEXT,
  peer_name TEXT,
  access TEXT NOT NULL DEFAULT 'look',
  state TEXT,
  created_by INTEGER,
  created_at TEXT NOT NULL,
  expires_at TEXT NOT NULL,
  tries INTEGER NOT NULL DEFAULT 0,
  status TEXT NOT NULL DEFAULT 'live'
);

-- Wrong pairing codes per sender address (contract v1.1 section 5): the fifth within 10 minutes locks that address for 10 minutes. The code itself is never touched.
CREATE TABLE IF NOT EXISTS pair_lock (
  ip TEXT PRIMARY KEY,
  tries TEXT NOT NULL DEFAULT '[]',
  locked_until TEXT,
  updated_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS pair_request (
  id TEXT PRIMARY KEY,
  from_url TEXT NOT NULL,
  from_name TEXT NOT NULL,
  from_app TEXT NOT NULL,
  state TEXT NOT NULL,
  return_url TEXT NOT NULL,
  created_at TEXT NOT NULL
);

-- Where this app is waiting for a peer to hand a code back (our side of the one-click flow).
CREATE TABLE IF NOT EXISTS pair_out (
  state TEXT PRIMARY KEY,
  peer_url TEXT NOT NULL,
  peer_app TEXT,
  tenant_id TEXT NOT NULL DEFAULT '',
  created_by INTEGER,
  created_at TEXT NOT NULL
);

-- Where each lead stands on its way to the paired CRM (0.3.0). Written as 'sending' BEFORE the request, so a crash mid-send is never mistaken for "not sent".
CREATE TABLE IF NOT EXISTS crm_push (
  contact_id INTEGER PRIMARY KEY,
  state TEXT NOT NULL,                 -- waiting | sending | sent | unsure
  code TEXT,                           -- why it is waiting or unsure: unreachable | key_rejected | paused | busy | look_only | look_stuck | rejected | unsure
  message TEXT,                        -- the plain sentence shown to the admin
  attempts INTEGER NOT NULL DEFAULT 0,
  crm_lead_id INTEGER,
  peer_base TEXT,                      -- the CRM address it went to, so a lead sent to another CRM is not taken for one that is in this CRM
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
);

-- Follow-up sequences (0.7.1): a preset list of messages that starts by itself when a lead meets the conditions.
CREATE TABLE IF NOT EXISTS sequence (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  active INTEGER NOT NULL DEFAULT 0,
  mode TEXT NOT NULL DEFAULT 'draft',                  -- draft (a person approves) | auto (sent by itself)
  send_from TEXT NOT NULL DEFAULT 'chat',              -- chat (the number the customer wrote to) | company | own (the salesperson's own phone)
  conditions TEXT NOT NULL DEFAULT '{}',               -- JSON: stages, areas, sources, salespeople, min_messages, offers, words
  position INTEGER NOT NULL DEFAULT 0,
  created_by INTEGER,
  created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now'))
);
CREATE TABLE IF NOT EXISTS sequence_step (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  sequence_id INTEGER NOT NULL,
  step INTEGER NOT NULL,
  wait_hours INTEGER NOT NULL DEFAULT 24,              -- hours after the lead joined (step 1) or after the step before
  send_time TEXT NOT NULL DEFAULT '',                  -- "10:00" = send at that time of day; empty = as soon as the wait is over
  text TEXT NOT NULL DEFAULT '',
  UNIQUE (sequence_id, step)
);
CREATE TABLE IF NOT EXISTS sequence_run (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  sequence_id INTEGER NOT NULL,
  contact_id INTEGER NOT NULL,
  state TEXT NOT NULL DEFAULT 'active',                -- active | done | stopped
  steps_done INTEGER NOT NULL DEFAULT 0,
  started_at TEXT NOT NULL,
  last_at TEXT,
  stop_reason TEXT,
  last_result TEXT NOT NULL DEFAULT '',
  UNIQUE (sequence_id, contact_id)
);

-- ---- 0.8.0: tags (a lead may carry many), saved lead groups, lead intake links ----
CREATE TABLE IF NOT EXISTS tag (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL UNIQUE,
  color TEXT NOT NULL DEFAULT 'blue',
  auto TEXT NOT NULL DEFAULT '',                       -- JSON rule: leads that match get this tag by themselves ('' = manual only)
  created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now'))
);
CREATE TABLE IF NOT EXISTS contact_tag (
  contact_id INTEGER NOT NULL,
  tag_id INTEGER NOT NULL,
  by_staff INTEGER,
  at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
  PRIMARY KEY (contact_id, tag_id)
);
CREATE INDEX IF NOT EXISTS contact_tag_tag ON contact_tag(tag_id);
CREATE TABLE IF NOT EXISTS lead_group (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  filters TEXT NOT NULL DEFAULT '{}',
  created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now'))
);
CREATE TABLE IF NOT EXISTS lead_source (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,                                  -- Xiaohongshu, Facebook, Website...
  key TEXT NOT NULL UNIQUE,                            -- the secret part of the form / webhook address
  tag_ids TEXT NOT NULL DEFAULT '[]',
  opener TEXT NOT NULL DEFAULT '',                     -- the first message our salespeople send to leads from here; a match makes the chat a lead by itself
  active INTEGER NOT NULL DEFAULT 1,
  received INTEGER NOT NULL DEFAULT 0,
  last_at TEXT,
  created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now'))
);

-- 0.9.0: customer feedback to pass on to the design team (WeChat). A person copies it over and ticks it as sent.
CREATE TABLE IF NOT EXISTS handoff (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  contact_id INTEGER,
  room_id INTEGER,
  text TEXT NOT NULL,
  media TEXT NOT NULL DEFAULT '[]',                    -- json [{message_id, type, caption}]
  state TEXT NOT NULL DEFAULT 'pending',               -- pending | sent | dropped
  made_by TEXT NOT NULL DEFAULT 'ai',                  -- ai | staff
  created_by INTEGER,
  done_by INTEGER,
  done_at TEXT,
  alerted INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now'))
);

-- 0.9.9: pictures of our finished jobs, sent to customers by the AI or by staff.
CREATE TABLE IF NOT EXISTS sample (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  category TEXT NOT NULL,                              -- kitchen | wardrobe | wardrobe_study | tv_display | shoe_storage | vanity
  title TEXT NOT NULL DEFAULT '',
  project TEXT NOT NULL DEFAULT '',                    -- the finished job (condo / project name), so photos from different jobs are mixed when sent
  file TEXT NOT NULL,                                  -- a file in <data>/media/samples
  active INTEGER NOT NULL DEFAULT 1,
  created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now'))
);
CREATE TABLE IF NOT EXISTS sample_sent (
  contact_id INTEGER NOT NULL,
  sample_id INTEGER NOT NULL,
  at TEXT NOT NULL,
  PRIMARY KEY(contact_id, sample_id)
);

-- 0.10.0 (AI Agents, phase 1). All additive; 0.9.9 ignores these tables, so going back to it is safe. Every table carries tenant_id.
CREATE TABLE IF NOT EXISTS agents (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  tenant_id TEXT NOT NULL DEFAULT '',
  key TEXT NOT NULL,                                   -- sales | design | support | followup
  name TEXT NOT NULL,
  job_text TEXT NOT NULL DEFAULT '',
  mode TEXT NOT NULL DEFAULT 'draft',                  -- off | shadow | draft | auto
  live_version_id INTEGER,
  enabled INTEGER NOT NULL DEFAULT 1,
  UNIQUE(tenant_id, key)
);
CREATE TABLE IF NOT EXISTS playbook_versions (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  tenant_id TEXT NOT NULL DEFAULT '',
  agent_key TEXT NOT NULL DEFAULT 'sales',
  number TEXT NOT NULL,                                -- 1.0.0
  status TEXT NOT NULL DEFAULT 'draft',                -- draft | testing | live | retired
  changelog TEXT NOT NULL DEFAULT '',
  created_by INTEGER,
  created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
  core_text TEXT NOT NULL DEFAULT '',                  -- the rules that apply to every reply
  modules_json TEXT NOT NULL DEFAULT '{}',             -- the agent's own module (handover topics, switches)
  facts_snapshot TEXT NOT NULL DEFAULT '',             -- the approved fact sheet at that time
  examples_json TEXT NOT NULL DEFAULT '{}',            -- quick replies, good chats, style examples
  guardrails_json TEXT NOT NULL DEFAULT '{}',
  test_run_id INTEGER,
  passed INTEGER NOT NULL DEFAULT 0,
  live_at TEXT,                                        -- 0.10.2: when it last went live (rollback goes back to the latest earlier one)
  rolled_back_at TEXT,
  source TEXT NOT NULL DEFAULT '',                     -- import | current | copy | settings
  UNIQUE(tenant_id, agent_key, number)
);
CREATE TABLE IF NOT EXISTS playbook_tests (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  tenant_id TEXT NOT NULL DEFAULT '',
  agent_key TEXT NOT NULL DEFAULT 'sales',
  name TEXT NOT NULL,
  customer_message TEXT NOT NULL DEFAULT '',
  must_do_json TEXT NOT NULL DEFAULT '[]',
  must_not_json TEXT NOT NULL DEFAULT '[]',
  critical INTEGER NOT NULL DEFAULT 0,
  ref TEXT NOT NULL DEFAULT '',                        -- 0.10.2: stable id of a built-in case, e.g. sales-01
  position INTEGER NOT NULL DEFAULT 0,
  kind TEXT NOT NULL DEFAULT 'ai',                     -- ai (the AI writes, a judge scores) | code (the app's own rules are run)
  history_json TEXT NOT NULL DEFAULT '[]',
  checks_json TEXT NOT NULL DEFAULT '[]',
  code_id TEXT NOT NULL DEFAULT ''
);
CREATE TABLE IF NOT EXISTS test_runs (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  tenant_id TEXT NOT NULL DEFAULT '',
  version_id INTEGER NOT NULL,
  started_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
  cost_rm REAL NOT NULL DEFAULT 0,
  results_json TEXT NOT NULL DEFAULT '[]',
  agent_key TEXT NOT NULL DEFAULT 'sales',             -- 0.10.2
  status TEXT NOT NULL DEFAULT 'running',              -- running | done | failed
  finished_at TEXT,
  model TEXT NOT NULL DEFAULT '',
  est_cost_rm REAL NOT NULL DEFAULT 0,
  total INTEGER NOT NULL DEFAULT 0,
  done INTEGER NOT NULL DEFAULT 0,
  avg_score REAL,
  gate_json TEXT NOT NULL DEFAULT '{}',
  started_by INTEGER
);
CREATE TABLE IF NOT EXISTS ai_reply_log (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  tenant_id TEXT NOT NULL DEFAULT '',
  draft_id INTEGER,
  chat_id INTEGER,
  lead_id INTEGER,
  agent_key TEXT NOT NULL DEFAULT 'sales',
  version_id INTEGER,
  model TEXT NOT NULL DEFAULT '',
  scenario_tag TEXT NOT NULL DEFAULT '',
  rules_triggered_json TEXT NOT NULL DEFAULT '[]',
  draft_text TEXT NOT NULL DEFAULT '',
  final_text TEXT,                                     -- what actually left, only after a successful send
  sent_by TEXT,                                        -- ai | human
  edited INTEGER NOT NULL DEFAULT 0,
  outcome TEXT NOT NULL DEFAULT 'pending',             -- pending | sent | rejected | blocked | held
  mode TEXT NOT NULL DEFAULT 'draft',
  tokens_in INTEGER NOT NULL DEFAULT 0,
  tokens_out INTEGER NOT NULL DEFAULT 0,
  cost_rm REAL NOT NULL DEFAULT 0,
  customer_reaction TEXT,                              -- replied | closed | optout
  created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now'))
);
CREATE INDEX IF NOT EXISTS ai_reply_log_draft ON ai_reply_log(draft_id);
CREATE INDEX IF NOT EXISTS ai_reply_log_chat ON ai_reply_log(chat_id, id);
CREATE TABLE IF NOT EXISTS lesson_items (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  tenant_id TEXT NOT NULL DEFAULT '',
  version_target INTEGER,
  agent_key TEXT NOT NULL DEFAULT 'sales',
  evidence_ref TEXT NOT NULL DEFAULT '',
  proposed_text TEXT NOT NULL DEFAULT '',
  status TEXT NOT NULL DEFAULT 'proposed'
);

-- 0.11.0 translation layer, phase 1 (read side by side). Additive only: older versions ignore these tables.
CREATE TABLE IF NOT EXISTS message_lang (
  message_id INTEGER PRIMARY KEY,
  tenant_id TEXT NOT NULL DEFAULT '',
  lang TEXT NOT NULL DEFAULT 'und',                    -- en | ms | zh | other | und (cannot tell)
  confidence REAL NOT NULL DEFAULT 0,
  detected_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now'))
);
CREATE TABLE IF NOT EXISTS message_translation (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  tenant_id TEXT NOT NULL DEFAULT '',
  message_id INTEGER NOT NULL,
  src_lang TEXT NOT NULL DEFAULT 'und',
  target_lang TEXT NOT NULL,
  text TEXT NOT NULL DEFAULT '',
  status TEXT NOT NULL DEFAULT 'done',                 -- done | failed
  error TEXT NOT NULL DEFAULT '',
  provider TEXT NOT NULL DEFAULT '',
  model TEXT NOT NULL DEFAULT '',
  tokens_in INTEGER NOT NULL DEFAULT 0,
  tokens_out INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
  UNIQUE(message_id, target_lang)
);
CREATE INDEX IF NOT EXISTS message_translation_msg ON message_translation(message_id);
CREATE TABLE IF NOT EXISTS translation_flag (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  tenant_id TEXT NOT NULL DEFAULT '',
  translation_id INTEGER NOT NULL,
  message_id INTEGER NOT NULL,
  room_id INTEGER NOT NULL DEFAULT 0,
  flagged_by INTEGER,
  note TEXT NOT NULL DEFAULT '',
  original_text TEXT NOT NULL DEFAULT '',              -- copied at flag time so the evidence survives edits and clean-ups
  translated_text TEXT NOT NULL DEFAULT '',
  src_lang TEXT NOT NULL DEFAULT '',
  target_lang TEXT NOT NULL DEFAULT '',
  status TEXT NOT NULL DEFAULT 'open',                 -- open | resolved
  resolved_by INTEGER,
  created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
  UNIQUE(translation_id, flagged_by)
);
CREATE TABLE IF NOT EXISTS reading_pref (
  staff_id INTEGER PRIMARY KEY,
  tenant_id TEXT NOT NULL DEFAULT '',
  lang TEXT NOT NULL DEFAULT '',                       -- '' = follow the language chosen in Me
  mode TEXT NOT NULL DEFAULT 'auto'                    -- auto | translated | bilingual
);
