-- ============================================
-- Virtual Foreman — Supabase schema (DELTA)
-- Apply via Supabase SQL editor (read README.md first)
-- ============================================
--
-- The host project already ships with these tables (do NOT drop):
--   public.projects          — owned by an earlier deployment; columns
--                              are: name, contract_number, client,
--                              tech_supervisor, contractor,
--                              responsible_foreman, chief_engineer,
--                              id, created_at
--   public.workflow_history  — used by an n8n workflow; keep as-is
--
-- This file only ADDS the table that Virtual Foreman needs and is
-- idempotent (safe to re-run).

-- Inference history: one row per /work/infer call.
create table if not exists inference_history (
    id uuid primary key default gen_random_uuid(),
    -- project_id is left unconstrained because the host project's
    -- `projects` table uses different columns; valid UUIDs are accepted
    -- even when the foreign-table link is broken.
    project_id uuid,
    request_id text not null,
    source_text text,
    payload jsonb not null,
    model_used text,
    created_at timestamptz not null default now()
);

create index if not exists idx_inference_history_created_at
    on inference_history(created_at desc);

create index if not exists idx_inference_history_project_id
    on inference_history(project_id);

alter table inference_history enable row level security;

drop policy if exists "service_role_all_inference_history" on inference_history;
create policy "service_role_all_inference_history" on inference_history
    for all using (true) with check (true);

comment on table inference_history is
'Virtual Foreman: audit trail of /work/infer responses';

-- ============================================
-- Project-document ingestion (Phase 2)
-- Persists the uploaded PDF + per-page extracted text so a VLM call
-- can later reference its sibling pages as context.
-- ============================================

create table if not exists blueprint_uploads (
    id uuid primary key default gen_random_uuid(),
    project_id uuid,
    file_name text not null,
    storage_path text,             -- relative path under /uploads / bucket
    sha256 text not null,
    page_count int,
    has_native_text bool default true,
    mime_type text not null,
    status text default 'uploaded',
    created_at timestamptz not null default now()
);

create index if not exists idx_blueprint_uploads_project
    on blueprint_uploads(project_id);
create index if not exists idx_blueprint_uploads_created_at
    on blueprint_uploads(created_at desc);

alter table blueprint_uploads enable row level security;
drop policy if exists "service_role_all_blueprint_uploads" on blueprint_uploads;
create policy "service_role_all_blueprint_uploads" on blueprint_uploads
    for all using (true) with check (true);

comment on table blueprint_uploads is
'Virtual Foreman: stored upload artefacts (PDF/JPG/HEIC)';

create table if not exists blueprint_pages (
    id uuid primary key default gen_random_uuid(),
    upload_id uuid not null references blueprint_uploads(id) on delete cascade,
    page_no int not null,
    text text,                     -- full extracted text (native or empty)
    stamp text,                    -- e.g. 'АР-1', 'КЖ-3'
    title_guess text,              -- first non-trivial line / stamp label
    char_count int default 0,      -- to spot light vs text-heavy pages
    created_at timestamptz not null default now(),
    unique(upload_id, page_no)
);

create index if not exists idx_blueprint_pages_upload
    on blueprint_pages(upload_id, page_no);

alter table blueprint_pages enable row level security;
drop policy if exists "service_role_all_blueprint_pages" on blueprint_pages;
create policy "service_role_all_blueprint_pages" on blueprint_pages
    for all using (true) with check (true);

comment on table blueprint_pages is
'Virtual Foreman: per-page extracted text + stamp heuristics';