-- Sakara Ops — Phase 1 schema (SQLite)
-- Telegram-related columns are included now (per SAKARA_OPS_TELEGRAM_AUTOMATION_SPEC)
-- so Phase 2 (Telegram wiring) doesn't require a migration.

CREATE TABLE IF NOT EXISTS users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  email TEXT UNIQUE NOT NULL,
  password_hash TEXT NOT NULL,
  role TEXT NOT NULL,                 -- ceo, admin, strategist, planner, creative_head, photo, video, designer
  telegram_chat_id TEXT,
  telegram_username TEXT,
  telegram_connected_at TEXT,
  is_active INTEGER NOT NULL DEFAULT 1,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS clients (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  code TEXT UNIQUE NOT NULL,          -- business code the client types at login, e.g. "AURUM"
  name TEXT NOT NULL,
  vertical TEXT,
  wa_group TEXT,
  planner_id INTEGER REFERENCES users(id),
  status TEXT NOT NULL DEFAULT 'active',
  contract_start TEXT,
  contract_end TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

-- A client can have multiple contacts (PICs); each logs in independently,
-- scoped only to their own client_id.
CREATE TABLE IF NOT EXISTS client_contacts (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  client_id INTEGER NOT NULL REFERENCES clients(id),
  name TEXT NOT NULL,
  email TEXT NOT NULL,
  password_hash TEXT NOT NULL,
  is_primary INTEGER NOT NULL DEFAULT 1,
  telegram_chat_id TEXT,
  telegram_username TEXT,
  telegram_connected_at TEXT,
  is_active INTEGER NOT NULL DEFAULT 1,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now')),
  UNIQUE(client_id, email)
);

CREATE TABLE IF NOT EXISTS workflow_steps (
  step_no INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  responsible TEXT,
  description TEXT,
  objective TEXT,
  consequence TEXT,
  checklist TEXT,  -- JSON array of strings — "Sakara's Checklist" sub-items for
                   -- this step, same list shown in the Job Desk view. A task's
                   -- reminder is considered done for all of these at once —
                   -- they aren't tracked individually outside Job Desk.
  sla_days INTEGER -- from Master Data > SLA & Aturan — how many days out the
                   -- next step's auto-generated task is due when this step
                   -- completes. NULL where SLA & Aturan has no explicit SLA
                   -- ("—") — falls back to a 2-day default in that case.
);

CREATE TABLE IF NOT EXISTS client_progress (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  client_id INTEGER NOT NULL REFERENCES clients(id),
  current_step INTEGER NOT NULL DEFAULT 1,
  overall_status TEXT NOT NULL DEFAULT 'ontrack',  -- ontrack | waiting | overdue
  days_in_step INTEGER NOT NULL DEFAULT 0,
  active_pic TEXT,        -- staff name actually on the hook right now for this step
                           -- (workflow_steps.responsible is a generic role template like
                           -- "Planner" — this is the specific person, for real "my tasks" views)
  satisfaction TEXT,       -- Happy | Neutral | At Risk — same pulse concept as
                            -- Step 17 Monthly Report in the original SOP
  note TEXT,
  updated_at TEXT NOT NULL DEFAULT (datetime('now')),
  UNIQUE(client_id)
);

CREATE TABLE IF NOT EXISTS tasks (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  client_id INTEGER NOT NULL REFERENCES clients(id),
  step_no INTEGER,
  step_name TEXT,
  title TEXT NOT NULL,
  description TEXT,
  assigned_user_id INTEGER REFERENCES users(id),
  client_contact_id INTEGER REFERENCES client_contacts(id),
  due_date TEXT,
  status TEXT NOT NULL DEFAULT 'incoming',  -- incoming | acknowledged | completed | cancelled | blocked
  internal_acknowledged_at TEXT,
  internal_acknowledged_by INTEGER REFERENCES users(id),
  client_acknowledged_at TEXT,
  client_acknowledged_by INTEGER REFERENCES client_contacts(id),
  completed_at TEXT,
  completed_by INTEGER REFERENCES users(id),
  cancelled_at TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS task_recipients (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  task_id INTEGER NOT NULL REFERENCES tasks(id),
  recipient_type TEXT NOT NULL,      -- internal | client
  user_id INTEGER REFERENCES users(id),
  client_contact_id INTEGER REFERENCES client_contacts(id),
  channel TEXT NOT NULL DEFAULT 'telegram',
  is_enabled INTEGER NOT NULL DEFAULT 1,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS notification_logs (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  task_id INTEGER REFERENCES tasks(id),
  recipient_type TEXT,
  recipient_user_id INTEGER,
  recipient_client_contact_id INTEGER,
  channel TEXT,
  reminder_type TEXT,               -- upcoming | due_today | overdue_1 | overdue_2_plus
  scheduled_for TEXT,
  sent_at TEXT,
  telegram_message_id TEXT,
  status TEXT,                      -- pending | sent | failed
  error_message TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS task_events (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  task_id INTEGER REFERENCES tasks(id),
  event_type TEXT,
  actor_type TEXT,                  -- internal | client | system
  actor_id TEXT,
  metadata TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

-- Full transcript of the Telegram bot conversation — every message sent
-- (direction='out') and every message/action received (direction='in'),
-- so admins can audit what a specific user/client actually saw and did.
-- Only covers activity from when this table was added onward; nothing
-- retroactive since raw message text was never stored before this.
CREATE TABLE IF NOT EXISTS telegram_message_log (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  chat_id TEXT NOT NULL,
  direction TEXT NOT NULL,          -- in | out
  text TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

-- Master Reminder — CMS for the wording every reminder email (and later,
-- Telegram message) actually sends. One row per (reminder_type, recipient_type,
-- channel) combination. subject/body use {{variable}} placeholders resolved by
-- lib/templateEngine.js at send time.
-- Proof-of-work uploaded against a task — from the web "Lampirkan bukti"
-- button (source='web') or a Telegram reply-photo (source='telegram').
CREATE TABLE IF NOT EXISTS task_attachments (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  task_id INTEGER NOT NULL REFERENCES tasks(id),
  file_name TEXT NOT NULL,
  url TEXT NOT NULL,
  source TEXT NOT NULL DEFAULT 'web',   -- web | telegram
  uploaded_by_type TEXT,                 -- internal | client
  uploaded_by_id INTEGER,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS reminder_templates (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  reminder_type TEXT NOT NULL,      -- upcoming | due_today | overdue_1 | overdue_2_plus
  recipient_type TEXT NOT NULL,     -- internal | client
  channel TEXT NOT NULL DEFAULT 'email',
  subject TEXT NOT NULL,
  body TEXT NOT NULL,
  updated_at TEXT NOT NULL DEFAULT (datetime('now')),
  UNIQUE(reminder_type, recipient_type, channel)
);