-- ============================================
-- CRÉATION DE LA BASE DE DONNÉES
-- ============================================

CREATE DATABASE IF NOT EXISTS nelsius_pay 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

USE nelsius_pay;

-- ============================================
-- TABLE: user_profiles
-- ============================================
CREATE TABLE user_profiles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    date_naissance DATE NULL,
    photo VARCHAR(255) NULL,
    ville VARCHAR(100) NULL,
    quartier VARCHAR(100) NULL,
    salaire DECIMAL(18,2) NULL,
    etudiant BOOLEAN DEFAULT FALSE,
    genre ENUM('male','female','other') NULL,
    adresse TEXT NULL,
    pays VARCHAR(50) DEFAULT 'Cameroun',
    code_postal VARCHAR(20),
    bio TEXT,
    site_web VARCHAR(255),
    facebook_url VARCHAR(255),
    twitter_url VARCHAR(255),
    linkedin_url VARCHAR(255),
    autres_infos JSON NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_user_profile (user_id),
    INDEX idx_user_profiles_user (user_id)
) ENGINE=InnoDB;

-- ============================================
-- TABLE: wallets
-- ============================================
CREATE TABLE wallets (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    currency CHAR(3) DEFAULT 'XAF',
    balance DECIMAL(18,2) DEFAULT 0.00,
    available_balance DECIMAL(18,2) DEFAULT 0.00,
    hold_balance DECIMAL(18,2) DEFAULT 0.00,
    is_primary BOOLEAN DEFAULT TRUE,
    wallet_type ENUM('personal','business','escrow','savings') DEFAULT 'personal',
    wallet_number VARCHAR(50) UNIQUE NOT NULL,
    wallet_name VARCHAR(100) DEFAULT 'Portefeuille principal',
    status ENUM('active','frozen','closed','suspended') DEFAULT 'active',
    min_balance DECIMAL(18,2) DEFAULT 0.00,
    max_balance DECIMAL(18,2) DEFAULT 100000000.00,
    last_transaction_at DATETIME NULL,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_user_currency (user_id, currency, wallet_type),
    INDEX idx_wallets_user (user_id),
    INDEX idx_wallets_number (wallet_number),
    INDEX idx_wallets_status (status)
) ENGINE=InnoDB;

-- ============================================
-- TABLE: payment_links
-- ============================================
CREATE TABLE payment_links (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    type ENUM('simple','multiple','donate','investment','subscription','product','service') DEFAULT 'simple',
    title VARCHAR(255) NOT NULL,
    description TEXT NULL,
    image VARCHAR(255) NULL,
    amount DECIMAL(18,2) NULL,
    currency CHAR(3) DEFAULT 'XAF',
    qr_code VARCHAR(255) NULL,
    short_url VARCHAR(100) UNIQUE,
    custom_id VARCHAR(100) UNIQUE,
    views_count INT DEFAULT 0,
    success_count INT DEFAULT 0,
    payment_methods JSON,
    redirect_url VARCHAR(500) NULL,
    webhook_url VARCHAR(500) NULL,
    metadata JSON,
    expires_at DATETIME NULL,
    is_reusable BOOLEAN DEFAULT FALSE,
    max_uses INT NULL,
    current_uses INT DEFAULT 0,
    status ENUM('active','inactive','expired','deleted') DEFAULT 'active',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_payment_links_user (user_id),
    INDEX idx_payment_links_status (status),
    INDEX idx_payment_links_custom (custom_id),
    INDEX idx_payment_links_short (short_url),
    INDEX idx_payment_links_expires (expires_at)
) ENGINE=InnoDB;

-- ============================================
-- TABLE: items
-- ============================================
CREATE TABLE items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    payment_link_id INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    description TEXT NULL,
    price DECIMAL(18,2) NOT NULL,
    currency CHAR(3) DEFAULT 'XAF',
    image VARCHAR(255) NULL,
    url VARCHAR(255) NULL,
    sku VARCHAR(100) NULL,
    quantity_available INT DEFAULT NULL,
    quantity_sold INT DEFAULT 0,
    other_infos JSON NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (payment_link_id) REFERENCES payment_links(id) ON DELETE CASCADE,
    INDEX idx_items_payment_link (payment_link_id),
    INDEX idx_items_sku (sku)
) ENGINE=InnoDB;

-- ============================================
-- TABLE: merchant_products
-- ============================================
CREATE TABLE merchant_products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    merchant_id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    sku VARCHAR(100) UNIQUE,
    price DECIMAL(18,2) NOT NULL,
    currency CHAR(3) DEFAULT 'XAF',
    cost_price DECIMAL(18,2) NULL,
    stock_quantity INT DEFAULT 0,
    low_stock_threshold INT DEFAULT 10,
    category VARCHAR(100),
    subcategory VARCHAR(100),
    images JSON,
    attributes JSON,
    weight DECIMAL(10,2) NULL,
    dimensions VARCHAR(100) NULL,
    tax_rate DECIMAL(5,2) DEFAULT 0,
    is_digital BOOLEAN DEFAULT FALSE,
    download_url VARCHAR(255) NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (merchant_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_products_merchant (merchant_id),
    INDEX idx_products_sku (sku),
    INDEX idx_products_category (category),
    INDEX idx_products_active (is_active)
) ENGINE=InnoDB;

