-- 0005_plasmids.sql — per-user saved plasmid constructs (Plasmid Studio, M1+M2)
--
-- Run this in the Supabase SQL editor (Dashboard → SQL → New query → Run)
-- BEFORE/at deploy of the Plasmid Studio library. Idempotent: safe to run
-- more than once.
--
-- Mirrors public.crispr_designs (0004): owner-only Row Level Security, a
-- 90-day TTL via expires_at (NULL = keep forever, for future pro plans), and
-- indexes for the list query + the lazy expiry sweep. The TuringDNA Space
-- backend writes/reads with the service-role key (which bypasses RLS) and
-- enforces ownership in code by filtering on user_id; the RLS policies below
-- are the defense-in-depth layer for any direct, anon-key client access.

create table if not exists public.plasmids (
    id            uuid primary key default gen_random_uuid(),
    user_id       uuid not null references auth.users (id) on delete cascade,
    created_at    timestamptz not null default now(),
    expires_at    timestamptz,                       -- NULL = never expires
    name          text not null default 'Untitled plasmid',
    topology      text not null default 'circular',  -- 'circular' | 'linear'
    length        integer,
    gc            real,
    source        text,                              -- 'genbank' | 'fasta' | 'raw'
    sequence      text not null default '',
    features      jsonb not null default '[]'::jsonb,
    meta          jsonb not null default '{}'::jsonb
);

comment on table public.plasmids is
    'User-saved plasmid constructs. 90-day TTL (expires_at); owner-only RLS.';

alter table public.plasmids enable row level security;

-- Owner-only access. (Backend uses the service-role key and additionally
-- filters by user_id; these policies guard any direct client access.)
drop policy if exists "plasmids_select_own" on public.plasmids;
create policy "plasmids_select_own"
    on public.plasmids for select
    using (auth.uid() = user_id);

drop policy if exists "plasmids_insert_own" on public.plasmids;
create policy "plasmids_insert_own"
    on public.plasmids for insert
    with check (auth.uid() = user_id);

drop policy if exists "plasmids_update_own" on public.plasmids;
create policy "plasmids_update_own"
    on public.plasmids for update
    using (auth.uid() = user_id);

drop policy if exists "plasmids_delete_own" on public.plasmids;
create policy "plasmids_delete_own"
    on public.plasmids for delete
    using (auth.uid() = user_id);

-- List query: a user's plasmids, newest first.
create index if not exists plasmids_user_created_idx
    on public.plasmids (user_id, created_at desc);

-- Lazy expiry sweep target.
create index if not exists plasmids_expires_idx
    on public.plasmids (expires_at)
    where expires_at is not null;