-- =====================================================================
-- callanalyst.app — Projects / Data Rooms feature
-- Supabase / PostgreSQL schema
-- Project: abnilsekuwoergkhupci.supabase.co
-- =====================================================================
-- Run this entire file in the Supabase SQL editor as a single migration.
-- pgvector is intentionally NOT used here. Embeddings are Phase 3.
-- =====================================================================

-- ---------------------------------------------------------------------
-- 0. Extensions
-- ---------------------------------------------------------------------
create extension if not exists "pgcrypto"; -- gen_random_uuid()

-- ---------------------------------------------------------------------
-- 1. Enum: project status
-- ---------------------------------------------------------------------
do $$
begin
  if not exists (select 1 from pg_type where typname = 'project_status') then
    create type project_status as enum ('active', 'archived');
  end if;
end$$;

-- ---------------------------------------------------------------------
-- 2. Tables
-- ---------------------------------------------------------------------

create table if not exists public.projects (
  id               uuid primary key default gen_random_uuid(),
  owner_id         uuid not null references auth.users(id) on delete cascade,
  name             text not null,
  company_name     text,
  status           project_status not null default 'active',
  source_call_id   text,            -- Fireflies/Otter/Fathom transcript id
  transcript_text  text,            -- full transcript, nullable for manual projects
  created_at       timestamptz not null default now(),
  updated_at       timestamptz not null default now()
);

-- Dedupe: one project per (owner, source_call_id) when set
create unique index if not exists projects_owner_source_call_uniq
  on public.projects (owner_id, source_call_id)
  where source_call_id is not null;

-- Projects list index
create index if not exists projects_owner_updated_idx
  on public.projects (owner_id, updated_at desc);

create table if not exists public.project_files (
  id              uuid primary key default gen_random_uuid(),
  project_id      uuid not null references public.projects(id) on delete cascade,
  owner_id        uuid not null references auth.users(id) on delete cascade,
  storage_path    text not null,   -- e.g. {owner_id}/{project_id}/{uuid}-{filename}
  name            text not null,
  mime            text,
  size_bytes      bigint,
  extracted_text  text,            -- populated by extractor job (PDF/docx text)
  created_at      timestamptz not null default now()
);

create index if not exists project_files_project_idx
  on public.project_files (project_id, created_at desc);

create table if not exists public.project_contacts (
  id           uuid primary key default gen_random_uuid(),
  project_id   uuid not null references public.projects(id) on delete cascade,
  owner_id     uuid not null references auth.users(id) on delete cascade,
  name         text not null,
  role         text,
  email        text,
  linkedin     text,
  notes        text,
  created_at   timestamptz not null default now()
);

create index if not exists project_contacts_project_idx
  on public.project_contacts (project_id, created_at desc);

create table if not exists public.project_notes (
  id           uuid primary key default gen_random_uuid(),
  project_id   uuid not null references public.projects(id) on delete cascade,
  owner_id     uuid not null references auth.users(id) on delete cascade,
  body         text not null,
  created_at   timestamptz not null default now()
);

create index if not exists project_notes_project_idx
  on public.project_notes (project_id, created_at desc);

-- ---------------------------------------------------------------------
-- 3. Triggers
-- ---------------------------------------------------------------------

-- 3a. Keep projects.updated_at fresh on any row update
create or replace function public.tg_projects_touch_updated()
returns trigger language plpgsql as $$
begin
  new.updated_at = now();
  return new;
end$$;

drop trigger if exists projects_touch_updated on public.projects;
create trigger projects_touch_updated
  before update on public.projects
  for each row execute function public.tg_projects_touch_updated();

-- 3b. Bump parent project's updated_at when a file is inserted
create or replace function public.tg_project_files_bump_parent()
returns trigger language plpgsql security definer as $$
begin
  update public.projects
     set updated_at = now()
   where id = new.project_id;
  return new;
end$$;

drop trigger if exists project_files_bump_parent on public.project_files;
create trigger project_files_bump_parent
  after insert on public.project_files
  for each row execute function public.tg_project_files_bump_parent();

-- ---------------------------------------------------------------------
-- 4. Row Level Security
-- ---------------------------------------------------------------------
alter table public.projects         enable row level security;
alter table public.project_files    enable row level security;
alter table public.project_contacts enable row level security;
alter table public.project_notes    enable row level security;

