-- schema.sql — full DDL for the jobhunt platform POC
-- Plain SQL, no ORM. Applied by the migration runner in db/migrate.py.

CREATE TABLE IF NOT EXISTS profile (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    full_name text NOT NULL,
    email text NOT NULL,
    phone text,
    location text,
    headline text,
    summary text,
    languages jsonb NOT NULL DEFAULT '[]',
    hard_rules jsonb NOT NULL DEFAULT '{}',
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS cv_section (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    profile_id uuid NOT NULL REFERENCES profile(id) ON DELETE CASCADE,
    kind text NOT NULL CHECK (kind IN ('experience','education','skills','projects','other')),
    title text NOT NULL,
    org text,
    location text,
    start_date date,
    end_date date,
    bullets jsonb NOT NULL DEFAULT '[]',
    tags text[] NOT NULL DEFAULT '{}',
    sort_order int NOT NULL DEFAULT 0,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS job_posting (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    source text NOT NULL,
    external_id text,
    url text NOT NULL,
    company text NOT NULL,
    title text NOT NULL,
    location text,
    description text NOT NULL DEFAULT '',
    raw jsonb NOT NULL DEFAULT '{}',
    fetched_at timestamptz NOT NULL DEFAULT now(),
    UNIQUE (source, url)
);

CREATE TABLE IF NOT EXISTS application (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    job_posting_id uuid NOT NULL REFERENCES job_posting(id) ON DELETE CASCADE,
    state text NOT NULL DEFAULT 'discovered' CHECK (state IN
        ('discovered','scored','approved','rejected','drafting','sent','interviewing','offer','closed','expired')),
    score numeric,
    score_rationale jsonb,
    notes text,
    state_changed_at timestamptz NOT NULL DEFAULT now(),
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS artifact (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    application_id uuid NOT NULL REFERENCES application(id) ON DELETE CASCADE,
    kind text NOT NULL CHECK (kind IN ('cv','cover_letter','email','other')),
    filename text NOT NULL,
    content_hash text NOT NULL,
    storage_path text NOT NULL,
    version int NOT NULL DEFAULT 1,
    origin text NOT NULL DEFAULT 'ai_reviewed' CHECK (origin IN ('user_drafted','ai_drafted','ai_reviewed')),
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS approval (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    application_id uuid NOT NULL REFERENCES application(id) ON DELETE CASCADE,
    artifact_id uuid NOT NULL REFERENCES artifact(id),
    artifact_hash text NOT NULL,
    action text NOT NULL CHECK (action IN ('send_email','submit_application')),
    confirmed_by_user boolean NOT NULL DEFAULT false,
    confirmed_at timestamptz,
    expires_at timestamptz NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS outbox (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    approval_id uuid NOT NULL REFERENCES approval(id),
    kind text NOT NULL DEFAULT 'email',
    payload jsonb NOT NULL,
    status text NOT NULL DEFAULT 'pending' CHECK (status IN ('pending','sent','failed','cancelled')),
    sent_at timestamptz,
    error text,
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS task_run (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    task text NOT NULL,
    model text NOT NULL,
    provider text NOT NULL,
    input_tokens int NOT NULL,
    output_tokens int NOT NULL,
    cost_usd numeric,
    duration_ms int NOT NULL,
    application_id uuid REFERENCES application(id),
    created_at timestamptz NOT NULL DEFAULT now()
);