USE sec_fsnds;
GO

/*
============================================================================
 dbo.usp_sec_get_text_facts
============================================================================
 Narrative XBRL facts from the txt table. These are the free-form text
 disclosures -- accounting policies, risk factors, commitments, etc. --
 that are reported as XBRL elements instead of numeric values.

 Parameters
 ----------
   @adsh        accession (required)
   @tagFilter   optional LIKE pattern on tag name
   @minLength   drop rows where txtlen < this (default 0 = all)
   @maxLength   truncate displayed value to this many chars (default 2000)
   @top         max rows (default 500)

 Performance warning
 -------------------
 txt has ~48M rows / ~100 GB and the only index is PK on txtID. Without
 the IX_txt_sub index recommended in
 sql/indexes/sec_fsnds_recommended_indexes.sql, this SP does a full
 table scan on every call -- minutes, not milliseconds. If you plan
 to call this SP more than a few times, create that index first.

 Shape
 -----
   adsh | tag | ddate | qtrs | dimn | lang | txtlen | value_excerpt
============================================================================
*/

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

CREATE PROCEDURE dbo.usp_sec_get_text_facts
    @adsh      VARCHAR(20),
    @tagFilter NVARCHAR(400) = NULL,
    @minLength INT           = 0,
    @maxLength INT           = 2000,
    @top       INT           = 500
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;

    SELECT TOP (@top)
         @adsh          AS adsh
        ,t.tag
        ,x.ddate
        ,x.qtrs
        ,x.dimn
        ,l.lang
        ,x.txtlen
        ,LEFT(x.value, @maxLength) AS value_excerpt
    FROM dbo.txt x
    INNER JOIN dbo.tag      t ON t.tagID  = x.tagID
    LEFT  JOIN dbo.lkp_lang l ON l.langID = x.langID
    WHERE x.subID  = @subID
      AND x.txtlen >= @minLength
      AND (@tagFilter IS NULL OR t.tag LIKE @tagFilter)
    ORDER BY x.ddate, t.tag;
END;
GO

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

EXEC dbo.usp_sec_get_text_facts
     @adsh      = '0000320193-23-000106',
     @tagFilter = N'%Policy%',
     @minLength = 100;
*/
