/* ---------------------------------------------------------------------------
   Report parameters are inlined rather than DECLAREd, so this is a single
   statement and can be used as a view or dropped straight into a reporting
   tool. The three tunables, if you need to change them:

     window start   DATEADD(year, -3, CAST(GETDATE() AS date))   (2 occurrences)
     "as of" date   CAST(GETDATE() AS date)                      (2 occurrences)
     duration floor 180                                          (commented out)

   Note: GETDATE() is now evaluated at several points rather than captured once.
   Each is wrapped in CAST(... AS date), so all of them agree unless the query
   happens to span midnight.
--------------------------------------------------------------------------- */

SELECT DISTINCT
    rid.return_id_num,
    l.callsign,
    ap.name,
    l.original_file_number,
    l.original_call_sign,
    CAST(l.issue_date AS DATE) AS issue_date,
    CAST(l.expiration_date AS DATE) AS expiration_date,
    CAST(l.status_date AS DATE) AS status_date,
    l.type_radio_srv,
    l.experiment_type,
    el.description AS experiment_type_description,
    CAST(a.date_entered AS DATE) AS date_entered,
    CAST(a.receipt_date AS DATE) AS receipt_date,
    sl.description AS status_description,
    cn.*,
    ul.first_name AS engineer_first_name,
    ul.last_name AS engineer_last_name,
    ul.email_address AS engineer_email_address,
    ul.phone_number AS engineer_phone_number,
    al.*,
    f.lower_frequency,
    f.upper_frequency,
    ch.date_opened,
    ch.date_closed,
    ch.comment,
    ch.date_created,
    ch1.description AS charge_description,
    CONCAT('https://apps.fcc.gov/oetcf/els/reports/442_print.cfm?mode=current&application_seq=', a.application_seq, '&license_seq=', l.license_seq) AS application_url,
    CONCAT('https://apps.fcc.gov/oetcf/els/reports/viewExhibitReport.cfm?id_file_num=', l.id_file_num, '&application_seq=', a.application_seq) AS exhibits_url,
    CONCAT('https://apps.fcc.gov/oetcf/els/reports/ViewCorrespodence.cfm?id_file_num=', l.id_file_num, '&application_seq=', a.application_seq) AS correspondence_url,
    CASE
        WHEN a.user_id = '1' THEN 'TRUE'
        ELSE 'FALSE'
    END AS Assigned,
    DATEDIFF(day, a.receipt_date, old_l.expiration_date) AS renewal_days,
    CASE
        WHEN l.id_file_num NOT LIKE '%R%' THEN ''
        WHEN old_l.expiration_date IS NULL THEN 'Unknown'
        WHEN DATEDIFF(day, a.receipt_date, old_l.expiration_date) >= 60 THEN 'Timely'
        WHEN DATEDIFF(day, a.receipt_date, old_l.expiration_date) BETWEEN 0 AND 59 THEN 'Untimely'
        WHEN DATEDIFF(day, a.receipt_date, old_l.expiration_date) < 0 THEN 'Lapsed'
        ELSE 'Unknown'
    END AS renewal_timeliness,

    /* ---- pending period -------------------------------------------------- */
    p.pending_start,
    p.pending_end,            -- NULL while open or unresolvable
    ps.pending_state,         -- Open | Closed | Unresolved
    pe.pending_end_basis,     -- which field the end date came from
    pd.pending_days           -- NULL when Unresolved

FROM APPLICATION a
INNER JOIN License l ON l.application_seq = a.application_seq
LEFT OUTER JOIN APPLICANT ap ON ap.applicant_id = a.applicant_id
LEFT OUTER JOIN STATUS_LOOKUP sl on sl.status = l.status
LEFT OUTER JOIN ADDRESS ad on ad.address_id = ap.address_id
LEFT OUTER JOIN CONTACT cn on cn.address_id = ap.address_id
LEFT OUTER JOIN USER_LOOKUP ul on ul.user_id = l.engineer_id
LEFT OUTER JOIN EXPERIMENT_TYPE_LOOKUP el on el.experiment_type = l.experiment_type
LEFT OUTER JOIN ANTENNA_LOCATION al on al.license_seq = l.license_seq
LEFT OUTER JOIN FREQUENCY f on f.antenna_id = al.antenna_id
LEFT OUTER JOIN CHARGEs ch on ch.license_id = l.license_id
LEFT OUTER JOIN CHARGE_CODE_LOOKUP ch1 on ch1.charge_code = ch.charge_code
LEFT OUTER JOIN (
        SELECT
            r.application_seq AS renewal_application_seq,
            old_base.id_file_num AS evaluated_license_file_num,
            old_base.expiration_date
        FROM RENEWAL r
        INNER JOIN LICENSE referred_l ON referred_l.license_seq = r.previous_license_seq
        INNER JOIN LICENSE old_base ON old_base.id_file_num =
            CASE
                WHEN referred_l.id_file_num LIKE '%CM%'
                    OR referred_l.id_file_num LIKE '%PM%'
                THEN referred_l.original_file_number
                ELSE referred_l.id_file_num
            END
        WHERE old_base.expiration_date IS NOT NULL
            AND old_base.status IN ('E','G')
) old_l
    ON old_l.renewal_application_seq = a.application_seq

