-- ════════════════════════════════════════════════════════════════
-- SIRA+ — Migration v3
-- Ajoute : HLS adaptatif + transactions de paiement LigdiCash
-- Idempotente (réexécutable sans erreur)
-- ════════════════════════════════════════════════════════════════

-- Procédure utilitaire pour ajouter une colonne si absente
DROP PROCEDURE IF EXISTS sira_add_col_v3;
DELIMITER $$
CREATE PROCEDURE sira_add_col_v3(
    IN tbl VARCHAR(64), IN col VARCHAR(64), IN def TEXT
)
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns
        WHERE table_schema = DATABASE() AND table_name = tbl AND column_name = col
    ) THEN
        SET @sql = CONCAT('ALTER TABLE `', tbl, '` ADD COLUMN `', col, '` ', def);
        PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
    END IF;
END$$
DELIMITER ;

-- Colonnes HLS
CALL sira_add_col_v3('contents', 'hls_path', "VARCHAR(255) DEFAULT NULL");
CALL sira_add_col_v3('episodes', 'hls_path', "VARCHAR(255) DEFAULT NULL");

-- Table des transactions de paiement (LigdiCash / Mobile Money)
CREATE TABLE IF NOT EXISTS payment_transactions (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    user_id         INT NOT NULL,
    plan_id         INT NOT NULL,
    amount          INT NOT NULL,
    currency        VARCHAR(8) DEFAULT 'XOF',
    provider        VARCHAR(32) DEFAULT 'ligdicash',
    token           VARCHAR(128) DEFAULT NULL,
    invoice_token   VARCHAR(128) DEFAULT NULL,
    status          ENUM('pending','completed','failed','cancelled') DEFAULT 'pending',
    payment_method  VARCHAR(32) DEFAULT NULL,
    customer_phone  VARCHAR(32) DEFAULT NULL,
    raw_response    TEXT DEFAULT NULL,
    created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_user (user_id),
    INDEX idx_token (token),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

DROP PROCEDURE IF EXISTS sira_add_col_v3;