-- Ortodoncia: columnas en persons + tabla orthodontic_progress
-- Ejecutar en la base activa de .env (DB_DATABASE, p. ej. dental).
-- Idempotente: ignore error 1060 (columna duplicada) y 1050 (tabla existente).

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;

-- Verificación
SHOW COLUMNS FROM `persons` LIKE 'is_orthodontic';
SHOW TABLES LIKE 'orthodontic_progress';
