-- ============================================================
-- Money+Xfer — Migration Portail Client (tables wallet)
-- À exécuter APRÈS schema.sql et AVANT migration_v2.1
--
-- Crée :
--   - client_accounts       (comptes portail client)
--   - client_wallets        (wallet XAF par compte)
--   - client_wallet_transactions (mouvements wallet)
--   - wallet_transactions   (VIEW alias pour le portail)
--   - client_notifications  (notifications in-app)
--
-- Compatible MySQL 5.7+ / MySQL 8+ (sans IF NOT EXISTS sur ALTER)
-- ============================================================

USE akuxfer;

-- ============================================================
-- 1. COMPTES PORTAIL CLIENT
-- ============================================================
CREATE TABLE IF NOT EXISTS client_accounts (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    first_name    VARCHAR(80)  NOT NULL,
    last_name     VARCHAR(80)  NOT NULL,
    email         VARCHAR(160) NOT NULL UNIQUE,
    phone         VARCHAR(40)  DEFAULT NULL,
    password_hash VARCHAR(255) NOT NULL,
    kyc_status    ENUM('non_soumis','soumis','en_cours','approuve','rejete') NOT NULL DEFAULT 'non_soumis',
    is_active     TINYINT(1)   NOT NULL DEFAULT 1,
    created_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_email(email)
) ENGINE=InnoDB;

-- ============================================================
-- 2. WALLETS XAF
-- ============================================================
CREATE TABLE IF NOT EXISTS client_wallets (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    account_id  INT UNSIGNED NOT NULL UNIQUE,
    balance     DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    currency    VARCHAR(10)   NOT NULL DEFAULT 'XAF',
    status      ENUM('actif','bloque','ferme') NOT NULL DEFAULT 'actif',
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (account_id) REFERENCES client_accounts(id) ON DELETE CASCADE,
    INDEX idx_account(account_id)
) ENGINE=InnoDB;

-- ============================================================
-- 3. MOUVEMENTS WALLET (utilisé par le back-office)
-- ============================================================
CREATE TABLE IF NOT EXISTS client_wallet_transactions (
    id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    wallet_id      INT UNSIGNED NOT NULL,
    account_id     INT UNSIGNED NOT NULL,
    type           ENUM('credit','debit') NOT NULL,
    amount         DECIMAL(15,2) NOT NULL,
    balance_before DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    balance_after  DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    label          VARCHAR(255)  DEFAULT NULL,
    recharge_id    INT UNSIGNED  DEFAULT NULL,
    request_id     INT UNSIGNED  DEFAULT NULL,
    created_by     VARCHAR(100)  DEFAULT NULL,
    created_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (wallet_id)  REFERENCES client_wallets(id) ON DELETE CASCADE,
    FOREIGN KEY (account_id) REFERENCES client_accounts(id) ON DELETE CASCADE,
    INDEX idx_wallet(wallet_id),
    INDEX idx_account(account_id),
    INDEX idx_created(created_at)
) ENGINE=InnoDB;

-- ============================================================
-- 4. VIEW wallet_transactions (alias pour le portail client)
--    Le portail utilise wallet_transactions,
--    le back-office utilise client_wallet_transactions.
--    Cette vue unifie les deux.
-- ============================================================
CREATE OR REPLACE VIEW wallet_transactions AS
SELECT
    id,
    wallet_id,
    account_id,
    type,
    amount,
    balance_before,
    balance_after,
    label,
    recharge_id,
    request_id,
    created_by,
    NULL AS created_by_user_id,
    created_at
FROM client_wallet_transactions;

-- ============================================================
-- 5. NOTIFICATIONS IN-APP CLIENT
-- ============================================================
CREATE TABLE IF NOT EXISTS client_notifications (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    account_id INT UNSIGNED NOT NULL,
    type       ENUM(
                   'success','info','warning','error',
                   'kyc','kyc_submitted',
                   'transfer_created','transfer_update',
                   'docs_submitted',
                   'wallet_credit','wallet_debit'
               ) NOT NULL DEFAULT 'info',
    title      VARCHAR(160) 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 (account_id) REFERENCES client_accounts(id) ON DELETE CASCADE,
    INDEX idx_account_read(account_id, is_read),
    INDEX idx_created(created_at)
) ENGINE=InnoDB;

-- ============================================================
-- 6. TABLE wallet_recharges (si pas encore créée par v3.1)
-- ============================================================
CREATE TABLE IF NOT EXISTS wallet_recharges (
    id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    account_id   INT UNSIGNED NOT NULL,
    wallet_id    INT UNSIGNED NOT NULL,
    amount       DECIMAL(15,2) NOT NULL,
    currency     VARCHAR(10)   NOT NULL DEFAULT 'XAF',
    method       ENUM('virement','mobile_money','cash','carte','wallet_interne') NOT NULL DEFAULT 'virement',
    reference    VARCHAR(100)  DEFAULT NULL,
    proof_file   VARCHAR(255)  DEFAULT NULL,
    notes        TEXT          DEFAULT NULL,
    status       ENUM('en_attente','approuvee','rejetee') NOT NULL DEFAULT 'en_attente',
    admin_id     INT UNSIGNED  DEFAULT NULL,
    admin_notes  TEXT          DEFAULT NULL,
    processed_at DATETIME      DEFAULT NULL,
    created_at   DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at   DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (account_id) REFERENCES client_accounts(id) ON DELETE CASCADE,
    FOREIGN KEY (wallet_id)  REFERENCES client_wallets(id)  ON DELETE CASCADE,
    INDEX idx_account(account_id),
    INDEX idx_status(status),
    INDEX idx_created(created_at)
) ENGINE=InnoDB;

-- ============================================================
-- 7. PERMISSIONS WALLET (idempotent)
-- ============================================================
INSERT IGNORE INTO permissions (module, action, label) VALUES
('wallet', 'view',             'Voir les wallets clients'),
('wallet', 'manage',           'Gérer les wallets clients'),
('wallet', 'manage_recharges', 'Gérer les demandes de recharge wallet'),
('wallet', 'approve_recharge', 'Approuver une recharge wallet'),
('wallet', 'reject_recharge',  'Rejeter une recharge wallet');

-- Assigner toutes les permissions wallet aux rôles 1 et 2 (super_admin, admin)
INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r, permissions p
WHERE r.id IN (1,2) AND p.module = 'wallet';

-- Manager (role_id=3) : voir + gérer + approuver + rejeter recharges
INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r, permissions p
WHERE r.id = 3 AND p.module = 'wallet'
  AND p.action IN ('view','manage_recharges','approve_recharge','reject_recharge');

-- ============================================================
-- 8. ENUM kyc_status sur client_accounts : ajouter 'complement'
-- ============================================================
ALTER TABLE client_accounts
  MODIFY COLUMN kyc_status ENUM('non_soumis','soumis','en_attente','approuve','complement','rejete','valide')
  NOT NULL DEFAULT 'non_soumis';

SELECT 'Migration portail client (wallet) appliquée avec succès.' AS result;