backend/database/migration_ingest_v2.sql

-- ============================================
-- Invoice Ingest Pipeline - Complete Schema
-- Version: 2.0
-- ============================================

-- Table 1: ingest_jobs
CREATE TABLE IF NOT EXISTS ingest_jobs (
    job_id TEXT PRIMARY KEY,
    doc_id TEXT,
    filename TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'queued',
    error_text TEXT,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
    updated_at TEXT DEFAULT CURRENT_TIMESTAMP
);

DROP INDEX IF EXISTS idx_ingest_jobs_status;
DROP INDEX IF EXISTS idx_ingest_jobs_created;
CREATE INDEX idx_ingest_jobs_status ON ingest_jobs(status);
CREATE INDEX idx_ingest_jobs_created ON ingest_jobs(created_at DESC);

-- Table 2: documents
CREATE TABLE IF NOT EXISTS documents (
    doc_id TEXT PRIMARY KEY,
    job_id TEXT NOT NULL,
    path TEXT NOT NULL,
    filename TEXT NOT NULL,
    content_type TEXT NOT NULL,
    uploaded_at TEXT DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (job_id) REFERENCES ingest_jobs(job_id)
);

DROP INDEX IF EXISTS idx_documents_job_id;
CREATE INDEX idx_documents_job_id ON documents(job_id);

-- Table 3: extractions
CREATE TABLE IF NOT EXISTS extractions (
    doc_id TEXT PRIMARY KEY,
    raw_text TEXT,
    tables_json TEXT,
    entities_json TEXT,
    classification_json TEXT,
    summary_text TEXT,
    extracted_at TEXT DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (doc_id) REFERENCES documents(doc_id)
);

-- Table 4: invoice_fields
CREATE TABLE IF NOT EXISTS invoice_fields (
    invoice_id INTEGER PRIMARY KEY AUTOINCREMENT,
    doc_id TEXT NOT NULL,
    cust_number TEXT,
    posting_date TEXT,
    total_open_amount REAL,
    business_code TEXT,
    cust_payment_terms TEXT,
    confidence_map TEXT,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (doc_id) REFERENCES documents(doc_id)
);

DROP INDEX IF EXISTS idx_invoice_fields_doc_id;
CREATE INDEX idx_invoice_fields_doc_id ON invoice_fields(doc_id);