-- FDA Exclusivity Export — Example Queries (Evaluation Sample)
-- Run against fda_exclusivity.db (SQLite 3)
--
-- EVALUATION USE ONLY. This sample is licensed for evaluation purposes only under
-- the RxDataLab Terms of Service (https://rxdatalab.com/terms-of-service).
-- Commercial use, redistribution, and sharing are not permitted.
--
-- NOTE: This sample contains ~200 NDAs and ~44 BLAs. ANDA (generic application)
-- data is not included, so the first_generic_entry view is empty and the
-- "Products that have lost exclusivity" query is omitted. The full dataset
-- includes ~22,000 ANDAs and ~2,400 formulations with generic competition.

-- ============================================================
-- ORANGE BOOK (NDA/ANDA) QUERIES
-- ============================================================

-- All active NDA protections expiring in the next 3 years
SELECT
    a.appl_no,
    a.sponsor_name,
    a.rxdl_company,
    l.trade_name,
    l.ingredient,
    l.reported_loe_date,
    l.years_to_loe,
    l.excl_codes
FROM ob_loe_estimates l
JOIN ob_applications a ON a.appl_no = l.appl_no
WHERE l.years_to_loe BETWEEN 0 AND 3
ORDER BY l.reported_loe_date;

-- NDAs with pediatric exclusivity extension (PED adds 6 months to patent)
SELECT
    a.appl_no,
    a.sponsor_name,
    a.rxdl_company,
    p.patent_no,
    p.patent_expire_date,
    DATE(p.patent_expire_date, '+6 months') AS ped_adjusted_expiry
FROM ob_patents p
JOIN ob_applications a ON a.appl_no = p.appl_no
WHERE p.pediatric_excl = 1
ORDER BY p.patent_expire_date;

-- Drugs with both patent AND exclusivity protection
SELECT
    a.appl_no,
    a.sponsor_name,
    p.patent_no,
    p.patent_expire_date,
    e.exclusivity_code,
    e.exclusivity_expiration_date
FROM ob_applications a
JOIN ob_patents p ON p.appl_no = a.appl_no
JOIN ob_exclusivity e ON e.appl_no = a.appl_no
WHERE a.appl_type = 'NDA'
ORDER BY p.patent_expire_date DESC
LIMIT 20;

-- NDA LOE by year (count of drugs losing protection each year)
SELECT
    STRFTIME('%Y', reported_loe_date) AS loe_year,
    COUNT(*) AS nda_count,
    GROUP_CONCAT(trade_name, ', ') AS drugs
FROM ob_loe_estimates
WHERE reported_loe_date >= DATE('now')
GROUP BY loe_year
ORDER BY loe_year;

-- Orphan drug exclusivity (7-year ODE)
SELECT
    a.appl_no,
    a.sponsor_name,
    a.rxdl_company,
    e.exclusivity_expiration_date,
    dl.indications_and_usage
FROM ob_exclusivity e
JOIN ob_applications a ON a.appl_no = e.appl_no
LEFT JOIN drug_labels dl ON dl.appl_no = a.appl_no
WHERE e.exclusivity_code LIKE 'ODE%'
ORDER BY e.exclusivity_expiration_date;

-- What use code U-2145 covers (join to lookup)
SELECT p.appl_no, p.patent_no, p.use_code, uc.definition
FROM ob_patents p
JOIN ob_patent_use_codes uc ON uc.use_code = p.use_code
WHERE p.use_code = 'U-2145'
LIMIT 10;


-- ============================================================
-- PURPLE BOOK (BLA / BIOLOGICS) QUERIES
-- ============================================================

-- All BLA reference products with active exclusivity
SELECT
    a.bla_number,
    a.sponsor_name,
    a.rxdl_company,
    p.proprietary_name,
    p.proper_name,
    e.exclusivity_type,
    e.exclusivity_date
FROM pb_exclusivity e
JOIN pb_applications a ON a.bla_number = e.bla_number
JOIN pb_products p ON p.bla_number = e.bla_number AND p.product_number = e.product_number
WHERE e.exclusivity_type IN ('standard', 'ref_product', 'orphan')
  AND e.exclusivity_date >= DATE('now')
ORDER BY e.exclusivity_date;

-- BLA LOE estimates (biologics losing protection in next 5 years)
SELECT
    l.bla_number,
    l.sponsor_name,
    l.rxdl_company,
    l.trade_name,
    l.proper_name,
    l.standard_excl_date,
    l.orphan_excl_date,
    l.reported_loe_date,
    l.years_to_loe
FROM pb_loe_estimates l
WHERE l.years_to_loe BETWEEN 0 AND 5
ORDER BY l.reported_loe_date;

-- Interchangeable biosimilars (highest regulatory designation)
SELECT
    a.bla_number,
    a.sponsor_name,
    p.proprietary_name,
    p.proper_name,
    e.exclusivity_type,
    e.exclusivity_date
FROM pb_products p
JOIN pb_applications a ON a.bla_number = p.bla_number
LEFT JOIN pb_exclusivity e ON e.bla_number = p.bla_number AND e.product_number = p.product_number
WHERE p.license_type = '351(k) Interchangeable'
ORDER BY p.bla_number;

-- Reference products and their biosimilar competitors
SELECT
    ref.bla_number AS reference_bla,
    ref.proper_name AS reference_molecule,
    ref_app.sponsor_name AS reference_sponsor,
    bio.bla_number AS biosimilar_bla,
    bio.proprietary_name AS biosimilar_brand,
    bio.proper_name AS biosimilar_proper_name,
    bio_app.sponsor_name AS biosimilar_sponsor
FROM pb_products ref
JOIN pb_applications ref_app ON ref_app.bla_number = ref.bla_number
JOIN pb_products bio ON LOWER(SUBSTR(bio.proper_name, 1, INSTR(bio.proper_name || '-', '-') - 1))
    = LOWER(SUBSTR(ref.proper_name, 1, INSTR(ref.proper_name || '-', '-') - 1))
JOIN pb_applications bio_app ON bio_app.bla_number = bio.bla_number
WHERE ref.license_type = '351(a)'
  AND bio.license_type IN ('351(k) Biosimilar', '351(k) Interchangeable')
  AND ref.bla_number != bio.bla_number
ORDER BY ref.proper_name, bio_app.sponsor_name;

-- Exclusivity changes in the last year (date extensions or new grants)
SELECT
    c.pb_release_date,
    c.event,
    a.sponsor_name,
    a.rxdl_company,
    p.proprietary_name,
    c.exclusivity_type,
    c.exclusivity_date
FROM pb_exclusivity_changes c
JOIN pb_applications a ON a.bla_number = c.bla_number
LEFT JOIN pb_products p ON p.bla_number = c.bla_number AND p.product_number = c.product_number
WHERE c.pb_release_date >= DATE('now', '-12 months')
  AND c.exclusivity_type IN ('standard', 'ref_product', 'orphan')
ORDER BY c.pb_release_date DESC;


-- ============================================================
-- CROSS-SOURCE QUERIES (NDA + BLA)
-- ============================================================

-- Full LOE calendar for a company across NDA and BLA portfolio
-- Replace 'Pfizer' with your company of interest
SELECT
    l.source,
    l.app_type,
    l.app_id,
    l.trade_name,
    l.drug_name,
    l.reported_loe_date,
    l.years_to_loe
FROM all_loe_estimates l
WHERE l.rxdl_company = 'PFIZER INC'
ORDER BY l.reported_loe_date;

-- LOE calendar by year across all companies (NDA + BLA)
SELECT
    STRFTIME('%Y', l.reported_loe_date) AS loe_year,
    l.source,
    COUNT(*) AS drug_count
FROM all_loe_estimates l
WHERE l.reported_loe_date >= DATE('now')
GROUP BY loe_year, l.source
ORDER BY loe_year, l.source;

-- Company portfolio overview (count NDAs, BLAs, and active protections)
SELECT
    a.rxdl_company,
    COUNT(DISTINCT CASE WHEN a.source = 'ob' AND a.appl_type = 'NDA' THEN a.app_id END) AS nda_count,
    COUNT(DISTINCT CASE WHEN a.source = 'pb' THEN a.app_id END) AS bla_count,
    COUNT(DISTINCT l.app_id) AS active_loe_count
FROM all_applications a
LEFT JOIN all_loe_estimates l
    ON l.app_id = a.app_id AND l.source = a.source AND l.reported_loe_date >= DATE('now')
WHERE a.rxdl_company != ''
GROUP BY a.rxdl_company
ORDER BY (nda_count + bla_count) DESC
LIMIT 20;

-- Drug label search: find NDAs and BLAs with a given active ingredient
-- Cross-source via drug_labels (covers both NDA and BLA appl_no)
SELECT
    dl.appl_no,
    dl.brand_name,
    dl.generic_name,
    dl.manufacturer_name,
    dl.indications_and_usage,
    dl.pharm_class_epc
FROM drug_labels dl
WHERE LOWER(dl.generic_name) LIKE '%pembrolizumab%'
   OR LOWER(dl.brand_name) LIKE '%keytruda%';

-- Newly added exclusivity grants in the last 6 months (OB + PB)
SELECT 'OB' AS source, ob_release_date AS release_date, appl_no AS app_id,
       exclusivity_code AS excl_type, exclusivity_expiration_date AS excl_date
FROM ob_exclusivity_changes
WHERE event = 'added'
  AND ob_release_date >= DATE('now', '-6 months')
UNION ALL
SELECT 'PB', pb_release_date, bla_number,
       exclusivity_type, exclusivity_date
FROM pb_exclusivity_changes
WHERE event = 'added'
  AND pb_release_date >= DATE('now', '-6 months')
ORDER BY release_date DESC;