/* ---------------------------------------------------------------------------
   return_id_num is computed here rather than in the SELECT list so that the
   exclusion filter in the WHERE clause can reference it. SQL Server cannot see
   SELECT-list aliases from WHERE, and the alternative is repeating the whole
   CASE expression in two places that then have to be kept in sync.
--------------------------------------------------------------------------- */
CROSS APPLY (VALUES (
        CASE
            --AL and TC records are children of AU and TU records.
            WHEN l.id_file_num LIKE '%AL%'
                OR l.id_file_num LIKE '%TC%'
            THEN A.ID_FILE_NUM
            ELSE l.id_file_num
        END
)) AS rid (return_id_num)

/* ---------------------------------------------------------------------------
   Pending window derivation.

   pending_end resolution order:
     1. Status is still P/AS  -> NULL, the clock is still running.
     2. issue_date populated  -> authoritative for granted / denied / expired.
     3. status_date populated AND not equal to expiration_date -> safe to use.
        The inequality test is what protects against the expiry overwrite: if
        status_date and expiration_date land on the same day, status_date has
        been stamped by expiration and cannot be read as a disposition date.
     4. Otherwise NULL -> flagged Unresolved rather than guessed at.
--------------------------------------------------------------------------- */
CROSS APPLY (VALUES (
        CAST(a.receipt_date AS date),
        CASE
            WHEN l.status IN ('P','AS')   THEN NULL
            WHEN l.issue_date IS NOT NULL THEN CAST(l.issue_date AS date)
            WHEN l.status_date IS NOT NULL
                 AND (l.expiration_date IS NULL
                      OR CAST(l.status_date AS date) <> CAST(l.expiration_date AS date))
                                          THEN CAST(l.status_date AS date)
            ELSE NULL
        END
)) AS p (pending_start, pending_end)

CROSS APPLY (VALUES (
        CASE
            WHEN l.status IN ('P','AS')    THEN 'Open'
            WHEN p.pending_end IS NOT NULL THEN 'Closed'
            ELSE 'Unresolved'
        END
)) AS ps (pending_state)

CROSS APPLY (VALUES (
        CASE
            WHEN l.status IN ('P','AS')   THEN 'still pending'
            WHEN l.issue_date IS NOT NULL THEN 'issue_date'
            WHEN p.pending_end IS NOT NULL THEN 'status_date'
            WHEN l.status_date IS NOT NULL THEN 'status_date overwritten by expiry'
            ELSE 'no usable date'
        END
)) AS pe (pending_end_basis)

CROSS APPLY (VALUES (
        CASE ps.pending_state
            WHEN 'Open'   THEN DATEDIFF(day, p.pending_start, CAST(GETDATE() AS date))
            WHEN 'Closed' THEN DATEDIFF(day, p.pending_start, p.pending_end)
            ELSE NULL
        END
)) AS pd (pending_days)

WHERE
/* ---- original record-selection logic, now parenthesised as a unit --------
   Without these outer parentheses the pending filter below would attach only
   to the second OR branch and the first branch would go unfiltered.
-------------------------------------------------------------------------- */
(
    (l.status IN ('P','AS')
        AND a.snapshot_flag IN ('C','I')
        AND a.receipt_date IS NOT NULL
        AND (
                a.snapshot_flag = 'C'
                OR NOT EXISTS (
                    SELECT 1
                    FROM APPLICATION a2
                    WHERE a2.id_file_num = a.id_file_num
                        AND a2.snapshot_flag = 'C'
                )
        )
        AND NOT EXISTS (
            SELECT 1
            FROM LICENSE l2
            INNER JOIN APPLICATION a2 ON a2.application_seq = l2.application_seq
            WHERE l2.id_file_num = l.id_file_num
                AND (a2.snapshot_flag = 'F' OR l2.status = 'D')
                )
        )
    OR (l.status NOT IN ('P','AS','I')
        AND a.snapshot_flag IN ('F','C')
        AND a.receipt_date IS NOT NULL
        AND l.id_file_num NOT LIKE '%TU%'
        AND l.id_file_num NOT LIKE '%AU%'
    )
)

/* ---- pending-window filter ----------------------------------------------
   Interval overlap, not "filed in the last 3 years". An application filed in
   2019 and granted in 2024 was pending across most of the window and belongs
   in the report.
-------------------------------------------------------------------------- */
AND p.pending_start IS NOT NULL
AND p.pending_start <= CAST(GETDATE() AS date)
AND (
        ps.pending_state = 'Open'                       -- open items always overlap
     OR p.pending_end >= DATEADD(year, -3, CAST(GETDATE() AS date))   -- closed inside the window
     OR (ps.pending_state = 'Unresolved'                -- no end date: fall back to start
         AND p.pending_start >= DATEADD(year, -3, CAST(GETDATE() AS date)))
)

/* ---- data quality exclusions --------------------------------------------
   status_date values before 2001 are not trustworthy. NULL status_date is
   kept: it is unknown, not bad. To drop those too, delete the IS NULL branch.

   The date literal is written YYYYMMDD deliberately - that form is unambiguous
   under every SET DATEFORMAT / SET LANGUAGE setting, whereas '2001-01-01' can
   be misread against a datetime column under some non-US settings.
-------------------------------------------------------------------------- */
AND (l.status_date IS NULL OR l.status_date >= '20010101')
AND rid.return_id_num NOT IN (
        '0028-EX-ML-2004',
        '0121-EX-PL-2004',
        '0305-EX-PL-2004',
        '0040-EX-ML-2005',
        '0174-EX-ST-2005',
        '0023-EX-TU-2007',
        '0020-EX-PN-2017'
)

/* Optional duration floor. Leaves Unresolved rows out entirely, since NULL
   >= 180 is never true - drop the line to keep them visible.  */
--AND pd.pending_days >= 180
