<?php
require_once __DIR__ . '/../api/config.php';
require_once __DIR__ . '/../includes/Database.php';

header('Content-Type: text/plain');

echo "--- Quicky Quicky Database Setup ---\n\n";

// 1. Check Database Connection
echo "1. Checking database connection...\n";
try {
    $db = new Database();
    echo "   Database connection successful!\n";
} catch (PDOException $e) {
    echo "   Database connection FAILED: " . $e->getMessage() . "\n";
    echo "   Please ensure your database is running and 'api/config.php' has correct credentials.\n";
    exit;
}

// 2. Define Table Schemas
echo "2. Defining table schemas...\n";
$tableSchemas = [
    'users' => [
        'sql' => "CREATE TABLE IF NOT EXISTS users (
            id INT AUTO_INCREMENT PRIMARY KEY,
            email VARCHAR(255) UNIQUE NOT NULL,
            password VARCHAR(255) NOT NULL,
            name VARCHAR(255) NOT NULL,
            role ENUM('customer', 'shop_owner', 'rider', 'admin') DEFAULT 'customer',
            phone VARCHAR(20),
            avatar VARCHAR(255),
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        )",
        'columns' => ['id', 'email', 'password', 'name', 'role', 'phone', 'avatar', 'created_at', 'updated_at']
    ],
    'shops' => [
        'sql' => "CREATE TABLE IF NOT EXISTS shops (
            id INT AUTO_INCREMENT PRIMARY KEY,
            owner_id INT NOT NULL,
            name VARCHAR(255) NOT NULL,
            description TEXT,
            location TEXT NOT NULL,
            latitude DECIMAL(10, 8),
            longitude DECIMAL(11, 8),
            image VARCHAR(255),
            is_approved BOOLEAN DEFAULT FALSE,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE CASCADE
        )",
        'columns' => ['id', 'owner_id', 'name', 'description', 'location', 'latitude', 'longitude', 'image', 'is_approved', 'created_at']
    ],
    'products' => [
        'sql' => "CREATE TABLE IF NOT EXISTS products (
            id INT AUTO_INCREMENT PRIMARY KEY,
            shop_id INT NOT NULL,
            name VARCHAR(255) NOT NULL,
            description TEXT,
            price DECIMAL(10, 2) NOT NULL,
            image VARCHAR(255),
            category VARCHAR(100),
            is_available BOOLEAN DEFAULT TRUE,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (shop_id) REFERENCES shops(id) ON DELETE CASCADE
        )",
        'columns' => ['id', 'shop_id', 'name', 'description', 'price', 'image', 'category', 'is_available', 'created_at']
    ],
    'deliveries' => [
        'sql' => "CREATE TABLE IF NOT EXISTS deliveries (
            id INT AUTO_INCREMENT PRIMARY KEY,
            customer_id INT NOT NULL,
            rider_id INT,
            tracking_id VARCHAR(20) UNIQUE,
            pickup_location TEXT NOT NULL,
            delivery_location TEXT NOT NULL,
            pickup_lat DECIMAL(10, 8),
            pickup_lng DECIMAL(11, 8),
            delivery_lat DECIMAL(10, 8),
            delivery_lng DECIMAL(11, 8),
            package_description TEXT,
            delivery_mode ENUM('standard', 'express') DEFAULT 'standard',
            package_weight DECIMAL(8, 2),
            delivery_instructions TEXT,
            package_value DECIMAL(10, 2),
            status ENUM('pending', 'accepted', 'picked_up', 'in_transit', 'delivered', 'cancelled') DEFAULT 'pending',
            price DECIMAL(8, 2),
            distance DECIMAL(8, 2),
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            FOREIGN KEY (customer_id) REFERENCES users(id),
            FOREIGN KEY (rider_id) REFERENCES users(id)
        )",
        'columns' => ['id', 'customer_id', 'rider_id', 'tracking_id', 'pickup_location', 'delivery_location', 'pickup_lat', 'pickup_lng', 'delivery_lat', 'delivery_lng', 'package_description', 'delivery_mode', 'package_weight', 'delivery_instructions', 'package_value', 'status', 'price', 'distance', 'created_at', 'updated_at']
    ],
    'orders' => [
        'sql' => "CREATE TABLE IF NOT EXISTS orders (
            id INT AUTO_INCREMENT PRIMARY KEY,
            user_id INT NOT NULL,
            shop_id INT NOT NULL,
            delivery_id INT,
            total_amount DECIMAL(10, 2) NOT NULL,
            status ENUM('pending', 'confirmed', 'preparing', 'ready', 'picked_up', 'delivered', 'cancelled') DEFAULT 'pending',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users(id),
            FOREIGN KEY (shop_id) REFERENCES shops(id),
            FOREIGN KEY (delivery_id) REFERENCES deliveries(id)
        )",
        'columns' => ['id', 'user_id', 'shop_id', 'delivery_id', 'total_amount', 'status', 'created_at']
    ],
    'order_items' => [
        'sql' => "CREATE TABLE IF NOT EXISTS order_items (
            id INT AUTO_INCREMENT PRIMARY KEY,
            order_id INT NOT NULL,
            product_id INT NOT NULL,
            quantity INT NOT NULL,
            price DECIMAL(10, 2) NOT NULL,
            FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
            FOREIGN KEY (product_id) REFERENCES products(id)
        )",
        'columns' => ['id', 'order_id', 'product_id', 'quantity', 'price']
    ],
    'slides' => [
        'sql' => "CREATE TABLE IF NOT EXISTS slides (
            id INT AUTO_INCREMENT PRIMARY KEY,
            title VARCHAR(255) NOT NULL,
            description TEXT,
            image_path VARCHAR(255) NOT NULL,
            is_active BOOLEAN DEFAULT TRUE,
            sort_order INT NOT NULL DEFAULT 0,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )",
        'columns' => ['id', 'title', 'description', 'image_path', 'is_active', 'sort_order', 'created_at']
    ],
    'notifications' => [
        'sql' => "CREATE TABLE IF NOT EXISTS notifications (
            id INT AUTO_INCREMENT PRIMARY KEY,
            user_id INT NOT NULL,
            message TEXT NOT NULL,
            link VARCHAR(255),
            is_read BOOLEAN DEFAULT FALSE,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users(id)
        )",
        'columns' => ['id', 'user_id', 'message', 'link', 'is_read', 'created_at']
    ],
    'settings' => [
        'sql' => "CREATE TABLE IF NOT EXISTS settings (
            setting_key VARCHAR(50) PRIMARY KEY,
            setting_value TEXT,
            description VARCHAR(255)
        )",
        'columns' => ['setting_key', 'setting_value', 'description']
    ],
    'user_addresses' => [
        'sql' => "CREATE TABLE IF NOT EXISTS user_addresses (
            id INT AUTO_INCREMENT PRIMARY KEY,
            user_id INT NOT NULL,
            label VARCHAR(100) NOT NULL,
            address TEXT NOT NULL,
            is_default BOOLEAN DEFAULT FALSE,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
        )",
        'columns' => ['id', 'user_id', 'label', 'address', 'is_default', 'created_at']
    ],
    'push_subscriptions' => [
        'sql' => "CREATE TABLE IF NOT EXISTS push_subscriptions (
            id INT AUTO_INCREMENT PRIMARY KEY,
            user_id INT NOT NULL,
            subscription TEXT NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
        )",
        'columns' => ['id', 'user_id', 'subscription', 'created_at']
    ],
];

