-- =============================================================================
-- dentalbuysv_genesis — Solo cambios de BASE DE DATOS (sin tocar código)
-- Ejecutar en phpMyAdmin o consola MySQL del servidor de producción.
-- Idempotente: ignora columna duplicada (1060) y tabla existente (1050).
-- =============================================================================
--
-- Según el dump dentalbuysv_genesis.sql, YA EXISTEN en producción:
--   sale_notes.voided_at, sale_notes.voided_by_user_id
--   evolution.treatment_id
--   history.prescription_number
--   establishments.agenda_start_hour, establishments.agenda_end_hour
--
-- LO QUE FALTA y el código de producción espera:
--   1) forecast.medic_id, forecast.date, forecast.plan_items  (plan de tratamiento)
--   2) sale_note_payments                                     (pagos al guardar factura / PDF)
-- =============================================================================

DROP PROCEDURE IF EXISTS dentalsys_genesis_schema;

DELIMITER $$

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

  -- Plan de tratamiento
  ALTER TABLE `forecast`
    ADD COLUMN `medic_id` INT UNSIGNED NULL DEFAULT NULL AFTER `specialty_id`;

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

  ALTER TABLE `forecast`
    ADD COLUMN `plan_items` JSON NULL DEFAULT NULL AFTER `description`;

  -- Pagos de notas de venta (igual estructura que document_payments + change)
  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;
END$$

DELIMITER ;

CALL dentalsys_genesis_schema();
DROP PROCEDURE dentalsys_genesis_schema;

-- Verificación
SHOW COLUMNS FROM `forecast` LIKE 'medic_id';
SHOW COLUMNS FROM `forecast` LIKE 'date';
SHOW COLUMNS FROM `forecast` LIKE 'plan_items';
SHOW TABLES LIKE 'sale_note_payments';

-- =============================================================================
-- Si CREATE PROCEDURE está bloqueado en el hosting, ejecute línea por línea
-- (ignore error 1060/1050 si ya existe):
--
-- ALTER TABLE `forecast` ADD COLUMN `medic_id` INT UNSIGNED NULL AFTER `specialty_id`;
-- ALTER TABLE `forecast` ADD COLUMN `date` DATE NULL AFTER `medic_id`;
-- ALTER TABLE `forecast` ADD COLUMN `plan_items` JSON NULL AFTER `description`;
--
-- CREATE TABLE IF NOT EXISTS `sale_note_payments` ( ... igual que arriba ... );
-- =============================================================================
