-- ============================================================================
-- NEXUS AI — Supabase schema (Postgres)
-- Run this in Supabase SQL Editor, or via `supabase db push`.
-- Design notes:
--   * workspace_id + user_id (Slack team_id / user_id) scope all data —
--     no separate auth system needed; Slack IS the identity provider.
--   * Every table has created_at for auditability and analytics.
--   * Indexes are added on the columns actually queried by the bot
--     (workspace_id, user_id, due_date, remind_at) to keep p95 latency low
--     even as workspaces scale to thousands of tasks.
-- ============================================================================

create extension if not exists "uuid-ossp";

-- ---------------------------------------------------------------------------
-- workspaces: one row per Slack team that has installed the app
-- ---------------------------------------------------------------------------
create table if not exists workspaces (
  id uuid primary key default uuid_generate_v4(),
  slack_team_id text unique not null,
  team_name text,
  installed_at timestamptz not null default now(),
  bot_access_token text -- encrypted at rest by Supabase; never logged
);

-- ---------------------------------------------------------------------------
-- users: Slack member scoped to a workspace
-- ---------------------------------------------------------------------------
create table if not exists users (
  id uuid primary key default uuid_generate_v4(),
  workspace_id uuid not null references workspaces(id) on delete cascade,
  slack_user_id text not null,
  display_name text,
  timezone text default 'UTC',
  created_at timestamptz not null default now(),
  unique (workspace_id, slack_user_id)
);

create index if not exists idx_users_workspace on users(workspace_id);

-- ---------------------------------------------------------------------------
-- tasks: core task-management entity, also used for deadline detection output
-- ---------------------------------------------------------------------------
create table if not exists tasks (
  id uuid primary key default uuid_generate_v4(),
  workspace_id uuid not null references workspaces(id) on delete cascade,
  user_id uuid not null references users(id) on delete cascade,
  title text not null,
  description text,
  status text not null default 'open' check (status in ('open','in_progress','done','cancelled')),
  priority text not null default 'medium' check (priority in ('low','medium','high','urgent')),
  due_date timestamptz,
  source_channel text,
  source_message_ts text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index if not exists idx_tasks_user_status on tasks(user_id, status);
create index if not exists idx_tasks_due_date on tasks(due_date) where status in ('open','in_progress');

-- ---------------------------------------------------------------------------
-- reminders: standalone time-based reminders (distinct from task due dates)
-- ---------------------------------------------------------------------------
create table if not exists reminders (
  id uuid primary key default uuid_generate_v4(),
  workspace_id uuid not null references workspaces(id) on delete cascade,
  user_id uuid not null references users(id) on delete cascade,
  message text not null,
  remind_at timestamptz not null,
  delivered boolean not null default false,
  created_at timestamptz not null default now()
);

create index if not exists idx_reminders_due on reminders(remind_at) where delivered = false;

-- ---------------------------------------------------------------------------
-- memory_entries: "personal memory" — notes, PDF summaries, saved Slack
-- messages. Powers intelligent search + meeting prep.
-- ---------------------------------------------------------------------------
create table if not exists memory_entries (
  id uuid primary key default uuid_generate_v4(),
  workspace_id uuid not null references workspaces(id) on delete cascade,
  user_id uuid not null references users(id) on delete cascade,
  content text not null,
  source_type text not null check (source_type in ('message','note','pdf','manual','image')),
  keywords text[], -- lightweight lexical index for fast search (see search.service.ts)
  created_at timestamptz not null default now()
);

create index if not exists idx_memory_user on memory_entries(user_id);
create index if not exists idx_memory_keywords on memory_entries using gin(keywords);

-- ---------------------------------------------------------------------------
-- conversation_history: rolling AI chat context per user (for /ask and DMs)
-- ---------------------------------------------------------------------------
create table if not exists conversation_history (
  id uuid primary key default uuid_generate_v4(),
  workspace_id uuid not null references workspaces(id) on delete cascade,
  user_id uuid not null references users(id) on delete cascade,
  role text not null check (role in ('user','assistant')),
  content text not null,
  created_at timestamptz not null default now()
);

create index if not exists idx_conversation_user_time on conversation_history(user_id, created_at desc);

-- ---------------------------------------------------------------------------
-- Housekeeping: trim conversation history so table doesn't grow unbounded.
-- Call periodically from a cron job (see services/digest.service.ts) or as a
-- Supabase scheduled function.
-- ---------------------------------------------------------------------------
create or replace function trim_conversation_history(p_user_id uuid, p_keep int default 40)
returns void as $$
begin
  delete from conversation_history
  where user_id = p_user_id
    and id not in (
      select id from conversation_history
      where user_id = p_user_id
      order by created_at desc
      limit p_keep
    );
end;
$$ language plpgsql;
