USE sec_fsnds;
GO

/*
============================================================================
 dbo.usp_fsnds_get_statement_series_combined
============================================================================
 Raw combined time-series: 10-K and 10-Q filings laid out side by side for
 one company. No math, no derivation - every value is pulled straight from
 the filing that reported it.

 When to use this one
 --------------------
 * You want an auditable, "what did this company actually file" view.
 * You want BS trended across every filing regardless of form.
 * You want IS/CF with 10-Q quarterly slices shown alongside 10-K annual
   totals, and you're OK reading two different period-lengths in the same
   grid.

 What it does NOT do
 -------------------
 * It does NOT derive Q4. For IS/CF, 10-K columns are full-year totals,
   10-Q columns are 3-month slices. Rows do NOT sum across quarters to
   reconcile with the FY column.
 * It does NOT show YTD 6-month or 9-month flows - only qtrs=1 from 10-Qs.

 Shape of the output
 -------------------
   plabel | tag | 2023-09-30 | 2023-12-31_FY | 2024-03-31 | 2024-06-30 | ...

 * 10-K columns are suffixed with "_FY".
 * BS uses the same suffix to make filing provenance visible.

 Parameters
 ----------
   @cik       company CIK (int)
   @stmt      'BS' | 'IS' | 'CF' | 'CI' | 'EQ'
   @fromDate  optional lower bound on filing period end
   @toDate    optional upper bound on filing period end
   @menucat   default 'S'
   @inpth     default 0
   @iprx      default 0
============================================================================
*/

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

CREATE PROCEDURE dbo.usp_fsnds_get_statement_series_combined
    @cik      INT,
    @stmt     VARCHAR(5),
    @fromDate DATE        = NULL,
    @toDate   DATE        = NULL,
    @menucat  VARCHAR(1)  = 'S',
    @inpth    INT         = 0,
    @iprx     INT         = 0
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @stmtID INT = (SELECT stmtID FROM dbo.lkp_stmt WHERE stmt = @stmt);
    IF @stmtID IS NULL
    BEGIN
        RAISERROR('Unknown @stmt=%s (expected BS/IS/CF/CI/EQ)', 16, 1, @stmt);
        RETURN;
    END;

    IF OBJECT_ID('tempdb..#filings') IS NOT NULL DROP TABLE #filings;
    CREATE TABLE #filings (
        subID      INT PRIMARY KEY,
        adsh       VARCHAR(20) COLLATE DATABASE_DEFAULT,
        form       VARCHAR(20) COLLATE DATABASE_DEFAULT,
        fy         INT,
        fp         VARCHAR(2)  COLLATE DATABASE_DEFAULT,
        period     DATE,
        use_qtrs   INT,
        period_col VARCHAR(20) COLLATE DATABASE_DEFAULT
    );

    INSERT INTO #filings (subID, adsh, form, fy, fp, period, use_qtrs, period_col)
    SELECT
         s.subID
        ,s.adsh
        ,s.form
        ,s.fy
        ,s.fp
        ,CONVERT(date, s.period)
        ,CASE
             WHEN @stmt = 'BS'    THEN 0
             WHEN s.form = '10-K' THEN 4
             ELSE 1
         END
        ,CASE
             WHEN s.form = '10-K'
                 THEN CONVERT(varchar(10), CONVERT(date, s.period), 120) + '_FY'
             ELSE CONVERT(varchar(10), CONVERT(date, s.period), 120)
         END
    FROM dbo.sub s
    WHERE s.cik  = @cik
      AND s.form IN ('10-K','10-Q')
      AND (@fromDate IS NULL OR CONVERT(date, s.period) >= @fromDate)
      AND (@toDate   IS NULL OR CONVERT(date, s.period) <= @toDate);

    IF NOT EXISTS (SELECT 1 FROM #filings)
    BEGIN
        RAISERROR('No 10-K or 10-Q filings for cik=%d in the requested range', 16, 1, @cik);
        RETURN;
    END;

    DECLARE @cols NVARCHAR(MAX);
    SELECT @cols = STRING_AGG(QUOTENAME(period_col), ',')
                   WITHIN GROUP (ORDER BY period, form)
    FROM #filings;

    DECLARE @sql NVARCHAR(MAX) = N'
    ;WITH src AS (
        SELECT
             f.period
            ,f.period_col
            ,t.tag
            ,p.plabel
            ,p.report
            ,p.line
            ,IIF(p.negating=1, n.value*-1, n.value) AS val
        FROM #filings f
        INNER JOIN dbo.pre p
          ON  p.subID  = f.subID
          AND p.stmtID = @stmtID
          AND p.inpth  = @inpth
        INNER JOIN dbo.ren r
          ON  r.subID   = p.subID
          AND r.report  = p.report
          AND r.menucat = @menucat
        INNER JOIN dbo.num n
          ON  n.subID   = p.subID
          AND n.tagID   = p.tagID
          AND n.qtrs    = f.use_qtrs
          AND n.ddate   = f.period
        INNER JOIN dbo.tag t
          ON  t.tagID = n.tagID
        WHERE n.iprx    = @iprx
          AND n.dimn    = 0
          AND n.coregID IS NULL
          AND n.value   IS NOT NULL
          AND n.value   <> 0
    ),
    dedup AS (
        SELECT
             period, period_col, tag, plabel, report, line, val
            ,ROW_NUMBER() OVER (PARTITION BY tag, period_col ORDER BY report, line) AS rn
        FROM src
    ),
    ref AS (
        SELECT
             tag, plabel, report, line
            ,ROW_NUMBER() OVER (PARTITION BY tag ORDER BY period DESC, report, line) AS rn_ref
        FROM dedup
        WHERE rn = 1
    )
    SELECT
         r.plabel
        ,r.tag
        ,' + @cols + N'
    FROM (
        SELECT tag, period_col, val FROM dedup WHERE rn = 1
    ) s
    PIVOT (
        MAX(val) FOR period_col IN (' + @cols + N')
    ) pvt
    INNER JOIN ref r
      ON  r.tag    = pvt.tag
      AND r.rn_ref = 1
    ORDER BY r.report, r.line
    OPTION (RECOMPILE);
    ';

    EXEC sp_executesql @sql,
        N'@stmtID INT, @menucat VARCHAR(1), @inpth INT, @iprx INT',
        @stmtID=@stmtID, @menucat=@menucat, @inpth=@inpth, @iprx=@iprx;

    DROP TABLE #filings;
END;
GO

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

EXEC dbo.usp_fsnds_get_statement_series_combined
     @cik  = 789019,
     @stmt = 'BS';

EXEC dbo.usp_fsnds_get_statement_series_combined
     @cik      = 320193,
     @stmt     = 'IS',
     @fromDate = '2020-01-01';
*/
