-- =====================================================================
-- 2026_sprint4_bi.sql — اسپرینت ۴
-- بانکِ دیتا: نماهای واحدِ سطح‌رکوردی برای داشبورد/گزارش/اکسل.
-- CREATE OR REPLACE → اجرای مجدد امن. Rollback پایین. دیتابیس: shadicli_nurse
-- =====================================================================
SET NAMES utf8mb4;

-- ۱) بانکِ دیتای پروسیجرها (هر record_procedure، بدونِ حذف‌شده‌ها)
CREATE OR REPLACE VIEW `vw_databank_procedures` AS
SELECT
  rp.id                       AS rp_id,
  r.id                        AS record_id,
  r.record_date               AS record_date,
  rp.procedure_id             AS procedure_id,
  p.title                     AS procedure_title,
  r.doctor_id                 AS doctor_id,
  d.full_name                 AS doctor_name,
  rp.duration_minutes         AS proc_duration,
  r.numbing_duration_minutes  AS numbing_duration,
  r.cw_visit_id               AS visit_id,
  r.cw_patient_id             AS patient_id,
  r.patient_name              AS patient_name,
  r.status                    AS record_status,
  rp.bom_consumed_at          AS bom_consumed_at,
  (SELECT n.full_name FROM record_nurses rn JOIN nurses n ON n.id=rn.nurse_id
     WHERE rn.record_id=r.id AND rn.is_primary=1 LIMIT 1) AS primary_nurse
FROM record_procedures rp
JOIN nursing_records r ON r.id = rp.record_id
LEFT JOIN procedures p ON p.id = rp.procedure_id
LEFT JOIN doctors d ON d.id = r.doctor_id
WHERE rp.deleted_at IS NULL AND r.deleted_at IS NULL;

-- ۲) بانکِ دیتای پرستاران (هر سهمِ پرستار در فرایند)
CREATE OR REPLACE VIEW `vw_databank_nurses` AS
SELECT
  rn.id          AS rn_id,
  r.id           AS record_id,
  r.record_date  AS record_date,
  rn.nurse_id    AS nurse_id,
  n.full_name    AS nurse_name,
  rn.role_type   AS role_type,
  rn.is_primary  AS is_primary,
  rn.is_assistant AS is_assistant,
  rn.share_pct   AS share_pct,
  r.doctor_id    AS doctor_id,
  r.cw_visit_id  AS visit_id
FROM record_nurses rn
JOIN nursing_records r ON r.id = rn.record_id
JOIN nurses n ON n.id = rn.nurse_id
WHERE r.deleted_at IS NULL;

-- ۳) بانکِ دیتای پرداخت‌ها (هر فاکتور با اطلاعاتِ بیمار)
CREATE OR REPLACE VIEW `vw_databank_payments` AS
SELECT
  pay.id            AS payment_id,
  pay.created_at    AS created_at,
  DATE(pay.created_at) AS pay_date,
  pay.visit_id      AS visit_id,
  pay.patient_id    AS patient_id,
  pt.full_name      AS patient_name,
  pay.total_amount  AS total_amount,
  pay.discount_amount AS discount_amount,
  pay.payable_amount AS payable_amount,
  pay.paid_amount   AS paid_amount,
  (pay.payable_amount - pay.paid_amount) AS balance_amount,
  pay.method        AS method,
  pay.status        AS status,
  pay.is_proforma   AS is_proforma,
  pay.kind          AS kind
FROM cw_payments pay
LEFT JOIN cw_patients pt ON pt.id = pay.patient_id;

-- ۴) بانکِ دیتای ویزیت‌ها
CREATE OR REPLACE VIEW `vw_databank_visits` AS
SELECT
  v.id            AS visit_id,
  v.created_at    AS created_at,
  DATE(v.created_at) AS visit_day,
  v.patient_id    AS patient_id,
  pt.full_name    AS patient_name,
  v.entry_reason  AS entry_reason,
  v.current_status AS current_status,
  v.queue_number  AS queue_number
FROM cw_visits v
LEFT JOIN cw_patients pt ON pt.id = v.patient_id;

-- =====================================================================
-- ROLLBACK:
-- DROP VIEW IF EXISTS `vw_databank_visits`;
-- DROP VIEW IF EXISTS `vw_databank_payments`;
-- DROP VIEW IF EXISTS `vw_databank_nurses`;
-- DROP VIEW IF EXISTS `vw_databank_procedures`;
-- =====================================================================