-- ============================================
-- TABLE: transactions
-- ============================================
CREATE TABLE transactions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    wallet_id INT NOT NULL,
    link_id INT NULL,
    product_id INT NULL,
    merchant_id INT NULL,
    transaction_code VARCHAR(50) UNIQUE NOT NULL,
    reference_id VARCHAR(100) NULL,
    parent_transaction_id INT NULL,
    amount DECIMAL(18,2) NOT NULL,
    currency CHAR(3) DEFAULT 'XAF',
    status ENUM('pending','completed','failed','cancelled','refunded','disputed','on_hold') DEFAULT 'pending',
    type ENUM('payment','withdraw','transfer','investment','refund','deposit','commission','fee') DEFAULT 'payment',
    method ENUM('mobile_money','bank','card','paypal','wallet','cash') DEFAULT 'mobile_money',
    sub_method VARCHAR(50) NULL,
    payer_name VARCHAR(100) NULL,
    payer_email VARCHAR(150) NULL,
    payer_phone VARCHAR(20) NULL,
    sender_phone VARCHAR(20) NULL,
    receiver_phone VARCHAR(20) NULL,
    sender_wallet_id INT NULL,
    receiver_wallet_id INT NULL,
    details JSON,
    fees DECIMAL(18,2) DEFAULT 0.00,
    net_amount DECIMAL(18,2) NOT NULL,
    is_refund BOOLEAN DEFAULT FALSE,
    refund_reason TEXT NULL,
    refunded_amount DECIMAL(18,2) DEFAULT 0.00,
    metadata JSON,
    operator_transaction_id VARCHAR(100) NULL,
    bank_reference VARCHAR(100) NULL,
    failure_reason TEXT NULL,
    ip_address VARCHAR(45) NULL,
    user_agent TEXT NULL,
    confirmed_at DATETIME NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (wallet_id) REFERENCES wallets(id) ON DELETE CASCADE,
    FOREIGN KEY (link_id) REFERENCES payment_links(id) ON DELETE SET NULL,
    FOREIGN KEY (product_id) REFERENCES items(id) ON DELETE SET NULL,
    FOREIGN KEY (merchant_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (parent_transaction_id) REFERENCES transactions(id) ON DELETE SET NULL,
    FOREIGN KEY (sender_wallet_id) REFERENCES wallets(id) ON DELETE SET NULL,
    FOREIGN KEY (receiver_wallet_id) REFERENCES wallets(id) ON DELETE SET NULL,
    INDEX idx_transactions_user (user_id),
    INDEX idx_transactions_code (transaction_code),
    INDEX idx_transactions_status_date (status, created_at),
    INDEX idx_transactions_merchant (merchant_id),
    INDEX idx_transactions_reference (reference_id),
    INDEX idx_transactions_parent (parent_transaction_id),
    INDEX idx_transactions_type_method (type, method),
    INDEX idx_transactions_created (created_at)
) ENGINE=InnoDB;

-- ============================================
-- TABLE: invoices
-- ============================================
CREATE TABLE invoices (
    id INT PRIMARY KEY AUTO_INCREMENT,
    merchant_id INT NOT NULL,
    transaction_id INT NOT NULL,
    invoice_number VARCHAR(100) UNIQUE NOT NULL,
    customer_name VARCHAR(150),
    customer_email VARCHAR(150),
    customer_phone VARCHAR(20),
    customer_address TEXT,
    amount DECIMAL(18,2),
    currency CHAR(3) DEFAULT 'XAF',
    tax_amount DECIMAL(18,2) DEFAULT 0.00,
    total_amount DECIMAL(18,2),
    items JSON,
    due_date DATE NULL,
    paid_at DATETIME NULL,
    notes TEXT,
    status ENUM('draft','pending','paid','cancelled','overdue','partially_paid') DEFAULT 'pending',
    pdf_url VARCHAR(255) NULL,
    details JSON,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (merchant_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (transaction_id) REFERENCES transactions(id) ON DELETE CASCADE,
    INDEX idx_invoices_merchant (merchant_id),
    INDEX idx_invoices_number (invoice_number),
    INDEX idx_invoices_status (status),
    INDEX idx_invoices_due_date (due_date)
) ENGINE=InnoDB;

-- ============================================
-- TABLE: fees
-- ============================================
CREATE TABLE fees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    type ENUM('transaction','withdraw','transfer','investment','deposit','monthly','annual') DEFAULT 'transaction',
    method ENUM('mobile_money','bank','card','paypal','wallet','cash') DEFAULT 'mobile_money',
    sub_method VARCHAR(50) NULL,
    country CHAR(2) DEFAULT 'CM',
    value_type ENUM('percentage','fixed','tiered') DEFAULT 'percentage',
    value DECIMAL(10,4) NOT NULL,
    min_amount DECIMAL(18,2) DEFAULT 0.00,
    max_amount DECIMAL(18,2) NULL,
    min_fee DECIMAL(18,2) DEFAULT 0.00,
    max_fee DECIMAL(18,2) NULL,
    currency CHAR(3) DEFAULT 'XAF',
    applies_to ENUM('sender','receiver','both','platform') DEFAULT 'sender',
    description VARCHAR(255),
    active BOOLEAN DEFAULT TRUE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_fees_type_method (type, method),
    INDEX idx_fees_active (active),
    INDEX idx_fees_country (country)
) ENGINE=InnoDB;

