-- =============================================================================
-- ODONTOGENESIS — Actualización BD dentalbuysv_dh
-- Referencia: dump dentalbuysv_dh.sql (Dental House / Dra. Kenia Macías)
-- SOLO BASE DE DATOS — no modifica código de la aplicación.
-- Idempotente: ignora columna duplicada (1060) y tabla existente (1050).
-- Ejecutar en phpMyAdmin sobre la base activa del servidor.
-- =============================================================================
--
-- YA EXISTE en dentalbuysv_dh (no requiere acción):
--   forecast.plan_items, forecast.medic_id, forecast.date
--   evolution.treatment_id
--   history.prescription_number
--   establishments.agenda_start_hour, establishments.agenda_end_hour
--   history_informed_consent_signatures, informed_consents
--   boxes, warehouses, quotation_payments, lab_*
--
-- FALTA en dentalbuysv_dh (este script lo agrega):
--   [Facturación]  sale_note_payments, sale_notes.voided_at / voided_by_user_id
--   [Ortodoncia]   persons (columnas ortho*), orthodontic_progress
--   [Opcional]     sale_notes.treatment_id/specialty_id/medic_id
--                  treatments.attachment_files, inventory_kardexes
--                  quote_day_notes.quotes_id, users.person_id
-- =============================================================================

DROP PROCEDURE IF EXISTS odontogenesis_dh_schema_update;

DELIMITER $$

