-- PIEDTB — Database Setup
-- Import: mysql -u root -p piedtb < setup.sql

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

CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    account_number VARCHAR(20) NOT NULL UNIQUE,
    balance DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    is_admin TINYINT(1) NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    otp_code VARCHAR(6) NULL,
    otp_expires_at DATETIME NULL,
    otp_attempts TINYINT(1) NOT NULL DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    type ENUM('credit','debit') NOT NULL,
    category VARCHAR(50) NOT NULL DEFAULT 'transfer',
    amount DECIMAL(15,2) NOT NULL,
    description VARCHAR(255) NOT NULL,
    reference VARCHAR(50) NOT NULL UNIQUE,
    recipient_name VARCHAR(100) NULL,
    recipient_account VARCHAR(50) NULL,
    recipient_bank VARCHAR(100) NULL,
    balance_after DECIMAL(15,2) NOT NULL,
    status ENUM('completed','pending','failed') NOT NULL DEFAULT 'completed',
    created_by INT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS admin_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    admin_id INT NOT NULL,
    action VARCHAR(100) NOT NULL,
    target_user_id INT NULL,
    details TEXT NULL,
    ip_address VARCHAR(45) NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ============================================================
-- Demo Accounts
-- Admin@123 and User@123 hashes (bcrypt $2b$12, PHP-compatible)
-- PHP password_verify() accepts both $2b$ and $2y$ prefixes
-- ============================================================

-- Admin: admin@piedtb.com / Admin@123
INSERT INTO users (name, email, password, account_number, balance, is_admin) VALUES (
    'PIEDTB Admin',
    'admin@piedtb.com',
    '$2b$12$Tf.UwIzFhlHw/vaWxBI4jOXMzhVdgd4Z98QexVHljNFvQk43czLLe',
    'ADMIN0000001', 0.00, 1
) ON DUPLICATE KEY UPDATE
    password = '$2b$12$Tf.UwIzFhlHw/vaWxBI4jOXMzhVdgd4Z98QexVHljNFvQk43czLLe',
    is_admin = 1;

-- User 1: john@example.com / User@123
INSERT INTO users (name, email, password, account_number, balance, is_admin) VALUES (
    'John Doe',
    'john@example.com',
    '$2b$12$tguFWdrDpV6QdCCN/QgE3.F6khWSwJ2BEJENLVy/Nww/fMp8/3m46',
    'NXB0000002', 400.00, 0
) ON DUPLICATE KEY UPDATE
    password = '$2b$12$tguFWdrDpV6QdCCN/QgE3.F6khWSwJ2BEJENLVy/Nww/fMp8/3m46';

-- User 2: sarah@example.com / User@123
INSERT INTO users (name, email, password, account_number, balance, is_admin) VALUES (
    'Sarah Miller',
    'sarah@example.com',
    '$2b$12$tguFWdrDpV6QdCCN/QgE3.F6khWSwJ2BEJENLVy/Nww/fMp8/3m46',
    'NXB0000003', 1200.00, 0
) ON DUPLICATE KEY UPDATE
    password = '$2b$12$tguFWdrDpV6QdCCN/QgE3.F6khWSwJ2BEJENLVy/Nww/fMp8/3m46';

-- Sample transactions for John Doe (user_id 2)
INSERT IGNORE INTO transactions (user_id, type, category, amount, description, reference, balance_after, status) VALUES
(2, 'credit', 'salary',        2500.00, 'Salary Deposit — February 2026',     'TXN20260201001', 2900.00, 'completed'),
(2, 'debit',  'shopping',        49.99, 'Amazon Purchase',                     'TXN20260205001', 2850.01, 'completed'),
(2, 'debit',  'subscription',    15.99, 'Netflix Subscription',                'TXN20260210001', 2834.02, 'completed'),
(2, 'debit',  'housing',        850.00, 'Monthly Rent Transfer',               'TXN20260215001', 1984.02, 'completed'),
(2, 'credit', 'freelance',      700.00, 'Freelance Payment — Web Project',     'TXN20260218001', 2684.02, 'completed'),
(2, 'debit',  'utilities',       89.00, 'Electricity Bill',                    'TXN20260220001', 2595.02, 'completed'),
(2, 'credit', 'admin_fund',     500.00, 'Account Funding — PIEDTB',    'TXN20260222001',  400.00, 'completed');