-- ============================================
-- TABLE: commissions
-- ============================================
CREATE TABLE commissions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    transaction_id INT NOT NULL,
    merchant_id INT NOT NULL,
    platform_fee DECIMAL(18,2),
    merchant_amount DECIMAL(18,2),
    commission_rate DECIMAL(5,2),
    status ENUM('pending','paid','hold','cancelled') DEFAULT 'pending',
    payout_date DATE NULL,
    paid_at DATETIME NULL,
    notes TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (transaction_id) REFERENCES transactions(id) ON DELETE CASCADE,
    FOREIGN KEY (merchant_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_commissions_merchant (merchant_id),
    INDEX idx_commissions_status (status),
    INDEX idx_commissions_payout (payout_date)
) ENGINE=InnoDB;

-- ============================================
-- TABLE: exchange_rates
-- ============================================
CREATE TABLE exchange_rates (
    id INT PRIMARY KEY AUTO_INCREMENT,
    from_currency CHAR(3) NOT NULL,
    to_currency CHAR(3) NOT NULL,
    rate DECIMAL(18,6) NOT NULL,
    inverse_rate DECIMAL(18,6) NOT NULL,
    source VARCHAR(50) DEFAULT 'manual',
    valid_from DATETIME NOT NULL,
    valid_to DATETIME NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_rate_pair (from_currency, to_currency, valid_from),
    INDEX idx_exchange_rates_active (is_active),
    INDEX idx_exchange_rates_from (from_currency),
    INDEX idx_exchange_rates_to (to_currency)
) ENGINE=InnoDB;

-- ============================================
-- TABLE: webhook_events
-- ============================================
CREATE TABLE webhook_events (
    id INT PRIMARY KEY AUTO_INCREMENT,
    event_name VARCHAR(100) UNIQUE NOT NULL,
    description VARCHAR(255),
    is_active BOOLEAN DEFAULT TRUE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_webhook_events_name (event_name)
) ENGINE=InnoDB;

-- ============================================
-- TABLE: push_notifications
-- ============================================
CREATE TABLE push_notifications (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    device_id INT NULL,
    title VARCHAR(255) NOT NULL,
    message TEXT NOT NULL,
    data JSON,
    type ENUM('transaction','security','promotion','system','alert') DEFAULT 'system',
    priority ENUM('normal','high','urgent') DEFAULT 'normal',
    status ENUM('pending','sent','delivered','failed','read') DEFAULT 'pending',
    sent_at DATETIME NULL,
    delivered_at DATETIME NULL,
    read_at DATETIME NULL,
    failure_reason TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (device_id) REFERENCES user_devices(id) ON DELETE SET NULL,
    INDEX idx_push_notifications_user (user_id),
    INDEX idx_push_notifications_status (status),
    INDEX idx_push_notifications_type (type)
) ENGINE=InnoDB;

-- ============================================
-- TABLE: subscription_plans
-- ============================================
CREATE TABLE subscription_plans (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) UNIQUE NOT NULL,
    description TEXT,
    price DECIMAL(18,2) NOT NULL,
    currency CHAR(3) DEFAULT 'XAF',
    `interval` ENUM('daily','weekly','monthly','yearly','lifetime') DEFAULT 'monthly',
    trial_days INT DEFAULT 0,
    features JSON,
    is_active BOOLEAN DEFAULT TRUE,
    is_popular BOOLEAN DEFAULT FALSE,
    max_transactions_per_month INT NULL,
    max_amount_per_transaction DECIMAL(18,2) NULL,
    fee_percentage DECIMAL(5,2) DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_subscription_plans_slug (slug),
    INDEX idx_subscription_plans_active (is_active)
) ENGINE=InnoDB;

-- ============================================
-- TABLE: user_subscriptions
-- ============================================
CREATE TABLE user_subscriptions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    plan_id INT NOT NULL,
    stripe_subscription_id VARCHAR(255) NULL,
    paddle_subscription_id VARCHAR(255) NULL,
    status ENUM('active','canceled','past_due','unpaid','trialing','expired') DEFAULT 'active',
    current_period_start DATETIME,
    current_period_end DATETIME,
    cancel_at_period_end BOOLEAN DEFAULT FALSE,
    canceled_at DATETIME NULL,
    trial_ends_at DATETIME NULL,
    payment_method ENUM('card','mobile_money','bank','wallet') DEFAULT 'card',
    metadata JSON,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (plan_id) REFERENCES subscription_plans(id) ON DELETE CASCADE,
    INDEX idx_user_subscriptions_user (user_id),
    INDEX idx_user_subscriptions_status (status),
    INDEX idx_user_subscriptions_plan (plan_id)
) ENGINE=InnoDB;

