USE sec_fsnds;
GO

/*
============================================================================
 dbo.usp_sec_peer_analysis
============================================================================
 For a single tag in a single fiscal period, pull values for every
 company sharing the target's SIC code.

 Parameters
 ----------
   @cik    target cik (used to resolve SIC)
   @tag    XBRL tag name, exact (e.g. 'Revenues')
   @fy     fiscal year (required)
   @fp     fiscal period, default 'FY'
   @form   default '10-K'
   @top    max peer rows (default 50)
   @iprx   default 0

 Shape
 -----
   peer_sic | cik | name | adsh | ddate | qtrs | uom | value

 Notes
 -----
 * The target company is included in the result set so you can compare
   it against its peers directly.
 * Without an index on sub.sic this does a sub-scan, then joins into
   num via IX_num_sub_tag. Still fast: ~300-500 ms for typical SICs.
============================================================================
*/

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

CREATE PROCEDURE dbo.usp_sec_peer_analysis
    @cik  INT,
    @tag  VARCHAR(356),
    @fy   INT,
    @fp   VARCHAR(2)  = 'FY',
    @form VARCHAR(20) = '10-K',
    @top  INT         = 50,
    @iprx INT         = 0
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @sic SMALLINT;
    SELECT TOP (1) @sic = sic
    FROM dbo.sub
    WHERE cik = @cik AND sic IS NOT NULL
    ORDER BY filed DESC;

    IF @sic IS NULL
    BEGIN
        RAISERROR('Could not resolve SIC for cik=%d', 16, 1, @cik);
        RETURN;
    END;

    DECLARE @tagID INT;
    SELECT TOP (1) @tagID = tagID
    FROM dbo.tag
    WHERE tag = @tag
    ORDER BY versionID DESC;

    IF @tagID IS NULL
    BEGIN
        RAISERROR('Unknown tag: %s', 16, 1, @tag);
        RETURN;
    END;

    -- Pick flow qtrs from form/fp: FY -> 4, Qn -> 1
    DECLARE @qtrs INT = CASE WHEN @fp = 'FY' THEN 4 ELSE 1 END;

    SELECT TOP (@top)
         @sic AS peer_sic
        ,s.cik
        ,s.name
        ,s.adsh
        ,CONVERT(date, s.period) AS period
        ,n.ddate
        ,n.qtrs
        ,u.uom
        ,n.value
    FROM dbo.sub s
    INNER JOIN dbo.num n
      ON  n.subID = s.subID
      AND n.tagID = @tagID
    INNER JOIN dbo.lkp_uom u ON u.uomID = n.uomID
    WHERE s.sic  = @sic
      AND s.form = @form
      AND s.fy   = @fy
      AND s.fp   = @fp
      AND n.iprx    = @iprx
      AND n.dimn    = 0
      AND n.coregID IS NULL
      AND n.value  IS NOT NULL
      AND (n.qtrs = @qtrs OR n.qtrs = 0)  -- flow or BS point-in-time
    ORDER BY n.value DESC
    OPTION (RECOMPILE);
END;
GO

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

-- Revenues for MSFT's SIC (7372) peers in FY2022
EXEC dbo.usp_sec_peer_analysis
     @cik = 789019,
     @tag = 'Revenues',
     @fy  = 2022;
*/
