<?php
/**
 * Installation Script - Upgraded Database Schema
 * Creates base tables and adds new relational tables for unlimited gallery and specifications.
 */

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> ডাটাবেজ সংযোগ স্থাপন করা সম্ভব হয়নি।
         </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 / Products Table (Base)
$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. New Table: Product Multi-Images Gallery (যত খুশি ইমেজ আপলোড করার জন্য)
$sql_gallery = "CREATE TABLE IF NOT EXISTS `{$prefix}product_images` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `product_id` INT NOT NULL,
    `image_url` VARCHAR(255) NOT NULL,
    FOREIGN KEY (`product_id`) REFERENCES `{$prefix}services`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;";

// 4. New Table: Product Dynamic Specifications (মডেল, সাইজ, kVA, ক্যাপাসিটি ইত্যাদি যত খুশি স্পেক্স যুক্ত করার জন্য)
$sql_specs = "CREATE TABLE IF NOT EXISTS `{$prefix}product_specs` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `product_id` INT NOT NULL,
    `spec_key` VARCHAR(150) NOT NULL,
    `spec_value` VARCHAR(255) NOT NULL,
    FOREIGN KEY (`product_id`) REFERENCES `{$prefix}services`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;";

// 5. 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;";

// 6. 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;";

// 7. 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,
    'product_images' => $sql_gallery,
    'product_specs' => $sql_specs,
    '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;
    }
}
?>
<!DOCTYPE html>
<html lang="bn">
<head>
    <meta charset="UTF-8">
    <title>Database Upgrade</title>
    <style>
        body { font-family: sans-serif; background: #0f172a; padding: 40px; color: #cbd5e1; }
        .container { max-width: 600px; background: #1e293b; padding: 30px; border-radius: 12px; box-shadow: 0 4px 6px rgba(0,0,0,0.3); margin: 0 auto; border: 1px solid #334155; }
        h2 { color: #38bdf8; border-bottom: 2px solid #334155; padding-bottom: 10px; }
        ul { list-style-type: none; padding: 0; }
        li { padding: 10px; margin-bottom: 5px; border-radius: 6px; background: #0f172a; border-left: 4px solid #0ea5e9; font-size: 14px; }
        li.error { border-left: 4px solid #ef4444; color: #f87171; }
    </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: #4ade80; font-weight: bold;">অভিনন্দন! ডাইনামিক গ্যালারি ও স্পেসিফিকেশন টেবিল ডাটাবেজে সফলভাবে সংযুক্ত হয়েছে।</p>
        <?php else: ?>
            <p style="color: #f87171; font-weight: bold;">আপগ্রেডে কিছু সমস্যা হয়েছে। অনুগ্রহ করে ডাটাবেজ কানেকশন চেক করুন।</p>
        <?php endif; ?>
    </div>
</body>
</html>