-- ============================================
-- TABLE: subscription_invoices
-- ============================================
CREATE TABLE subscription_invoices (
    id INT PRIMARY KEY AUTO_INCREMENT,
    subscription_id INT NOT NULL,
    transaction_id INT NULL,
    invoice_number VARCHAR(100) UNIQUE NOT NULL,
    amount DECIMAL(18,2) NOT NULL,
    currency CHAR(3) DEFAULT 'XAF',
    period_start DATETIME,
    period_end DATETIME,
    status ENUM('draft','open','paid','void','uncollectible') DEFAULT 'open',
    pdf_url VARCHAR(500) NULL,
    paid_at DATETIME NULL,
    due_date DATETIME,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (subscription_id) REFERENCES user_subscriptions(id) ON DELETE CASCADE,
    FOREIGN KEY (transaction_id) REFERENCES transactions(id) ON DELETE SET NULL,
    INDEX idx_subscription_invoices_subscription (subscription_id),
    INDEX idx_subscription_invoices_status (status),
    INDEX idx_subscription_invoices_number (invoice_number)
) ENGINE=InnoDB;

-- ============================================
-- TABLE: dynamic_qr_codes
-- ============================================
CREATE TABLE dynamic_qr_codes (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    code VARCHAR(100) UNIQUE NOT NULL,
    amount DECIMAL(18,2) NULL,
    currency CHAR(3) DEFAULT 'XAF',
    description VARCHAR(255),
    payment_link_id INT NULL,
    is_reusable BOOLEAN DEFAULT FALSE,
    max_uses INT NULL,
    current_uses INT DEFAULT 0,
    expires_at DATETIME NULL,
    status ENUM('active','inactive','expired') DEFAULT 'active',
    image_url VARCHAR(255) NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (payment_link_id) REFERENCES payment_links(id) ON DELETE SET NULL,
    INDEX idx_qr_codes_user (user_id),
    INDEX idx_qr_codes_code (code),
    INDEX idx_qr_codes_status (status)
) ENGINE=InnoDB;

-- ============================================
-- TABLE: email_logs
-- ============================================
CREATE TABLE email_logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NULL,
    type ENUM('payment_success','merchant_notification','system','verification','password_reset','invoice','newsletter') DEFAULT 'system',
    to_email VARCHAR(150) NOT NULL,
    subject VARCHAR(255) NOT NULL,
    body TEXT,
    template VARCHAR(100) NULL,
    attachments JSON,
    status ENUM('pending','sent','failed','delivered') DEFAULT 'pending',
    error_message TEXT,
    sent_at DATETIME NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_email_logs_user (user_id),
    INDEX idx_email_logs_status (status),
    INDEX idx_email_logs_created (created_at),
    INDEX idx_email_logs_type (type)
) ENGINE=InnoDB;

