-- =====================================================
-- SCRIPT: workflow_migrate.sql
-- Sistema Check-in/Check-out - Migrazione Dati Esistenti
-- ESEGUIRE DOPO workflow_schema.sql
-- Data: 2026-01-20
-- =====================================================

-- =====================================================
-- FASE 1: BACKUP DATI ORIGINALI (per sicurezza)
-- =====================================================

-- Crea tabella temporanea con stato originale
CREATE TEMPORARY TABLE IF NOT EXISTS _Appointment_Backup_Workflow AS
SELECT ID, PatientArrived, PatientPaid, Canceled, WorkflowStatus
FROM Appointment;

SELECT 'Backup creato: ' AS Info, COUNT(*) AS Records FROM _Appointment_Backup_Workflow;

-- =====================================================
-- FASE 2: POPOLARE WorkflowStatus DA CAMPI ESISTENTI
-- =====================================================

-- Mapping logico:
-- Canceled=1                          → 'cancelled'
-- PatientArrived=1 AND PatientPaid=1  → 'completed'
-- PatientArrived=1 AND PatientPaid=0  → 'arrived'
-- Default                             → 'scheduled'
-- (no_show e in_visit non hanno equivalente legacy)

UPDATE Appointment SET WorkflowStatus =
    CASE
        -- Annullato (priorità massima)
        WHEN Canceled = 1 THEN 'cancelled'

        -- Completato (arrivato E pagato)
        WHEN PatientArrived = 1 AND PatientPaid = 1 THEN 'completed'

        -- Arrivato ma non pagato
        WHEN PatientArrived = 1 AND (PatientPaid = 0 OR PatientPaid IS NULL) THEN 'arrived'

        -- Default: scheduled
        ELSE 'scheduled'
    END
WHERE WorkflowStatus IS NULL
   OR WorkflowStatus = 'scheduled';

-- Conta risultati migrazione
SELECT 'Migrazione WorkflowStatus completata:' AS Info;

SELECT
    WorkflowStatus,
    COUNT(*) AS Totale,
    SUM(CASE WHEN PatientArrived = 1 THEN 1 ELSE 0 END) AS ConArrived,
    SUM(CASE WHEN PatientPaid = 1 THEN 1 ELSE 0 END) AS ConPaid,
    SUM(CASE WHEN Canceled = 1 THEN 1 ELSE 0 END) AS Cancellati
FROM Appointment
GROUP BY WorkflowStatus
ORDER BY
    FIELD(WorkflowStatus, 'scheduled', 'arrived', 'in_visit', 'completed', 'cancelled', 'no_show');

-- =====================================================
-- FASE 3: CREARE LOG INIZIALE PER APPUNTAMENTI NON-SCHEDULED
-- =====================================================

-- Per ogni appuntamento con WorkflowStatus diverso da 'scheduled',
-- crea un record log "migrazione automatica"

INSERT INTO AppointmentStatusLog (
    AppointmentID,
    PreviousStatus,
    NewStatus,
    ChangedByUserID,
    ChangedByRole,
    ChangedAt,
    Note
)
SELECT
    a.ID,
    NULL,                                                    -- PreviousStatus NULL = stato iniziale
    a.WorkflowStatus,
    COALESCE(a.Created_byUserID, 1),                        -- Usa creatore appuntamento o user ID 1
    'system',                                                -- Ruolo sistema
    COALESCE(a.Created_date, a.StartDateTime, NOW()),       -- Data creazione o inizio appuntamento
    'Migrazione automatica da sistema precedente (pallini)' -- Nota esplicativa
FROM Appointment a
WHERE a.WorkflowStatus IS NOT NULL
  AND a.WorkflowStatus != 'scheduled'
  AND NOT EXISTS (
      SELECT 1 FROM AppointmentStatusLog l
      WHERE l.AppointmentID = a.ID
  );

-- Conta log creati
SELECT 'Log migrazione creati:' AS Info, COUNT(*) AS LogCreati
FROM AppointmentStatusLog
WHERE Note LIKE 'Migrazione automatica%';

-- =====================================================
-- FASE 4: VERIFICA COERENZA
-- =====================================================

-- Verifica che non ci siano incongruenze
SELECT 'Verifica coerenza WorkflowStatus vs Pallini:' AS Info;

SELECT
    'ATTENZIONE: Incongruenze trovate' AS Problema,
    COUNT(*) AS Numero
FROM Appointment
WHERE
    -- arrived/in_visit/completed dovrebbero avere PatientArrived=1
    (WorkflowStatus IN ('arrived', 'in_visit', 'completed') AND PatientArrived != 1)
    OR
    -- completed dovrebbe avere PatientPaid=1
    (WorkflowStatus = 'completed' AND PatientPaid != 1)
    OR
    -- cancelled dovrebbe avere Canceled=1
    (WorkflowStatus = 'cancelled' AND Canceled != 1)
HAVING COUNT(*) > 0;

-- Se nessuna incongruenza, mostra successo
SELECT 'Nessuna incongruenza trovata - Migrazione OK!' AS Status
WHERE NOT EXISTS (
    SELECT 1 FROM Appointment
    WHERE
        (WorkflowStatus IN ('arrived', 'in_visit', 'completed') AND PatientArrived != 1)
        OR (WorkflowStatus = 'completed' AND PatientPaid != 1)
        OR (WorkflowStatus = 'cancelled' AND Canceled != 1)
);

-- =====================================================
-- FASE 5: RIEPILOGO FINALE
-- =====================================================

SELECT '========== RIEPILOGO MIGRAZIONE ==========' AS Info;

SELECT 'Totale appuntamenti:' AS Metrica, COUNT(*) AS Valore FROM Appointment
UNION ALL
SELECT 'Appuntamenti migrati (non-scheduled):' AS Metrica, COUNT(*) AS Valore
FROM Appointment WHERE WorkflowStatus != 'scheduled'
UNION ALL
SELECT 'Log transizioni creati:' AS Metrica, COUNT(*) AS Valore FROM AppointmentStatusLog;

SELECT '===========================================' AS Info;
SELECT 'Migrazione completata con successo!' AS Status;
