<?php
$host = '127.0.0.1';
$user = 'root';
$pass = ''; 
$charset = 'utf8mb4';

$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];

try {
    $pdo = new PDO("mysql:host=$host;charset=$charset", $user, $pass, $options);
    
    // Create database
    $pdo->exec("CREATE DATABASE IF NOT EXISTS sulejamarkets");
    $pdo->exec("USE sulejamarkets");

    // Admins
    $pdo->exec("CREATE TABLE IF NOT EXISTS admins (
        id INT AUTO_INCREMENT PRIMARY KEY,
        username VARCHAR(50) NOT NULL UNIQUE,
        password_hash VARCHAR(255) NOT NULL
    )");

    // Vendors
    $pdo->exec("CREATE TABLE IF NOT EXISTS vendors (
        id VARCHAR(50) PRIMARY KEY,
        status VARCHAR(20) DEFAULT 'pending',
        appliedAt BIGINT,
        storeSlug VARCHAR(100),
        businessName VARCHAR(100),
        businessType VARCHAR(50),
        category VARCHAR(50),
        rcNumber VARCHAR(50),
        taxId VARCHAR(50),
        contactName VARCHAR(100),
        contactEmail VARCHAR(100),
        contactPhone VARCHAR(20),
        altPhone VARCHAR(20),
        address TEXT,
        city VARCHAR(50),
        state VARCHAR(50),
        country VARCHAR(50),
        is_verified BOOLEAN DEFAULT FALSE,
        lat DOUBLE,
        lng DOUBLE,
        website VARCHAR(150),
        productInfo TEXT,
        bankName VARCHAR(100),
        accountName VARCHAR(100),
        accountNumber VARCHAR(50),
        banner VARCHAR(255),
        logo VARCHAR(255),
        wallet_balance DOUBLE DEFAULT 0,
        loyalty_coins INT DEFAULT 0
    )");

    // Ensure columns exist for existing vendors
    try {
        $pdo->exec("ALTER TABLE vendors ADD COLUMN wallet_balance DOUBLE DEFAULT 0");
    } catch (\PDOException $e) {}
    try {
        $pdo->exec("ALTER TABLE vendors ADD COLUMN loyalty_coins INT DEFAULT 0");
    } catch (\PDOException $e) {}

    // Withdrawals
    $pdo->exec("CREATE TABLE IF NOT EXISTS withdrawals (
        id INT AUTO_INCREMENT PRIMARY KEY,
        vendor_id VARCHAR(50),
        amount DOUBLE,
        status VARCHAR(20) DEFAULT 'pending',
        requestedAt BIGINT,
        processedAt BIGINT,
        FOREIGN KEY (vendor_id) REFERENCES vendors(id) ON DELETE CASCADE
    )");

    // Loyalty Transactions
    $pdo->exec("CREATE TABLE IF NOT EXISTS loyalty_transactions (
        id INT AUTO_INCREMENT PRIMARY KEY,
        vendor_email VARCHAR(100),
        amount INT,
        type VARCHAR(50),
        description TEXT,
        order_id VARCHAR(50),
        product_id VARCHAR(50),
        createdAt BIGINT
    )");

    // Notifications
    $pdo->exec("CREATE TABLE IF NOT EXISTS notifications (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_email VARCHAR(100),
        role VARCHAR(20) DEFAULT 'all',
        title VARCHAR(150),
        message TEXT,
        type VARCHAR(50),
        is_read BOOLEAN DEFAULT FALSE,
        link VARCHAR(255),
        createdAt BIGINT
    )");

    // Products
    $pdo->exec("CREATE TABLE IF NOT EXISTS products (
        id VARCHAR(50) PRIMARY KEY,
        name VARCHAR(150) NOT NULL,
        price DOUBLE NOT NULL,
        old_price DOUBLE,
        disc VARCHAR(20),
        seed VARCHAR(50),
        vendorEmail VARCHAR(100),
        category VARCHAR(50),
        description TEXT,
        summary TEXT,
        images JSON,
        colors JSON,
        sizes JSON,
        is_flash BOOLEAN DEFAULT FALSE,
        is_top BOOLEAN DEFAULT FALSE,
        is_featured BOOLEAN DEFAULT FALSE
    )");

    // Ensure is_featured exists for existing products
    try {
        $pdo->exec("ALTER TABLE products ADD COLUMN is_featured BOOLEAN DEFAULT FALSE");
    } catch (\PDOException $e) {}

    // Users
    $pdo->exec("CREATE TABLE IF NOT EXISTS users (
        email VARCHAR(100) PRIMARY KEY,
        name VARCHAR(100),
        role VARCHAR(20)
    )");

    // Addresses
    $pdo->exec("CREATE TABLE IF NOT EXISTS addresses (
        id VARCHAR(50) PRIMARY KEY,
        user_email VARCHAR(100),
        name VARCHAR(100),
        phone VARCHAR(20),
        street TEXT,
        city VARCHAR(50),
        state VARCHAR(50),
        FOREIGN KEY (user_email) REFERENCES users(email) ON DELETE CASCADE
    )");

    // Orders
    $pdo->exec("CREATE TABLE IF NOT EXISTS orders (
        id VARCHAR(50) PRIMARY KEY,
        user_email VARCHAR(100),
        subtotal DOUBLE,
        shipping DOUBLE,
        total DOUBLE,
        address_id VARCHAR(50),
        paymentMethod VARCHAR(50),
        paymentRef VARCHAR(100),
        status VARCHAR(50),
        createdAt BIGINT
    )");

    // Order Items
    $pdo->exec("CREATE TABLE IF NOT EXISTS order_items (
        id INT AUTO_INCREMENT PRIMARY KEY,
        order_id VARCHAR(50),
        product_id VARCHAR(50),
        name VARCHAR(150),
        price DOUBLE,
        seed VARCHAR(50),
        qty INT,
        FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
    )");

    // Order History
    $pdo->exec("CREATE TABLE IF NOT EXISTS order_history (
        id INT AUTO_INCREMENT PRIMARY KEY,
        order_id VARCHAR(50),
        status VARCHAR(50),
        at BIGINT,
        FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
    )");

    // Banners
    $pdo->exec("CREATE TABLE IF NOT EXISTS top_banners (
        id VARCHAR(50) PRIMARY KEY,
        title VARCHAR(100),
        image VARCHAR(255)
    )");
    
    $pdo->exec("CREATE TABLE IF NOT EXISTS hero_banners (
        id VARCHAR(50) PRIMARY KEY,
        image VARCHAR(255),
        link VARCHAR(255)
    )");

    // Insert Admin
    $stmt = $pdo->prepare("SELECT * FROM admins WHERE username = 'admin'");
    $stmt->execute();
    if ($stmt->rowCount() == 0) {
        $hash = password_hash('heropass1234', PASSWORD_DEFAULT);
        $pdo->exec("INSERT INTO admins (username, password_hash) VALUES ('admin', '$hash')");
    }

    // Insert Seed Products if empty
    $stmt = $pdo->query("SELECT COUNT(*) FROM products");
    if ($stmt->fetchColumn() == 0) {
        $seedProducts = [
            ["fs0", "Samsung Galaxy A26 4G Smartphone", 144499, 180000, "-20%", "fs0", "Phones & Tablets"],
            ["fs1", "HP 15.6 inches USB 4G Laptop", 419999, 500000, "-17%", "fs1", "Computing"],
            ["fs2", "Sunking HomePlus Solar Kit", 73110, 90000, "-19%", "fs2", "Electronics"],
            ["fs3", "Water Thermocool 3kg Washer", 216706, 260000, "-17%", "fs3", "Appliances"],
            ["fs4", 'Sunking 15" Rechargeable Fan', 76721, 90000, "-15%", "fs4", "Appliances"],
            ["fs5", "Ace 20000 mAh Power Bank", 7500, 10000, "-25%", "fs5", "Mobile Accessories"],
            ["ts0", "Hithium 15kWh Power Station", 199999, 260000, "-22%", "ts0", "Electronics"],
            ["ts1", 'Royal 10" QLED TV', 212000, 250000, "-16%", "ts1", "Electronics"],
            ["ts2", "Oraimo SpaceBuds 2 Pro", 12499, 20000, "-38%", "ts2", "Mobile Accessories"],
            ["ts3", "Samsung Galaxy A26 Shell Case", 128991, 150000, "-14%", "ts3", "Mobile Accessories"],
            ["ts4", "SunKit InfiKarn 2.5kva Inverter", 210244, 235000, "-11%", "ts4", "Electronics"],
            ["ts5", "Martini Rossi Red Vermouth", 10890, 14500, "-25%", "ts5", "Groceries"],
        ];
        $insertProd = $pdo->prepare("INSERT INTO products (id, name, price, old_price, disc, seed, category) VALUES (?, ?, ?, ?, ?, ?, ?)");
        foreach ($seedProducts as $p) {
            $insertProd->execute($p);
        }
    }
    
    // Insert top banners if empty
    $stmt = $pdo->query("SELECT COUNT(*) FROM top_banners");
    if ($stmt->fetchColumn() == 0) {
        $pdo->exec("INSERT INTO top_banners (id, title, image) VALUES 
            ('1', 'Call to order', 'https://picsum.photos/seed/promo1/300/100'),
            ('2', 'Sell on Suleja', 'https://picsum.photos/seed/promo2/300/100'),
            ('3', 'Best Deals', 'https://picsum.photos/seed/promo3/300/100')
        ");
    }

    // Insert hero banners if empty
    $stmt = $pdo->query("SELECT COUNT(*) FROM hero_banners");
    if ($stmt->fetchColumn() == 0) {
        $pdo->exec("INSERT INTO hero_banners (id, image, link) VALUES 
            ('1', 'https://picsum.photos/seed/banner1/800/400', '#'),
            ('2', 'https://picsum.photos/seed/banner2/800/400', '#'),
            ('3', 'https://picsum.photos/seed/banner3/800/400', '#'),
            ('4', 'https://picsum.photos/seed/banner4/800/400', '#'),
            ('5', 'https://picsum.photos/seed/banner5/800/400', '#'),
            ('6', 'https://picsum.photos/seed/banner6/800/400', '#')
        ");
    }

    echo "Database initialized successfully! <a href='/'>Go to App</a>";
} catch (\PDOException $e) {
    echo "Setup failed: " . $e->getMessage();
}
?>
