-- =========================================================
--  CLINIC DB (Dental / Hospital) - From scratch
--  MariaDB 10.11+  |  utf8mb4
-- =========================================================


START TRANSACTION;

-- ====== Crea BD ======
CREATE DATABASE IF NOT EXISTS clinic_db
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_general_ci;

USE clinic_db;

-- =========================================================
--  2) PACIENTES / EXPEDIENTE
-- =========================================================

CREATE TABLE patients (
  id_patient        INT(11) NOT NULL AUTO_INCREMENT,
  expediente_codigo VARCHAR(30) NOT NULL,         -- tipo "P-000123"
  first_name        VARCHAR(80) NOT NULL,
  last_name         VARCHAR(120) NOT NULL,
  dob               DATE DEFAULT NULL,
  sex               ENUM('M','F','OTRO') DEFAULT NULL,

  email             VARCHAR(190) DEFAULT NULL,
  phone             VARCHAR(20) DEFAULT NULL,

  address_line      VARCHAR(160) DEFAULT NULL,
  city              VARCHAR(80)  DEFAULT NULL,
  state             VARCHAR(80)  DEFAULT NULL,
  zip_code          VARCHAR(10)  DEFAULT NULL,

  emergency_name    VARCHAR(120) DEFAULT NULL,
  emergency_phone   VARCHAR(20)  DEFAULT NULL,

  -- Saldos tipo tu "dinero_a_favor"
  wallet_balance    DECIMAL(13,2) NOT NULL DEFAULT 0.00,

  created_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at        DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (id_patient),
  UNIQUE KEY uk_patients_expediente (expediente_codigo),
  KEY idx_patients_name (last_name, first_name),
  KEY idx_patients_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Datos clínicos base (historial)
CREATE TABLE patient_medical_profile (
  id_profile   INT(11) NOT NULL AUTO_INCREMENT,
  id_patient   INT(11) NOT NULL,
  allergies    TEXT DEFAULT NULL,
  conditions   TEXT DEFAULT NULL,
  medications  TEXT DEFAULT NULL,
  notes        TEXT DEFAULT NULL,
  created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at   DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id_profile),
  UNIQUE KEY uq_profile_patient (id_patient),
  CONSTRAINT fk_profile_patient
    FOREIGN KEY (id_patient) REFERENCES patients(id_patient) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- =========================================================
--  3) AGENDA (AREAS / CITAS / BLOQUEOS)
-- =========================================================

