<?php
// --- DATABASE CONFIGURATION ---
// Local development uses SQLite. If you deploy to webapp69.cm.in.th (or any hosting) where SQLite is disabled,
// the system will automatically fall back to MySQL.
// Please configure your MySQL credentials here:
$mysql_host = 'localhost';
$mysql_user = 'u69319090017'; // Replace with your MySQL Username (usually your Student ID)
$mysql_pass = '@6917';        // Replace with your MySQL Password (usually @ + year + student ID suffix)
$mysql_db   = 'shop69319090017'; // Replace with your MySQL Database Name (usually shop + student ID)

$db_driver = 'sqlite';
if (!extension_loaded('pdo_sqlite')) {
    $db_driver = 'mysql';
}

try {
    if ($db_driver === 'mysql') {
        $pdo = new PDO("mysql:host=$mysql_host;dbname=$mysql_db;charset=utf8mb4", $mysql_user, $mysql_pass);
    } else {
        $dbPath = __DIR__ . '/shop.sqlite';
        $pdo = new PDO("sqlite:" . $dbPath);
    }
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);

    // Create Tables if they do not exist
    if ($db_driver === 'mysql') {
        // --- MYSQL SCHEMA ---
        $pdo->exec("CREATE TABLE IF NOT EXISTS users (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(255) NOT NULL,
            email VARCHAR(255) NOT NULL UNIQUE,
            password VARCHAR(255) NOT NULL,
            role VARCHAR(50) NOT NULL DEFAULT 'buyer'
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

        $pdo->exec("CREATE TABLE IF NOT EXISTS shops (
            id INT AUTO_INCREMENT PRIMARY KEY,
            seller_id INT NOT NULL,
            name VARCHAR(255) NOT NULL,
            description TEXT,
            status VARCHAR(50) NOT NULL DEFAULT 'approved',
            FOREIGN KEY (seller_id) REFERENCES users(id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

        try {
            $pdo->exec("ALTER TABLE shops ADD COLUMN status VARCHAR(50) NOT NULL DEFAULT 'approved'");
        } catch (PDOException $e) {
            // Column status might already exist, ignore error
        }

        // Check if products table has rating, if not, drop it to recreate with new schema
        try {
            $pdo->query("SELECT rating FROM products LIMIT 1");
        } catch (PDOException $e) {
            $pdo->exec("DROP TABLE IF EXISTS products");
        }

        $pdo->exec("CREATE TABLE IF NOT EXISTS products (
            id INT AUTO_INCREMENT PRIMARY KEY,
            shop_id INT NOT NULL,
            name VARCHAR(255) NOT NULL,
            name_th VARCHAR(255),
            name_en VARCHAR(255),
            description TEXT,
            price DECIMAL(10,2) NOT NULL,
            image TEXT,
            category VARCHAR(255),
            sizes VARCHAR(255) DEFAULT 'S, M, L, XL',
            colors_th VARCHAR(255) DEFAULT 'ขาว, ดำ',
            colors_en VARCHAR(255) DEFAULT 'White, Black',
            stock INT DEFAULT 10,
            rating DECIMAL(3,1) DEFAULT 4.9,
            sold_count INT DEFAULT 128,
            brand VARCHAR(255) DEFAULT 'VIVY BRANDNAME',
            model VARCHAR(255) DEFAULT 'Standard Edition',
            dimensions VARCHAR(255) DEFAULT '19 x 12 x 8 ซม.',
            year VARCHAR(50) DEFAULT '2024',
            equipment VARCHAR(255) DEFAULT 'ถุงผ้า, กล่อง, การ์ด',
            `condition` VARCHAR(255) DEFAULT 'Like New 99%',
            product_code VARCHAR(100) DEFAULT '26070901',
            gallery_images TEXT,
            FOREIGN KEY (shop_id) REFERENCES shops(id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

        // Check if orders table has product_id and payment_method, if not, drop it to recreate with new schema
        try {
            $pdo->query("SELECT product_id, payment_method FROM orders LIMIT 1");
        } catch (PDOException $e) {
            $pdo->exec("DROP TABLE IF EXISTS orders");
        }

        $pdo->exec("CREATE TABLE IF NOT EXISTS orders (
            id INT AUTO_INCREMENT PRIMARY KEY,
            user_id INT NOT NULL,
            shop_id INT NOT NULL,
            product_id INT NULL,
            product_name VARCHAR(255) NOT NULL,
            quantity INT NOT NULL,
            total_price DECIMAL(10,2) NOT NULL,
            payment_method VARCHAR(100) DEFAULT 'PromptPay',
            status VARCHAR(50) NOT NULL DEFAULT 'Pending',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
            FOREIGN KEY (shop_id) REFERENCES shops(id) ON DELETE CASCADE,
            FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE SET NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    } else {
        // --- SQLITE SCHEMA ---
        $pdo->exec("CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            email TEXT NOT NULL UNIQUE,
            password TEXT NOT NULL,
            role TEXT NOT NULL CHECK(role IN ('admin', 'seller', 'buyer')) DEFAULT 'buyer'
        )");

        $pdo->exec("CREATE TABLE IF NOT EXISTS shops (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            seller_id INTEGER NOT NULL,
            name TEXT NOT NULL,
            description TEXT,
            status TEXT NOT NULL CHECK(status IN ('approved', 'suspended', 'pending')) DEFAULT 'approved',
            FOREIGN KEY (seller_id) REFERENCES users(id) ON DELETE CASCADE
        )");

        try {
            $pdo->exec("ALTER TABLE shops ADD COLUMN status TEXT NOT NULL DEFAULT 'approved'");
        } catch (PDOException $e) {
            // Column status might already exist, ignore error
        }

        // Check if products table has rating, if not, drop it to recreate with new schema
        try {
            $pdo->query("SELECT rating FROM products LIMIT 1");
        } catch (PDOException $e) {
            $pdo->exec("DROP TABLE IF EXISTS products");
        }

        $pdo->exec("CREATE TABLE IF NOT EXISTS products (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            shop_id INTEGER NOT NULL,
            name TEXT NOT NULL,
            name_th TEXT,
            name_en TEXT,
            description TEXT,
            price REAL NOT NULL,
            image TEXT,
            category TEXT,
            sizes TEXT DEFAULT 'S, M, L, XL',
            colors_th TEXT DEFAULT 'ขาว, ดำ',
            colors_en TEXT DEFAULT 'White, Black',
            stock INTEGER DEFAULT 10,
            rating REAL DEFAULT 4.9,
            sold_count INTEGER DEFAULT 128,
            brand TEXT DEFAULT 'VIVY BRANDNAME',
            model TEXT DEFAULT 'Standard Edition',
            dimensions TEXT DEFAULT '19 x 12 x 8 ซม.',
            year TEXT DEFAULT '2024',
            equipment TEXT DEFAULT 'ถุงผ้า, กล่อง, การ์ด',
            `condition` TEXT DEFAULT 'Like New 99%',
            product_code TEXT DEFAULT '26070901',
            gallery_images TEXT,
            FOREIGN KEY (shop_id) REFERENCES shops(id) ON DELETE CASCADE
        )");

        // Check if orders table has product_id and payment_method, if not, drop it to recreate with new schema
        try {
            $pdo->query("SELECT product_id, payment_method FROM orders LIMIT 1");
        } catch (PDOException $e) {
            $pdo->exec("DROP TABLE IF EXISTS orders");
        }

        $pdo->exec("CREATE TABLE IF NOT EXISTS orders (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            shop_id INTEGER NOT NULL,
            product_id INTEGER,
            product_name TEXT NOT NULL,
            quantity INTEGER NOT NULL,
            total_price REAL NOT NULL,
            payment_method TEXT DEFAULT 'PromptPay',
            status TEXT NOT NULL CHECK(status IN ('Pending', 'Shipped', 'Delivered', 'Cancelled')) DEFAULT 'Pending',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
            FOREIGN KEY (shop_id) REFERENCES shops(id) ON DELETE CASCADE,
            FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE SET NULL
        )");
    }

    // Seed Users if empty
    $userCount = $pdo->query("SELECT COUNT(*) FROM users")->fetchColumn();
    if ($userCount == 0) {
        $passHash = password_hash('password123', PASSWORD_DEFAULT);
        $pdo->prepare("INSERT INTO users (name, email, password, role) VALUES (?, ?, ?, ?)")
            ->execute(['สมชาย พลดี', 'seller1@example.com', $passHash, 'seller']);
        $pdo->prepare("INSERT INTO users (name, email, password, role) VALUES (?, ?, ?, ?)")
            ->execute(['สมหญิง รักดี', 'seller2@example.com', $passHash, 'seller']);
        $pdo->prepare("INSERT INTO users (name, email, password, role) VALUES (?, ?, ?, ?)")
            ->execute(['ลูกค้าทดสอบ (Buyer)', 'buyer@example.com', $passHash, 'buyer']);
    }

    // Seed Shops if empty
    $shopCount = $pdo->query("SELECT COUNT(*) FROM shops")->fetchColumn();
    if ($shopCount == 0) {
        $seller1Id = $pdo->query("SELECT id FROM users WHERE email = 'seller1@example.com'")->fetchColumn();
        $seller2Id = $pdo->query("SELECT id FROM users WHERE email = 'seller2@example.com'")->fetchColumn();
        
        if ($seller1Id) {
            $pdo->prepare("INSERT INTO shops (seller_id, name, description) VALUES (?, ?, ?)")
                ->execute([$seller1Id, 'TechPulse Store', 'จำหน่ายอุปกรณ์อิเล็กทรอนิกส์และคอมพิวเตอร์ระดับพรีเมียม']);
        }
        if ($seller2Id) {
            $pdo->prepare("INSERT INTO shops (seller_id, name, description) VALUES (?, ?, ?)")
                ->execute([$seller2Id, 'VogueThread Boutique', 'เสื้อผ้าและแฟชั่นทันสมัยส่งตรงจากเกาหลี']);
        }
    }

    // Seed Products if empty
    $productCount = $pdo->query("SELECT COUNT(*) FROM products")->fetchColumn();
    if ($productCount == 0) {
        $shop1Id = $pdo->query("SELECT id FROM shops WHERE name = 'VIVY BRANDNAME'")->fetchColumn() ?: 1;
        $shop2Id = $pdo->query("SELECT id FROM shops WHERE name = 'Noa Luxury Boutique'")->fetchColumn() ?: 2;

        $pinkBagGalleries = [
            'https://images.unsplash.com/photo-1584917865442-de89df76afd3?w=800&auto=format&fit=crop&q=80',
            'https://images.unsplash.com/photo-1590874103328-eac38a683ce7?w=800&auto=format&fit=crop&q=80',
            'https://images.unsplash.com/photo-1566150905458-1bf1fc113f0d?w=800&auto=format&fit=crop&q=80',
            'https://images.unsplash.com/photo-1591561954557-26941169b49e?w=800&auto=format&fit=crop&q=80',
            'https://images.unsplash.com/photo-1548036328-c9fa89d128fa?w=800&auto=format&fit=crop&q=80',
            'https://images.unsplash.com/photo-1608256246200-53e635b5b65f?w=800&auto=format&fit=crop&q=80'
        ];

        $watchGalleries = [
            'https://images.unsplash.com/photo-1524592094714-0f0654e20314?w=800&auto=format&fit=crop&q=80',
            'https://images.unsplash.com/photo-1522335789203-aabd1fc54bc9?w=800&auto=format&fit=crop&q=80',
            'https://images.unsplash.com/photo-1523275335684-37898b6baf30?w=800&auto=format&fit=crop&q=80',
            'https://images.unsplash.com/photo-1539185441755-769473a23570?w=800&auto=format&fit=crop&q=80'
        ];

        $jacketGalleries = [
            'https://images.unsplash.com/photo-1576995853123-5a10305d93c0?w=800&auto=format&fit=crop&q=80',
            'https://images.unsplash.com/photo-1551028719-00167b16eac5?w=800&auto=format&fit=crop&q=80',
            'https://images.unsplash.com/photo-1544441893-675973e31985?w=800&auto=format&fit=crop&q=80'
        ];

        $tshirtGalleries = [
            'https://images.unsplash.com/photo-1521572267360-ee0c2909d518?w=800&auto=format&fit=crop&q=80',
            'https://images.unsplash.com/photo-1618354691373-d851c5c3a990?w=800&auto=format&fit=crop&q=80',
            'https://images.unsplash.com/photo-1583743814966-8936f5b7be1a?w=800&auto=format&fit=crop&q=80'
        ];

        $productsToSeed = [
            [
                'shop_id' => $shop1Id,
                'name' => 'Balenciaga hourglass',
                'name_th' => 'Balenciaga hourglass XS sweet pink',
                'name_en' => 'Balenciaga hourglass XS sweet pink',
                'description' => 'กระเป๋า Balenciaga hourglass หนังแท้พรีเมียมสีชมพูประกายระยิบระยับ สภาพดีเยี่ยม อุปกรณ์ครบพร้อมถุงผ้า',
                'price' => 16900.00,
                'image' => 'https://images.unsplash.com/photo-1584917865442-de89df76afd3?w=800&auto=format&fit=crop&q=80',
                'category' => 'Bags',
                'sizes' => 'XS',
                'colors_th' => 'ชมพู Sweet Pink',
                'colors_en' => 'Sweet Pink',
                'stock' => 5,
                'rating' => 4.9,
                'sold_count' => 84,
                'brand' => 'Dior',
                'model' => 'hourglass XS sweet pink',
                'dimensions' => '19 x 12 x 8 ซม.',
                'year' => '2022',
                'equipment' => 'ถุงผ้า',
                'condition' => 'Good',
                'product_code' => '26070903',
                'gallery_images' => json_encode($pinkBagGalleries)
            ],
            [
                'shop_id' => $shop1Id,
                'name' => 'Rolex Datejust 36 Blue Dial',
                'name_th' => 'Rolex Datejust 36 Blue Dial',
                'name_en' => 'Rolex Datejust 36 Blue Dial',
                'description' => 'นาฬิกาข้อมือ Rolex Datejust 36 หน้าปัดน้ำเงินขอบสลัก สาย Jubilee สภาพสะสม อุปกรณ์ครบกล่อง',
                'price' => 389000.00,
                'image' => 'https://images.unsplash.com/photo-1524592094714-0f0654e20314?w=800&auto=format&fit=crop&q=80',
                'category' => 'Watches',
                'sizes' => '36mm',
                'colors_th' => 'เงิน / หน้าปัดน้ำเงิน',
                'colors_en' => 'Silver / Blue Dial',
                'stock' => 2,
                'rating' => 5.0,
                'sold_count' => 12,
                'brand' => 'Rolex',
                'model' => 'Datejust 36 Jubilee',
                'dimensions' => '36 mm',
                'year' => '2023',
                'equipment' => 'กล่อง ใบรับประกัน ถุงผ้า',
                'condition' => 'Like New',
                'product_code' => '26070904',
                'gallery_images' => json_encode($watchGalleries)
            ],
            [
                'shop_id' => $shop2Id,
                'name' => 'Saint Laurent Shopping Tote',
                'name_th' => 'Saint Laurent Classic Leather Tote',
                'name_en' => 'Saint Laurent Classic Leather Tote',
                'description' => 'กระเป๋าหนังเรียบหรูสไตล์มินิมอล อะไหล่ทองคำ สวยเด่นสง่างาม',
                'price' => 45000.00,
                'image' => 'https://images.unsplash.com/photo-1590874103328-eac38a683ce7?w=800&auto=format&fit=crop&q=80',
                'category' => 'Bags',
                'sizes' => 'Medium',
                'colors_th' => 'ดำคลาสสิก',
                'colors_en' => 'Classic Black',
                'stock' => 4,
                'rating' => 4.8,
                'sold_count' => 56,
                'brand' => 'Saint Laurent',
                'model' => 'Shopping Tote Bag',
                'dimensions' => '35 x 28 x 13 ซม.',
                'year' => '2024',
                'equipment' => 'ถุงผ้า, กล่อง',
                'condition' => 'Brand New',
                'product_code' => '26070905',
                'gallery_images' => json_encode($pinkBagGalleries)
            ],
            [
                'shop_id' => $shop2Id,
                'name' => 'Balenciaga Denim Jacket',
                'name_th' => 'Balenciaga Oversized Washed Denim Jacket',
                'name_en' => 'Balenciaga Oversized Washed Denim Jacket',
                'description' => 'แจ็กเก็ตยีนส์โอเวอร์ไซส์ฟอกสไตล์สตรีทพรีเมียม สวมใส่สบายทรงสวยสะกดสายตา',
                'price' => 28500.00,
                'image' => 'https://images.unsplash.com/photo-1576995853123-5a10305d93c0?w=800&auto=format&fit=crop&q=80',
                'category' => 'Jacket',
                'sizes' => 'S, M, L',
                'colors_th' => 'ยีนส์ฟอก Vintage Blue',
                'colors_en' => 'Vintage Blue',
                'stock' => 6,
                'rating' => 4.9,
                'sold_count' => 142,
                'brand' => 'Balenciaga',
                'model' => 'Vintage Washed Denim',
                'dimensions' => 'Oversized Fit',
                'year' => '2023',
                'equipment' => 'ป้ายแท็กครบ, ถุงคลุม',
                'condition' => 'Brand New',
                'product_code' => '26070906',
                'gallery_images' => json_encode($jacketGalleries)
            ],
            [
                'shop_id' => $shop2Id,
                'name' => 'Celine Canvas Bucket Bag',
                'name_th' => 'Celine Monogram Canvas Bucket Bag',
                'name_en' => 'Celine Monogram Canvas Bucket Bag',
                'description' => 'กระเป๋าสายหนังทรงบักเก็ตลาย Monogram คลาสสิก น้ำหนักเบา จุของได้เยอะ',
                'price' => 34900.00,
                'image' => 'https://images.unsplash.com/photo-1566150905458-1bf1fc113f0d?w=800&auto=format&fit=crop&q=80',
                'category' => 'Bags',
                'sizes' => 'Small',
                'colors_th' => 'น้ำตาล / ลายแคนวาส',
                'colors_en' => 'Brown Monogram',
                'stock' => 3,
                'rating' => 4.9,
                'sold_count' => 97,
                'brand' => 'Celine',
                'model' => 'Triomphe Canvas Bucket',
                'dimensions' => '21 x 15 x 15 ซม.',
                'year' => '2023',
                'equipment' => 'ถุงผ้า, ใบรับประกัน',
                'condition' => 'Good',
                'product_code' => '26070907',
                'gallery_images' => json_encode($pinkBagGalleries)
            ],
            [
                'shop_id' => $shop2Id,
                'name' => 'Off-White Heavy Cotton Tee',
                'name_th' => 'Off-White Luxury Street Heavy T-Shirt',
                'name_en' => 'Off-White Luxury Street Heavy T-Shirt',
                'description' => 'เสื้อยืดผ้าคอตตอนเนื้อหนาเกรดพรีเมียม สกรีนลายกราฟิกไอคอนิกสตรีทแฟชั่น',
                'price' => 12500.00,
                'image' => 'https://images.unsplash.com/photo-1521572267360-ee0c2909d518?w=800&auto=format&fit=crop&q=80',
                'category' => 'T-Shirt',
                'sizes' => 'M, L, XL',
                'colors_th' => 'ขาวพรีเมียม, ดำ',
                'colors_en' => 'White, Black',
                'stock' => 10,
                'rating' => 4.7,
                'sold_count' => 210,
                'brand' => 'Off-White',
                'model' => 'Caravaggio Heavy Cotton',
                'dimensions' => 'Relaxed Fit',
                'year' => '2024',
                'equipment' => 'ถุงซิปล็อคแท้',
                'condition' => 'Brand New',
                'product_code' => '26070908',
                'gallery_images' => json_encode($tshirtGalleries)
            ]
        ];

        $stmt = $pdo->prepare("INSERT INTO products (shop_id, name, name_th, name_en, description, price, image, category, sizes, colors_th, colors_en, stock, rating, sold_count, brand, model, dimensions, year, equipment, `condition`, product_code, gallery_images) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
        foreach ($productsToSeed as $prod) {
            $stmt->execute([
                $prod['shop_id'], 
                $prod['name'], 
                $prod['name_th'], 
                $prod['name_en'], 
                $prod['description'], 
                $prod['price'], 
                $prod['image'],
                $prod['category'],
                $prod['sizes'],
                $prod['colors_th'],
                $prod['colors_en'],
                $prod['stock'],
                $prod['rating'],
                $prod['sold_count'],
                $prod['brand'],
                $prod['model'],
                $prod['dimensions'],
                $prod['year'],
                $prod['equipment'],
                $prod['condition'],
                $prod['product_code'],
                $prod['gallery_images']
            ]);
        }
    }

    // Always ensure admin exists
    $adminCount = $pdo->query("SELECT COUNT(*) FROM users WHERE role = 'admin'")->fetchColumn();
    if ($adminCount == 0) {
        $passHash = password_hash('password123', PASSWORD_DEFAULT);
        $pdo->prepare("INSERT INTO users (name, email, password, role) VALUES (?, ?, ?, ?)")
            ->execute(['ผู้ดูแลระบบ (Admin)', 'admin@example.com', $passHash, 'admin']);
    }

    // Always ensure some seeded orders exist if table is empty
    $orderCount = $pdo->query("SELECT COUNT(*) FROM orders")->fetchColumn();
    if ($orderCount == 0) {
        $products = $pdo->query("SELECT * FROM products")->fetchAll();
        $buyerId = $pdo->query("SELECT id FROM users WHERE role = 'buyer' LIMIT 1")->fetchColumn();
        if (!$buyerId) {
            $passHash = password_hash('password123', PASSWORD_DEFAULT);
            $pdo->prepare("INSERT INTO users (name, email, password, role) VALUES (?, ?, ?, ?)")
                ->execute(['ลูกค้าทดสอบ (Buyer)', 'buyer@example.com', $passHash, 'buyer']);
            $buyerId = $pdo->lastInsertId();
        }

        if (count($products) >= 2) {
            $pdo->prepare("INSERT INTO orders (user_id, shop_id, product_name, quantity, total_price, status) VALUES (?, ?, ?, ?, ?, ?)")
                ->execute([$buyerId, $products[0]['shop_id'], $products[0]['name'], 1, $products[0]['price'], 'Pending']);
            $pdo->prepare("INSERT INTO orders (user_id, shop_id, product_name, quantity, total_price, status) VALUES (?, ?, ?, ?, ?, ?)")
                ->execute([$buyerId, $products[1]['shop_id'], $products[1]['name'], 2, $products[1]['price'] * 2, 'Shipped']);
            $pdo->prepare("INSERT INTO orders (user_id, shop_id, product_name, quantity, total_price, status) VALUES (?, ?, ?, ?, ?, ?)")
                ->execute([$buyerId, $products[0]['shop_id'], $products[0]['name'], 1, $products[0]['price'], 'Delivered']);
        }
    }
} catch (PDOException $e) {
    die("เชื่อมต่อฐานข้อมูลล้มเหลว: " . $e->getMessage());
}