-- Schéma MySQL pour le callbot TOPELEC
-- A exécuter une fois dans la base MySQL de votre hébergement OVH (phpMyAdmin ou ligne de commande).
-- Cette table stocke l'état de conversation de chaque appel en cours (le webhook PHP étant sans état
-- entre deux requêtes HTTP, on a besoin de retrouver l'historique à chaque tour de parole).

CREATE TABLE IF NOT EXISTS callbot_calls (
    id INT AUTO_INCREMENT PRIMARY KEY,
    call_sid VARCHAR(64) NOT NULL UNIQUE,
    from_number VARCHAR(32) NULL,
    to_number VARCHAR(32) NULL,
    status ENUM('en_cours', 'transfere', 'rdv_pris', 'message_pris', 'termine', 'erreur') NOT NULL DEFAULT 'en_cours',
    turn_count INT NOT NULL DEFAULT 0,
    transcript_json MEDIUMTEXT NULL COMMENT 'Historique de la conversation au format JSON [{role, content}, ...]',
    lead_data_json TEXT NULL COMMENT 'Informations collectées sur le prospect (nom, tel, besoin) au format JSON',
    appointment_data_json TEXT NULL COMMENT 'Détails du rendez-vous proposé/confirmé au format JSON',
    dolibarr_thirdparty_id INT NULL COMMENT 'ID du tiers Dolibarr créé/mis à jour pour cet appel',
    dolibarr_event_id INT NULL COMMENT 'ID de l événement agenda Dolibarr créé pour le RDV',
    dolibarr_ticket_id INT NULL COMMENT 'ID du ticket Dolibarr créé (dépannage/urgence)',
    dolibarr_synced TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Passe à 1 une fois les données bien remontées dans Dolibarr',
    known_name VARCHAR(191) NULL COMMENT 'Nom du tiers Dolibarr si l appelant a été identifié par son numéro dès le début de l appel',
    flow_state VARCHAR(32) NULL COMMENT 'Sous-flux en cours en dehors de la boucle LLM normale : NULL, awaiting_otp, awaiting_appointment_choice',
    flow_context_json TEXT NULL COMMENT 'Contexte du sous-flux en cours (ex: liste des RDV proposés, action visée modify/cancel)',
    auth_verified TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Passe à 1 une fois le code OTP envoyé par SMS vérifié avec succès pour cet appel',
    auth_otp_code VARCHAR(10) NULL COMMENT 'Code OTP en attente de vérification (effacé une fois vérifié ou expiré)',
    auth_otp_expires_at DATETIME NULL,
    auth_otp_attempts INT NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_status (status),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
