USE sec_fsnds;
GO

/*
============================================================================
 dbo.usp_fsnds_get_statement_series_quarterly
============================================================================
 True quarterly time-series for IS / CF / CI / EQ statements. Produces one
 column per fiscal quarter (FYyyyyQ1..Q4) with Q4 computed from the 10-K.

 Why Q4 has to be derived
 ------------------------
 The SEC FSNDS raw data does not contain a standalone Q4 row. Every company
 reports Q1/Q2/Q3 in 10-Qs, but the fourth quarter is bundled into the 10-K
 as a full-fiscal-year number. There is no such thing as an "fp=Q4" filing.
 The only way to isolate Q4 is arithmetic:

     Q4 = 10-K annual (qtrs=4)  -  ( Q1 + Q2 + Q3 from that fy's 10-Qs )

 Q4 is NOT emitted if any of Q1/Q2/Q3 is missing for that tag (q_count=3
 guard). We don't fall back to "assume zero" because that silently
 inflates Q4.

 Not supported: BS
 -----------------
 If @stmt='BS' this SP raises. BS is point-in-time, so the arithmetic is
 meaningless. Use usp_fsnds_get_statement_series_combined for BS.

 Shape of the output
 -------------------
   plabel | tag | FY2022Q1 | FY2022Q2 | FY2022Q3 | FY2022Q4 | FY2023Q1 | ...

 Parameters
 ----------
   @cik       company CIK (int)
   @stmt      'IS' | 'CF' | 'CI' | 'EQ' (NOT 'BS')
   @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_quarterly', 'P') IS NOT NULL
    DROP PROCEDURE dbo.usp_fsnds_get_statement_series_quarterly;
GO

CREATE PROCEDURE dbo.usp_fsnds_get_statement_series_quarterly
    @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;

    IF @stmt = 'BS'
    BEGIN
        RAISERROR('BS is not supported. BS values are point-in-time. Use usp_fsnds_get_statement_series_combined.', 16, 1);
        RETURN;
    END;

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

    IF OBJECT_ID('tempdb..#quarters') IS NOT NULL DROP TABLE #quarters;
    CREATE TABLE #quarters (
        fy          INT            NOT NULL,
        qlabel      VARCHAR(2)     COLLATE DATABASE_DEFAULT NOT NULL,
        col_name    VARCHAR(20)    COLLATE DATABASE_DEFAULT NOT NULL,
        col_order   INT            NOT NULL,
        tag         VARCHAR(356)   COLLATE DATABASE_DEFAULT NOT NULL,
        val         DECIMAL(28,4)  NULL,
        plabel      NVARCHAR(512)  COLLATE DATABASE_DEFAULT NULL,
        report      INT            NULL,
        line        INT            NULL,
        ref_period  DATE           NULL
    );

    -- Pass 1: Q1/Q2/Q3 from 10-Qs
    INSERT INTO #quarters (fy, qlabel, col_name, col_order, tag, val, plabel, report, line, ref_period)
    SELECT
         s.fy
        ,s.fp
        ,CONCAT('FY', s.fy, s.fp)
        ,s.fy * 10 + CAST(SUBSTRING(s.fp, 2, 1) AS INT)
        ,t.tag
        ,IIF(p.negating=1, n.value*-1, n.value)
        ,p.plabel
        ,p.report
        ,p.line
        ,CONVERT(date, s.period)
    FROM dbo.sub s
    INNER JOIN dbo.pre p
      ON  p.subID  = s.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    = 1
      AND n.ddate   = CONVERT(date, s.period)
    INNER JOIN dbo.tag t
      ON  t.tagID = n.tagID
    WHERE s.cik   = @cik
      AND s.form  = '10-Q'
      AND s.fp    IN ('Q1','Q2','Q3')
      AND n.iprx    = @iprx
      AND n.dimn    = 0
      AND n.coregID IS NULL
      AND n.value   IS NOT NULL
      AND n.value   <> 0
      AND (@fromDate IS NULL OR CONVERT(date, s.period) >= @fromDate)
      AND (@toDate   IS NULL OR CONVERT(date, s.period) <= @toDate)
    OPTION (RECOMPILE);

    -- Pass 2: derive Q4 = 10-K annual - (Q1+Q2+Q3)
    ;WITH fy_k AS (
        SELECT
             s.fy
            ,CONVERT(date, s.period) AS k_period
            ,t.tag
            ,p.plabel
            ,p.report
            ,p.line
            ,IIF(p.negating=1, n.value*-1, n.value) AS fy_val
        FROM dbo.sub s
        INNER JOIN dbo.pre p
          ON  p.subID  = s.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    = 4
          AND n.ddate   = CONVERT(date, s.period)
        INNER JOIN dbo.tag t
          ON  t.tagID = n.tagID
        WHERE s.cik   = @cik
          AND s.form  = '10-K'
          AND s.fp    = 'FY'
          AND n.iprx    = @iprx
          AND n.dimn    = 0
          AND n.coregID IS NULL
          AND n.value   IS NOT NULL
          AND n.value   <> 0
          AND (@fromDate IS NULL OR CONVERT(date, s.period) >= @fromDate)
          AND (@toDate   IS NULL OR CONVERT(date, s.period) <= @toDate)
    ),
    q_sums AS (
        SELECT
             fy
            ,tag
            ,SUM(val) AS q123_sum
            ,COUNT(*) AS q_count
        FROM #quarters
        WHERE qlabel IN ('Q1','Q2','Q3')
        GROUP BY fy, tag
    )
    INSERT INTO #quarters (fy, qlabel, col_name, col_order, tag, val, plabel, report, line, ref_period)
    SELECT
         k.fy
        ,'Q4'
        ,CONCAT('FY', k.fy, 'Q4')
        ,k.fy * 10 + 4
        ,k.tag
        ,k.fy_val - qs.q123_sum
        ,k.plabel
        ,k.report
        ,k.line
        ,k.k_period
    FROM fy_k k
    INNER JOIN q_sums qs
      ON  qs.fy  = k.fy
      AND qs.tag = k.tag
    WHERE qs.q_count = 3
    OPTION (RECOMPILE);

    IF NOT EXISTS (SELECT 1 FROM #quarters)
    BEGIN
        RAISERROR('No quarterly data found for cik=%d stmt=%s in the requested range', 16, 1, @cik, @stmt);
        DROP TABLE #quarters;
        RETURN;
    END;

    DECLARE @cols NVARCHAR(MAX);
    SELECT @cols = STRING_AGG(QUOTENAME(col_name), ',') WITHIN GROUP (ORDER BY col_order)
    FROM (SELECT DISTINCT col_name, col_order FROM #quarters) u;

    DECLARE @sql NVARCHAR(MAX) = N'
    ;WITH dedup AS (
        SELECT
             fy, qlabel, col_name, col_order, tag, val, plabel, report, line, ref_period
            ,ROW_NUMBER() OVER (PARTITION BY tag, col_name ORDER BY report, line) AS rn
        FROM #quarters
    ),
    ref AS (
        SELECT
             tag, plabel, report, line
            ,ROW_NUMBER() OVER (PARTITION BY tag ORDER BY ref_period DESC, report, line) AS rn_ref
        FROM dedup
        WHERE rn = 1
    )
    SELECT
         r.plabel
        ,r.tag
        ,' + @cols + N'
    FROM (
        SELECT tag, col_name, val FROM dedup WHERE rn = 1
    ) s
    PIVOT (
        MAX(val) FOR col_name IN (' + @cols + N')
    ) pvt
    INNER JOIN ref r
      ON  r.tag    = pvt.tag
      AND r.rn_ref = 1
    ORDER BY r.report, r.line;
    ';

    EXEC sp_executesql @sql;

    DROP TABLE #quarters;
END;
GO

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

EXEC dbo.usp_fsnds_get_statement_series_quarterly
     @cik      = 789019,
     @stmt     = 'IS',
     @fromDate = '2020-07-01';

EXEC dbo.usp_fsnds_get_statement_series_quarterly
     @cik  = 320193,
     @stmt = 'CF';
*/
