-- Optional helper queries for Bangladesh Diagnostic Center System
-- Main schema should be created using Laravel migrations: php artisan migrate --seed

-- Find patient from billing interface
SELECT id, uhid, name, phone, gender, age_year
FROM patients
WHERE phone LIKE '%017%' OR uhid LIKE '%UHID%' OR name LIKE '%Karim%'
LIMIT 10;

-- Cashier wise daily collection
SELECT u.name AS cashier, DATE(p.created_at) AS date, SUM(p.amount) AS collection
FROM payments p
LEFT JOIN users u ON u.id = p.received_by
GROUP BY u.name, DATE(p.created_at);

-- Due report
SELECT i.invoice_no, p.name, p.phone, i.total, i.paid_amount, i.due_amount
FROM invoices i
JOIN patients p ON p.id = i.patient_id
WHERE i.due_amount > 0;

-- Stock ledger
SELECT ii.name, sm.movement_type, sm.qty_in, sm.qty_out, sm.balance_after, sm.reference_type, sm.created_at
FROM stock_movements sm
JOIN inventory_items ii ON ii.id = sm.inventory_item_id
ORDER BY sm.id DESC;