<?php
require_once 'auth.php';

// Enable error reporting
error_reporting(E_ALL);
ini_set('display_errors', 1);

$host = "localhost";
$user = "finggu_finggunewdash";
$pass = "Bunty@123";
$db   = "finggu_finggunewdash";

try {
    // Connect to MySQL server
    $pdo = new PDO("mysql:host=$host", $user, $pass);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    // Create database if not exists
    $pdo->exec("CREATE DATABASE IF NOT EXISTS `$db` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");
    
    // Select the database
    $pdo->exec("USE `$db`");
    
    // Create cities table
    $pdo->exec("CREATE TABLE IF NOT EXISTS `cities` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `name` varchar(100) NOT NULL,
        `slug` varchar(120) NOT NULL,
        `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
        `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (`id`),
        UNIQUE KEY `slug` (`slug`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    
    // Create services table
    $pdo->exec("CREATE TABLE IF NOT EXISTS `services` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `name` varchar(100) NOT NULL,
        `slug` varchar(120) NOT NULL,
        `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
        `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (`id`),
        UNIQUE KEY `slug` (`slug`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    
    // Create city_service junction table
    $pdo->exec("CREATE TABLE IF NOT EXISTS `city_service` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `city_id` int(11) NOT NULL,
        `service_id` int(11) NOT NULL,
        `seo_title` varchar(200) DEFAULT NULL,
        `seo_description` varchar(500) DEFAULT NULL,
        `content` text,
        `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
        `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (`id`),
        UNIQUE KEY `city_service_unique` (`city_id`,`service_id`),
        KEY `service_id` (`service_id`),
        CONSTRAINT `city_service_ibfk_1` FOREIGN KEY (`city_id`) REFERENCES `cities` (`id`) ON DELETE CASCADE,
        CONSTRAINT `city_service_ibfk_2` FOREIGN KEY (`service_id`) REFERENCES `services` (`id`) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    
    // Insert some sample services if none exist
    $stmt = $pdo->query("SELECT COUNT(*) as count FROM services");
    $result = $stmt->fetch(PDO::FETCH_ASSOC);
    
    if ($result['count'] == 0) {
        $services = [
            'Web Development',
            'Mobile App Development',
            'SEO Services',
            'Digital Marketing',
            'Graphic Design',
            'E-commerce Solutions',
            'Content Writing'
        ];
        
        $stmt = $pdo->prepare("INSERT INTO services (name, slug) VALUES (?, ?)");
        foreach ($services as $service) {
            $slug = strtolower(str_replace(' ', '-', $service));
            $stmt->execute([$service, $slug]);
        }
        
        echo "<p>✅ Added " . count($services) . " sample services</p>";
    }
    
    echo "<h2>✅ Database tables created successfully!</h2>";
    echo "<p><a href='check-db.php'>Check database status</a> | <a href='manage-cities-services.php'>Go to cities management</a></p>";
    
} catch (PDOException $e) {
    die("<h2 style='color: red;'>❌ Error: " . htmlspecialchars($e->getMessage()) . "</h2>");
}
?>