-- projects
drop policy if exists projects_select_own on public.projects;
create policy projects_select_own on public.projects
  for select using (owner_id = auth.uid());

drop policy if exists projects_insert_own on public.projects;
create policy projects_insert_own on public.projects
  for insert with check (owner_id = auth.uid());

drop policy if exists projects_update_own on public.projects;
create policy projects_update_own on public.projects
  for update using (owner_id = auth.uid())
              with check (owner_id = auth.uid());

drop policy if exists projects_delete_own on public.projects;
create policy projects_delete_own on public.projects
  for delete using (owner_id = auth.uid());

-- project_files
drop policy if exists project_files_select_own on public.project_files;
create policy project_files_select_own on public.project_files
  for select using (owner_id = auth.uid());

drop policy if exists project_files_insert_own on public.project_files;
create policy project_files_insert_own on public.project_files
  for insert with check (owner_id = auth.uid());

drop policy if exists project_files_update_own on public.project_files;
create policy project_files_update_own on public.project_files
  for update using (owner_id = auth.uid())
              with check (owner_id = auth.uid());

drop policy if exists project_files_delete_own on public.project_files;
create policy project_files_delete_own on public.project_files
  for delete using (owner_id = auth.uid());

-- project_contacts
drop policy if exists project_contacts_select_own on public.project_contacts;
create policy project_contacts_select_own on public.project_contacts
  for select using (owner_id = auth.uid());

drop policy if exists project_contacts_insert_own on public.project_contacts;
create policy project_contacts_insert_own on public.project_contacts
  for insert with check (owner_id = auth.uid());

drop policy if exists project_contacts_update_own on public.project_contacts;
create policy project_contacts_update_own on public.project_contacts
  for update using (owner_id = auth.uid())
              with check (owner_id = auth.uid());

drop policy if exists project_contacts_delete_own on public.project_contacts;
create policy project_contacts_delete_own on public.project_contacts
  for delete using (owner_id = auth.uid());

-- project_notes
drop policy if exists project_notes_select_own on public.project_notes;
create policy project_notes_select_own on public.project_notes
  for select using (owner_id = auth.uid());

drop policy if exists project_notes_insert_own on public.project_notes;
create policy project_notes_insert_own on public.project_notes
  for insert with check (owner_id = auth.uid());

drop policy if exists project_notes_update_own on public.project_notes;
create policy project_notes_update_own on public.project_notes
  for update using (owner_id = auth.uid())
              with check (owner_id = auth.uid());

drop policy if exists project_notes_delete_own on public.project_notes;
create policy project_notes_delete_own on public.project_notes
  for delete using (owner_id = auth.uid());

-- ---------------------------------------------------------------------
-- 5. Storage bucket + path-scoped RLS
-- ---------------------------------------------------------------------
-- Bucket layout convention:
--   {auth.uid()}/{project_id}/{uuid}-{filename}
-- The first path segment is always the owner's user id, which lets us
-- enforce per-user isolation purely from the storage path.

insert into storage.buckets (id, name, public)
values ('project-files', 'project-files', false)
on conflict (id) do nothing;

-- Path policies — first folder must equal auth.uid()
drop policy if exists project_files_storage_select on storage.objects;
create policy project_files_storage_select on storage.objects
  for select to authenticated
  using (
    bucket_id = 'project-files'
    and auth.uid()::text = (storage.foldername(name))[1]
  );

drop policy if exists project_files_storage_insert on storage.objects;
create policy project_files_storage_insert on storage.objects
  for insert to authenticated
  with check (
    bucket_id = 'project-files'
    and auth.uid()::text = (storage.foldername(name))[1]
  );

drop policy if exists project_files_storage_update on storage.objects;
create policy project_files_storage_update on storage.objects
  for update to authenticated
  using (
    bucket_id = 'project-files'
    and auth.uid()::text = (storage.foldername(name))[1]
  );

drop policy if exists project_files_storage_delete on storage.objects;
create policy project_files_storage_delete on storage.objects
  for delete to authenticated
  using (
    bucket_id = 'project-files'
    and auth.uid()::text = (storage.foldername(name))[1]
  );

-- =====================================================================
-- End of migration
-- =====================================================================
