Database.php

<?php

class Database {
    private $conn;

    public function __construct() {
        $this->connect();
    }

    private function connect() {
        try {
            $host = 'localhost';
            $port = 3307;
            $dbname = 'delivery_foodx';
            $user = 'root';
            $password = '';

            // Create connection without database first
            $dsn = "mysql:host=$host;port=$port;charset=utf8mb4";
            $pdo = new PDO($dsn, $user, $password);
            $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

            // Create database if it doesn't exist
            $pdo->exec("CREATE DATABASE IF NOT EXISTS `$dbname` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");

            // Connect to the database
            $dsn = "mysql:host=$host;port=$port;dbname=$dbname;charset=utf8mb4";
            $this->conn = new PDO($dsn, $user, $password);
            $this->conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        } catch(PDOException $e) {
            die("Connection failed: " . $e->getMessage());
        }
    }

    public function getConnection() {
        return $this->conn;
    }

    public function generateID($prefix) {
        $random = str_pad(mt_rand(1, 999999), 6, '0', STR_PAD_LEFT);
        return $prefix . '-' . $random;
    }

    public function createTables() {
        $queries = [
            // Admin users table
            "CREATE TABLE IF NOT EXISTS admin_users (
                admin_id INT AUTO_INCREMENT PRIMARY KEY,
                username VARCHAR(100) UNIQUE NOT NULL,
                password VARCHAR(255) NOT NULL,
                full_name VARCHAR(255),
                email VARCHAR(255),
                role VARCHAR(50) DEFAULT 'admin',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

            // Categories table
            "CREATE TABLE IF NOT EXISTS categories (
                category_id INT AUTO_INCREMENT PRIMARY KEY,
                category_name VARCHAR(100) UNIQUE NOT NULL,
                description TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

            // Customers table
            "CREATE TABLE IF NOT EXISTS customers (
                customer_id VARCHAR(50) PRIMARY KEY,
                username VARCHAR(100) UNIQUE NOT NULL,
                password VARCHAR(255) NOT NULL,
                full_name VARCHAR(255),
                nic VARCHAR(20),
                contact_no VARCHAR(20),
                dob DATE,
                location_no VARCHAR(50),
                lane VARCHAR(100),
                street VARCHAR(100),
                city VARCHAR(100),
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

            // Items table
            "CREATE TABLE IF NOT EXISTS items (
                item_id VARCHAR(50) PRIMARY KEY,
                item_name VARCHAR(255) NOT NULL,
                price DECIMAL(10, 2) NOT NULL,
                category_id INT,
                description TEXT,
                item_image VARCHAR(255),
                status VARCHAR(20) DEFAULT 'active',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (category_id) REFERENCES categories(category_id) ON DELETE SET NULL
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

            // Ingredients table
            "CREATE TABLE IF NOT EXISTS ingredients (
                ingredient_id INT AUTO_INCREMENT PRIMARY KEY,
                item_id VARCHAR(50),
                ingredient_name VARCHAR(255) NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (item_id) REFERENCES items(item_id) ON DELETE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

            // Riders table
            "CREATE TABLE IF NOT EXISTS riders (
                employee_id VARCHAR(50) PRIMARY KEY,
                first_name VARCHAR(100) NOT NULL,
                middle_name VARCHAR(100),
                last_name VARCHAR(100) NOT NULL,
                dob DATE,
                age INT,
                contact_no VARCHAR(20),
                license_no VARCHAR(50),
                location_no VARCHAR(50),
                status VARCHAR(20) DEFAULT 'active',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

            // Vehicles table
            "CREATE TABLE IF NOT EXISTS vehicles (
                vehicle_id VARCHAR(50) PRIMARY KEY,
                vehicle_no VARCHAR(50) UNIQUE NOT NULL,
                vehicle_type VARCHAR(50),
                assigned_rider VARCHAR(50),
                current_meter DECIMAL(10, 2) DEFAULT 0,
                status VARCHAR(20) DEFAULT 'active',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (assigned_rider) REFERENCES riders(employee_id) ON DELETE SET NULL
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

            // Orders table
            "CREATE TABLE IF NOT EXISTS orders (
                order_id VARCHAR(50) PRIMARY KEY,
                customer_id VARCHAR(50),
                employee_id VARCHAR(50),
                total_amount DECIMAL(10, 2) DEFAULT 0,
                order_status VARCHAR(50) DEFAULT 'Pending',
                payment_method VARCHAR(50),
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE,
                FOREIGN KEY (employee_id) REFERENCES riders(employee_id) ON DELETE SET NULL
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

            // Order items table
            "CREATE TABLE IF NOT EXISTS order_items (
                order_item_id INT AUTO_INCREMENT PRIMARY KEY,
                order_id VARCHAR(50),
                item_id VARCHAR(50),
                quantity INT NOT NULL,
                price DECIMAL(10, 2) NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE,
                FOREIGN KEY (item_id) REFERENCES items(item_id) ON DELETE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

            // Payments table
            "CREATE TABLE IF NOT EXISTS payments (
                payment_id INT AUTO_INCREMENT PRIMARY KEY,
                order_id VARCHAR(50),
                customer_id VARCHAR(50),
                amount DECIMAL(10, 2) NOT NULL,
                card_type VARCHAR(50),
                payment_status VARCHAR(50) DEFAULT 'pending',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE,
                FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

            // Vehicle meter table
            "CREATE TABLE IF NOT EXISTS vehicle_meter (
                meter_id INT AUTO_INCREMENT PRIMARY KEY,
                vehicle_id VARCHAR(50),
                total_distance DECIMAL(10, 2),
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (vehicle_id) REFERENCES vehicles(vehicle_id) ON DELETE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

            // Dependents table
            "CREATE TABLE IF NOT EXISTS dependents (
                dependent_id VARCHAR(50) PRIMARY KEY,
                employee_id VARCHAR(50),
                dependent_name VARCHAR(255) NOT NULL,
                dob DATE,
                relationship VARCHAR(50),
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (employee_id) REFERENCES riders(employee_id) ON DELETE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci"
        ];

        foreach ($queries as $query) {
            try {
                $this->conn->exec($query);
            } catch(PDOException $e) {
                error_log("Error creating table: " . $e->getMessage());
            }
        }

        // Insert default admin if not exists
        $this->createDefaultAdmin();
        
        // Insert default categories if not exist
        $this->createDefaultCategories();
    }

