-- =====================================================
-- SCRIPT: anamnesi_v2_migrate.sql
-- Migrazione dati da tabelle legacy a V2
-- Data: 2026-01-20
-- =====================================================

-- =====================================================
-- FASE 1: Calcolo NextExecutionDate da Log
-- Per ogni anamnesi esistente, calcola la prossima
-- data di esecuzione basandosi sulla data del Log
-- =====================================================

-- 1.1 Aggiungere colonna se non esiste
-- ALTER TABLE CaseHistory ADD COLUMN IF NOT EXISTS NextExecutionDate DATE DEFAULT NULL;

-- 1.2 Popolare NextExecutionDate basandosi sul Log
-- Per ogni anamnesi:
--   - Cerca la data di creazione nel Log (usando LogID)
--   - Imposta NextExecutionDate = data_creazione + 1 anno
UPDATE CaseHistory ch
LEFT JOIN Log l ON ch.LogID = l.UniqueID
SET ch.NextExecutionDate = DATE_ADD(
    COALESCE(
        DATE(l.Date),           -- Prima prova: data dal Log
        DATE(ch.DateSort),      -- Fallback: DateSort dell'anamnesi
        CURDATE()               -- Ultimo fallback: oggi
    ),
    INTERVAL 1 YEAR
)
WHERE ch.NextExecutionDate IS NULL;

-- 1.3 Verifica risultato NextExecutionDate
SELECT
    ch.ID,
    ch.PatientID,
    DATE(ch.DateSort) as DataAnamnesi,
    DATE(l.Date) as DataLog,
    ch.NextExecutionDate as ProssimaEsecuzione,
    CASE
        WHEN ch.NextExecutionDate < CURDATE() THEN 'SCADUTA'
        ELSE 'VALIDA'
    END as Stato
FROM CaseHistory ch
LEFT JOIN Log l ON ch.LogID = l.UniqueID
WHERE ch.Enabled = 1
ORDER BY ch.PatientID, ch.DateSort DESC
LIMIT 20;

-- 1.4 Conteggio finale
SELECT
    COUNT(*) as TotaleAnamnesi,
    SUM(CASE WHEN NextExecutionDate < CURDATE() THEN 1 ELSE 0 END) as Scadute,
    SUM(CASE WHEN NextExecutionDate >= CURDATE() THEN 1 ELSE 0 END) as Valide
FROM CaseHistory
WHERE Enabled = 1;

-- =====================================================

-- FASE 2: Migrazione Domande
INSERT INTO CaseHistoryQuestion_V2 (
    DoctorID,
    AnswerType,
    AnswerWarning,
    DependencyID,
    SortOrder,
    Enabled,
    LegacyID
)
SELECT
    DoctorID,
    -- Determina tipo in base a NeedYesNo e contenuto
    CASE
        WHEN NeedYesNo = 1 THEN 'yesno'
        WHEN Question LIKE '%Peso%' OR Question LIKE '%Weight%' THEN 'decimal'
        WHEN Question LIKE '%Altezza%' OR Question LIKE '%Height%' THEN 'decimal'
        WHEN Question LIKE '%Temperatura%' OR Question LIKE '%Temperature%' THEN 'decimal'
        WHEN Question LIKE '%Pressione%' OR Question LIKE '%Pressure%' THEN 'text'
        ELSE 'text'
    END AS AnswerType,
    COALESCE(SUBSTRING(AnswerWarning, 1, 1), '0'),
    CASE
        WHEN Dependency IS NOT NULL AND Dependency != '0' AND Dependency != ''
        THEN CAST(Dependency AS UNSIGNED)
        ELSE NULL
    END,
    COALESCE(SortOrder, 0),
    COALESCE(Enabled, 1),
    ID  -- LegacyID per riferimento
FROM CaseHistoryQuestion
WHERE Enabled = 1
ON DUPLICATE KEY UPDATE LegacyID = VALUES(LegacyID);

SELECT CONCAT('Migrate ', COUNT(*), ' domande in CaseHistoryQuestion_V2') AS Status
FROM CaseHistoryQuestion_V2;

-- FASE 3: Migrazione Testi Multilingua
-- Italiano (lingua principale)
INSERT INTO CaseHistoryQuestionText (QuestionID, LanguageCode, QuestionText)
SELECT v2.ID, 'IT', q.Question
FROM CaseHistoryQuestion q
JOIN CaseHistoryQuestion_V2 v2 ON v2.LegacyID = q.ID
WHERE q.Enabled = 1
ON DUPLICATE KEY UPDATE QuestionText = VALUES(QuestionText);

-- Inglese (se presente)
INSERT INTO CaseHistoryQuestionText (QuestionID, LanguageCode, QuestionText)
SELECT v2.ID, 'EN', q.QuestionEN
FROM CaseHistoryQuestion q
JOIN CaseHistoryQuestion_V2 v2 ON v2.LegacyID = q.ID
WHERE q.Enabled = 1 AND q.QuestionEN IS NOT NULL AND q.QuestionEN != ''
ON DUPLICATE KEY UPDATE QuestionText = VALUES(QuestionText);

SELECT CONCAT('Migrati ', COUNT(*), ' testi in CaseHistoryQuestionText') AS Status
FROM CaseHistoryQuestionText;

-- FASE 4: Verifica risultato
SELECT
    v2.ID,
    v2.AnswerType,
    v2.AnswerWarning,
    it.QuestionText as ITA,
    en.QuestionText as ENG
FROM CaseHistoryQuestion_V2 v2
LEFT JOIN CaseHistoryQuestionText it ON it.QuestionID = v2.ID AND it.LanguageCode = 'IT'
LEFT JOIN CaseHistoryQuestionText en ON en.QuestionID = v2.ID AND en.LanguageCode = 'EN'
ORDER BY v2.SortOrder;
