-- =====================================================
-- SCRIPT: workflow_schema.sql
-- Sistema Check-in/Check-out - Schema Database
-- Data: 2026-01-20
-- =====================================================

-- =====================================================
-- 1. AGGIUNGERE COLONNA WorkflowStatus A APPOINTMENT
-- =====================================================

-- Verifica se la colonna esiste già
SET @col_exists = (SELECT COUNT(*)
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'Appointment'
    AND COLUMN_NAME = 'WorkflowStatus');

-- Aggiunge colonna solo se non esiste
SET @sql = IF(@col_exists = 0,
    'ALTER TABLE Appointment ADD COLUMN WorkflowStatus
        ENUM(''pending'', ''scheduled'', ''arrived'', ''in_visit'', ''completed'', ''cancelled'', ''no_show'')
        DEFAULT ''pending''
        AFTER PatientToPay',
    'SELECT ''Colonna WorkflowStatus già esistente'' AS Status');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- =====================================================
-- 2. AGGIUNGERE INDICE PER PERFORMANCE
-- =====================================================

-- Verifica se l'indice esiste già
SET @idx_exists = (SELECT COUNT(*)
    FROM INFORMATION_SCHEMA.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'Appointment'
    AND INDEX_NAME = 'idx_workflow_status');

SET @sql = IF(@idx_exists = 0,
    'ALTER TABLE Appointment ADD INDEX idx_workflow_status (WorkflowStatus)',
    'SELECT ''Indice idx_workflow_status già esistente'' AS Status');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- =====================================================
-- 3. CREARE TABELLA AppointmentStatusLog
-- =====================================================

CREATE TABLE IF NOT EXISTS AppointmentStatusLog (
    ID INT PRIMARY KEY AUTO_INCREMENT,
    AppointmentID INT NOT NULL,

    -- Transizione
    PreviousStatus ENUM('pending', 'scheduled', 'arrived', 'in_visit', 'completed', 'cancelled', 'no_show'),
    NewStatus ENUM('pending', 'scheduled', 'arrived', 'in_visit', 'completed', 'cancelled', 'no_show') NOT NULL,

    -- Chi e quando
    ChangedByUserID INT NOT NULL,
    ChangedByRole VARCHAR(50),           -- 'doctor', 'assistant', 'secretary'
    ChangedAt DATETIME DEFAULT CURRENT_TIMESTAMP,

    -- Note passaggio (multilingua - salvata nella lingua utente)
    Note TEXT,

    -- Messaggio generato (opzionale)
    GeneratedMessageID INT,              -- FK a ChatMessage se generato

    -- Indici
    INDEX idx_appointment (AppointmentID),
    INDEX idx_changed_at (ChangedAt),
    INDEX idx_user (ChangedByUserID),

    -- Foreign Keys
    FOREIGN KEY (AppointmentID) REFERENCES Appointment(ID) ON DELETE CASCADE,
    FOREIGN KEY (ChangedByUserID) REFERENCES User(ID)
    -- Nota: FK a ChatMessage omessa per evitare dipendenza circolare
    -- GeneratedMessageID sarà validato a livello applicativo

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================
-- 4. VERIFICA CREAZIONE
-- =====================================================

-- Mostra struttura colonna WorkflowStatus
SELECT 'Colonna WorkflowStatus in Appointment:' AS Info;
SHOW COLUMNS FROM Appointment WHERE Field = 'WorkflowStatus';

-- Mostra struttura tabella AppointmentStatusLog
SELECT 'Struttura AppointmentStatusLog:' AS Info;
DESCRIBE AppointmentStatusLog;

SELECT 'Schema workflow creato con successo!' AS Status;
