-- ============================================================
-- Money+Xfer — Script de Migration v1.0 → v1.1
-- À exécuter sur une base existante (pas sur une nouvelle install)
-- ============================================================

USE akuxfer;

-- 1. Rendre la colonne destination nullable dans transactions
ALTER TABLE transactions
  MODIFY COLUMN destination VARCHAR(120) DEFAULT NULL;

-- 2. Ajouter les permissions manquantes (rates + audit si absentes)
INSERT IGNORE INTO permissions (module, action, label) VALUES
('rates', 'view', 'Voir les taux de change'),
('rates', 'edit', 'Modifier les taux de change'),
('audit', 'view', 'Voir les logs d\'audit');

-- 3. Donner les permissions rates + audit au super_admin et admin
INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT 1, id FROM permissions WHERE module IN ('rates','audit');

INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT 2, id FROM permissions WHERE module IN ('rates','audit');

-- 4. Donner rates view au manager
INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT 3, id FROM permissions WHERE module='rates' AND action='view';

-- 5. S'assurer que audit_logs existe (pour les anciennes install)
CREATE TABLE IF NOT EXISTS audit_logs (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id     INT UNSIGNED DEFAULT NULL,
    action      VARCHAR(80)  NOT NULL,
    module      VARCHAR(60)  NOT NULL,
    record_id   INT UNSIGNED DEFAULT NULL,
    details     JSON         DEFAULT NULL,
    ip          VARCHAR(45)  DEFAULT NULL,
    user_agent  VARCHAR(255) DEFAULT NULL,
    created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_module(module),
    INDEX idx_user(user_id)
) ENGINE=InnoDB;

-- ============================================================
-- Migration v1.3 — Notifications + Reports
-- ============================================================
CREATE TABLE IF NOT EXISTS notifications (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id    INT UNSIGNED DEFAULT NULL,
    type       ENUM('info','success','warning','error','kyc','txn') NOT NULL DEFAULT 'info',
    title      VARCHAR(120) NOT NULL,
    body       TEXT         DEFAULT NULL,
    link       VARCHAR(255) DEFAULT NULL,
    is_read    TINYINT(1)   NOT NULL DEFAULT 0,
    created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_read(user_id, is_read)
) ENGINE=InnoDB;

-- Permissions reports
INSERT IGNORE INTO permissions (module, action, label) VALUES
('reports', 'view',   'Voir les rapports'),
('reports', 'export', 'Exporter les rapports');

INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT 1, id FROM permissions WHERE module='reports';
INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT 2, id FROM permissions WHERE module='reports';
INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT 3, id FROM permissions WHERE module='reports' AND action='view';

-- ============================================================
-- Migration v1.4 — Sécurité, Remboursements, Alertes KYC
-- ============================================================

-- Rate limiting / brute force protection
CREATE TABLE IF NOT EXISTS login_attempts (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ip         VARCHAR(45)  NOT NULL,
    email      VARCHAR(160) DEFAULT NULL,
    attempted_at DATETIME   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    success    TINYINT(1)   NOT NULL DEFAULT 0,
    INDEX idx_ip_time(ip, attempted_at),
    INDEX idx_email(email)
) ENGINE=InnoDB;

-- Gestion des remboursements
CREATE TABLE IF NOT EXISTS refunds (
    id              INT UNSIGNED  AUTO_INCREMENT PRIMARY KEY,
    transaction_id  INT UNSIGNED  NOT NULL,
    requested_by    INT UNSIGNED  NOT NULL,
    approved_by     INT UNSIGNED  DEFAULT NULL,
    reason          TEXT          NOT NULL,
    amount          DECIMAL(18,2) NOT NULL,
    currency        VARCHAR(5)    NOT NULL DEFAULT 'XAF',
    status          ENUM('en_attente','approuve','rejete','traite') NOT NULL DEFAULT 'en_attente',
    admin_notes     TEXT          DEFAULT NULL,
    requested_at    DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    processed_at    DATETIME      DEFAULT NULL,
    FOREIGN KEY (transaction_id) REFERENCES transactions(id) ON DELETE CASCADE,
    FOREIGN KEY (requested_by)   REFERENCES users(id),
    FOREIGN KEY (approved_by)    REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_status(status),
    INDEX idx_txn(transaction_id)
) ENGINE=InnoDB;

-- Alertes KYC expirées (pour les documents à renouveler)
-- Compatible MySQL 5.7+ : on vérifie l'existence via INFORMATION_SCHEMA avant d'ajouter
SET @dbname = DATABASE();

SET @col1 = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA=@dbname AND TABLE_NAME='clients' AND COLUMN_NAME='id_expires_at');
SET @sql1 = IF(@col1=0,
  'ALTER TABLE clients ADD COLUMN id_expires_at DATE DEFAULT NULL',
  'SELECT "id_expires_at already exists" AS info');
PREPARE stmt1 FROM @sql1; EXECUTE stmt1; DEALLOCATE PREPARE stmt1;

SET @col2 = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA=@dbname AND TABLE_NAME='clients' AND COLUMN_NAME='kyc_alert_sent');
SET @sql2 = IF(@col2=0,
  'ALTER TABLE clients ADD COLUMN kyc_alert_sent TINYINT(1) DEFAULT 0',
  'SELECT "kyc_alert_sent already exists" AS info');
PREPARE stmt2 FROM @sql2; EXECUTE stmt2; DEALLOCATE PREPARE stmt2;

