-- ============================================================================
--  SCHEMA DISCOVERY — run on the vinita_skymedi database (phpMyAdmin / MySQL CLI)
--  Paste the output back so the leakage views can be finalised with real names.
--  All read-only. Nothing is changed.
-- ============================================================================

-- 1) Exact table names (is it prescription or prescriptions? sales or pharmacy_sales?)
SHOW TABLES LIKE '%prescri%';
SHOW TABLES LIKE '%sale%';
SHOW TABLES LIKE '%bill%';
SHOW TABLES LIKE '%medicine%';
SHOW TABLES LIKE '%drug%';
SHOW TABLES LIKE '%item%';

-- 2) Columns of each key table (fix the name if step 1 shows a different one)
SHOW COLUMNS FROM prescription;
SHOW COLUMNS FROM prescription_items;
SHOW COLUMNS FROM sales;
SHOW COLUMNS FROM sales_items;

-- 3) Which status/type values actually exist on appointments (what means "completed" / "OPD"?)
SELECT status, COUNT(*) AS n FROM appointments GROUP BY status ORDER BY n DESC;
SELECT visit_type, appointment_type, COUNT(*) AS n
FROM appointments GROUP BY visit_type, appointment_type ORDER BY n DESC;

-- 4) Is there any October 2026 data at all? (explains "No matching records")
SELECT COUNT(*) AS appts_oct_2026
FROM appointments
WHERE `date` >= '2026-10-01' AND `date` < '2026-11-01';

-- 5) Sanity: do appointments and sales actually share hr_no values?
SELECT
  (SELECT COUNT(DISTINCT hr_no) FROM appointments) AS appt_hr_nos,
  (SELECT COUNT(DISTINCT hr_no) FROM prescription) AS presc_hr_nos,
  (SELECT COUNT(DISTINCT hr_no) FROM sales)        AS sales_hr_nos;
