-- ============================================================
-- AKUXFER — Plateforme de Paiement International
-- République du Congo — Brazzaville
-- ============================================================

CREATE DATABASE IF NOT EXISTS akuxfer CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE akuxfer;

-- ============================================================
-- PARAMETRAGE SOCIETE
-- ============================================================
CREATE TABLE company_settings (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name            VARCHAR(120) NOT NULL DEFAULT 'Money+Xfer',
    slogan          VARCHAR(255) DEFAULT NULL,
    logo            VARCHAR(255) DEFAULT NULL,
    logo_dark       VARCHAR(255) DEFAULT NULL,
    email           VARCHAR(160) DEFAULT NULL,
    phone           VARCHAR(30)  DEFAULT NULL,
    phone2          VARCHAR(30)  DEFAULT NULL,
    address         TEXT         DEFAULT NULL,
    city            VARCHAR(80)  DEFAULT 'Brazzaville',
    country         VARCHAR(80)  DEFAULT 'République du Congo',
    website         VARCHAR(255) DEFAULT NULL,
    rccm            VARCHAR(80)  DEFAULT NULL,
    nif             VARCHAR(80)  DEFAULT NULL,
    capital         DECIMAL(18,2) DEFAULT NULL,
    iban            VARCHAR(60)  DEFAULT NULL,
    swift           VARCHAR(20)  DEFAULT NULL,
    bank_name       VARCHAR(120) DEFAULT NULL,
    primary_color   VARCHAR(10)  DEFAULT '#5AB849',
    secondary_color VARCHAR(10)  DEFAULT '#0B3D67',
    currency_base   VARCHAR(5)   DEFAULT 'XAF',
    invoice_prefix  VARCHAR(20)  DEFAULT 'MXF',
    invoice_footer  TEXT         DEFAULT NULL,
    qr_secret       VARCHAR(64)  DEFAULT NULL,
    updated_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ============================================================
-- ROLES, PERMISSIONS, USERS
-- ============================================================
CREATE TABLE roles (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(60)  NOT NULL UNIQUE,
    label       VARCHAR(100) NOT NULL,
    description TEXT         DEFAULT NULL,
    color       VARCHAR(10)  DEFAULT '#6B7280',
    created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE permissions (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    module      VARCHAR(60)  NOT NULL,
    action      VARCHAR(60)  NOT NULL,
    label       VARCHAR(120) NOT NULL,
    UNIQUE KEY uq_perm (module, action)
) ENGINE=InnoDB;

CREATE TABLE role_permissions (
    role_id       INT UNSIGNED NOT NULL,
    permission_id INT UNSIGNED NOT NULL,
    PRIMARY KEY (role_id, permission_id),
    FOREIGN KEY (role_id)       REFERENCES roles(id)       ON DELETE CASCADE,
    FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE users (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    role_id       INT UNSIGNED NOT NULL,
    first_name    VARCHAR(80)  NOT NULL,
    last_name     VARCHAR(80)  NOT NULL,
    email         VARCHAR(160) NOT NULL UNIQUE,
    phone         VARCHAR(30)  DEFAULT NULL,
    password_hash VARCHAR(255) NOT NULL,
    avatar        VARCHAR(255) DEFAULT NULL,
    status        ENUM('actif','inactif','suspendu') NOT NULL DEFAULT 'actif',
    last_login    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 (role_id) REFERENCES roles(id),
    INDEX idx_email(email)
) ENGINE=InnoDB;

-- ============================================================
-- MODULE 1 — PAIEMENT INTERNATIONAL
-- ============================================================
CREATE TABLE currencies (
    id        INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code      VARCHAR(5)  NOT NULL UNIQUE,
    name      VARCHAR(80) NOT NULL,
    symbol    VARCHAR(10) NOT NULL,
    country   VARCHAR(80) NOT NULL,
    region    ENUM('afrique','europe','asie','amerique','moyen_orient') NOT NULL,
    flag      VARCHAR(10) DEFAULT NULL,
    is_active TINYINT(1)  NOT NULL DEFAULT 1
) ENGINE=InnoDB;

CREATE TABLE exchange_rates (
    id            INT UNSIGNED  AUTO_INCREMENT PRIMARY KEY,
    from_currency VARCHAR(5)    NOT NULL,
    to_currency   VARCHAR(5)    NOT NULL,
    rate          DECIMAL(16,6) NOT NULL,
    fee_pct       DECIMAL(5,2)  NOT NULL DEFAULT 1.50,
    updated_at    DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_pair (from_currency, to_currency)
) ENGINE=InnoDB;

CREATE TABLE transactions (
    id              INT UNSIGNED  AUTO_INCREMENT PRIMARY KEY,
    reference       VARCHAR(30)   NOT NULL UNIQUE,
    user_id         INT UNSIGNED  NOT NULL,
    client_id       INT UNSIGNED  DEFAULT NULL,
    type            ENUM('envoi','reception') NOT NULL DEFAULT 'envoi',
    status          ENUM('en_attente','en_cours','complete','echoue','annule','rembourse') NOT NULL DEFAULT 'en_attente',
    amount_sent     DECIMAL(18,2) NOT NULL,
    currency_from   VARCHAR(5)    NOT NULL,
    amount_received DECIMAL(18,2) NOT NULL,
    currency_to     VARCHAR(5)    NOT NULL,
    exchange_rate   DECIMAL(16,6) NOT NULL,
    fee_amount      DECIMAL(18,2) NOT NULL DEFAULT 0,
    fee_pct         DECIMAL(5,2)  NOT NULL DEFAULT 0,
    destination     VARCHAR(120)  DEFAULT NULL,
    region          ENUM('afrique','europe','asie','amerique','moyen_orient') NOT NULL,
    beneficiary_name VARCHAR(120) NOT NULL,
    beneficiary_iban VARCHAR(60)  DEFAULT NULL,
    beneficiary_bank VARCHAR(120) DEFAULT NULL,
    beneficiary_phone VARCHAR(30) DEFAULT NULL,
    payment_method  ENUM('virement','mobile_money','cash','carte') NOT NULL DEFAULT 'virement',
    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 (user_id) REFERENCES users(id),
    INDEX idx_ref(reference),
    INDEX idx_status(status),
    INDEX idx_region(region)
) ENGINE=InnoDB;

-- ============================================================
-- MODULE 2 — SUIVI TRANSACTIONS (historique & statuts)
-- ============================================================
CREATE TABLE transaction_logs (
    id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    transaction_id INT UNSIGNED NOT NULL,
    user_id        INT UNSIGNED DEFAULT NULL,
    old_status     VARCHAR(30)  DEFAULT NULL,
    new_status     VARCHAR(30)  NOT NULL,
    message        TEXT         DEFAULT NULL,
    created_at     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (transaction_id) REFERENCES transactions(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id)        REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ============================================================
-- MODULE 3 — CALCULATEUR / SIMULATEUR
-- ============================================================
CREATE TABLE fee_rules (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    region        ENUM('afrique','europe','asie','amerique','moyen_orient') NOT NULL,
    min_amount    DECIMAL(18,2) NOT NULL DEFAULT 0,
    max_amount    DECIMAL(18,2) DEFAULT NULL,
    fee_pct       DECIMAL(5,2)  NOT NULL,
    fee_fixed     DECIMAL(18,2) NOT NULL DEFAULT 0,
    delay_hours   TINYINT       NOT NULL DEFAULT 24,
    is_active     TINYINT(1)    NOT NULL DEFAULT 1,
    created_at    DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ============================================================
-- MODULE 4 — KYC (Vérification d'identité)
-- ============================================================
CREATE TABLE clients (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id         INT UNSIGNED DEFAULT NULL,
    type            ENUM('particulier','entreprise') NOT NULL DEFAULT 'particulier',
    first_name      VARCHAR(80)  DEFAULT NULL,
    last_name       VARCHAR(80)  DEFAULT NULL,
    company_name    VARCHAR(120) DEFAULT NULL,
    email           VARCHAR(160) NOT NULL UNIQUE,
    phone           VARCHAR(30)  NOT NULL,
    nationality     VARCHAR(60)  DEFAULT NULL,
    country         VARCHAR(80)  DEFAULT NULL,
    city            VARCHAR(80)  DEFAULT NULL,
    address         TEXT         DEFAULT NULL,
    id_type         ENUM('cni','passeport','permis','rccm') DEFAULT NULL,
    id_number       VARCHAR(60)  DEFAULT NULL,
    id_doc          VARCHAR(255) DEFAULT NULL,
    id_doc2         VARCHAR(255) DEFAULT NULL,
    selfie          VARCHAR(255) DEFAULT NULL,
    kyc_status      ENUM('non_soumis','en_attente','approuve','rejete') NOT NULL DEFAULT 'non_soumis',
    kyc_notes       TEXT         DEFAULT NULL,
    kyc_verified_by INT UNSIGNED DEFAULT NULL,
    kyc_verified_at DATETIME     DEFAULT NULL,
    risk_level      ENUM('faible','moyen','eleve') DEFAULT 'faible',
    created_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id)         REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (kyc_verified_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_kyc_status(kyc_status)
) ENGINE=InnoDB;

-- ============================================================
-- FACTURES AVEC QR CODE
-- ============================================================
CREATE TABLE invoices (
    id              INT UNSIGNED  AUTO_INCREMENT PRIMARY KEY,
    number          VARCHAR(30)   NOT NULL UNIQUE,
    transaction_id  INT UNSIGNED  DEFAULT NULL,
    client_id       INT UNSIGNED  DEFAULT NULL,
    user_id         INT UNSIGNED  NOT NULL,
    type            ENUM('recu','facture','proforma') NOT NULL DEFAULT 'recu',
    status          ENUM('brouillon','emise','payee','annulee') NOT NULL DEFAULT 'emise',
    amount          DECIMAL(18,2) NOT NULL,
    currency        VARCHAR(5)    NOT NULL DEFAULT 'XAF',
    tax_pct         DECIMAL(5,2)  NOT NULL DEFAULT 0,
    tax_amount      DECIMAL(18,2) NOT NULL DEFAULT 0,
    total_amount    DECIMAL(18,2) NOT NULL,
    description     TEXT          DEFAULT NULL,
    notes           TEXT          DEFAULT NULL,
    qr_code         VARCHAR(500)  DEFAULT NULL,
    qr_hash         VARCHAR(64)   DEFAULT NULL,
    pdf_path        VARCHAR(255)  DEFAULT NULL,
    issued_at       DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    due_at          DATETIME      DEFAULT NULL,
    paid_at         DATETIME      DEFAULT NULL,
    created_at      DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (transaction_id) REFERENCES transactions(id) ON DELETE SET NULL,
    FOREIGN KEY (client_id)      REFERENCES clients(id)      ON DELETE SET NULL,
    FOREIGN KEY (user_id)        REFERENCES users(id),
    INDEX idx_number(number),
    INDEX idx_status(status)
) ENGINE=InnoDB;

-- ============================================================
-- AUDIT LOG
-- ============================================================
CREATE TABLE 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;

-- ============================================================
-- DONNEES PAR DEFAUT
-- ============================================================

-- Paramètres société
INSERT INTO company_settings (name, slogan, email, phone, city, country, rccm, nif, primary_color, secondary_color, invoice_prefix, qr_secret) VALUES
('Money+Xfer', 'Vos paiements internationaux en 24-72h', 'contact@moneyxfer.com', '+242 06 000 00 00', 'Brazzaville', 'République du Congo', 'CG-BZV-2025-001', 'M2025001001', '#5AB849', '#0B3D67', 'MXF', REPLACE(UUID(),'-',''));

-- Rôles
INSERT INTO roles (name, label, description, color) VALUES
('super_admin', 'Super Administrateur', 'Accès total à la plateforme', '#DC2626'),
('admin',       'Administrateur',       'Gestion de la plateforme',    '#7C3AED'),
('manager',     'Manager',              'Gestion des transactions et clients', '#5AB849'),
('agent',       'Agent',                'Traitement des transactions', '#0B3D67'),
('client',      'Client',               'Accès espace client',        '#6B7280');

-- Permissions
INSERT INTO permissions (module, action, label) VALUES
('dashboard',     'view',           'Voir le tableau de bord'),
('transactions',  'view',           'Voir les transactions'),
('transactions',  'create',         'Créer une transaction'),
('transactions',  'edit',           'Modifier une transaction'),
('transactions',  'delete',         'Supprimer une transaction'),
('transactions',  'process',        'Traiter une transaction'),
('transactions',  'export',         'Exporter les transactions'),
('clients',       'view',           'Voir les clients'),
('clients',       'create',         'Créer un client'),
('clients',       'edit',           'Modifier un client'),
('clients',       'delete',         'Supprimer un client'),
('kyc',           'view',           'Voir les KYC'),
('kyc',           'approve',        'Approuver un KYC'),
('kyc',           'reject',         'Rejeter un KYC'),
('invoices',      'view',           'Voir les factures'),
('invoices',      'create',         'Créer une facture'),
('invoices',      'delete',         'Supprimer une facture'),
('calculator',    'view',           'Utiliser le calculateur'),
('users',         'view',           'Voir les utilisateurs'),
('users',         'create',         'Créer un utilisateur'),
('users',         'edit',           'Modifier un utilisateur'),
('users',         'delete',         'Supprimer un utilisateur'),
('roles',         'view',           'Voir les rôles'),
('roles',         'create',         'Créer un rôle'),
('roles',         'edit',           'Modifier un rôle'),
('roles',         'delete',         'Supprimer un rôle'),
('settings',      'view',           'Voir les paramètres'),
('settings',      'edit',           'Modifier les paramètres'),
('rates',         'view',           'Voir les taux de change'),
('rates',         'edit',           'Modifier les taux de change'),
('audit',         'view',           'Voir les logs d''audit');

-- Super admin : toutes les permissions
INSERT INTO role_permissions (role_id, permission_id)
SELECT 1, id FROM permissions;

-- Admin : tout sauf audit et delete users
INSERT INTO role_permissions (role_id, permission_id)
SELECT 2, id FROM permissions WHERE NOT (module = 'audit') AND NOT (module = 'users' AND action = 'delete');

-- Manager
INSERT INTO role_permissions (role_id, permission_id)
SELECT 3, id FROM permissions WHERE module IN ('dashboard','transactions','clients','kyc','invoices','calculator') OR (module = 'users' AND action = 'view');

-- Agent
INSERT INTO role_permissions (role_id, permission_id)
SELECT 4, id FROM permissions WHERE module IN ('dashboard','calculator') OR (module = 'transactions' AND action IN ('view','create','process')) OR (module = 'clients' AND action IN ('view','create')) OR (module = 'invoices' AND action IN ('view','create'));

-- Client
INSERT INTO role_permissions (role_id, permission_id)
SELECT 5, id FROM permissions WHERE module IN ('calculator') OR (module = 'transactions' AND action = 'view') OR (module = 'invoices' AND action = 'view');

-- Admin par défaut (mot de passe: Admin@2025)
INSERT INTO users (role_id, first_name, last_name, email, phone, password_hash, status) VALUES
(1, 'Super', 'Admin', 'admin@moneyxfer.com', '+242 06 000 00 00', '$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'actif'),
(2, 'Wilfried', 'Nzouka', 'manager@moneyxfer.com', '+242 06 111 11 11', '$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'actif');

-- Devises
INSERT INTO currencies (code, name, symbol, country, region, flag) VALUES
('XAF', 'Franc CFA CEMAC',    'FCFA', 'Congo / CEMAC',    'afrique', '🇨🇬'),
('NGN', 'Naira Nigérian',      '₦',    'Nigeria',           'afrique', '🇳🇬'),
('USD', 'Dollar Américain',    '$',    'États-Unis',        'amerique','🇺🇸'),
('EUR', 'Euro',                '€',    'Zone Euro',         'europe',  '🇪🇺'),
('GBP', 'Livre Sterling',      '£',    'Royaume-Uni',       'europe',  '🇬🇧'),
('CNY', 'Yuan Chinois',        '¥',    'Chine',             'asie',    '🇨🇳'),
('INR', 'Roupie Indienne',     '₹',    'Inde',              'asie',    '🇮🇳'),
('MAD', 'Dirham Marocain',     'DH',   'Maroc',             'afrique', '🇲🇦'),
('XOF', 'Franc CFA UEMOA',    'FCFA', 'Afrique Ouest',     'afrique', '🌍'),
('ZAR', 'Rand Sud-Africain',   'R',    'Afrique du Sud',    'afrique', '🇿🇦'),
('AED', 'Dirham Émiratis',     'AED',  'Émirats Arabes Unis','moyen_orient','🇦🇪'),
('BRL', 'Réal Brésilien',      'R$',   'Brésil',            'amerique','🇧🇷'),
('CAD', 'Dollar Canadien',     'C$',   'Canada',            'amerique','🇨🇦');

-- Taux de change (base XAF)
INSERT INTO exchange_rates (from_currency, to_currency, rate, fee_pct) VALUES
('XAF','USD', 0.001639, 1.80),
('XAF','EUR', 0.001524, 1.50),
('XAF','GBP', 0.001298, 1.50),
('XAF','CNY', 0.011878, 2.00),
('XAF','NGN', 1.312500, 1.50),
('XAF','MAD', 0.016393, 1.80),
('XAF','ZAR', 0.031250, 2.00),
('XAF','AED', 0.006020, 2.00),
('USD','XAF', 610.0000, 1.80),
('EUR','XAF', 656.0000, 1.50),
('GBP','XAF', 770.0000, 1.50);

-- Règles de frais par région
INSERT INTO fee_rules (region, min_amount, max_amount, fee_pct, fee_fixed, delay_hours) VALUES
('afrique',     0,       500000,   1.50, 500,  24),
('afrique',     500001,  2000000,  1.20, 500,  24),
('afrique',     2000001, NULL,     1.00, 0,    24),
('europe',      0,       500000,   1.80, 1000, 48),
('europe',      500001,  2000000,  1.50, 1000, 48),
('europe',      2000001, NULL,     1.20, 0,    48),
('asie',        0,       500000,   2.00, 1000, 72),
('asie',        500001,  NULL,     1.80, 0,    72),
('amerique',    0,       500000,   2.00, 1500, 72),
('amerique',    500001,  NULL,     1.80, 0,    72),
('moyen_orient',0,       500000,   2.20, 1000, 48),
('moyen_orient',500001,  NULL,     2.00, 0,    48);

-- ============================================================
-- NOTIFICATIONS (v1.3)
-- ============================================================
CREATE TABLE IF NOT EXISTS notifications (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id    INT UNSIGNED DEFAULT NULL,          -- NULL = broadcast
    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;