new-site/api/migrations/068_usac_filing_history.sql

-- 068_usac_filing_history.sql
--
-- Stores scraped USAC E-File filing history per telecom entity.
-- Populated by the USAC audit scraper (usac_audit_scraper.py) when a
-- client grants us delegate access to their USAC account.
--
-- Each row represents one 499-A or 499-Q filing for a specific
-- reporting year. The audit compares what's filed vs what should
-- be filed to identify gaps and errors.

BEGIN;

CREATE TABLE IF NOT EXISTS usac_filing_history (
    id                      BIGSERIAL PRIMARY KEY,
    telecom_entity_id       INTEGER REFERENCES telecom_entities(id),
    frn                     TEXT NOT NULL,
    filer_id_499            TEXT,

    -- Filing identity
    form_type               TEXT NOT NULL CHECK (form_type IN ('499-A', '499-Q')),
    reporting_year          SMALLINT NOT NULL,
    reporting_quarter       SMALLINT,          -- 1-4 for 499-Q; NULL for 499-A

    -- Filing status from USAC
    filing_status           TEXT NOT NULL DEFAULT 'unknown'
                            CHECK (filing_status IN (
                                'filed', 'amended', 'missing', 'draft',
                                'rejected', 'unknown'
                            )),
    date_filed              DATE,
    date_amended            DATE,              -- most recent amendment date
    confirmation_number     TEXT,

    -- Revenue data from the filed form (cents)
    total_revenue_cents     BIGINT,
    interstate_revenue_cents BIGINT,
    international_revenue_cents BIGINT,
    contribution_amount_cents BIGINT,          -- what they owed USAC

    -- Line 105 categories reported
    line_105_primary        TEXT,
    line_105_categories     JSONB,

    -- De minimis / contributor status
    is_deminimis            BOOLEAN,
    is_contributor          BOOLEAN,

    -- Audit findings (populated by the audit report generator)
    audit_flag              TEXT CHECK (audit_flag IN (
                                NULL, 'ok', 'missing', 'revenue_suspect',
                                'wrong_category', 'should_amend', 'too_old'
                            )),
    audit_notes             TEXT,
    estimated_usf_owed_cents BIGINT,           -- retroactive estimate for missing years
    estimated_interest_cents BIGINT,

    -- Metadata
    scraped_at              TIMESTAMPTZ,       -- when we pulled this from USAC
    created_at              TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at              TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    UNIQUE (frn, form_type, reporting_year, reporting_quarter)
);

CREATE INDEX IF NOT EXISTS idx_usac_history_frn ON usac_filing_history(frn);
CREATE INDEX IF NOT EXISTS idx_usac_history_entity ON usac_filing_history(telecom_entity_id);
CREATE INDEX IF NOT EXISTS idx_usac_history_audit ON usac_filing_history(audit_flag)
    WHERE audit_flag IS NOT NULL AND audit_flag != 'ok';

-- Audit run tracking — one row per audit engagement
CREATE TABLE IF NOT EXISTS usac_audit_runs (
    id                      BIGSERIAL PRIMARY KEY,
    telecom_entity_id       INTEGER REFERENCES telecom_entities(id),
    frn                     TEXT NOT NULL,
    order_number            TEXT,              -- compliance_orders.order_number

    -- Access verification
    delegate_access_verified BOOLEAN NOT NULL DEFAULT FALSE,
    delegate_access_checked_at TIMESTAMPTZ,
    delegate_access_error   TEXT,

    -- Audit results summary
    years_audited           SMALLINT,          -- how many years we checked
    years_filed_ok          SMALLINT DEFAULT 0,
    years_missing           SMALLINT DEFAULT 0,
    years_needs_amendment   SMALLINT DEFAULT 0,
    years_too_old           SMALLINT DEFAULT 0, -- >5 years, needs USAC correspondence
    total_estimated_usf_owed_cents BIGINT DEFAULT 0,
    total_estimated_interest_cents BIGINT DEFAULT 0,

    -- Quote generated
    quote_new_filings_cents BIGINT,            -- price for missing years
    quote_amendments_cents  BIGINT,            -- price for amendments
    quote_total_cents       BIGINT,
    quote_sent_at           TIMESTAMPTZ,

    -- Report
    report_minio_path       TEXT,              -- path to the generated PDF
    report_sent_at          TIMESTAMPTZ,

    status                  TEXT NOT NULL DEFAULT 'pending'
                            CHECK (status IN (
                                'pending',          -- ordered, awaiting access
                                'access_verified',  -- delegate access confirmed
                                'scraping',         -- pulling filing history
                                'analyzing',        -- generating audit findings
                                'report_ready',     -- report + quote generated
                                'quote_sent',       -- emailed to client
                                'approved',         -- client approved the quote
                                'filing',           -- filing the missing/amended years
                                'completed',
                                'failed'
                            )),

    created_at              TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at              TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_usac_audit_frn ON usac_audit_runs(frn);
CREATE INDEX IF NOT EXISTS idx_usac_audit_status ON usac_audit_runs(status)
    WHERE status NOT IN ('completed', 'failed');

COMMIT;