USE sec_fsnds;
GO

/*
============================================================================
 dbo.usp_sec_validate_statement_integrity
============================================================================
 Three integrity checks across every 10-K / 10-Q filing:

   BS_check  Assets == Liabilities + StockholdersEquity
             (uses StockholdersEquityIncludingPortionAttributableToNoncontrollingInterest
              if the plain tag is missing)

   CF_check  CashChange == CFO + CFI + CFF + FXEffect
             (accepts two equivalent cash-change tags)

   IS_check  NetIncomeLoss is present (presence-only -- IS line items are
             too heterogeneous for a universal arithmetic check across
             all filers without tag-dictionary mapping)

 A check passes when |lhs - rhs| <= @tolerance * MAX(|lhs|, |rhs|, 1).

 A NULL result on either side means the check could not be performed
 (missing tag -- not a failure).

 Parameters
 ----------
   @cik        optional: limit to one company
   @form       optional: '10-K' or '10-Q' (NULL = both)
   @fromFiled  optional: filed >= this date
   @toFiled    optional: filed <= this date
   @top        max filings to scan (default 10000). Set higher to run
               across the whole database.
   @tolerance  relative tolerance (default 0.01 = 1%)
   @onlyFails  1 = return only rows where at least one check FAILED
               (helps spot problem filings in a huge sweep)

 Result sets
 -----------
   Result 1: per-filing rows with all raw values and pass/fail flags
   Result 2: summary counts (checked / passed / failed / null per check)

 Usage
 -----
   -- Single company, both forms
   EXEC dbo.usp_sec_validate_statement_integrity @cik = 789019;

   -- Whole database, only 10-Ks, only show failures
   EXEC dbo.usp_sec_validate_statement_integrity
        @form      = '10-K',
        @top       = 1000000,
        @onlyFails = 1;

 Performance
 -----------
 The #facts staging step seeks num via IX_num_sub_tag once per
 (subID, tagID of interest) pair. With ~10 tags of interest and @top
 filings, expect roughly 10 * @top seeks. On typical hardware that
 runs at ~30K seeks/sec, so @top=10000 takes ~3 seconds; @top=1000000
 (full DB) takes ~5-10 minutes.
============================================================================
*/

IF OBJECT_ID('dbo.usp_sec_validate_statement_integrity', 'P') IS NOT NULL
    DROP PROCEDURE dbo.usp_sec_validate_statement_integrity;
GO

CREATE PROCEDURE dbo.usp_sec_validate_statement_integrity
    @cik       INT            = NULL,
    @form      VARCHAR(20)    = NULL,
    @fromFiled DATE           = NULL,
    @toFiled   DATE           = NULL,
    @top       INT            = 10000,
    @tolerance DECIMAL(18,6)  = 0.01,
    @onlyFails BIT            = 0
