USE sec_fsnds;
GO

/*
============================================================================
 dbo.usp_sec_get_filing_metadata
============================================================================
 Given an accession number (adsh), return everything you'd want to know
 about the filing *before* pulling the statements themselves:

   Result set 1: the sub row (company + filing metadata)
   Result set 2: row counts per statement in pre   (non-dimensional)
   Result set 3: row counts per statement in num   (dimn>0 vs dimn=0)
   Result set 4: text fact count from txt

 Parameters
 ----------
   @adsh   accession number

 Notes
 -----
 * Text fact count (result set 4) will be slow on sec_fsnds unless you
   add IX_txt_sub (see sql/indexes/sec_fsnds_recommended_indexes.sql).
   Without that index the txt read is a full scan of 48M rows / 100 GB.
============================================================================
*/

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

CREATE PROCEDURE dbo.usp_sec_get_filing_metadata
    @adsh VARCHAR(20)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @subID INT = (SELECT subID FROM dbo.sub WHERE adsh = @adsh);

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

    -- Result 1: full sub row
    SELECT
         s.subID, s.adsh, s.cik, s.name, s.sic, s.form
        ,CONVERT(date, s.period) AS period
        ,s.filed, s.accepted, s.fy, s.fp, s.fye
        ,s.countryba, s.stprba, s.cityba, s.zipba
        ,s.ein, s.wksi, s.prevrpt, s.detail, s.instance
        ,s.pubfloatusd, s.floatdate
    FROM dbo.sub s
    WHERE s.subID = @subID;

    -- Result 2: presentation row counts per statement
    SELECT
         ls.stmt
        ,ls.label
        ,COUNT(*)                                  AS pre_rows
        ,SUM(CASE WHEN p.inpth = 0 THEN 1 ELSE 0 END) AS pre_rows_main
        ,SUM(CASE WHEN p.inpth = 1 THEN 1 ELSE 0 END) AS pre_rows_parenthetical
    FROM dbo.pre p
    INNER JOIN dbo.lkp_stmt ls ON ls.stmtID = p.stmtID
    WHERE p.subID = @subID
    GROUP BY ls.stmtID, ls.stmt, ls.label
    ORDER BY ls.stmtID;

    -- Result 3: numeric fact counts (dimensional vs flat)
    SELECT
         COUNT(*)                                   AS num_rows_total
        ,SUM(CASE WHEN n.dimn = 0 THEN 1 ELSE 0 END) AS num_rows_flat
        ,SUM(CASE WHEN n.dimn > 0 THEN 1 ELSE 0 END) AS num_rows_dimensional
        ,SUM(CASE WHEN n.coregID IS NOT NULL THEN 1 ELSE 0 END) AS num_rows_coreg
        ,COUNT(DISTINCT n.tagID)                    AS distinct_tags
        ,MIN(n.ddate)                               AS min_ddate
        ,MAX(n.ddate)                               AS max_ddate
    FROM dbo.num n
    WHERE n.subID = @subID
    OPTION (RECOMPILE);

    -- Result 4: text facts
    SELECT
         COUNT(*)                 AS txt_rows_total
        ,COUNT(DISTINCT x.tagID)  AS distinct_text_tags
        ,SUM(CAST(x.txtlen AS BIGINT)) AS total_text_length_bytes
    FROM dbo.txt x
    WHERE x.subID = @subID;
END;
GO

/*
============================================================================
 Example call
============================================================================

EXEC dbo.usp_sec_get_filing_metadata @adsh = '0000950170-23-035122';
*/
