<?php
/**
 * Installation Script - Phase 3 Database Upgrade
 * Creates: banners, marquee, categories, companies, chatbot_training, chat_history
 * Adds category_id & company_id to services table + seeds default data
 * Path: install-three.php
 */

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>ত্রুটি:</strong> ডাটাবেজ সংযোগ স্থাপন করা সম্ভব হয়নি।</div>");
}

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

function run_sql($conn, $sql, $label, &$messages, &$success) {
    if ($conn->query($sql) === TRUE) {
        $messages[] = "✓ {$label}";
        return true;
    }
    $messages[] = "✗ {$label} — " . $conn->error;
    $success = false;
    return false;
}

function column_exists($conn, $table, $column) {
    $result = $conn->query("SHOW COLUMNS FROM `{$table}` LIKE '{$column}'");
    return ($result && $result->num_rows > 0);
}

// ─── ১. নতুন টেবিল তৈরি ───────────────────────────────────────────

$sql_banners = "CREATE TABLE IF NOT EXISTS `{$prefix}banners` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `title` VARCHAR(255) NULL,
    `subtitle` TEXT NULL,
    `button_text` VARCHAR(100) NULL,
    `button_link` VARCHAR(255) NULL,
    `image_url` VARCHAR(500) NOT NULL,
    `image_source` ENUM('upload','url') NOT NULL DEFAULT 'url',
    `device_type` ENUM('desktop','mobile','both') NOT NULL DEFAULT 'both',
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `sort_order` INT NOT NULL DEFAULT 0,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;";

$sql_marquee = "CREATE TABLE IF NOT EXISTS `{$prefix}marquee` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `text` TEXT NOT NULL,
    `speed` ENUM('slow','normal','fast') NOT NULL DEFAULT 'normal',
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;";

$sql_categories = "CREATE TABLE IF NOT EXISTS `{$prefix}categories` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(150) NOT NULL,
    `slug` VARCHAR(100) NOT NULL UNIQUE,
    `icon` VARCHAR(50) NULL,
    `sort_order` INT NOT NULL DEFAULT 0,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;";

$sql_companies = "CREATE TABLE IF NOT EXISTS `{$prefix}companies` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `category_id` INT NOT NULL,
    `name` VARCHAR(150) NOT NULL,
    `slug` VARCHAR(100) NOT NULL,
    `logo_url` VARCHAR(500) NULL,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    UNIQUE KEY `cat_slug` (`category_id`, `slug`),
    FOREIGN KEY (`category_id`) REFERENCES `{$prefix}categories`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;";

$sql_chatbot_training = "CREATE TABLE IF NOT EXISTS `{$prefix}chatbot_training` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `title` VARCHAR(150) NOT NULL,
    `question` TEXT NOT NULL,
    `answer` TEXT NOT NULL,
    `category_tag` VARCHAR(100) NULL,
    `sort_order` INT NOT NULL DEFAULT 0,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;";

$sql_chat_history = "CREATE TABLE IF NOT EXISTS `{$prefix}chat_history` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_ip` VARCHAR(45) NULL,
    `user_message` TEXT NOT NULL,
    `bot_response` TEXT NOT NULL,
    `session_id` VARCHAR(64) NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;";

$tables = [
    'banners'           => $sql_banners,
    'marquee'             => $sql_marquee,
    'categories'          => $sql_categories,
    'companies'           => $sql_companies,
    'chatbot_training'    => $sql_chatbot_training,
    'chat_history'        => $sql_chat_history,
];

foreach ($tables as $name => $sql) {
    run_sql($conn, $sql, "টেবিল: `{$prefix}{$name}`", $messages, $success);
}

// ─── ২. services টেবিলে নতুন কলাম যোগ ─────────────────────────────

$services_table = "{$prefix}services";

if (!column_exists($conn, $services_table, 'category_id')) {
    run_sql($conn, "ALTER TABLE `{$services_table}` ADD COLUMN `category_id` INT NULL AFTER `slug`", "কলাম যোগ: category_id", $messages, $success);
}
if (!column_exists($conn, $services_table, 'company_id')) {
    run_sql($conn, "ALTER TABLE `{$services_table}` ADD COLUMN `company_id` INT NULL AFTER `category_id`", "কলাম যোগ: company_id", $messages, $success);
}

// ─── ৩. ডিফল্ট ক্যাটাগরি সিড ───────────────────────────────────────

$cat_check = $conn->query("SELECT COUNT(*) as cnt FROM `{$prefix}categories`");
$cat_count = ($cat_check) ? (int)$cat_check->fetch_assoc()['cnt'] : 0;