-- Permissions remboursements
INSERT IGNORE INTO permissions (module, action, label) VALUES
('refunds','view',    'Voir les remboursements'),
('refunds','create',  'Demander un remboursement'),
('refunds','approve', 'Approuver un remboursement');

INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT 1, id FROM permissions WHERE module='refunds';
INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT 2, id FROM permissions WHERE module='refunds';
INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT 3, id FROM permissions WHERE module='refunds' AND action IN ('view','create');
INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT 4, id FROM permissions WHERE module='refunds' AND action='create';

-- ============================================================
-- Migration v1.5 — Bénéficiaires, Portail Client, Tracking, Email
-- ============================================================

-- Bénéficiaires enregistrés
CREATE TABLE IF NOT EXISTS beneficiaries (
    id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    client_id    INT UNSIGNED DEFAULT NULL,
    user_id      INT UNSIGNED DEFAULT NULL,
    label        VARCHAR(80)  NOT NULL,
    full_name    VARCHAR(120) NOT NULL,
    bank_name    VARCHAR(120) DEFAULT NULL,
    iban         VARCHAR(60)  DEFAULT NULL,
    swift        VARCHAR(20)  DEFAULT NULL,
    phone        VARCHAR(30)  DEFAULT NULL,
    country      VARCHAR(80)  DEFAULT NULL,
    region       ENUM('afrique','europe','asie','amerique','moyen_orient') DEFAULT NULL,
    currency     VARCHAR(5)   DEFAULT NULL,
    is_active    TINYINT(1)   NOT NULL DEFAULT 1,
    use_count    INT UNSIGNED NOT NULL DEFAULT 0,
    created_at   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id)   REFERENCES users(id)   ON DELETE SET NULL,
    INDEX idx_client(client_id),
    INDEX idx_user(user_id)
) ENGINE=InnoDB;

-- Portail client : tokens d'accès
CREATE TABLE IF NOT EXISTS client_tokens (
    id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    client_id    INT UNSIGNED NOT NULL,
    token        VARCHAR(64)  NOT NULL UNIQUE,
    type         ENUM('login','tracking') NOT NULL DEFAULT 'login',
    expires_at   DATETIME     NOT NULL,
    used_at      DATETIME     DEFAULT NULL,
    created_at   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
    INDEX idx_token(token),
    INDEX idx_client(client_id)
) ENGINE=InnoDB;

-- File d'attente emails
CREATE TABLE IF NOT EXISTS email_queue (
    id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    to_email     VARCHAR(160) NOT NULL,
    to_name      VARCHAR(120) DEFAULT NULL,
    subject      VARCHAR(255) NOT NULL,
    body_html    MEDIUMTEXT   NOT NULL,
    status       ENUM('pending','sent','failed') NOT NULL DEFAULT 'pending',
    attempts     TINYINT      NOT NULL DEFAULT 0,
    error        TEXT         DEFAULT NULL,
    created_at   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    sent_at      DATETIME     DEFAULT NULL,
    INDEX idx_status(status)
) ENGINE=InnoDB;

-- Permissions nouvelles
INSERT IGNORE INTO permissions (module, action, label) VALUES
('beneficiaries','view',   'Voir les bénéficiaires'),
('beneficiaries','create', 'Créer un bénéficiaire'),
('beneficiaries','delete', 'Supprimer un bénéficiaire');

INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT 1, id FROM permissions WHERE module='beneficiaries';
INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT 2, id FROM permissions WHERE module='beneficiaries';
INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT 3, id FROM permissions WHERE module='beneficiaries';
INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT 4, id FROM permissions WHERE module='beneficiaries' AND action IN ('view','create');

-- ============================================================
-- Migration v1.6 — Double Authentification (2FA TOTP)
-- ============================================================
SET @dbname = DATABASE();

-- Colonne secret TOTP
SET @c1 = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@dbname AND TABLE_NAME='users' AND COLUMN_NAME='totp_secret');
SET @s1 = IF(@c1=0,'ALTER TABLE users ADD COLUMN totp_secret VARCHAR(32) DEFAULT NULL','SELECT "totp_secret exists" AS info');
PREPARE st FROM @s1; EXECUTE st; DEALLOCATE PREPARE st;

-- Colonne 2FA activé
SET @c2 = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@dbname AND TABLE_NAME='users' AND COLUMN_NAME='totp_enabled');
SET @s2 = IF(@c2=0,'ALTER TABLE users ADD COLUMN totp_enabled TINYINT(1) NOT NULL DEFAULT 0','SELECT "totp_enabled exists" AS info');
PREPARE st FROM @s2; EXECUTE st; DEALLOCATE PREPARE st;

-- Codes de secours (backup codes)
CREATE TABLE IF NOT EXISTS totp_backup_codes (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id    INT UNSIGNED NOT NULL,
    code_hash  VARCHAR(64)  NOT NULL,
    used_at    DATETIME     DEFAULT NULL,
    created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user(user_id)
) ENGINE=InnoDB;

-- ============================================================
-- Migration v1.7 — PIN de validation transfert
-- ============================================================
SET @dbname = DATABASE();
SET @cp = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@dbname AND TABLE_NAME='users' AND COLUMN_NAME='pin_hash');
SET @sp = IF(@cp=0,'ALTER TABLE users ADD COLUMN pin_hash VARCHAR(255) DEFAULT NULL','SELECT "pin_hash exists" AS info');
PREPARE st FROM @sp; EXECUTE st; DEALLOCATE PREPARE st;