CREATE PROCEDURE odontogenesis_dh_schema_update()
BEGIN
  DECLARE CONTINUE HANDLER FOR 1060 BEGIN END;
  DECLARE CONTINUE HANDLER FOR 1050 BEGIN END;

  -- ---------------------------------------------------------------------------
  -- 1. FACTURACIÓN / NOTAS DE VENTA
  -- ---------------------------------------------------------------------------

  ALTER TABLE `sale_notes`
    ADD COLUMN `voided_at` DATETIME NULL DEFAULT NULL;

  ALTER TABLE `sale_notes`
    ADD COLUMN `voided_by_user_id` INT UNSIGNED NULL DEFAULT NULL;

  ALTER TABLE `sale_notes`
    ADD COLUMN `treatment_id` INT UNSIGNED NULL DEFAULT NULL;

  ALTER TABLE `sale_notes`
    ADD COLUMN `specialty_id` INT UNSIGNED NULL DEFAULT NULL;

  ALTER TABLE `sale_notes`
    ADD COLUMN `medic_id` INT UNSIGNED NULL DEFAULT NULL;

  CREATE TABLE IF NOT EXISTS `sale_note_payments` (
    `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `sale_note_id` int(10) UNSIGNED NOT NULL,
    `date_of_payment` date NOT NULL,
    `payment_method_type_id` char(2) NOT NULL,
    `has_card` tinyint(1) NOT NULL DEFAULT 0,
    `card_brand_id` int(10) UNSIGNED DEFAULT NULL,
    `reference` varchar(255) DEFAULT NULL,
    `change` decimal(12,2) DEFAULT NULL,
    `payment` decimal(12,2) NOT NULL,
    PRIMARY KEY (`id`),
    KEY `sale_note_payments_sale_note_id_foreign` (`sale_note_id`),
    KEY `sale_note_payments_payment_method_type_id_foreign` (`payment_method_type_id`),
    KEY `sale_note_payments_card_brand_id_foreign` (`card_brand_id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC;

  -- ---------------------------------------------------------------------------
  -- 2. ORTODONCIA
  -- ---------------------------------------------------------------------------

  ALTER TABLE `persons`
    ADD COLUMN `is_orthodontic` tinyint(1) NOT NULL DEFAULT 0;

  ALTER TABLE `persons`
    ADD COLUMN `ortho_start_date` date DEFAULT NULL;

  ALTER TABLE `persons`
    ADD COLUMN `ortho_estimated_months` int(10) UNSIGNED DEFAULT NULL;

  ALTER TABLE `persons`
    ADD COLUMN `ortho_next_appointment` date DEFAULT NULL;

  ALTER TABLE `persons`
    ADD COLUMN `ortho_establishment_id` int(10) UNSIGNED DEFAULT NULL;

  ALTER TABLE `persons`
    ADD COLUMN `ortho_finished_at` datetime DEFAULT NULL;

  ALTER TABLE `persons`
    ADD COLUMN `ortho_finish_note` text DEFAULT NULL;

  CREATE TABLE IF NOT EXISTS `orthodontic_progress` (
    `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `patient_id` int(10) UNSIGNED NOT NULL,
    `month` date NOT NULL COMMENT 'Primer día del mes (YYYY-MM-01)',
    `appointment_date` date DEFAULT NULL,
    `attended` tinyint(1) DEFAULT NULL COMMENT '1=asistió, 0=no, NULL=pendiente',
    `progress_percent` tinyint(3) UNSIGNED DEFAULT NULL,
    `notes` varchar(1000) DEFAULT NULL,
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `orthodontic_progress_patient_month_unique` (`patient_id`,`month`),
    KEY `orthodontic_progress_patient_id_index` (`patient_id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC;

  -- ---------------------------------------------------------------------------
  -- 3. TRATAMIENTOS / EVOLUCIÓN (adjuntos en JSON)
  -- ---------------------------------------------------------------------------

  ALTER TABLE `treatments`
    ADD COLUMN `attachment_files` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL;

  -- ---------------------------------------------------------------------------
  -- 4. INVENTARIO (kardex — opcional, evita errores al facturar productos)
  -- ---------------------------------------------------------------------------

  CREATE TABLE IF NOT EXISTS `inventory_kardexes` (
    `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `date_of_issue` date NOT NULL,
    `item_id` int(10) UNSIGNED NOT NULL,
    `warehouse_id` int(10) UNSIGNED NOT NULL,
    `quantity` decimal(12,4) NOT NULL,
    `inventory_kardexable_id` int(10) UNSIGNED NOT NULL,
    `inventory_kardexable_type` varchar(255) NOT NULL,
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `inventory_kardexes_item_id_index` (`item_id`),
    KEY `inventory_kardexes_warehouse_id_index` (`warehouse_id`),
    KEY `inventory_kardexes_morph_index` (`inventory_kardexable_id`,`inventory_kardexable_type`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC;

  -- ---------------------------------------------------------------------------
  -- 5. AGENDA / USUARIOS (mejoras menores)
  -- ---------------------------------------------------------------------------

  ALTER TABLE `quote_day_notes`
    ADD COLUMN `quotes_id` int(10) UNSIGNED NULL DEFAULT NULL AFTER `note_date`;

  ALTER TABLE `users`
    ADD COLUMN `person_id` int(10) UNSIGNED NULL DEFAULT NULL AFTER `establishment_id`;

END$$

DELIMITER ;

CALL odontogenesis_dh_schema_update();
DROP PROCEDURE odontogenesis_dh_schema_update;

-- =============================================================================
-- VERIFICACIÓN
-- =============================================================================
SHOW COLUMNS FROM `sale_notes` LIKE 'voided_at';
SHOW COLUMNS FROM `sale_notes` LIKE 'voided_by_user_id';
SHOW TABLES LIKE 'sale_note_payments';
SHOW COLUMNS FROM `persons` LIKE 'is_orthodontic';
SHOW TABLES LIKE 'orthodontic_progress';
SHOW COLUMNS FROM `treatments` LIKE 'attachment_files';
SHOW TABLES LIKE 'inventory_kardexes';

-- =============================================================================
-- PAGOS YA PROCESADOS (histórico)
-- Los pagos antiguos están en `boxes` (sale_note_id). No se pierden al crear
-- sale_note_payments. Para copiarlos a la nueva tabla, ejecute también:
--   scripts/odontogenesis_migrate_sale_note_payments.sql
-- =============================================================================

-- =============================================================================
-- ALTERNATIVA MANUAL (si CREATE PROCEDURE está bloqueado en el hosting)
-- Copie y ejecute bloque por bloque; ignore error 1060/1050 si ya existe.
-- Solo ortodoncia: scripts/add_orthodontics_persons_columns.sql
-- Desde Laravel: php artisan migrate
-- =============================================================================