-- areas = consultorios / recursos agendables / especialidades
CREATE TABLE areas (
  id                INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  name              VARCHAR(100) NOT NULL,
  tipo              ENUM('DAY','HOUR') NOT NULL,        -- citas por día o por horas
  start_hour        TIME DEFAULT NULL,
  end_hour          TIME DEFAULT NULL,
  slot_capacity     INT(10) UNSIGNED NOT NULL DEFAULT 1,
  closed_weekdays   LONGTEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT (JSON_ARRAY()),
  capacity          INT(10) UNSIGNED DEFAULT NULL,
  active            TINYINT(1) NOT NULL DEFAULT 1,
  max_hours_per_day TINYINT(3) UNSIGNED DEFAULT NULL,

  created_at        TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at        TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (id),
  KEY idx_areas_tipo (tipo),
  KEY idx_areas_active (active),
  CHECK (json_valid(closed_weekdays))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE area_blockouts (
  id         INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  area_id    INT(10) UNSIGNED NOT NULL,
  fecha      DATE NOT NULL,
  scope      ENUM('DAY','HOURS') NOT NULL DEFAULT 'DAY',
  time_start TIME DEFAULT NULL,
  time_end   TIME DEFAULT NULL,
  note       VARCHAR(160) DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uniq_area_fecha_rango (area_id, fecha, scope, time_start, time_end),
  CONSTRAINT fk_block_area FOREIGN KEY (area_id) REFERENCES areas(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Citas
CREATE TABLE appointments (
  id            BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  area_id       INT(10) UNSIGNED NOT NULL,
  id_patient    INT(11) NOT NULL,
  doctor_id     INT(11) DEFAULT NULL, -- users.id con role=DOCTOR
  fecha         DATE NOT NULL,
  time_start    TIME DEFAULT NULL,
  time_end      TIME DEFAULT NULL,

  status        ENUM('PROGRAMADA','CONFIRMADA','EN_CURSO','COMPLETADA','NO_ASISTIO','CANCELADA') NOT NULL DEFAULT 'PROGRAMADA',
  reason        VARCHAR(160) DEFAULT NULL,
  notes         VARCHAR(255) DEFAULT NULL,

  created_by    INT(11) DEFAULT NULL,
  created_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY (id),
  KEY idx_app_area (area_id),
  KEY idx_app_patient (id_patient),
  KEY idx_app_doctor (doctor_id),
  KEY idx_app_search (id_patient, fecha, status),

  CONSTRAINT fk_app_area FOREIGN KEY (area_id) REFERENCES areas(id) ON DELETE CASCADE,
  CONSTRAINT fk_app_patient FOREIGN KEY (id_patient) REFERENCES patients(id_patient) ON DELETE CASCADE,
  CONSTRAINT fk_app_doctor FOREIGN KEY (doctor_id) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_app_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =========================================================
--  4) CATÁLOGOS (PAGOS / PROCEDIMIENTOS / SERVICIOS)
-- =========================================================

CREATE TABLE cat_payment_methods (
  id_method INT(11) NOT NULL AUTO_INCREMENT,
  code      VARCHAR(30) NOT NULL,
  name      VARCHAR(50) NOT NULL,
  active    TINYINT(1) NOT NULL DEFAULT 1,
  orden     INT(11) NOT NULL DEFAULT 100,
  PRIMARY KEY (id_method),
  UNIQUE KEY uk_method_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Catálogo de procedimientos/servicios clínicos
CREATE TABLE clinical_services (
  id_service   INT(11) NOT NULL AUTO_INCREMENT,
  code         VARCHAR(30) DEFAULT NULL,
  name         VARCHAR(120) NOT NULL,
  description  VARCHAR(255) DEFAULT NULL,
  base_price   DECIMAL(13,2) NOT NULL DEFAULT 0.00,
  active       TINYINT(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (id_service),
  UNIQUE KEY uk_service_code (code),
  KEY idx_service_active (active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- =========================================================
--  5) COBRANZA (CARGOS / PAGOS / APLICACIONES / SALDO A FAVOR / RECIBOS)
-- =========================================================

-- Cargos (equivalente a adeudo)
CREATE TABLE charges (
  id_charge      INT(11) NOT NULL AUTO_INCREMENT,
  id_patient     INT(11) NOT NULL,

  charge_type    ENUM('CONSULTA','PROCEDIMIENTO','PLAN_TRATAMIENTO','LABORATORIO','HOSPITALIZACION','OTRO') NOT NULL DEFAULT 'OTRO',
  concept        VARCHAR(200) NOT NULL,
  service_id     INT(11) DEFAULT NULL,    -- optional: clinical_services
  appointment_id BIGINT(20) UNSIGNED DEFAULT NULL,

  charge_date    DATE DEFAULT NULL,
  amount         DECIMAL(13,2) NOT NULL,
  paid_amount    DECIMAL(13,2) NOT NULL DEFAULT 0.00,

  paid           TINYINT(1) NOT NULL DEFAULT 0,
  canceled       TINYINT(1) NOT NULL DEFAULT 0,
  cancel_reason  VARCHAR(120) DEFAULT NULL,

  created_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by     INT(11) DEFAULT NULL,
  canceled_at    DATETIME DEFAULT NULL,
  canceled_by    INT(11) DEFAULT NULL,

  PRIMARY KEY (id_charge),
  KEY idx_charge_patient (id_patient),
  KEY idx_charge_date (charge_date),
  KEY idx_charge_type (charge_type),
  KEY idx_charge_created_by (created_by),
  KEY idx_charge_canceled_by (canceled_by),

  CONSTRAINT fk_charge_patient FOREIGN KEY (id_patient) REFERENCES patients(id_patient),
  CONSTRAINT fk_charge_created_by FOREIGN KEY (created_by) REFERENCES users(id),
  CONSTRAINT fk_charge_canceled_by FOREIGN KEY (canceled_by) REFERENCES users(id),
  CONSTRAINT fk_charge_service FOREIGN KEY (service_id) REFERENCES clinical_services(id_service) ON DELETE SET NULL,
  CONSTRAINT fk_charge_appointment FOREIGN KEY (appointment_id) REFERENCES appointments(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Pagos
CREATE TABLE payments (
  id_payment     INT(11) NOT NULL AUTO_INCREMENT,

  payment_type   ENUM('PACIENTE','VENTA','SERVICIO','OTRO') NOT NULL DEFAULT 'PACIENTE',
  id_patient     INT(11) DEFAULT NULL,

  id_sale        INT(11) DEFAULT NULL, -- ventas de productos
  id_service     INT(11) DEFAULT NULL, -- si quieres pago directo a servicio (opcional)
  amount         DECIMAL(13,2) NOT NULL,

  method         ENUM('EFECTIVO','TRANSFERENCIA','TARJETA','CHEQUE','OTRO','BONIFICACION','PAGO POR LOTE','TARJETA DEBITO','TARJETA CREDITO','SALDO_FAVOR') NOT NULL DEFAULT 'EFECTIVO',
  reference      VARCHAR(60) DEFAULT NULL,
  note           VARCHAR(160) DEFAULT NULL,

  payment_date   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by     INT(11) NOT NULL,

  status         ENUM('APLICADO','CANCELADO') NOT NULL DEFAULT 'APLICADO',
  canceled_at    DATETIME DEFAULT NULL,
  canceled_by    INT(11) DEFAULT NULL,
  canceled_reason VARCHAR(120) DEFAULT NULL,

  PRIMARY KEY (id_payment),
  KEY idx_pay_type (payment_type),
  KEY idx_pay_patient (id_patient),
  KEY idx_pay_date (payment_date),
  KEY idx_pay_created_by (created_by),
  KEY idx_pay_canceled_by (canceled_by),

  CONSTRAINT fk_pay_patient FOREIGN KEY (id_patient) REFERENCES patients(id_patient) ON DELETE SET NULL,
  CONSTRAINT fk_pay_created_by FOREIGN KEY (created_by) REFERENCES users(id),
  CONSTRAINT fk_pay_canceled_by FOREIGN KEY (canceled_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Aplicación de pago a un cargo (equivalente pago_aplicacion)
CREATE TABLE payment_applications (
  id_application  INT(11) NOT NULL AUTO_INCREMENT,
  id_payment      INT(11) NOT NULL,
  id_charge       INT(11) NOT NULL,
  amount          DECIMAL(13,2) NOT NULL,

  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by      INT(11) DEFAULT NULL,

  canceled        TINYINT(1) NOT NULL DEFAULT 0,
  canceled_at     DATETIME DEFAULT NULL,
  canceled_by     INT(11) DEFAULT NULL,
  canceled_reason VARCHAR(120) DEFAULT NULL,

  PRIMARY KEY (id_application),
  KEY idx_pa_payment (id_payment),
  KEY idx_pa_charge (id_charge),
  KEY idx_pa_created_by (created_by),
  KEY idx_pa_canceled_by (canceled_by),

  CONSTRAINT fk_pa_payment FOREIGN KEY (id_payment) REFERENCES payments(id_payment),
  CONSTRAINT fk_pa_charge  FOREIGN KEY (id_charge)  REFERENCES charges(id_charge),
  CONSTRAINT fk_pa_created_by FOREIGN KEY (created_by) REFERENCES users(id),
  CONSTRAINT fk_pa_canceled_by FOREIGN KEY (canceled_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Movimientos de saldo a favor (equivalente favor_mov)
CREATE TABLE wallet_movements (
  id_wallet     INT(11) NOT NULL AUTO_INCREMENT,
  id_patient    INT(11) NOT NULL,
  id_payment    INT(11) DEFAULT NULL,

  mov_type      ENUM('ABONO','RETIRO','AJUSTE') NOT NULL,
  amount        DECIMAL(13,2) NOT NULL,
  note          VARCHAR(160) DEFAULT NULL,

  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by    INT(11) DEFAULT NULL,

  canceled      TINYINT(1) NOT NULL DEFAULT 0,
  canceled_at   DATETIME DEFAULT NULL,
  canceled_by   INT(11) DEFAULT NULL,
  canceled_reason VARCHAR(120) DEFAULT NULL,

  PRIMARY KEY (id_wallet),
  KEY idx_wallet_patient (id_patient),
  KEY idx_wallet_payment (id_payment),
  KEY idx_wallet_created_by (created_by),
  KEY idx_wallet_canceled_by (canceled_by),

  CONSTRAINT fk_wallet_patient FOREIGN KEY (id_patient) REFERENCES patients(id_patient),
  CONSTRAINT fk_wallet_payment FOREIGN KEY (id_payment) REFERENCES payments(id_payment) ON DELETE SET NULL,
  CONSTRAINT fk_wallet_created_by FOREIGN KEY (created_by) REFERENCES users(id),
  CONSTRAINT fk_wallet_canceled_by FOREIGN KEY (canceled_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Recibos / póliza (equivalente resibos)
CREATE TABLE receipts (
  id_receipt     INT(11) NOT NULL AUTO_INCREMENT,
  receipt_date   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

  created_by     INT(11) NOT NULL,

  id_payment     INT(11) DEFAULT NULL,
  id_charge      INT(11) DEFAULT NULL,
  id_application INT(11) DEFAULT NULL,
  id_wallet      INT(11) DEFAULT NULL,
  id_sale        INT(11) DEFAULT NULL,
  id_service     INT(11) DEFAULT NULL,

  record_type    ENUM('PAGO','ABONO','A_FAVOR_ABONO','A_FAVOR_RETIRO','VENTA','SERVICIO','CANCELACION','CARGO','AJUSTE') NOT NULL,
  movement       VARCHAR(80) NOT NULL,
  concept        VARCHAR(200) DEFAULT NULL,
  amount         DECIMAL(13,2) NOT NULL,

  canceled       TINYINT(1) NOT NULL DEFAULT 0,
  canceled_at    DATETIME DEFAULT NULL,
  canceled_by    INT(11) DEFAULT NULL,
  canceled_reason VARCHAR(120) DEFAULT NULL,

  PRIMARY KEY (id_receipt),
  KEY idx_receipt_date (receipt_date),
  KEY idx_receipt_payment (id_payment),
  KEY idx_receipt_charge (id_charge),
  KEY idx_receipt_app (id_application),
  KEY idx_receipt_wallet (id_wallet),
  KEY idx_receipt_created_by (created_by),

  CONSTRAINT fk_receipt_created_by FOREIGN KEY (created_by) REFERENCES users(id),

  CONSTRAINT fk_receipt_payment FOREIGN KEY (id_payment) REFERENCES payments(id_payment) ON DELETE SET NULL,
  CONSTRAINT fk_receipt_charge  FOREIGN KEY (id_charge)  REFERENCES charges(id_charge) ON DELETE SET NULL,
  CONSTRAINT fk_receipt_app     FOREIGN KEY (id_application) REFERENCES payment_applications(id_application) ON DELETE SET NULL,
  CONSTRAINT fk_receipt_wallet  FOREIGN KEY (id_wallet) REFERENCES wallet_movements(id_wallet) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- =========================================================
--  6) VENTAS (PRODUCTOS / ITEMS)
-- =========================================================

CREATE TABLE sales (
  id_sale       INT(11) NOT NULL AUTO_INCREMENT,
  sale_date     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  total         DECIMAL(13,2) NOT NULL,
  status        ENUM('PAGADA','CANCELADA') NOT NULL DEFAULT 'PAGADA',
  note          VARCHAR(160) DEFAULT NULL,

  id_patient    INT(11) DEFAULT NULL,  -- venta asociada a paciente (opcional)
  created_by    INT(11) DEFAULT NULL,

  canceled_at   DATETIME DEFAULT NULL,
  canceled_by   INT(11) DEFAULT NULL,
  canceled_reason VARCHAR(120) DEFAULT NULL,

  PRIMARY KEY (id_sale),
  KEY idx_sale_date (sale_date),
  KEY idx_sale_patient (id_patient),

  CONSTRAINT fk_sale_patient FOREIGN KEY (id_patient) REFERENCES patients(id_patient) ON DELETE SET NULL,
  CONSTRAINT fk_sale_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_sale_canceled_by FOREIGN KEY (canceled_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE sale_items (
  id_item       INT(11) NOT NULL AUTO_INCREMENT,
  id_sale       INT(11) NOT NULL,
  description   VARCHAR(160) NOT NULL,
  qty           DECIMAL(13,2) NOT NULL DEFAULT 1.00,
  unit_price    DECIMAL(13,2) NOT NULL,
  total         DECIMAL(13,2) NOT NULL,

  PRIMARY KEY (id_item),
  KEY idx_sale_items_sale (id_sale),
  CONSTRAINT fk_sale_items_sale FOREIGN KEY (id_sale) REFERENCES sales(id_sale) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Vincula payments con sales (FKs)
ALTER TABLE payments
  ADD CONSTRAINT fk_pay_sale FOREIGN KEY (id_sale) REFERENCES sales(id_sale) ON DELETE SET NULL,
  ADD CONSTRAINT fk_pay_service FOREIGN KEY (id_service) REFERENCES clinical_services(id_service) ON DELETE SET NULL;

ALTER TABLE receipts
  ADD COLUMN id_sale_item INT(11) DEFAULT NULL,
  ADD KEY idx_receipt_sale (id_sale),
  ADD CONSTRAINT fk_receipt_sale FOREIGN KEY (id_sale) REFERENCES sales(id_sale) ON DELETE SET NULL;

-- =========================================================
--  7) PROVEEDORES / GASTOS / CENTROS DE COSTO
-- =========================================================

CREATE TABLE suppliers (
  id_supplier INT(11) NOT NULL AUTO_INCREMENT,
  name        VARCHAR(100) NOT NULL,
  description VARCHAR(300) DEFAULT NULL,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  active      TINYINT(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (id_supplier),
  KEY idx_suppliers_active (active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE cost_centers (
  id          INT(11) NOT NULL AUTO_INCREMENT,
  name        VARCHAR(80) NOT NULL,
  description VARCHAR(160) DEFAULT NULL,
  active      TINYINT(1) NOT NULL DEFAULT 1,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_cost_center_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE supplier_expenses (
  id_expense    INT(11) NOT NULL AUTO_INCREMENT,
  id_supplier   INT(11) NOT NULL,
  id_cost_center INT(11) DEFAULT NULL,

  status        ENUM('APLICADO','CANCELADO') NOT NULL DEFAULT 'APLICADO',
  expense_date  DATE NOT NULL,
  concept       TEXT DEFAULT NULL,
  amount        DECIMAL(10,2) NOT NULL,
  reference     VARCHAR(255) DEFAULT NULL,

  created_by    INT(11) NOT NULL,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY (id_expense),
  KEY idx_exp_supplier (id_supplier),
  KEY idx_exp_center (id_cost_center),
  KEY idx_exp_status (status),

  CONSTRAINT fk_exp_supplier FOREIGN KEY (id_supplier) REFERENCES suppliers(id_supplier) ON UPDATE CASCADE,
  CONSTRAINT fk_exp_center   FOREIGN KEY (id_cost_center) REFERENCES cost_centers(id) ON UPDATE CASCADE,
  CONSTRAINT fk_exp_created_by FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- =========================================================
--  8) NOTIFICACIONES
-- =========================================================

CREATE TABLE notifications (
  id          INT(11) NOT NULL AUTO_INCREMENT,
  user_id     INT(11) NOT NULL,
  id_up       INT(11) DEFAULT NULL,  -- si quieres asociar al vínculo usuario-paciente
  title       VARCHAR(255) NOT NULL,
  body        TEXT NOT NULL,
  destination VARCHAR(100) DEFAULT NULL,
  platform    VARCHAR(50) DEFAULT NULL,
  sent_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_notifications_user (user_id),
  KEY idx_notifications_up (id_up),
  CONSTRAINT fk_notifications_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_notifications_up FOREIGN KEY (id_up) REFERENCES user_patient(id_up) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- =========================================================
--  9) AUDITORÍA / LOGS (acciones)
-- =========================================================

CREATE TABLE actions_log (
  id        INT(11) NOT NULL AUTO_INCREMENT,
  action_date DATE DEFAULT NULL,
  action_text TEXT DEFAULT NULL,
  action_time TIME NOT NULL,
  user_id   INT(11) DEFAULT NULL,
  PRIMARY KEY (id),
  KEY idx_actions_user (user_id),
  CONSTRAINT fk_actions_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;



ALTER TABLE patient_medical_profile
  ADD COLUMN hosp_recently TINYINT(1) NULL,
  ADD COLUMN hosp_reason VARCHAR(160) NULL,

  ADD COLUMN under_treatment TINYINT(1) NULL,
  ADD COLUMN treatment_type VARCHAR(160) NULL,

  ADD COLUMN taking_meds TINYINT(1) NULL,
  ADD COLUMN meds_detail VARCHAR(255) NULL,

  ADD COLUMN allergic_meds TINYINT(1) NULL,
  ADD COLUMN allergy_detail VARCHAR(255) NULL,

  ADD COLUMN hemorrhage_history TINYINT(1) NULL,
  ADD COLUMN hemorrhage_when VARCHAR(80) NULL,

  ADD COLUMN anesthesia_complications TINYINT(1) NULL,
  ADD COLUMN anesthesia_detail VARCHAR(160) NULL,

  ADD COLUMN comp_hepatitis ENUM('SI','NO','NO_SE') NULL,
  ADD COLUMN comp_diabetes  ENUM('SI','NO','NO_SE') NULL,
  ADD COLUMN comp_htn       ENUM('SI','NO','NO_SE') NULL,
  ADD COLUMN comp_cardio    ENUM('SI','NO','NO_SE') NULL,

  ADD COLUMN habit_smokes TINYINT(1) NULL,
  ADD COLUMN habit_bites_nails TINYINT(1) NULL,
  ADD COLUMN habit_bruxism TINYINT(1) NULL,

  ADD COLUMN women_pregnant TINYINT(1) NULL,
  ADD COLUMN women_contraceptives TINYINT(1) NULL,

  ADD COLUMN annotations TEXT NULL,
  ADD COLUMN observations TEXT NULL;

COMMIT;
