-- ============================================================
-- migrations/001_initial.sql
-- TranscriptIQ — initial database schema
-- Run once on the DGX PostgreSQL server:
--   psql -U postgres -d transcriptiq_db -f migrations/001_initial.sql
-- ============================================================

-- Enable UUID extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- ── workspaces ───────────────────────────────────────────────────────────────
CREATE TYPE plan_type AS ENUM ('starter', 'professional', 'enterprise');

CREATE TABLE IF NOT EXISTS workspaces (
    id                   UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name                 VARCHAR(255) NOT NULL,
    plan                 plan_type NOT NULL DEFAULT 'starter',
    stripe_customer_id   VARCHAR(255) UNIQUE,
    stripe_sub_id        VARCHAR(255),
    transcripts_used     INTEGER NOT NULL DEFAULT 0,
    projects_count       INTEGER NOT NULL DEFAULT 0,
    billing_cycle_start  TIMESTAMP NOT NULL DEFAULT NOW(),
    created_at           TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at           TIMESTAMP NOT NULL DEFAULT NOW()
);

-- ── users ─────────────────────────────────────────────────────────────────────
CREATE TYPE user_role AS ENUM ('admin', 'member', 'viewer');

CREATE TABLE IF NOT EXISTS users (
    id            UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    email         VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    full_name     VARCHAR(255),
    role          user_role NOT NULL DEFAULT 'member',
    workspace_id  UUID REFERENCES workspaces(id) ON DELETE SET NULL,
    created_at    TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at    TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_users_email        ON users(email);
CREATE INDEX IF NOT EXISTS idx_users_workspace_id ON users(workspace_id);

-- ── projects ──────────────────────────────────────────────────────────────────
CREATE TYPE project_status AS ENUM ('draft', 'processing', 'complete', 'failed');

CREATE TABLE IF NOT EXISTS projects (
    id           UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    workspace_id UUID NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
    owner_id     UUID NOT NULL REFERENCES users(id) ON DELETE SET NULL,
    name         VARCHAR(255) NOT NULL,
    domain       VARCHAR(100),
    framework    JSONB,
    status       project_status NOT NULL DEFAULT 'draft',
    created_at   TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at   TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_projects_workspace_id ON projects(workspace_id);
CREATE INDEX IF NOT EXISTS idx_projects_status       ON projects(status);

-- ── transcripts ───────────────────────────────────────────────────────────────
CREATE TYPE transcript_status AS ENUM (
    'uploaded', 'extracted', 'preprocessed', 'coded', 'complete', 'failed'
);

CREATE TABLE IF NOT EXISTS transcripts (
    id                UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    project_id        UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    original_filename VARCHAR(500) NOT NULL,
    file_path         VARCHAR(1000) NOT NULL,
    file_type         VARCHAR(20),
    raw_text          TEXT,
    cleaned_text      TEXT,
    chunks            JSONB,
    status            transcript_status NOT NULL DEFAULT 'uploaded',
    error_message     TEXT,
    created_at        TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at        TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_transcripts_project_id ON transcripts(project_id);
CREATE INDEX IF NOT EXISTS idx_transcripts_status     ON transcripts(status);

-- ── code_results ──────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS code_results (
    id                UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    project_id        UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    transcript_id     UUID NOT NULL REFERENCES transcripts(id) ON DELETE CASCADE,
    chunk_id          INTEGER,
    group_label       VARCHAR(255),
    theme             VARCHAR(255),
    code              VARCHAR(500),
    quote             TEXT,
    evidence_snippet  TEXT,
    excel_context     TEXT,
    llm_justification TEXT,
    created_at        TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_code_results_project_id    ON code_results(project_id);
CREATE INDEX IF NOT EXISTS idx_code_results_transcript_id ON code_results(transcript_id);
CREATE INDEX IF NOT EXISTS idx_code_results_group_label   ON code_results(group_label);

-- ── updated_at trigger ────────────────────────────────────────────────────────
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_workspaces_updated_at
    BEFORE UPDATE ON workspaces
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();

CREATE TRIGGER trg_users_updated_at
    BEFORE UPDATE ON users
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();

CREATE TRIGGER trg_projects_updated_at
    BEFORE UPDATE ON projects
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();

CREATE TRIGGER trg_transcripts_updated_at
    BEFORE UPDATE ON transcripts
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();

-- ── done ──────────────────────────────────────────────────────────────────────
-- Verify:
-- \dt
-- \d users
-- \d workspaces