if ($cat_count === 0) {
    $categories = [
        ['Passenger Elevator',  'passenger-elevator',   'fa-elevator',        1],
        ['Residential Elevator','residential-elevator', 'fa-house',           2],
        ['Car Elevator',        'car-elevator',         'fa-car',             3],
        ['Car Parking Lift',    'car-parking-lift',     'fa-square-parking',  4],
        ['Diesel Generator',    'diesel-generator',     'fa-bolt',            5],
        ['Power Transformer',   'power-transformer',    'fa-plug',            6],
        ['Solar System',        'solar-system',         'fa-solar-panel',     7],
    ];

    $stmt_cat = $conn->prepare("INSERT INTO `{$prefix}categories` (name, slug, icon, sort_order) VALUES (?, ?, ?, ?)");
    foreach ($categories as $cat) {
        $stmt_cat->bind_param("sssi", $cat[0], $cat[1], $cat[2], $cat[3]);
        $stmt_cat->execute();
    }
    $stmt_cat->close();
    $messages[] = "✓ ৭টি ক্যাটাগরি সিড করা হয়েছে";
}

// ─── ৪. ডিফল্ট কোম্পানি সিড ─────────────────────────────────────────

$comp_check = $conn->query("SELECT COUNT(*) as cnt FROM `{$prefix}companies`");
$comp_count = ($comp_check) ? (int)$comp_check->fetch_assoc()['cnt'] : 0;

if ($comp_count === 0) {
    $cat_map = [];
    $cat_res = $conn->query("SELECT id, slug FROM `{$prefix}categories`");
    while ($row = $cat_res->fetch_assoc()) {
        $cat_map[$row['slug']] = (int)$row['id'];
    }

    $companies_by_cat = [
        'passenger-elevator' => ['Otis', 'KONE', 'Mitsubishi', 'Schindler', 'Hitachi', 'Hyundai'],
        'residential-elevator' => ['Otis Homelift', 'KONE Home', 'Mitsubishi Home', 'ThyssenKrupp Home', 'Stiltz', 'PVE'],
        'car-elevator' => ['Otis', 'KONE', 'Mitsubishi', '0scar', 'Canny', 'SANYO'],
        'car-parking-lift' => ['KLAUS', 'Wohr', 'KOMPAN', 'KLEEMANN', 'CARMEC', 'Rotary'],
        'diesel-generator' => ['Perkins', 'Cummins', 'Caterpillar', 'MTU', 'Doosan', 'Yanmar'],
        'power-transformer' => ['ABB', 'Siemens', 'Schneider', 'GE', 'Hyundai Electric', 'Toshiba'],
        'solar-system' => ['LONGi', 'Jinko Solar', 'Trina Solar', 'Canadian Solar', 'JA Solar', 'Sungrow'],
    ];

    $stmt_comp = $conn->prepare("INSERT INTO `{$prefix}companies` (category_id, name, slug) VALUES (?, ?, ?)");
    foreach ($companies_by_cat as $cat_slug => $brands) {
        if (!isset($cat_map[$cat_slug])) continue;
        $cat_id = $cat_map[$cat_slug];
        foreach ($brands as $brand) {
            $slug = strtolower(preg_replace('/[^a-z0-9]+/i', '-', $brand));
            $stmt_comp->bind_param("iss", $cat_id, $brand, $slug);
            $stmt_comp->execute();
        }
    }
    $stmt_comp->close();
    $messages[] = "✓ সব ক্যাটাগরির কোম্পানি সিড করা হয়েছে";
}

// ─── ৫. ডিফল্ট প্রডাক্ট সিড (প্রতি ক্যাটাগরিতে ৫-৬টি) ───────────────

$prod_check = $conn->query("SELECT COUNT(*) as cnt FROM `{$prefix}services` WHERE category_id IS NOT NULL");
$prod_count = ($prod_check) ? (int)$prod_check->fetch_assoc()['cnt'] : 0;

