-- =====================================================
-- SCRIPT: anamnesi_v2_schema.sql
-- Creazione tabelle V2 per sistema anamnesi
-- Data: 2026-01-20
-- =====================================================

-- Tabella domande V2
CREATE TABLE IF NOT EXISTS CaseHistoryQuestion_V2 (
    ID INT PRIMARY KEY AUTO_INCREMENT,
    DoctorID INT NOT NULL,

    -- Tipo risposta
    AnswerType ENUM('yesno', 'integer', 'decimal', 'text', 'calculated') NOT NULL DEFAULT 'yesno',

    -- Configurazione numerica
    DecimalPlaces TINYINT DEFAULT 0,
    `MinValue` DECIMAL(10,3) DEFAULT NULL,
    `MaxValue` DECIMAL(10,3) DEFAULT NULL,
    WarningMinValue DECIMAL(10,3) DEFAULT NULL,
    WarningMaxValue DECIMAL(10,3) DEFAULT NULL,
    Unit VARCHAR(20) DEFAULT NULL,

    -- Warning per Si/No
    AnswerWarning CHAR(1) DEFAULT '0',

    -- Campo calcolato
    Formula VARCHAR(500) DEFAULT NULL,
    FormulaAlias VARCHAR(50) DEFAULT NULL,

    -- Dipendenza
    DependencyID INT DEFAULT NULL,
    DependencyValue VARCHAR(10) DEFAULT '1',

    -- Ordinamento e stato
    SortOrder INT DEFAULT 0,
    Enabled TINYINT DEFAULT 1,

    -- Riferimento a vecchia tabella (per migrazione)
    LegacyID INT DEFAULT NULL,

    -- Timestamp
    CreatedDate DATETIME DEFAULT CURRENT_TIMESTAMP,
    UpdatedDate DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_doctor (DoctorID),
    INDEX idx_sort (DoctorID, SortOrder),
    INDEX idx_legacy (LegacyID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Tabella testi multilingua
CREATE TABLE IF NOT EXISTS CaseHistoryQuestionText (
    ID INT PRIMARY KEY AUTO_INCREMENT,
    QuestionID INT NOT NULL,
    LanguageCode CHAR(2) NOT NULL,
    QuestionText VARCHAR(500) NOT NULL,
    HelpText VARCHAR(500) DEFAULT NULL,

    UNIQUE KEY uk_question_lang (QuestionID, LanguageCode),
    FOREIGN KEY (QuestionID) REFERENCES CaseHistoryQuestion_V2(ID) ON DELETE CASCADE,
    INDEX idx_lang (LanguageCode)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Tabella risposte V2
CREATE TABLE IF NOT EXISTS CaseHistoryRow_V2 (
    ID INT PRIMARY KEY AUTO_INCREMENT,
    CaseHistoryID INT NOT NULL,
    QuestionID INT NOT NULL,

    -- Valori tipizzati
    AnswerYesNo TINYINT DEFAULT NULL,
    AnswerInteger INT DEFAULT NULL,
    AnswerDecimal DECIMAL(10,3) DEFAULT NULL,
    AnswerText TEXT DEFAULT NULL,
    AnswerCalculated DECIMAL(10,3) DEFAULT NULL,

    -- Note
    Note TEXT DEFAULT NULL,

    -- Riferimento a vecchia riga (per migrazione)
    LegacyID INT DEFAULT NULL,

    -- Timestamp
    LastModified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    LastModifiedByUserID INT,

    FOREIGN KEY (CaseHistoryID) REFERENCES CaseHistory(ID) ON DELETE CASCADE,
    FOREIGN KEY (QuestionID) REFERENCES CaseHistoryQuestion_V2(ID),
    UNIQUE KEY uk_history_question (CaseHistoryID, QuestionID),
    INDEX idx_question (QuestionID),
    INDEX idx_legacy (LegacyID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SELECT 'Tabelle V2 create con successo' AS Status;
