-- Initial schema for AfriPay-Tontine
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    phone VARCHAR(20) NOT NULL UNIQUE,
    country VARCHAR(50) DEFAULT 'Togo',
    currency_pref ENUM('XOF', 'GHS', 'GNF') DEFAULT 'XOF',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS wallets (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    type ENUM('current', 'savings', 'tontine', 'securities', 'escrow') NOT NULL,
    balance DECIMAL(15, 2) DEFAULT 0.00,
    currency VARCHAR(10) DEFAULT 'XOF',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Seed initial user and wallets if not exists
INSERT IGNORE INTO users (id, name, phone, country, currency_pref) VALUES (1, 'Koffi Mensah', '+22890000000', 'Togo', 'XOF');
INSERT IGNORE INTO wallets (user_id, type, balance, currency) VALUES 
(1, 'current', 250000.00, 'XOF'),
(1, 'savings', 1250000.00, 'XOF'),
(1, 'tontine', 50000.00, 'XOF'),
(1, 'securities', 0.00, 'XOF'),
(1, 'escrow', 15000.00, 'XOF');