// 3. Process Each Table
echo "3. Processing tables...\n";
foreach ($tableSchemas as $tableName => $schema) {
    echo "   - Checking table: {$tableName}\n";

    // Check if table exists
    $stmt = $db->query("SHOW TABLES LIKE '{$tableName}'");
    if ($stmt->rowCount() == 0) {
        echo "      - Table does not exist. Creating...\n";
        $db->query($schema['sql']);
        echo "      - Table {$tableName} created successfully.\n";
    } else {
        echo "      - Table exists. Checking for missing columns...\n";
        
        // Fetch existing columns
        $colStmt = $db->query("SHOW COLUMNS FROM {$tableName}");
        $existingColumns = array_column($colStmt->fetchAll(PDO::FETCH_ASSOC), 'Field');

        // Find missing columns from the schema's definition
        $missingColumns = array_diff($schema['columns'], $existingColumns);

        if (!empty($missingColumns)) {
            echo "      - Missing columns found. Adding...\n";

            // Extract the body of the CREATE TABLE statement to find column definitions
            $tableBody = '';
            if (preg_match('/CREATE TABLE.*?\((.*)\)/si', $schema['sql'], $matches)) {
                $tableBody = $matches[1];
            }

            foreach ($missingColumns as $column) {
                // Regex to find the column definition: start of line, whitespace, column name, whitespace, definition, optional comma, end of line
                $pattern = '/^\s*`?' . preg_quote($column, '/') . '`?\s+(.*?)(\s*,)?$/m';

                if ($tableBody && preg_match($pattern, $tableBody, $columnMatch)) {
                    $definition = trim($columnMatch[1]);
                    $addColumnSql = "ALTER TABLE {$tableName} ADD COLUMN {$column} {$definition}";
                    try {
                        $db->query($addColumnSql);
                        echo "         - Column {$column} added successfully.\n";
                    } catch (PDOException $e) {
                        echo "         - Error adding column {$column}: " . $e->getMessage() . "\n";
                    }
                } else {
                    echo "         - Error: Could not extract definition for column '{$column}'. Skipped.\n";
                }
            }
        } else {
            echo "      - No missing columns found.\n";
        }
    }
}

