<?php
/**
 * Installation Script
 * Creates necessary database tables and inserts seed data.
 */

require_once 'config.php';

header('Content-Type: text/html; charset=utf-8');

if (!$conn) {
    die("<div style='color: red; font-family: sans-serif; padding: 20px;'>
            <strong>ত্রুটি (Error):</strong> ডাটাবেজ সংযোগ স্থাপন করা সম্ভব হয়নি। 
            অনুগ্রহ করে <code>config.php</code> ফাইলে আপনার ডাটাবেজ ক্রেডেনশিয়াল সঠিক আছে কিনা তা যাচাই করুন।
         </div>");
}

$prefix = DB_PREFIX;
$success = true;
$messages = [];

// 1. Settings Table
$sql_settings = "CREATE TABLE IF NOT EXISTS `{$prefix}settings` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `meta_key` VARCHAR(100) NOT NULL UNIQUE,
    `meta_value` TEXT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;";

// 2. Services Table
$sql_services = "CREATE TABLE IF NOT EXISTS `{$prefix}services` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `title` VARCHAR(255) NOT NULL,
    `description` TEXT NOT NULL,
    `image_url` VARCHAR(255) NULL,
    `slug` VARCHAR(100) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;";

// 3. Testimonials Table
$sql_testimonials = "CREATE TABLE IF NOT EXISTS `{$prefix}testimonials` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `client_name` VARCHAR(150) NOT NULL,
    `rating` INT NOT NULL DEFAULT 5,
    `review_text` TEXT NOT NULL,
    `source` VARCHAR(50) NOT NULL DEFAULT 'Google',
    `review_date` DATE NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;";

// 4. FAQs Table
$sql_faqs = "CREATE TABLE IF NOT EXISTS `{$prefix}faqs` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `question` TEXT NOT NULL,
    `answer` TEXT NOT NULL,
    `sort_order` INT NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;";

// 5. Blogs Table
$sql_blogs = "CREATE TABLE IF NOT EXISTS `{$prefix}blogs` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `title` VARCHAR(255) NOT NULL,
    `content` LONGTEXT NOT NULL,
    `excerpt` TEXT NULL,
    `thumbnail_url` VARCHAR(255) NULL,
    `is_featured` TINYINT(1) NOT NULL DEFAULT 0,
    `video_url` VARCHAR(255) NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;";

// Execute Table Creation
$tables = [
    'settings' => $sql_settings,
    'services' => $sql_services,
    'testimonials' => $sql_testimonials,
    'faqs' => $sql_faqs,
    'blogs' => $sql_blogs
];

foreach ($tables as $name => $sql) {
    if ($conn->query($sql) === TRUE) {
        $messages[] = "টেবিল তৈরি সফল: `{$prefix}{$name}`";
    } else {
        $messages[] = "টেবিল তৈরিতে সমস্যা: `{$prefix}{$name}` - " . $conn->error;
        $success = false;
    }
}

// Insert Default Settings (Seed Data) if tables were successfully created
if ($success) {
    // Insert default settings
    $default_settings = [
        'site_logo' => 'https://i.ibb.co/680pZ7y/star-elevator-logo.png', // Temporary default logo placeholder
        'site_phone' => '1-800-555-0199',
        'site_email' => 'info@starelevator.local',
        'site_address' => 'San Francisco Bay Area, CA'
    ];

    foreach ($default_settings as $key => $val) {
        $stmt = $conn->prepare("INSERT IGNORE INTO `{$prefix}settings` (meta_key, meta_value) VALUES (?, ?)");
        $stmt->bind_param("ss", $key, $val);
        $stmt->execute();
        $stmt->close();
    }

    // Insert dynamic services seed data (matching the image)
    $stmt = $conn->prepare("INSERT IGNORE INTO `{$prefix}services` (title, description, image_url, slug) VALUES (?, ?, ?, ?)");
    
    $services_data = [
        ['Elevator Maintenance', 'Preventative maintenance plans customized to ensure safety and code compliance.', 'https://images.unsplash.com/photo-1518156677180-95a2893f3e9f?w=600', 'elevator-maintenance'],
        ['Elevator Repair', 'Rapid response repair services by certified technicians to minimize downtime.', 'https://images.unsplash.com/photo-1504307651254-35680f356dfd?w=600', 'elevator-repair'],
        ['Modernization Services', 'Upgrade your aging elevator systems with the latest technology and safety features.', 'https://images.unsplash.com/photo-1486406146926-c627a92ad1ab?w=600', 'modernization'],
        ['Cab Renovation', 'Premium interior designs to modernize and refresh your elevator cab aesthetics.', 'https://images.unsplash.com/photo-1600585154340-be6161a56a0c?w=600', 'cab-renovation'],
        ['Residential Elevators', 'Tailored home mobility solutions for standard and custom residential designs.', 'https://images.unsplash.com/photo-1584622650111-993a426fbf0a?w=600', 'residential-elevators']
    ];

    foreach ($services_data as $srv) {
        $stmt->bind_param("ssss", $srv[0], $srv[1], $srv[2], $srv[3]);
        $stmt->execute();
    }
    $stmt->close();

    // Insert dynamic FAQs
    $stmt = $conn->prepare("INSERT IGNORE INTO `{$prefix}faqs` (question, answer, sort_order) VALUES (?, ?, ?)");
    $faqs_data = [
        ['How often should my elevator be serviced?', 'Generally, commercial elevators require monthly maintenance checks to ensure complete safety and code compliance.', 1],
        ['Are your technicians certified?', 'Yes, all our elevator technicians are fully licensed, certified, and receive regular industry training.', 2],
        ['What areas do you currently serve?', 'We serve the entire San Francisco Bay Area, East Bay, South Bay, and Sacramento regions.', 3]
    ];
    foreach ($faqs_data as $faq) {
        $stmt->bind_param("ssi", $faq[0], $faq[1], $faq[2]);
        $stmt->execute();
    }
    $stmt->close();
}

?>
<!DOCTYPE html>
<html lang="bn">
<head>
    <meta charset="UTF-8">
    <title>Database Setup</title>
    <style>
        body { font-family: sans-serif; background: #f3f4f6; padding: 40px; color: #333; }
        .container { max-width: 600px; background: #fff; padding: 30px; border-radius: 8px; box-shadow: 0 4px 6px rgba(0,0,0,0.1); margin: 0 auto; }
        h2 { color: #1e3a8a; border-bottom: 2px solid #e5e7eb; padding-bottom: 10px; }
        ul { list-style-type: none; padding: 0; }
        li { padding: 8px; margin-bottom: 5px; border-radius: 4px; background: #ecfdf5; border-left: 4px solid #10b981; }
        li.error { background: #fef2f2; border-left: 4px solid #ef4444; }
        .btn { display: inline-block; background: #2563eb; color: #fff; padding: 10px 20px; text-decoration: none; border-radius: 5px; margin-top: 20px; }
    </style>
</head>
<body>
    <div class="container">
        <h2>ডাটাবেজ ইনস্টলেশন স্ট্যাটাস</h2>
        <ul>
            <?php foreach ($messages as $msg): ?>
                <li class="<?php echo (strpos($msg, 'সমস্যা') !== false) ? 'error' : ''; ?>">
                    <?php echo htmlspecialchars($msg); ?>
                </li>
            <?php endforeach; ?>
        </ul>
        <?php if ($success): ?>
            <p style="color: green; font-weight: bold;">ইনস্টলেশন সফলভাবে সম্পন্ন হয়েছে! ডাটাবেজে প্রয়োজনীয় টেবিল ও ডেমো ডাটা যুক্ত করা হয়েছে।</p>
            <p style="font-size: 0.9em; color: #666;">নিরাপত্তার স্বার্থে, লাইভ সার্ভারে কাজ শেষে এই ফাইলটি (install.php) মুছে ফেলবেন বা রিনেম করে রাখবেন।</p>
        <?php else: ?>
            <p style="color: red; font-weight: bold;">ইনস্টলেশনে কিছু সমস্যা হয়েছে। অনুগ্রহ করে ডাটাবেজ চেক করুন।</p>
        <?php endif; ?>
    </div>
</body>
</html>