if ($prod_count === 0) {
    $product_images = [
        'passenger-elevator'   => 'https://images.unsplash.com/photo-1518156677180-95a2893f3e9f?w=600',
        'residential-elevator' => 'https://images.unsplash.com/photo-1600585154340-be6161a56a0c?w=600',
        'car-elevator'         => 'https://images.unsplash.com/photo-1584622650111-993a426fbf0a?w=600',
        'car-parking-lift'     => 'https://images.unsplash.com/photo-1504307651254-35680f356dfd?w=600',
        'diesel-generator'     => 'https://images.unsplash.com/photo-1581092160607-ee22621dd758?w=600',
        'power-transformer'    => 'https://images.unsplash.com/photo-1486406146926-c627a92ad1ab?w=600',
        'solar-system'         => 'https://images.unsplash.com/photo-1509391366360-2e959784a276?w=600',
    ];

    $product_templates = [
        'passenger-elevator' => [
            'Gearless PM Passenger Lift 8-Person', 'High-Speed Passenger Elevator 13-Person',
            'Machine Room Less (MRL) Passenger Lift', 'Panoramic Glass Passenger Elevator',
            'Hospital Stretcher Passenger Lift', 'Freight-Passenger Combined Lift'
        ],
        'residential-elevator' => [
            'Home Hydraulic Lift 2-Stop', 'Residential MRL Home Elevator',
            'Vacuum Home Elevator PVE-30', 'Compact Residential Lift 3-Person',
            'Luxury Home Elevator with Glass Cabin', 'Outdoor Weatherproof Home Lift'
        ],
        'car-elevator' => [
            'Hydraulic Car Elevator 2700 Kg', 'Traction Car Lift 3500 Kg',
            'Underground Car Elevator System', 'Automated Car Lift 2-Level',
            'SUV Car Elevator 3000 Kg Capacity', 'Commercial Car Elevator 4000 Kg'
        ],
        'car-parking-lift' => [
            '2-Level Hydraulic Car Stacker', '3-Level Automated Parking System',
            'Puzzle Parking Lift 6-Car', 'Rotary Car Parking Platform',
            'Underground Car Parking Lift', 'Tower Parking System 10-Car'
        ],
        'diesel-generator' => [
            'Soundproof Diesel Generator 100 kVA', 'Industrial Generator 250 kVA Perkins',
            'Standby Generator 500 kVA Cummins', 'Containerized Generator 750 kVA',
            'Silent Generator 150 kVA Doosan', 'Prime Power Generator 1000 kVA'
        ],
        'power-transformer' => [
            '11kV/0.415kV Distribution Transformer 500 kVA', 'Oil Immersed Transformer 1000 kVA',
            'Dry Type Transformer 250 kVA', 'Pad Mounted Transformer 315 kVA',
            'HT Sub-station Transformer 2000 kVA', 'Compact Substation Transformer 1600 kVA'
        ],
        'solar-system' => [
            'On-Grid Solar System 10 kW', 'Hybrid Solar System 20 kW with Battery',
            'Off-Grid Solar System 5 kW', 'Commercial Rooftop Solar 50 kW',
            'Industrial Solar Plant 100 kW', 'Residential Solar Package 3 kW'
        ],
    ];

    $comp_res = $conn->query("SELECT c.id as comp_id, c.name as comp_name, cat.slug as cat_slug, cat.id as cat_id
        FROM `{$prefix}companies` c
        JOIN `{$prefix}categories` cat ON c.category_id = cat.id
        ORDER BY cat.sort_order, c.id");

    $companies_list = [];
    while ($row = $comp_res->fetch_assoc()) {
        $companies_list[$row['cat_slug']][] = $row;
    }

    $stmt_prod = $conn->prepare("INSERT INTO `{$prefix}services` (title, description, image_url, slug, category_id, company_id) VALUES (?, ?, ?, ?, ?, ?)");

    foreach ($product_templates as $cat_slug => $titles) {
        if (!isset($companies_list[$cat_slug])) continue;
        $comps = $companies_list[$cat_slug];
        $img = $product_images[$cat_slug] ?? 'https://images.unsplash.com/photo-1581092160607-ee22621dd758?w=600';

        foreach ($titles as $i => $title) {
            $comp = $comps[$i % count($comps)];
            $desc = "Premium ISO-certified {$title} by {$comp['comp_name']}. Engineered for Bangladesh market with full installation, warranty and 24/7 support.";
            $slug = $cat_slug . '-' . strtolower(preg_replace('/[^a-z0-9]+/i', '-', $comp['comp_name'])) . '-' . ($i + 1);
            $stmt_prod->bind_param("ssssii", $title, $desc, $img, $slug, $comp['cat_id'], $comp['comp_id']);
            $stmt_prod->execute();
        }
    }
    $stmt_prod->close();
    $messages[] = "✓ সব ক্যাটাগরিতে ৫-৬টি প্রডাক্ট সিড করা হয়েছে";
}

// ─── ৬. ডিফল্ট মারকিউ টেক্সট ────────────────────────────────────────

$mq_check = $conn->query("SELECT COUNT(*) as cnt FROM `{$prefix}marquee`");
$mq_count = ($mq_check) ? (int)$mq_check->fetch_assoc()['cnt'] : 0;

if ($mq_count === 0) {
    $default_marquee = "★ ISO Certified Engineering Solutions ★ 24/7 Emergency Support ★ Passenger Elevator | Diesel Generator | Power Transformer | Solar System ★ Call Now: +88 01711-701747 ★";
    $stmt_mq = $conn->prepare("INSERT INTO `{$prefix}marquee` (text, speed, is_active) VALUES (?, 'normal', 1)");
    $stmt_mq->bind_param("s", $default_marquee);
    $stmt_mq->execute();
    $stmt_mq->close();
    $messages[] = "✓ ডিফল্ট মারকিউ টেক্সট সেট করা হয়েছে";
}

// ─── ৭. ডিফল্ট চ্যাটবট ট্রেইনিং ─────────────────────────────────────

$train_check = $conn->query("SELECT COUNT(*) as cnt FROM `{$prefix}chatbot_training`");
$train_count = ($train_check) ? (int)$train_check->fetch_assoc()['cnt'] : 0;

if ($train_count === 0) {
    $training_data = [
        ['প্রডাক্ট সম্পর্কিত প্রশ্ন', 'প্যাসেঞ্জার লিফ্টের দাম কত?', 'প্যাসেঞ্জার লিফ্টের দাম ক্যাপাসিটি, স্টপেজ ও ব্র্যান্ডের উপর নির্ভর করে। বিস্তারিত জানতে আমাদের হটলাইনে কল করুন।', 'products'],
        ['প্রডাক্ট সম্পর্কিত প্রশ্ন', 'জেনারেটর কত kVA লাগবে?', 'আপনার মোট লোড ক্যালকুলেশন করে জেনারেটর সাইজ নির্ধারণ করা হয়। সাধারণত ১০০-৫০০ kVA বাণিজ্যিক ভবনে ব্যবহৃত হয়।', 'products'],
        ['সার্ভিস সম্পর্কিত প্রশ্ন', 'ইনস্টলেশন কত দিন লাগে?', 'স্ট্যান্ডার্ড প্যাসেঞ্জার লিফ্ট ইনস্টলেশনে ৪-৮ সপ্তাহ সময় লাগে। প্রকল্পের স্কোপ অনুযায়ী সময় পরিবর্তন হতে পারে।', 'services'],
        ['যোগাযোগ', 'অফিসের ঠিকানা কোথায়?', 'আমাদের অফিস: House No. 398, (3rd Floor), Road No. 29, New DOHS Mohakhali, Dhaka-1206', 'contact'],
        ['সোলার সম্পর্কিত প্রশ্ন', 'সোলার সিস্টেমে কত টাকা লাগে?', 'সোলার সিস্টেমের খরচ kW ক্ষমতা ও সিস্টেম টাইপ (On-Grid/Hybrid) অনুযায়ী ভিন্ন। ৫ kW থেকে শুরু।', 'products'],
    ];

    $stmt_train = $conn->prepare("INSERT INTO `{$prefix}chatbot_training` (title, question, answer, category_tag, sort_order) VALUES (?, ?, ?, ?, ?)");
    foreach ($training_data as $i => $t) {
        $order = $i + 1;
        $stmt_train->bind_param("ssssi", $t[0], $t[1], $t[2], $t[3], $order);
        $stmt_train->execute();
    }
    $stmt_train->close();
    $messages[] = "✓ ডিফল্ট চ্যাটবট ট্রেইনিং ডেটা সেট করা হয়েছে";
}
?>
<!DOCTYPE html>
<html lang="bn">
<head>
    <meta charset="UTF-8">
    <title>Install Three — Database Upgrade</title>
    <style>
        body { font-family: sans-serif; background: #0f172a; padding: 40px; color: #cbd5e1; }
        .container { max-width: 650px; background: #1e293b; padding: 30px; border-radius: 12px; margin: 0 auto; border: 1px solid #334155; }
        h2 { color: #38bdf8; border-bottom: 2px solid #334155; padding-bottom: 10px; }
        ul { list-style: none; padding: 0; }
        li { padding: 8px 12px; margin-bottom: 4px; border-radius: 6px; background: #0f172a; font-size: 13px; border-left: 3px solid #0ea5e9; }
        li.err { border-left-color: #ef4444; color: #f87171; }
        .ok { color: #4ade80; font-weight: bold; margin-top: 16px; }
        .fail { color: #f87171; font-weight: bold; margin-top: 16px; }
    </style>
</head>
<body>
    <div class="container">
        <h2>Phase 3 — Database Upgrade Status</h2>
        <ul>
            <?php foreach ($messages as $msg): ?>
                <li class="<?php echo (strpos($msg, '✗') !== false) ? 'err' : ''; ?>">
                    <?php echo htmlspecialchars($msg); ?>
                </li>
            <?php endforeach; ?>
        </ul>
        <?php if ($success): ?>
            <p class="ok">সফল! install-three.php সম্পন্ন। এখন admin panel ব্যবহার করতে পারবেন।</p>
        <?php else: ?>
            <p class="fail">কিছু সমস্যা হয়েছে। উপরের ত্রুটি মেসেজ দেখুন।</p>
        <?php endif; ?>
    </div>
</body>
</html>