echo "\n4. Seeding initial settings...\n";
$defaultSettings = [
    'base_price_standard' => ['5.00', 'Base fee for a Standard delivery.'],
    'price_per_km_standard' => ['0.50', 'Cost per kilometer for a Standard delivery.'],
    'base_price_express' => ['10.00', 'Base fee for an Express delivery.'],
    'price_per_km_express' => ['0.75', 'Cost per kilometer for an Express delivery.'],
    'multi_shop_fee' => ['2.50', 'Additional fee for orders from multiple shops.'],
    'loyalty_delivery_threshold' => ['10', 'Number of deliveries to trigger a loyalty discount.'],
    'loyalty_delivery_discount_percent' => ['50', 'Percentage discount for loyalty reward.'],
    'allow_registrations' => ['true', 'Enable or disable new user registrations.'],
    'allow_shop_creation' => ['true', 'Enable or disable new shop creation by users.'],
    'operating_hours_start' => ['08:00', 'The time the service starts operating.'],
    'operating_hours_end' => ['22:00', 'The time the service stops operating.'],
    'payment_cod' => ['true', 'Enable Cash on Delivery.'],
    'payment_mobile' => ['true', 'Enable Mobile Money payments.'],
    'payment_card' => ['false', 'Enable Bank Card payments.'],
    'currency_symbol' => ['$', 'The currency symbol to display in the app.']
];

try {
    // The query method in the Database class prepares and executes.
    // We will call it inside the loop.
    $count = 0;
    foreach ($defaultSettings as $key => $data) {
        $value = $data[0];
        $description = $data[1];
        
        $checkStmt = $db->query("SELECT COUNT(*) FROM settings WHERE setting_key = ?", [$key]);
        if ($checkStmt->fetchColumn() == 0) {
            $db->query("INSERT INTO settings (setting_key, setting_value, description) VALUES (?, ?, ?)", [$key, $value, $description]);
            $count++;
        }
    }
    echo "   - {$count} new settings seeded successfully.\n";
} catch (PDOException $e) {
    echo "   - Error seeding settings: " . $e->getMessage() . "\n";
}


echo "\n--- Database setup complete! ---\n";
?>