-- ============================================
-- TABLE: merchant_reviews
-- ============================================
CREATE TABLE merchant_reviews (
    id INT PRIMARY KEY AUTO_INCREMENT,
    merchant_id INT NOT NULL,
    user_id INT NULL,
    transaction_id INT NULL,
    rating INT NOT NULL CHECK (rating >= 1 AND rating <= 5),
    comment TEXT,
    response TEXT,
    response_by INT NULL,
    response_at DATETIME NULL,
    is_verified_purchase BOOLEAN DEFAULT FALSE,
    status ENUM('pending','approved','rejected','flagged') DEFAULT 'pending',
    helpful_count INT DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (merchant_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (transaction_id) REFERENCES transactions(id) ON DELETE SET NULL,
    FOREIGN KEY (response_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_reviews_merchant (merchant_id),
    INDEX idx_reviews_user (user_id),
    INDEX idx_reviews_rating (rating),
    INDEX idx_reviews_status (status)
) ENGINE=InnoDB;

-- ============================================
-- TABLE: support_tickets
-- ============================================
CREATE TABLE support_tickets (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NULL,
    email VARCHAR(150) NULL,
    whatsapp VARCHAR(20) NULL,
    subject VARCHAR(255) NOT NULL,
    message TEXT NOT NULL,
    priority ENUM('low','medium','high','urgent') DEFAULT 'medium',
    category ENUM('technical','payment','account','merchant','refund','other') DEFAULT 'other',
    assigned_to INT NULL,
    response TEXT,
    status ENUM('open','in_progress','resolved','closed') DEFAULT 'open',
    resolved_at DATETIME NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (assigned_to) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_tickets_user (user_id),
    INDEX idx_tickets_status (status),
    INDEX idx_tickets_priority (priority),
    INDEX idx_tickets_category (category)
) ENGINE=InnoDB;

-- ============================================
-- TABLE: kyc_documents
-- ============================================
CREATE TABLE kyc_documents (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    document_type ENUM('id_card','passport','driver_license','utility_bill','business_registration','tax_certificate') NOT NULL,
    document_number VARCHAR(100),
    document_url VARCHAR(500) NOT NULL,
    front_image VARCHAR(500) NULL,
    back_image VARCHAR(500) NULL,
    selfie_image VARCHAR(500) NULL,
    status ENUM('pending','verified','rejected','expired') DEFAULT 'pending',
    verified_by INT NULL,
    verified_at DATETIME NULL,
    rejection_reason TEXT,
    expiry_date DATE NULL,
    submitted_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (verified_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_kyc_user (user_id),
    INDEX idx_kyc_status (status),
    INDEX idx_kyc_type (document_type)
) ENGINE=InnoDB;

-- ============================================
-- TABLE: email_verifications
-- ============================================
CREATE TABLE email_verifications (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    code VARCHAR(10) NOT NULL,
    email VARCHAR(150) NOT NULL,
    type ENUM('signup','password_reset','email_change','two_factor') DEFAULT 'signup',
    status ENUM('pending','verified','expired') DEFAULT 'pending',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    expires_at DATETIME NOT NULL,
    verified_at DATETIME NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_email_verifications_code (code),
    INDEX idx_email_verifications_user (user_id),
    INDEX idx_email_verifications_expires (expires_at),
    INDEX idx_email_verifications_type (type)
) ENGINE=InnoDB;

-- ============================================
-- TABLE: audit_logs
-- ============================================
CREATE TABLE audit_logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NULL,
    action VARCHAR(100) NOT NULL,
    entity_type VARCHAR(50) NOT NULL,
    entity_id INT NULL,
    old_values JSON,
    new_values JSON,
    changes JSON,
    ip_address VARCHAR(45),
    user_agent TEXT,
    metadata JSON,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_audit_user (user_id),
    INDEX idx_audit_entity (entity_type, entity_id),
    INDEX idx_audit_action (action),
    INDEX idx_audit_created (created_at)
) ENGINE=InnoDB;

-- ============================================
-- TABLE: notifications
-- ============================================
CREATE TABLE notifications (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    type ENUM('payment','withdrawal','system','promotion','security','kyc','transaction','announcement') DEFAULT 'system',
    title VARCHAR(255) NOT NULL,
    message TEXT NOT NULL,
    data JSON,
    is_read BOOLEAN DEFAULT FALSE,
    read_at DATETIME NULL,
    expires_at DATETIME NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_notifications_user (user_id),
    INDEX idx_notifications_read (is_read),
    INDEX idx_notifications_created (created_at),
    INDEX idx_notifications_type (type)
) ENGINE=InnoDB;

-- ============================================
-- TABLE: mobile_money_accounts
-- ============================================
CREATE TABLE mobile_money_accounts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    phone_number VARCHAR(20) NOT NULL,
    operator ENUM('orange_money','mtn_money','moov_money','express_union','western_union','other') NOT NULL,
    country CHAR(2) DEFAULT 'CM',
    is_primary BOOLEAN DEFAULT FALSE,
    is_verified BOOLEAN DEFAULT FALSE,
    verified_at DATETIME NULL,
    status ENUM('active','inactive') DEFAULT 'active',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_mobile_account (user_id, phone_number, operator),
    INDEX idx_mobile_accounts_user (user_id),
    INDEX idx_mobile_accounts_operator (operator)
) ENGINE=InnoDB;

-- ============================================
-- TABLE: disputes
-- ============================================
CREATE TABLE disputes (
    id INT PRIMARY KEY AUTO_INCREMENT,
    transaction_id INT NOT NULL,
    user_id INT NOT NULL,
    merchant_id INT NOT NULL,
    dispute_reason ENUM('not_received','defective','not_as_described','fraud','duplicate','unauthorized','other') NOT NULL,
    description TEXT NOT NULL,
    evidence JSON,
    status ENUM('open','under_review','resolved','closed','escalated') DEFAULT 'open',
    resolution ENUM('refund','partial_refund','merchant_wins','user_wins','compromise') NULL,
    resolved_by INT NULL,
    resolved_at DATETIME NULL,
    refund_amount DECIMAL(18,2) NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (transaction_id) REFERENCES transactions(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (merchant_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (resolved_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_disputes_transaction (transaction_id),
    INDEX idx_disputes_user (user_id),
    INDEX idx_disputes_status (status),
    INDEX idx_disputes_merchant (merchant_id)
) ENGINE=InnoDB;

-- ============================================
-- TABLE: api_cache
-- ============================================
CREATE TABLE api_cache (
    id INT PRIMARY KEY AUTO_INCREMENT,
    cache_key VARCHAR(255) UNIQUE NOT NULL,
    data JSON NOT NULL,
    expires_at DATETIME NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_api_cache_key (cache_key),
    INDEX idx_api_cache_expires (expires_at)
) ENGINE=InnoDB;

-- ============================================
-- TABLE: user_favorites
-- ============================================
CREATE TABLE user_favorites (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    type ENUM('merchant','product','service','payment_link') NOT NULL,
    target_id INT NOT NULL,
    metadata JSON,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_favorite (user_id, type, target_id),
    INDEX idx_user_favorites_user (user_id),
    INDEX idx_user_favorites_type (type)
) ENGINE=InnoDB;

-- ============================================
-- TABLE: sms_logs
-- ============================================
CREATE TABLE sms_logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NULL,
    phone_number VARCHAR(20) NOT NULL,
    message TEXT NOT NULL,
    type ENUM('verification','notification','alert','promotion') DEFAULT 'notification',
    status ENUM('pending','sent','failed','delivered') DEFAULT 'pending',
    provider VARCHAR(50),
    message_id VARCHAR(100),
    error_message TEXT,
    sent_at DATETIME NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_sms_logs_phone (phone_number),
    INDEX idx_sms_logs_status (status),
    INDEX idx_sms_logs_created (created_at)
) ENGINE=InnoDB;

-- ============================================
-- TABLE: security_events
-- ============================================
CREATE TABLE security_events (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NULL,
    event_type ENUM('login_success','login_failed','logout','password_change','token_refresh','token_revoked','suspicious_activity') NOT NULL,
    ip_address VARCHAR(45),
    user_agent TEXT,
    location JSON,
    metadata JSON,
    risk_level ENUM('low','medium','high','critical') DEFAULT 'low',
    is_handled BOOLEAN DEFAULT FALSE,
    handled_at DATETIME NULL,
    handled_by INT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (handled_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_security_events_user (user_id),
    INDEX idx_security_events_type (event_type),
    INDEX idx_security_events_risk (risk_level),
    INDEX idx_security_events_created (created_at)
) ENGINE=InnoDB;

-- ============================================
-- TABLE: failed_jobs
-- ============================================
CREATE TABLE failed_jobs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    connection TEXT NOT NULL,
    queue TEXT NOT NULL,
    payload LONGTEXT NOT NULL,
    exception LONGTEXT NOT NULL,
    failed_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_failed_jobs_failed_at (failed_at)
) ENGINE=InnoDB;

-- ============================================
-- TRIGGERS COMPLETS
-- ============================================

-- Trigger 1: Création automatique du wallet et profil à l'inscription
DELIMITER $$
    
    -- Créer un profil utilisateur vide
    INSERT INTO user_profiles (user_id)
    VALUES (NEW.id);
    
    -- Enregistrer l'événement de sécurité
    INSERT INTO security_events (user_id, event_type, risk_level, metadata)
    VALUES (NEW.id, 'login_success', 'low', 
            JSON_OBJECT('registration_type', 'manual', 'timestamp', NOW()));
END$$
DELIMITER ;

-- Trigger 2: Mise à jour des soldes après transaction complétée
DELIMITER $$
CREATE TRIGGER after_transaction_completed
AFTER UPDATE ON transactions
FOR EACH ROW
BEGIN
    IF OLD.status != 'completed' AND NEW.status = 'completed' THEN
        -- Transaction de type PAYMENT: créditer le wallet du marchand
        IF NEW.type = 'payment' AND NEW.receiver_wallet_id IS NOT NULL THEN
            UPDATE wallets 
            SET balance = balance + NEW.net_amount,
                available_balance = available_balance + NEW.net_amount,
                last_transaction_at = NOW(),
                updated_at = NOW()
            WHERE id = NEW.receiver_wallet_id;
        END IF;
        
        -- Transaction de type TRANSFER: débiter l'expéditeur, créditer le destinataire
        IF NEW.type = 'transfer' THEN
            -- Débiter l'expéditeur
            IF NEW.sender_wallet_id IS NOT NULL THEN
                UPDATE wallets 
                SET balance = balance - NEW.amount,
                    available_balance = available_balance - NEW.amount,
                    last_transaction_at = NOW(),
                    updated_at = NOW()
                WHERE id = NEW.sender_wallet_id;
            END IF;
            
            -- Créditer le destinataire
            IF NEW.receiver_wallet_id IS NOT NULL THEN
                UPDATE wallets 
                SET balance = balance + NEW.net_amount,
                    available_balance = available_balance + NEW.net_amount,
                    last_transaction_at = NOW(),
                    updated_at = NOW()
                WHERE id = NEW.receiver_wallet_id;
            END IF;
        END IF;
        
        -- Transaction de type WITHDRAW: débiter le wallet
        IF NEW.type = 'withdraw' AND NEW.sender_wallet_id IS NOT NULL THEN
            UPDATE wallets 
            SET balance = balance - NEW.amount,
                available_balance = available_balance - NEW.amount,
                last_transaction_at = NOW(),
                updated_at = NOW()
            WHERE id = NEW.sender_wallet_id;
        END IF;
        
        -- Transaction de type DEPOSIT: créditer le wallet
        IF NEW.type = 'deposit' AND NEW.receiver_wallet_id IS NOT NULL THEN
            UPDATE wallets 
            SET balance = balance + NEW.net_amount,
                available_balance = available_balance + NEW.net_amount,
                last_transaction_at = NOW(),
                updated_at = NOW()
            WHERE id = NEW.receiver_wallet_id;
        END IF;
        
        -- Mettre à jour le compteur de succès du payment link
        IF NEW.link_id IS NOT NULL THEN
            UPDATE payment_links 
            SET success_count = success_count + 1,
                current_uses = current_uses + 1,
                updated_at = NOW()
            WHERE id = NEW.link_id;
        END IF;
        
        -- Mettre à jour le compteur de ventes pour les items
        IF NEW.product_id IS NOT NULL THEN
            UPDATE items 
            SET quantity_sold = quantity_sold + 1,
                updated_at = NOW()
            WHERE id = NEW.product_id;
        END IF;
        
        -- Créer une notification
        INSERT INTO notifications (user_id, type, title, message, data)
        VALUES (NEW.user_id, 'transaction', 
                CONCAT('Transaction ', UPPER(NEW.type), ' complétée'),
                CONCAT('Votre transaction #', NEW.transaction_code, ' de ', NEW.amount, ' ', NEW.currency, ' a été complétée avec succès.'),
                JSON_OBJECT(
                    'transaction_id', NEW.id,
                    'transaction_code', NEW.transaction_code,
                    'amount', NEW.amount,
                    'currency', NEW.currency,
                    'type', NEW.type,
                    'net_amount', NEW.net_amount,
                    'fees', NEW.fees
                ));
    END IF;
END$$
DELIMITER ;

-- Trigger 3: Blacklist automatique après changement de mot de passe
DELIMITER $$
        
        -- Blacklist les tokens via application (côté Laravel)
        INSERT INTO audit_logs (user_id, action, entity_type, entity_id, metadata)
        VALUES (NEW.id, 'user.password_changed', 'users', NEW.id,
                JSON_OBJECT('tokens_revoked', 'all', 'reason', 'password_change'));
    END IF;
END$$
DELIMITER ;

-- Trigger 4: Sécurité après KYC rejeté
DELIMITER $$
        
        -- Geler le wallet principal
        UPDATE wallets 
        SET status = 'frozen',
            updated_at = NOW()
        WHERE user_id = NEW.id 
          AND is_primary = TRUE 
          AND status = 'active';
    END IF;
END$$
DELIMITER ;

-- Trigger 5: Audit des modifications utilisateur
DELIMITER $$
    END IF;
END$$
DELIMITER ;

-- Trigger 6: Mise à jour du stock après vente
DELIMITER $$
CREATE TRIGGER after_item_sold
AFTER UPDATE ON items
FOR EACH ROW
BEGIN
    IF NEW.quantity_sold > OLD.quantity_sold THEN
        -- Vérifier le stock bas
        IF NEW.quantity_available IS NOT NULL AND NEW.quantity_available <= NEW.low_stock_threshold THEN
            -- Créer une notification pour le marchand
            INSERT INTO notifications (user_id, type, title, message, data)
            SELECT p.user_id, 'alert', 'Stock faible',
                   CONCAT('Le produit "', NEW.title, '" est en stock faible (', NEW.quantity_available, ' restants)'),
                   JSON_OBJECT('product_id', NEW.id, 'product_name', NEW.title, 
                               'current_stock', NEW.quantity_available, 'low_threshold', NEW.low_stock_threshold)
            FROM payment_links p
            WHERE p.id = NEW.payment_link_id;
        END IF;
    END IF;
END$$
DELIMITER ;

-- Trigger 7: Nettoyage automatique des sessions expirées
DELIMITER $$
END$$
DELIMITER ;

-- Insérer les plans d'abonnement
INSERT INTO subscription_plans (name, slug, description, price, `interval`, trial_days, features, is_active, is_popular, max_transactions_per_month, fee_percentage) VALUES
('Gratuit', 'free', 'Plan gratuit pour les petits commerçants', 0.00, 'monthly', 14, 
 '["Jusqu\\''à 10 transactions/mois", "Frais à 2.5%", "Support par email", "1 utilisateur"]', TRUE, FALSE, 10, 2.5),
('Pro', 'pro', 'Pour les commerçants en croissance', 5000.00, 'monthly', 7,
 '["Transactions illimitées", "Frais à 1.5%", "Support prioritaire", "API avancée", "5 utilisateurs", "Statistiques détaillées"]', TRUE, TRUE, NULL, 1.5),
('Business', 'business', 'Pour les entreprises', 15000.00, 'monthly', 0,
 '["Toutes fonctionnalités Pro", "Frais à 0.5%", "Support dédié", "Dashboard avancé", "Utilisateurs illimités", "Intégrations personnalisées"]', TRUE, FALSE, NULL, 0.5);

-- Insérer les frais par défaut
INSERT INTO fees (type, method, value_type, value, min_amount, min_fee, max_fee, currency, applies_to, description) VALUES
('transaction', 'mobile_money', 'percentage', 1.5, 100, 50, 500, 'XAF', 'sender', 'Frais de transaction mobile money'),
('transaction', 'bank', 'percentage', 2.0, 1000, 100, 2000, 'XAF', 'sender', 'Frais de transaction bancaire'),
('transaction', 'card', 'percentage', 2.5, 100, 100, 2500, 'XAF', 'sender', 'Frais de transaction par carte'),
('withdraw', 'mobile_money', 'fixed', 100, 100, 100, 100, 'XAF', 'sender', 'Frais de retrait mobile money'),
('withdraw', 'bank', 'fixed', 500, 5000, 500, 500, 'XAF', 'sender', 'Frais de retrait bancaire'),
('transfer', 'wallet', 'percentage', 0.5, 10, 10, 500, 'XAF', 'sender', 'Frais de transfert entre wallets');

-- Insérer les taux de change
INSERT INTO exchange_rates (from_currency, to_currency, rate, inverse_rate, source, valid_from, is_active) VALUES
('XAF', 'EUR', 0.001524, 656.33, 'manual', NOW(), TRUE),
('XAF', 'USD', 0.001667, 600.00, 'manual', NOW(), TRUE),
('EUR', 'XAF', 656.33, 0.001524, 'manual', NOW(), TRUE),
('USD', 'XAF', 600.00, 0.001667, 'manual', NOW(), TRUE),
('XAF', 'XOF', 1.000, 1.000, 'manual', NOW(), TRUE);

-- Insérer les événements webhook
INSERT INTO webhook_events (event_name, description) VALUES
('payment.completed', 'Paiement complété avec succès'),
('payment.failed', 'Paiement échoué'),
('payment.pending', 'Paiement en attente'),
('withdrawal.completed', 'Retrait complété'),
('withdrawal.failed', 'Retrait échoué'),
('transfer.completed', 'Transfert complété'),
('invoice.paid', 'Facture payée'),
('invoice.overdue', 'Facture en retard'),
('merchant.payout', 'Paiement au marchand'),
('kyc.verified', 'KYC vérifié'),
('user.registered', 'Nouvel utilisateur inscrit'),
('subscription.created', 'Nouvel abonnement'),
('subscription.cancelled', 'Abonnement annulé'),
('refund.processed', 'Remboursement traité'),
('dispute.opened', 'Litige ouvert'),
('api_key.created', 'Nouvelle clé API créée'),
('api_key.revoked', 'Clé API révoquée'),
('jwt.token_revoked', 'Token JWT révoqué'),
('user.password_changed', 'Mot de passe utilisateur changé'),
('security.alert', 'Alerte de sécurité détectée');

-- ============================================
-- FONCTIONS UTILITAIRES
-- ============================================

-- Fonction pour générer un numéro de transaction unique
DELIMITER $$
CREATE FUNCTION generate_transaction_code() RETURNS VARCHAR(50)
DETERMINISTIC
BEGIN
    DECLARE new_code VARCHAR(50);
    DECLARE counter INT DEFAULT 1;
    
    WHILE counter <= 10 DO
        SET new_code = CONCAT('TXN', DATE_FORMAT(NOW(), '%Y%m%d'), LPAD(FLOOR(RAND() * 10000), 4, '0'));
        
        IF NOT EXISTS (SELECT 1 FROM transactions WHERE transaction_code = new_code) THEN
            RETURN new_code;
        END IF;
        
        SET counter = counter + 1;
    END WHILE;
    
    -- Fallback: timestamp + random
    RETURN CONCAT('TXN', UNIX_TIMESTAMP(), LPAD(FLOOR(RAND() * 1000), 3, '0'));
END$$
DELIMITER ;

-- Fonction pour vérifier si un token est blacklisté
DELIMITER $$
CREATE FUNCTION is_token_blacklisted(token_hash VARCHAR(255)) RETURNS BOOLEAN
DETERMINISTIC
BEGIN
    DECLARE token_count INT;
    
    RETURN token_count > 0;
END$$
DELIMITER ;

-- ============================================
-- ÉVÉNEMENTS MYSQL (nettoyage automatique)
-- ============================================

-- Événement pour nettoyer les données expirées quotidiennement
DELIMITER $$
    DELETE FROM email_verifications WHERE expires_at < NOW();
    
    -- Nettoyer le cache API expiré
    DELETE FROM api_cache WHERE expires_at < NOW();
    
    -- Désactiver les liens de paiement expirés
    UPDATE payment_links 
    SET status = 'expired' 
    WHERE expires_at < NOW() AND status = 'active';
    
    -- Désactiver les QR codes expirés
    UPDATE dynamic_qr_codes 
    SET status = 'expired' 
    WHERE expires_at < NOW() AND status = 'active';
    
    -- Nettoyer les notifications expirées
    DELETE FROM notifications WHERE expires_at < NOW();
END$$
DELIMITER ;

-- Événement pour réinitialiser les compteurs d'API quotidiennement
DELIMITER $$
END$$
DELIMITER ;

-- ============================================
-- FIN - MESSAGE DE CONFIRMATION
-- ============================================

-- Message de confirmation
SELECT '✅ BASE DE DONNÉES CRÉÉE AVEC SUCCÈS!' as message;

-- Vérification des tables
SHOW TABLES;

-- Afficher la liste des triggers créés
SHOW TRIGGERS;