-- =============================================================================
-- DENTALSYS — Actualización COMPLETA para base vieja (dump vieja.sql / producción)
-- Ejecutar en phpMyAdmin o consola MySQL sobre la base activa.
-- Idempotente: ignora columna duplicada (1060) y tabla existente (1050).
--
-- Incluye todo lo que el código actual espera y que vieja.sql NO trae:
--   • Plan de tratamiento (forecast)
--   • Ortodoncia (persons + orthodontic_progress)
--   • Pagos de notas de venta (sale_note_payments)
--   • Facturación extendida (sale_notes)
--   • Tratamientos con adjuntos, inventario kardex, agenda, usuarios
--
-- YA EXISTE en vieja (este script no lo repite si ya está):
--   evolution.treatment_id, history.prescription_number
--   establishments.agenda_start_hour / agenda_end_hour
--   sale_notes.voided_at / voided_by_user_id
--   informed_consents, history_informed_consent_signatures
--
-- DESPUÉS de este script, opcional:
--   scripts/odontogenesis_migrate_sale_note_payments.sql  (copiar pagos históricos)
-- =============================================================================

DROP PROCEDURE IF EXISTS dentalsys_vieja_complete_update;

DELIMITER $$

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

  -- ---------------------------------------------------------------------------
  -- 1. PLAN DE TRATAMIENTO (forecast)
  -- ---------------------------------------------------------------------------

  ALTER TABLE `forecast`
    ADD COLUMN `plan_items` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL AFTER `description`;

  ALTER TABLE `forecast`
    ADD COLUMN `medic_id` int(10) UNSIGNED NULL DEFAULT NULL AFTER `specialty_id`;

  ALTER TABLE `forecast`
    ADD COLUMN `date` date NULL DEFAULT NULL AFTER `medic_id`;

  -- ---------------------------------------------------------------------------
  -- 2. EVOLUCIÓN / HISTORIA (por si el dump es más antiguo)
  -- ---------------------------------------------------------------------------

  ALTER TABLE `evolution`
    ADD COLUMN `treatment_id` int(10) UNSIGNED NULL DEFAULT NULL AFTER `medic_id`;

  ALTER TABLE `history`
    ADD COLUMN `prescription_number` int(10) UNSIGNED NULL DEFAULT NULL AFTER `prescription_id`;

  -- ---------------------------------------------------------------------------
  -- 3. 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(10) UNSIGNED NULL DEFAULT NULL;

  ALTER TABLE `sale_notes`
    ADD COLUMN `treatment_id` int(10) UNSIGNED NULL DEFAULT NULL;

  ALTER TABLE `sale_notes`
    ADD COLUMN `specialty_id` int(10) UNSIGNED NULL DEFAULT NULL;

  ALTER TABLE `sale_notes`
    ADD COLUMN `medic_id` int(10) 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;

  -- ---------------------------------------------------------------------------
  -- 4. 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;

  -- ---------------------------------------------------------------------------
  -- 5. TRATAMIENTOS (adjuntos)
  -- ---------------------------------------------------------------------------

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

  -- ---------------------------------------------------------------------------
  -- 6. INVENTARIO (kardex)
  -- ---------------------------------------------------------------------------

  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;

  -- ---------------------------------------------------------------------------
  -- 7. AGENDA / USUARIOS / ESTABLECIMIENTO
  -- ---------------------------------------------------------------------------

  ALTER TABLE `establishments`
    ADD COLUMN `agenda_start_hour` tinyint(3) UNSIGNED DEFAULT NULL;

  ALTER TABLE `establishments`
    ADD COLUMN `agenda_end_hour` tinyint(3) UNSIGNED DEFAULT NULL;

  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`;

  -- ---------------------------------------------------------------------------
  -- 8. LIMPIEZA OPCIONAL (columna temporal en provinces del dump viejo)
  -- Descomente si quiere igualar esquema a nueva.sql:
  -- ALTER TABLE `provinces` DROP COLUMN `_tmp_id`;

END$$

DELIMITER ;

CALL dentalsys_vieja_complete_update();
DROP PROCEDURE dentalsys_vieja_complete_update;

-- =============================================================================
-- VERIFICACIÓN
-- =============================================================================
SHOW COLUMNS FROM `forecast` LIKE 'plan_items';
SHOW COLUMNS FROM `forecast` LIKE 'medic_id';
SHOW COLUMNS FROM `forecast` LIKE 'date';
SHOW COLUMNS FROM `persons` LIKE 'is_orthodontic';
SHOW TABLES LIKE 'orthodontic_progress';
SHOW TABLES LIKE 'sale_note_payments';
SHOW COLUMNS FROM `sale_notes` LIKE 'voided_at';
SHOW COLUMNS FROM `treatments` LIKE 'attachment_files';
SHOW TABLES LIKE 'inventory_kardexes';
SHOW COLUMNS FROM `quote_day_notes` LIKE 'quotes_id';
SHOW COLUMNS FROM `users` LIKE 'person_id';

-- =============================================================================
-- PASO 2 (OPCIONAL): migrar pagos históricos de caja → sale_note_payments
-- Ejecute en una segunda pestaña después de este script:
--   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.
-- =============================================================================