    private function createDefaultAdmin() {
        try {
            $stmt = $this->conn->query("SELECT COUNT(*) as count FROM admin_users");
            $result = $stmt->fetch(PDO::FETCH_ASSOC);
            
            if ($result['count'] == 0) {
                $password = password_hash('admin123', PASSWORD_DEFAULT);
                $stmt = $this->conn->prepare("
                    INSERT INTO admin_users (username, password, full_name, email, role) 
                    VALUES ('admin', :password, 'System Administrator', 'admin@foodhub.com', 'admin')
                ");
                $stmt->execute(['password' => $password]);
            }
        } catch(PDOException $e) {
            error_log("Error creating default admin: " . $e->getMessage());
        }
    }

    private function createDefaultCategories() {
        try {
            $stmt = $this->conn->query("SELECT COUNT(*) as count FROM categories");
            $result = $stmt->fetch(PDO::FETCH_ASSOC);
            
            if ($result['count'] == 0) {
                $categories = [
                    ['Fast Food', 'Burgers, fries, pizza, etc.'],
                    ['Drinks', 'Soft drinks, juices, shakes'],
                    ['Desserts', 'Cakes, ice cream, sweets']
                ];
                
                $stmt = $this->conn->prepare("
                    INSERT INTO categories (category_name, description) 
                    VALUES (:name, :description)
                ");
                
                foreach ($categories as $category) {
                    $stmt->execute([
                        'name' => $category[0],
                        'description' => $category[1]
                    ]);
                }
            }
        } catch(PDOException $e) {
            error_log("Error creating default categories: " . $e->getMessage());
        }
    }
}