AS
BEGIN
    SET NOCOUNT ON;

    -- ------------------------------------------------------------
    -- 1. Build tag map: (group, tagID) for every tagID that shares a
    --    canonical tag name across all namespaces / taxonomy years.
    --    We can't just resolve a single "latest" tagID per name because
    --    older filings point at older versionIDs (e.g. us-gaap/2022).
    -- ------------------------------------------------------------
    IF OBJECT_ID('tempdb..#tag_map') IS NOT NULL DROP TABLE #tag_map;
    CREATE TABLE #tag_map (
        tag_group VARCHAR(30) COLLATE DATABASE_DEFAULT NOT NULL,
        tagID     INT         NOT NULL,
        PRIMARY KEY (tagID, tag_group)
    );

    INSERT INTO #tag_map (tag_group, tagID)
    SELECT grp, tagID
    FROM (
        VALUES
             ('Assets',    'Assets')
            ,('Liab',      'Liabilities')
            ,('Equity1',   'StockholdersEquity')
            ,('Equity2',   'StockholdersEquityIncludingPortionAttributableToNoncontrollingInterest')
            ,('CFO',       'NetCashProvidedByUsedInOperatingActivities')
            ,('CFI',       'NetCashProvidedByUsedInInvestingActivities')
            ,('CFF',       'NetCashProvidedByUsedInFinancingActivities')
            ,('FX1',       'EffectOfExchangeRateOnCashCashEquivalentsRestrictedCashAndRestrictedCashEquivalents')
            ,('FX2',       'EffectOfExchangeRateOnCashAndCashEquivalents')
            ,('CashChg1',  'CashCashEquivalentsRestrictedCashAndRestrictedCashEquivalentsPeriodIncreaseDecreaseIncludingExchangeRateEffect')
            ,('CashChg2',  'CashAndCashEquivalentsPeriodIncreaseDecreaseIncludingExchangeRateEffect')
            ,('CashChg3',  'CashAndCashEquivalentsPeriodIncreaseDecrease')
            ,('NI',        'NetIncomeLoss')
    ) AS m(grp, tag_name)
    INNER JOIN dbo.tag t
      ON  t.tag = m.tag_name COLLATE DATABASE_DEFAULT;

    -- ------------------------------------------------------------
    -- 2. Stage target filings
    -- ------------------------------------------------------------
    IF OBJECT_ID('tempdb..#targets') IS NOT NULL DROP TABLE #targets;
    CREATE TABLE #targets (
        subID      INT PRIMARY KEY,
        adsh       VARCHAR(20)   COLLATE DATABASE_DEFAULT,
        cik        INT,
        name       NVARCHAR(250) COLLATE DATABASE_DEFAULT,
        form       VARCHAR(20)   COLLATE DATABASE_DEFAULT,
        fy         SMALLINT,
        fp         CHAR(2)       COLLATE DATABASE_DEFAULT,
        period     DATE,
        flow_qtrs  TINYINT
    );

    INSERT INTO #targets (subID, adsh, cik, name, form, fy, fp, period, flow_qtrs)
    SELECT TOP (@top)
         s.subID
        ,s.adsh
        ,s.cik
        ,s.name
        ,s.form
        ,s.fy
        ,s.fp
        ,CONVERT(date, s.period)
        ,CASE WHEN s.form = '10-K' THEN CAST(4 AS tinyint) ELSE CAST(1 AS tinyint) END
    FROM dbo.sub s
    WHERE s.form IN ('10-K','10-Q')
      AND (@cik       IS NULL OR s.cik  = @cik)
      AND (@form      IS NULL OR s.form = @form)
      AND (@fromFiled IS NULL OR s.filed >= @fromFiled)
      AND (@toFiled   IS NULL OR s.filed <= @toFiled)
    ORDER BY s.filed DESC;

    -- ------------------------------------------------------------
    -- 3. Pull the facts of interest for the staged filings.
    --    One row per (subID, tag_group) we care about. The join to
    --    #tag_map collapses every version of the same canonical tag
    --    name into a single "group" so year-boundary crosses don't
    --    lose rows.
    -- ------------------------------------------------------------
    IF OBJECT_ID('tempdb..#facts') IS NOT NULL DROP TABLE #facts;
    CREATE TABLE #facts (
        subID     INT,
        tag_group VARCHAR(30) COLLATE DATABASE_DEFAULT,
        qtrs      TINYINT,
        ddate     DATE,
        value     DECIMAL(28,4)
    );

    INSERT INTO #facts (subID, tag_group, qtrs, ddate, value)
    SELECT
         n.subID, m.tag_group, n.qtrs, n.ddate, n.value
    FROM #targets t
    INNER JOIN dbo.num     n ON n.subID = t.subID
    INNER JOIN #tag_map    m ON m.tagID = n.tagID
    WHERE n.dimn    = 0
      AND n.coregID IS NULL
      AND n.iprx    = 0
      AND n.value  IS NOT NULL
    OPTION (RECOMPILE);

    CREATE INDEX IX_facts ON #facts (subID, tag_group, qtrs, ddate);

    -- ------------------------------------------------------------
    -- 4. Compute checks
    -- ------------------------------------------------------------
    ;WITH v AS (
        SELECT
             t.subID, t.adsh, t.cik, t.name, t.form, t.fy, t.fp, t.period, t.flow_qtrs

            -- BS (qtrs=0, ddate=period)
            ,(SELECT TOP (1) f.value FROM #facts f
              WHERE f.subID=t.subID AND f.tag_group='Assets'   AND f.qtrs=0 AND f.ddate=t.period) AS bs_assets
            ,(SELECT TOP (1) f.value FROM #facts f
              WHERE f.subID=t.subID AND f.tag_group='Liab'     AND f.qtrs=0 AND f.ddate=t.period) AS bs_liab
            ,(SELECT TOP (1) f.value FROM #facts f
              WHERE f.subID=t.subID AND f.tag_group='Equity1'  AND f.qtrs=0 AND f.ddate=t.period) AS bs_equity1
            ,(SELECT TOP (1) f.value FROM #facts f
              WHERE f.subID=t.subID AND f.tag_group='Equity2'  AND f.qtrs=0 AND f.ddate=t.period) AS bs_equity2

            -- CF (qtrs=flow_qtrs, ddate=period)
            ,(SELECT TOP (1) f.value FROM #facts f
              WHERE f.subID=t.subID AND f.tag_group='CFO'      AND f.qtrs=t.flow_qtrs AND f.ddate=t.period) AS cfo
            ,(SELECT TOP (1) f.value FROM #facts f
              WHERE f.subID=t.subID AND f.tag_group='CFI'      AND f.qtrs=t.flow_qtrs AND f.ddate=t.period) AS cfi
            ,(SELECT TOP (1) f.value FROM #facts f
              WHERE f.subID=t.subID AND f.tag_group='CFF'      AND f.qtrs=t.flow_qtrs AND f.ddate=t.period) AS cff
            ,(SELECT TOP (1) f.value FROM #facts f
              WHERE f.subID=t.subID AND f.tag_group='FX1'      AND f.qtrs=t.flow_qtrs AND f.ddate=t.period) AS fx1
            ,(SELECT TOP (1) f.value FROM #facts f
              WHERE f.subID=t.subID AND f.tag_group='FX2'      AND f.qtrs=t.flow_qtrs AND f.ddate=t.period) AS fx2
            ,(SELECT TOP (1) f.value FROM #facts f
              WHERE f.subID=t.subID AND f.tag_group='CashChg1' AND f.qtrs=t.flow_qtrs AND f.ddate=t.period) AS cash_chg1
            ,(SELECT TOP (1) f.value FROM #facts f
              WHERE f.subID=t.subID AND f.tag_group='CashChg2' AND f.qtrs=t.flow_qtrs AND f.ddate=t.period) AS cash_chg2
            ,(SELECT TOP (1) f.value FROM #facts f
              WHERE f.subID=t.subID AND f.tag_group='CashChg3' AND f.qtrs=t.flow_qtrs AND f.ddate=t.period) AS cash_chg3

            -- IS (presence only)
            ,(SELECT TOP (1) f.value FROM #facts f
              WHERE f.subID=t.subID AND f.tag_group='NI'       AND f.qtrs=t.flow_qtrs AND f.ddate=t.period) AS net_income
        FROM #targets t
    ),
    calc AS (
        SELECT
             v.*
            ,COALESCE(v.bs_equity1, v.bs_equity2)                        AS bs_equity
            ,COALESCE(v.cash_chg1, v.cash_chg2, v.cash_chg3)              AS cash_change
            ,COALESCE(v.fx1, v.fx2, 0)                                    AS fx_effect
        FROM v
    ),
    calc2 AS (
        SELECT
             c.*
            ,CASE WHEN c.bs_assets IS NOT NULL AND c.bs_liab IS NOT NULL AND c.bs_equity IS NOT NULL
                  THEN c.bs_liab + c.bs_equity END                        AS bs_liab_plus_equity
            ,CASE WHEN c.cfo IS NOT NULL AND c.cfi IS NOT NULL AND c.cff IS NOT NULL
                  THEN c.cfo + c.cfi + c.cff + c.fx_effect END            AS cf_sum_sections
        FROM calc c
    ),
    flagged AS (
        SELECT
             c.*
            -- BS check
            ,CASE
                WHEN c.bs_assets IS NULL OR c.bs_liab_plus_equity IS NULL
                    THEN NULL
                WHEN ABS(c.bs_assets - c.bs_liab_plus_equity)
                     <= @tolerance * (CASE WHEN ABS(c.bs_assets) > ABS(c.bs_liab_plus_equity)
                                           THEN ABS(c.bs_assets)
                                           ELSE ABS(c.bs_liab_plus_equity) END + 1)
                    THEN CAST(1 AS bit)
                ELSE CAST(0 AS bit)
             END AS bs_ok
            ,c.bs_assets - c.bs_liab_plus_equity AS bs_delta
            -- CF check
            ,CASE
                WHEN c.cash_change IS NULL OR c.cf_sum_sections IS NULL
                    THEN NULL
                WHEN ABS(c.cash_change - c.cf_sum_sections)
                     <= @tolerance * (CASE WHEN ABS(c.cash_change) > ABS(c.cf_sum_sections)
                                           THEN ABS(c.cash_change)
                                           ELSE ABS(c.cf_sum_sections) END + 1)
                    THEN CAST(1 AS bit)
                ELSE CAST(0 AS bit)
             END AS cf_ok
            ,c.cash_change - c.cf_sum_sections AS cf_delta
            -- IS check: presence only
            ,CASE WHEN c.net_income IS NULL THEN CAST(0 AS bit) ELSE CAST(1 AS bit) END AS is_ok
        FROM calc2 c
    )
    SELECT
         f.adsh, f.cik, f.name, f.form, f.fy, f.fp, f.period
        ,f.bs_assets, f.bs_liab, f.bs_equity, f.bs_liab_plus_equity, f.bs_delta, f.bs_ok
        ,f.cfo, f.cfi, f.cff, f.fx_effect, f.cash_change, f.cf_sum_sections, f.cf_delta, f.cf_ok
        ,f.net_income, f.is_ok
    FROM flagged f
    WHERE @onlyFails = 0
       OR (f.bs_ok = 0 OR f.cf_ok = 0 OR f.is_ok = 0)
    ORDER BY f.cik, f.form, f.period;

    -- ------------------------------------------------------------
    -- 5. Summary counts
    -- ------------------------------------------------------------
    ;WITH v AS (
        SELECT
             t.subID, t.flow_qtrs, t.period
            ,(SELECT TOP (1) f.value FROM #facts f
              WHERE f.subID=t.subID AND f.tag_group='Assets'   AND f.qtrs=0 AND f.ddate=t.period) AS bs_assets
            ,(SELECT TOP (1) f.value FROM #facts f
              WHERE f.subID=t.subID AND f.tag_group='Liab'     AND f.qtrs=0 AND f.ddate=t.period) AS bs_liab
            ,COALESCE(
                (SELECT TOP (1) f.value FROM #facts f
                 WHERE f.subID=t.subID AND f.tag_group='Equity1' AND f.qtrs=0 AND f.ddate=t.period),
                (SELECT TOP (1) f.value FROM #facts f
                 WHERE f.subID=t.subID AND f.tag_group='Equity2' AND f.qtrs=0 AND f.ddate=t.period)
             ) AS bs_equity
            ,(SELECT TOP (1) f.value FROM #facts f
              WHERE f.subID=t.subID AND f.tag_group='CFO'      AND f.qtrs=t.flow_qtrs AND f.ddate=t.period) AS cfo
            ,(SELECT TOP (1) f.value FROM #facts f
              WHERE f.subID=t.subID AND f.tag_group='CFI'      AND f.qtrs=t.flow_qtrs AND f.ddate=t.period) AS cfi
            ,(SELECT TOP (1) f.value FROM #facts f
              WHERE f.subID=t.subID AND f.tag_group='CFF'      AND f.qtrs=t.flow_qtrs AND f.ddate=t.period) AS cff
            ,COALESCE(
                (SELECT TOP (1) f.value FROM #facts f
                 WHERE f.subID=t.subID AND f.tag_group='CashChg1' AND f.qtrs=t.flow_qtrs AND f.ddate=t.period),
                (SELECT TOP (1) f.value FROM #facts f
                 WHERE f.subID=t.subID AND f.tag_group='CashChg2' AND f.qtrs=t.flow_qtrs AND f.ddate=t.period),
                (SELECT TOP (1) f.value FROM #facts f
                 WHERE f.subID=t.subID AND f.tag_group='CashChg3' AND f.qtrs=t.flow_qtrs AND f.ddate=t.period)
             ) AS cash_change
            ,COALESCE(
                (SELECT TOP (1) f.value FROM #facts f
                 WHERE f.subID=t.subID AND f.tag_group='FX1'      AND f.qtrs=t.flow_qtrs AND f.ddate=t.period),
                (SELECT TOP (1) f.value FROM #facts f
                 WHERE f.subID=t.subID AND f.tag_group='FX2'      AND f.qtrs=t.flow_qtrs AND f.ddate=t.period),
                0
             ) AS fx_effect
            ,(SELECT TOP (1) f.value FROM #facts f
              WHERE f.subID=t.subID AND f.tag_group='NI'       AND f.qtrs=t.flow_qtrs AND f.ddate=t.period) AS net_income
        FROM #targets t
    ),
    summary AS (
        SELECT
             COUNT(*) AS filings_scanned
            -- BS
            ,SUM(CASE WHEN bs_assets IS NOT NULL AND bs_liab IS NOT NULL AND bs_equity IS NOT NULL THEN 1 ELSE 0 END) AS bs_evaluable
            ,SUM(CASE
                   WHEN bs_assets IS NOT NULL AND bs_liab IS NOT NULL AND bs_equity IS NOT NULL
                    AND ABS(bs_assets - (bs_liab + bs_equity))
                        <= @tolerance * (CASE WHEN ABS(bs_assets) > ABS(bs_liab + bs_equity)
                                              THEN ABS(bs_assets)
                                              ELSE ABS(bs_liab + bs_equity) END + 1)
                   THEN 1 ELSE 0 END) AS bs_pass
            -- CF
            ,SUM(CASE WHEN cash_change IS NOT NULL AND cfo IS NOT NULL AND cfi IS NOT NULL AND cff IS NOT NULL THEN 1 ELSE 0 END) AS cf_evaluable
            ,SUM(CASE
                   WHEN cash_change IS NOT NULL AND cfo IS NOT NULL AND cfi IS NOT NULL AND cff IS NOT NULL
                    AND ABS(cash_change - (cfo + cfi + cff + fx_effect))
                        <= @tolerance * (CASE WHEN ABS(cash_change) > ABS(cfo + cfi + cff + fx_effect)
                                              THEN ABS(cash_change)
                                              ELSE ABS(cfo + cfi + cff + fx_effect) END + 1)
                   THEN 1 ELSE 0 END) AS cf_pass
            -- IS
            ,SUM(CASE WHEN net_income IS NOT NULL THEN 1 ELSE 0 END) AS is_pass
        FROM v
    )
    SELECT
         filings_scanned
        ,bs_evaluable, bs_pass, bs_evaluable - bs_pass AS bs_fail
        ,cf_evaluable, cf_pass, cf_evaluable - cf_pass AS cf_fail
        ,filings_scanned - is_pass AS is_missing
        ,CAST(100.0 * bs_pass / NULLIF(bs_evaluable,0) AS DECIMAL(5,2)) AS bs_pass_pct
        ,CAST(100.0 * cf_pass / NULLIF(cf_evaluable,0) AS DECIMAL(5,2)) AS cf_pass_pct
    FROM summary;

    DROP TABLE #facts;
    DROP TABLE #targets;
    DROP TABLE #tag_map;
END;
GO

/*
============================================================================
 Example calls
============================================================================

-- Single company sanity check
EXEC dbo.usp_sec_validate_statement_integrity @cik = 789019;

-- All 10-Ks filed in 2023, failures only
EXEC dbo.usp_sec_validate_statement_integrity
     @form      = '10-K',
     @fromFiled = '2023-01-01',
     @toFiled   = '2023-12-31',
     @top       = 100000,
     @onlyFails = 1;
*/
