USE sec_fsnds;
GO

/*
============================================================================
 dbo.usp_fsnds_get_statement
============================================================================
 Returns the primary-precision, non-dimensional, consolidated rows for a
 single filing's statement (BS / IS / CF / CI / EQ).

 Parameters:
   @adsh               accession, e.g. '0000950170-23-035122'
   @stmt               'BS' | 'IS' | 'CF' | 'CI' | 'EQ'
   @form               default '10-K'
   @fp                 default 'FY'  ('FY' | 'Q1' | 'Q2' | 'Q3' | 'H1' | 'H2' | ...)
   @menucat            default 'S'   (Statements)
   @inpth              default 0     (0 = not parenthetical, 1 = parenthetical)
   @iprx               default 0     (fsnds-local canonical precision; SEC raw uses 1)
   @includeComparative default 0     (1 = also emit prior fiscal-year comparative column)

 Behavior:
   * @qtrs is derived from @fp + @stmt: BS -> 0, IS/CF/CI/EQ -> 4 on FY, 1 on Qn.
   * CF "Cash at beginning of period" (proleID of periodStartLabel) and
     periodEndLabel rows are allowed through with qtrs=0 even though their
     ddate sits at the prior/current year-end, regardless of @qtrs.
   * fsnds-local convention: iprx=0 is the canonical (highest-precision) row in
     this build. SEC raw FSNDS zips use iprx>=1; this DB is 0-shifted.
   * Raises error 50001 if the adsh/fp/form combo doesn't exist.

 Performance note:
   OPTION (RECOMPILE) is set so the optimizer can embed @iprx=0 as a
   literal at plan time and match a filtered index of the shape
   (WHERE dimn=0 AND coregID IS NULL AND iprx=0). If you create such an
   index (see sql/indexes/sec_fsnds_recommended_indexes.sql), the
   hot-path num read drops ~15x. If no such index exists the plan just
   falls back to IX_num_sub_tag.
============================================================================
*/

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

CREATE PROCEDURE dbo.usp_fsnds_get_statement
    @adsh               VARCHAR(20),
    @stmt               VARCHAR(5),
    @form               VARCHAR(20) = '10-K',
    @fp                 VARCHAR(2)  = 'FY',
    @menucat            VARCHAR(1)  = 'S',
    @inpth              INT         = 0,
    @iprx               INT         = 0,
    @includeComparative BIT         = 0
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @qtrs INT =
        CASE
            WHEN @stmt IN ('IS','CF','CI','EQ')
                THEN CASE WHEN @fp = 'FY' THEN 4 ELSE 1 END
            ELSE 0
        END;

    DECLARE @stmtID  INT = (SELECT stmtID  FROM dbo.lkp_stmt  WHERE stmt  = @stmt);
    DECLARE @cfStart INT = (SELECT proleID FROM dbo.lkp_prole WHERE prole = 'periodStartLabel');
    DECLARE @cfEnd   INT = (SELECT proleID FROM dbo.lkp_prole WHERE prole = 'periodEndLabel');

    IF @stmtID IS NULL
    BEGIN
        RAISERROR('Unknown @stmt=%s (expected BS/IS/CF/CI/EQ)', 16, 1, @stmt);
        RETURN;
    END;

    DECLARE @subID  INT;
    DECLARE @period DATE;
    SELECT
        @subID  = subID,
        @period = CONVERT(date, period)
    FROM dbo.sub
    WHERE adsh = @adsh
      AND fp   = @fp
      AND form = @form;

    IF @subID IS NULL
    BEGIN
        RAISERROR('No matching sub row for adsh=%s fp=%s form=%s', 16, 1, @adsh, @fp, @form);
        RETURN;
    END;

    DECLARE @periodPrior DATE = DATEADD(year, -1, @period);

    SELECT
         x.[ddate]
        ,x.[plabel]
        ,x.[uom]
        ,IIF(
             CHARINDEX('PerShare', x.[tag]) = 0,
             FORMAT(x.[value], '#,##0'),
             FORMAT(x.[value], '#,##0.00')
         ) AS [value]
        ,x.[report]
        ,x.[line]
    FROM (
        SELECT
             n.[ddate]
            ,p.[plabel]
            ,t.[tag]
            ,u.[uom]
            ,IIF(p.[negating]=1, n.[value]*-1, n.[value]) AS [value]
            ,p.[report]
            ,p.[line]
        FROM dbo.pre p
        INNER JOIN dbo.ren r
          ON  r.[subID]  = p.[subID]
          AND r.[report] = p.[report]
        INNER JOIN dbo.num n
          ON  n.[subID]  = p.[subID]
          AND n.[tagID]  = p.[tagID]
        INNER JOIN dbo.tag t
          ON  t.[tagID]  = n.[tagID]
        INNER JOIN dbo.lkp_uom u
          ON  u.[uomID]  = n.[uomID]
        WHERE p.[subID]   = @subID
          AND p.[stmtID]  = @stmtID
          AND p.[inpth]   = @inpth
          AND r.[menucat] = @menucat
          AND n.[iprx]    = @iprx
          AND n.[dimn]    = 0
          AND n.[coregID] IS NULL
          AND n.[value]  IS NOT NULL
          AND n.[value] <> 0
          AND (
                (
                    n.[qtrs] = @qtrs
                AND (
                        n.[ddate] = @period
                     OR (@includeComparative = 1 AND n.[ddate] = @periodPrior)
                    )
                )
             OR (
                    p.[proleID] IN (@cfStart, @cfEnd)
                AND n.[qtrs]    = 0
                AND n.[ddate]   IN (@period, @periodPrior)
                )
              )
    ) AS x
    ORDER BY x.[ddate], x.[report], x.[line]
    OPTION (RECOMPILE);
END;
GO

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

-- MSFT FY2023 10-K Balance Sheet
EXEC dbo.usp_fsnds_get_statement
     @adsh = '0000950170-23-035122',
     @stmt = 'BS';

-- AAPL FY2023 10-K Income Statement with prior-year comparative
EXEC dbo.usp_fsnds_get_statement
     @adsh               = '0000320193-23-000106',
     @stmt               = 'IS',
     @includeComparative = 1;

-- 10-Q example
EXEC dbo.usp_fsnds_get_statement
     @adsh = '<some-10q-adsh>',
     @stmt = 'IS',
     @form = '10-Q',
     @fp   = 'Q2';
*/
