new-site/api/migrations/055_reseller_certifications.sql

-- 055: Reseller certifications + non-contributing reseller tracking
--
-- 2026 FCC Form 499-A Section IV.C.4: to claim revenue on Line 303
-- (carrier's carrier), the filer must have an annually signed
-- certification from each reseller customer that:
--   (a) the reseller is purchasing for resale, at least in part, AND
--   (b) the reseller (or a downstream entity) contributes to USF.
--
-- Sample certification language lives in
-- scripts/document_gen/templates/reseller_cert_attestation.docx.
--
-- Line 511 (Block 5) separately tracks revenues from resellers that
-- DO NOT contribute (de minimis, intl-only, government). These revenues
-- are excluded from TRS/NANPA/LNP/ITSP contribution bases.

-- ── Reseller certifications (drives Line 303 eligibility) ───────────────
CREATE TABLE IF NOT EXISTS reseller_certifications (
    id                       BIGSERIAL PRIMARY KEY,

    -- The filer (our customer — the upstream wholesale provider)
    filer_telecom_entity_id  INTEGER NOT NULL REFERENCES telecom_entities(id)
                                     ON DELETE CASCADE,

    -- The reseller (their customer — the downstream carrier buying wholesale)
    reseller_filer_id_499    TEXT NOT NULL,          -- USAC Filer ID (6-8 digit)
    reseller_legal_name      TEXT NOT NULL,
    reseller_contact_name    TEXT,
    reseller_contact_email   TEXT,
    reseller_contact_phone   TEXT,
    reseller_legal_address   JSONB,                  -- {street,city,state,zip,country}

    -- The signed attestation
    certification_date       DATE NOT NULL,
    certification_text       TEXT NOT NULL,          -- full signed body
    certification_minio_path TEXT,                   -- signed PDF in MinIO
    signer_name              TEXT,
    signer_title             TEXT,

    -- Renewal tracking — renewal_worker.py emails at T-30/14/7/1
    renewal_due              DATE NOT NULL,          -- cert_date + 1 year
    status                   TEXT NOT NULL DEFAULT 'active'
                             CHECK (status IN ('active','expired','revoked')),

    -- First reporting year this certification covers
    reporting_year_first     SMALLINT,

    notes                    TEXT,
    created_at               TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at               TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    -- A filer can have the same reseller with different cert dates
    -- (annual renewals). Uniqueness is (filer, reseller, cert_date).
    UNIQUE (filer_telecom_entity_id, reseller_filer_id_499, certification_date)
);

CREATE INDEX IF NOT EXISTS idx_reseller_certs_filer_renewal
    ON reseller_certifications (filer_telecom_entity_id, renewal_due)
    WHERE status = 'active';
CREATE INDEX IF NOT EXISTS idx_reseller_certs_reseller_id
    ON reseller_certifications (reseller_filer_id_499);
CREATE INDEX IF NOT EXISTS idx_reseller_certs_renewal_due
    ON reseller_certifications (renewal_due)
    WHERE status = 'active';

-- ── Non-contributing reseller customers (Line 511) ──────────────────────
-- Separate table because these are reported on Line 511 (allowed deduction
-- from TRS/NANPA/LNP/ITSP bases) rather than supporting Line 303 revenue.
-- Every row requires the reseller's Filer ID per 2026 instructions.
CREATE TABLE IF NOT EXISTS non_contributing_reseller_customers (
    id                       BIGSERIAL PRIMARY KEY,
    filer_telecom_entity_id  INTEGER NOT NULL REFERENCES telecom_entities(id)
                                     ON DELETE CASCADE,
    reseller_filer_id_499    TEXT NOT NULL,
    reseller_legal_name      TEXT NOT NULL,
    non_contributing_reason  TEXT NOT NULL
        CHECK (non_contributing_reason IN (
            'de_minimis','intl_only','government','other'
        )),
    revenue_cents            BIGINT NOT NULL DEFAULT 0,
    reporting_year           SMALLINT NOT NULL,
    notes                    TEXT,
    created_at               TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (filer_telecom_entity_id, reseller_filer_id_499, reporting_year)
);

CREATE INDEX IF NOT EXISTS idx_nonctr_reseller_filer_year
    ON non_contributing_reseller_customers (filer_telecom_entity_id, reporting_year);