-- ============================================================================
--  OPD Pharmacy Revenue-Leakage views for the AI Solution Builder
-- ----------------------------------------------------------------------------
--  Run this ONCE on the hospital database that is connected to Skynyx AI as a
--  QueryDataSource (the same DB the "dynamic_readonly_query_builder" tool reads).
--
--  WHY A VIEW?  The leakage answer is a 5-table join + anti-join:
--     appointment(hr_no) -> prescription(hr_no, pid) -> prescription_items(pid)
--                compared against
--     sales(hr_no, bill_no) -> sales_items(bill_no)
--  Asking the LLM to re-assemble that join on every question is unreliable.
--  A view turns it into a single-table query the AI can filter safely.
--
--  >>> CONFIRM THESE NAMES against your real schema before running. <<<
--  CONFIRMED from a live run (data_source vinita_skymedi):
--     appointments : app_id (PK), hr_no, `date`, status, visit_type, appointment_type,
--                    doctor_id, patient_name, speciality_id, ...   (table is PLURAL)
--  STILL TO CONFIRM (run opd_schema_discovery.sql, then fix below):
--     prescription(s) : pid (PK), hr_no, prescription_date ?
--     prescription_items : pid (FK), medicine_id, medicine_name, quantity, unit_price ?
--     sales       : bill_no (PK), hr_no, sale_date ?
--     sales_items : bill_no (FK), medicine_id, quantity, price ?
--  Also confirm: which `status` value means completed, and whether visit_type /
--  appointment_type marks an OPD visit.
-- ============================================================================


-- ----------------------------------------------------------------------------
-- VIEW 1 — FULL leakage (visit level)
-- One row per COMPLETED OPD appointment that HAS a prescription but NO pharmacy
-- sale at all. This answers "which appointments / how many leaked".
-- ----------------------------------------------------------------------------
CREATE OR REPLACE VIEW opd_revenue_leakage AS
SELECT
    a.hr_no                         AS hr_no,
    a.`date`                        AS appointment_date,          -- appointments.date (confirmed)
    MIN(p.pid)                      AS prescription_id,
    COUNT(DISTINCT p.pid)           AS prescriptions,
    COUNT(pi.pid)                   AS prescribed_items
FROM appointments a
JOIN prescription p
      ON p.hr_no = a.hr_no
LEFT JOIN prescription_items pi
      ON pi.pid = p.pid
WHERE a.status = 'completed'                                      -- CONFIRM status value (see discovery)
  -- AND a.visit_type = 'OPD'                                     -- uncomment/adjust if OPD must be filtered
  AND NOT EXISTS (                                                -- no pharmacy sale for this HR number
        SELECT 1 FROM sales s WHERE s.hr_no = a.hr_no
  )
GROUP BY a.hr_no, a.`date`;

-- Visit-accurate alternative: if hr_no is a PATIENT number (not per-visit), the
-- NOT EXISTS above treats a patient who ever bought anything as "not leaked".
-- To tie the sale to THIS visit, scope it by date instead — swap the NOT EXISTS for:
--     AND NOT EXISTS (
--         SELECT 1 FROM sales s
--         WHERE s.hr_no = a.hr_no
--           AND s.sale_date >= a.appointment_date
--           AND s.sale_date <  a.appointment_date + INTERVAL 7 DAY   -- grace window
--     )
-- and add the same date tie on the prescription JOIN if prescription_date exists.


-- ----------------------------------------------------------------------------
-- VIEW 2 — PARTIAL leakage (medicine / amount level)
-- One row per prescribed medicine that was NOT fully purchased at the pharmacy,
-- with the lost quantity and estimated lost revenue. This answers
-- "how much revenue leaked" and "which medicines leak most".
-- ----------------------------------------------------------------------------
CREATE OR REPLACE VIEW opd_rx_sales_item_leakage AS
SELECT
    a.hr_no                                             AS hr_no,
    a.`date`                                            AS appointment_date,
    pi.medicine_id                                      AS medicine_id,        -- CONFIRM common key
    pi.medicine_name                                    AS medicine_name,
    SUM(pi.quantity)                                    AS prescribed_qty,
    COALESCE(sold.sold_qty, 0)                          AS sold_qty,
    GREATEST(SUM(pi.quantity) - COALESCE(sold.sold_qty, 0), 0)                 AS unsold_qty,
    GREATEST(SUM(pi.quantity) - COALESCE(sold.sold_qty, 0), 0) * MAX(pi.unit_price) AS revenue_leak_est
FROM appointments a
JOIN prescription p         ON p.hr_no = a.hr_no
JOIN prescription_items pi  ON pi.pid = p.pid
LEFT JOIN (
        SELECT s.hr_no, si.medicine_id, SUM(si.quantity) AS sold_qty
        FROM sales s
        JOIN sales_items si ON si.bill_no = s.bill_no
        GROUP BY s.hr_no, si.medicine_id
) sold
      ON sold.hr_no = a.hr_no
     AND sold.medicine_id = pi.medicine_id
WHERE a.status = 'completed'
GROUP BY a.hr_no, a.`date`, pi.medicine_id, pi.medicine_name, sold.sold_qty
HAVING unsold_qty > 0;


-- ============================================================================
--  SAMPLE QUESTION -> SQL
--  "Show all completed OPD appointments in October 2026 where prescriptions
--   were generated but no pharmacy sales were recorded."
-- ============================================================================
-- With the view (what the AI Solution will run):
SELECT hr_no, appointment_date, prescriptions, prescribed_items
FROM   opd_revenue_leakage
WHERE  appointment_date >= '2026-10-01'
  AND  appointment_date <  '2026-11-01'
ORDER BY appointment_date;

-- Total revenue leaked in October 2026 (amount level):
-- SELECT ROUND(SUM(revenue_leak_est), 2) AS total_leak
-- FROM   opd_rx_sales_item_leakage
-- WHERE  appointment_date >= '2026-10-01' AND appointment_date < '2026-11-01';
