<?php
/**
 * Installation Script - Phase 7
 * 1) Deletes old demo products/categories/companies
 * 2) Seeds Category → Sub → Brand → Real client products + specs
 * Path: install-seven.php  (+ install-seven-products.php same folder)
 */
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;
$messages = [];
$success = true;

function col_exists_seven($conn, $table, $col) {
    $table = $conn->real_escape_string($table);
    $col   = $conn->real_escape_string($col);
    $r = $conn->query("SHOW COLUMNS FROM `{$table}` LIKE '{$col}'");
    return $r && $r->num_rows > 0;
}

// 1) Subcategories table
$sql_sub = "CREATE TABLE IF NOT EXISTS `{$prefix}subcategories` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `category_id` INT NOT NULL,
    `name` VARCHAR(150) NOT NULL,
    `slug` VARCHAR(100) NOT NULL,
    `sort_order` INT NOT NULL DEFAULT 0,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    UNIQUE KEY `cat_sub_slug` (`category_id`, `slug`),
    FOREIGN KEY (`category_id`) REFERENCES `{$prefix}categories`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;";

if ($conn->query($sql_sub) === TRUE) {
    $messages[] = "✓ টেবিল: `{$prefix}subcategories`";
} else {
    $messages[] = "✗ Subcategories: " . $conn->error;
    $success = false;
}

// 2) Columns
if (!col_exists_seven($conn, "{$prefix}companies", 'subcategory_id')) {
    if ($conn->query("ALTER TABLE `{$prefix}companies` ADD `subcategory_id` INT NULL AFTER `category_id`")) {
        $messages[] = "✓ companies.subcategory_id যোগ";
    } else {
        $messages[] = "✗ companies column: " . $conn->error;
        $success = false;
    }
} else {
    $messages[] = "○ companies.subcategory_id আগে থেকেই আছে";
}

if (!col_exists_seven($conn, "{$prefix}services", 'subcategory_id')) {
    if ($conn->query("ALTER TABLE `{$prefix}services` ADD `subcategory_id` INT NULL AFTER `category_id`")) {
        $messages[] = "✓ services.subcategory_id যোগ";
    } else {
        $messages[] = "✗ services column: " . $conn->error;
        $success = false;
    }
} else {
    $messages[] = "○ services.subcategory_id আগে থেকেই আছে";
}

// 2.5) পুরনো ডেমো প্রডাক্ট / ক্যাটালগ মুছে ফেলা (রিয়েল ক্লায়েন্ট ডেটার আগে)
$conn->query("SET FOREIGN_KEY_CHECKS = 0");
$old_count = 0;
$cnt_res = $conn->query("SELECT COUNT(*) AS c FROM `{$prefix}services`");
if ($cnt_res && $row = $cnt_res->fetch_assoc()) $old_count = (int)$row['c'];

$conn->query("DELETE FROM `{$prefix}product_specs`");
$conn->query("DELETE FROM `{$prefix}product_images`");
$conn->query("DELETE FROM `{$prefix}services`");
$conn->query("DELETE FROM `{$prefix}companies`");
$conn->query("DELETE FROM `{$prefix}subcategories`");
$conn->query("DELETE FROM `{$prefix}categories`");

$conn->query("ALTER TABLE `{$prefix}services` AUTO_INCREMENT = 1");
$conn->query("ALTER TABLE `{$prefix}companies` AUTO_INCREMENT = 1");
$conn->query("ALTER TABLE `{$prefix}subcategories` AUTO_INCREMENT = 1");
$conn->query("ALTER TABLE `{$prefix}categories` AUTO_INCREMENT = 1");
$conn->query("SET FOREIGN_KEY_CHECKS = 1");
$messages[] = "✓ পুরনো ডেমো ডেটা মুছে ফেলা হয়েছে (প্রডাক্ট: {$old_count}টি) — এখন রিয়েল ক্যাটালগ লোড হবে";

// 3) Seed hierarchy
$tree = [
    'generator-sets' => [
        'name' => 'Generators',
        'icon' => 'fa-bolt',
        'sort' => 1,
        'subs' => [
            'diesel-generators' => [
                'name' => 'Diesel Generators',
                'brands' => ['Welland Power', 'FG Wilson', 'Denyo', 'Cummins', 'Generson', 'GB Power', 'G.Power', 'Aleo Power'],
            ],
            'gas-generators' => [
                'name' => 'Gas Generators',
                'brands' => ['Genesal Energy', 'Aleo Power'],
            ],
            'marine-generators' => [
                'name' => 'Marine Generators',
                'brands' => ['G.Power', 'Genesal Energy', 'Aleo Power'],
            ],
            'hybrid-microgrid' => [
                'name' => 'Hybrid & Microgrid',
                'brands' => ['Genesal Energy', 'Aleo Power'],
            ],
        ],
    ],
    'elevators' => [
        'name' => 'Elevators',
        'icon' => 'fa-elevator',
        'sort' => 2,
        'subs' => [
            'passenger-elevators' => [
                'name' => 'Passenger Elevators',
                'brands' => ['BLT Brilliant'],
            ],
            'panoramic-elevators' => [
                'name' => 'Panoramic Elevators',
                'brands' => ['BLT Brilliant'],
            ],
            'freight-elevators' => [
                'name' => 'Freight Elevators',
                'brands' => ['BLT Brilliant'],
            ],
            'hospital-elevators' => [
                'name' => 'Hospital Bed Elevators',
                'brands' => ['BLT Brilliant'],
            ],
            'home-elevators' => [
                'name' => 'Home / Residential Lifts',
                'brands' => ['Fuji Elevators'],
            ],
        ],
    ],
    'lighting-towers' => [
        'name' => 'Lighting Towers',
        'icon' => 'fa-lightbulb',
        'sort' => 3,
        'subs' => [
            'mobile-lighting-towers' => [
                'name' => 'Mobile Lighting Towers',
                'brands' => ['GB Power', 'Genesal Energy', 'G.Power'],
            ],
        ],
    ],
    'diesel-pump-sets' => [
        'name' => 'Diesel Pump Sets',
        'icon' => 'fa-faucet',
        'sort' => 4,
        'subs' => [
            'diesel-water-pumps' => [
                'name' => 'Diesel Water Pump Sets',
                'brands' => ['GB Power'],
            ],
        ],
    ],
];

$cat_ids = [];
$sub_ids = [];
$brand_ids = []; // key: sub_slug|brand_slug => id

foreach ($tree as $cat_slug => $cat) {
    $stmt = $conn->prepare("SELECT id FROM `{$prefix}categories` WHERE slug = ? LIMIT 1");
    $stmt->bind_param("s", $cat_slug);
    $stmt->execute();
    $row = $stmt->get_result()->fetch_assoc();
    $stmt->close();

    if ($row) {
        $cat_ids[$cat_slug] = (int)$row['id'];
        $conn->query("UPDATE `{$prefix}categories` SET name='" . $conn->real_escape_string($cat['name']) . "', icon='" . $conn->real_escape_string($cat['icon']) . "', sort_order=" . (int)$cat['sort'] . ", is_active=1 WHERE id=" . (int)$row['id']);
    } else {
        $stmt = $conn->prepare("INSERT INTO `{$prefix}categories` (name, slug, icon, sort_order, is_active) VALUES (?,?,?,?,1)");
        $stmt->bind_param("sssi", $cat['name'], $cat_slug, $cat['icon'], $cat['sort']);
        $stmt->execute();
        $cat_ids[$cat_slug] = (int)$conn->insert_id;
        $stmt->close();
        $messages[] = "✓ Category: {$cat['name']}";
    }

    foreach ($cat['subs'] as $sub_slug => $sub) {
        $cid = $cat_ids[$cat_slug];
        $stmt = $conn->prepare("SELECT id FROM `{$prefix}subcategories` WHERE category_id = ? AND slug = ? LIMIT 1");
        $stmt->bind_param("is", $cid, $sub_slug);
        $stmt->execute();
        $srow = $stmt->get_result()->fetch_assoc();
        $stmt->close();

        if ($srow) {
            $sub_ids[$sub_slug] = (int)$srow['id'];
        } else {
            $stmt = $conn->prepare("INSERT INTO `{$prefix}subcategories` (category_id, name, slug, sort_order, is_active) VALUES (?,?,?,0,1)");
            $stmt->bind_param("iss", $cid, $sub['name'], $sub_slug);
            $stmt->execute();
            $sub_ids[$sub_slug] = (int)$conn->insert_id;
            $stmt->close();
            $messages[] = "✓ Sub: {$sub['name']}";
        }

        $sort_b = 0;
        foreach ($sub['brands'] as $brand_name) {
            $brand_slug = strtolower(preg_replace('/[^a-z0-9]+/i', '-', $brand_name));
            $brand_slug = trim($brand_slug, '-') . '-' . $sub_slug; // unique per sub
            $sid = $sub_ids[$sub_slug];
            $key = $sub_slug . '|' . $brand_slug;

            $stmt = $conn->prepare("SELECT id FROM `{$prefix}companies` WHERE slug = ? LIMIT 1");
            $stmt->bind_param("s", $brand_slug);
            $stmt->execute();
            $brow = $stmt->get_result()->fetch_assoc();
            $stmt->close();

            if ($brow) {
                $brand_ids[$key] = (int)$brow['id'];
                $conn->query("UPDATE `{$prefix}companies` SET category_id={$cid}, subcategory_id={$sid}, name='" . $conn->real_escape_string($brand_name) . "', is_active=1 WHERE id=" . (int)$brow['id']);
            } else {
                $logo = '';
                $stmt = $conn->prepare("INSERT INTO `{$prefix}companies` (category_id, subcategory_id, name, slug, logo_url, is_active) VALUES (?,?,?,?,?,1)");
                $stmt->bind_param("iisss", $cid, $sid, $brand_name, $brand_slug, $logo);
                if ($stmt->execute()) {
                    $brand_ids[$key] = (int)$conn->insert_id;
                    $messages[] = "✓ Brand: {$brand_name} ({$sub['name']})";
                }
                $stmt->close();
            }
            $sort_b++;
        }
    }
}

// Deactivate unrelated old categories (keep the 4 active)
$keep = "'" . implode("','", array_keys($tree)) . "'";
$conn->query("UPDATE `{$prefix}categories` SET is_active = 0 WHERE slug NOT IN ({$keep})");
$messages[] = "✓ পুরনো অপ্রাসঙ্গিক ক্যাটাগরি নিষ্ক্রিয় করা হয়েছে (৪টি সক্রিয়)";

// 4) Full client product catalog (models + specs)
$products = require __DIR__ . '/install-seven-products.php';
if (!is_array($products)) $products = [];

$stmt_p = $conn->prepare("INSERT INTO `{$prefix}services` (title, description, image_url, slug, category_id, subcategory_id, company_id) VALUES (?,?,?,?,?,?,?)");
$stmt_sp = $conn->prepare("INSERT INTO `{$prefix}product_specs` (product_id, spec_key, spec_value) VALUES (?,?,?)");
$added = 0;
$skipped = 0;

foreach ($products as $s) {
    $cat_slug   = $s['cat'];
    $sub_slug   = $s['sub'];
    $brand_name = $s['brand'];
    $title      = $s['title'];
    $desc       = $s['desc'];
    $img        = $s['img'];
    $specs      = $s['specs'] ?? [];

    $brand_slug = strtolower(preg_replace('/[^a-z0-9]+/i', '-', $brand_name));
    $brand_slug = trim($brand_slug, '-') . '-' . $sub_slug;
    $key = $sub_slug . '|' . $brand_slug;
    if (!isset($cat_ids[$cat_slug], $sub_ids[$sub_slug], $brand_ids[$key])) {
        $messages[] = "⚠ Skip (brand missing): {$title}";
        continue;
    }

    $slug = strtolower(preg_replace('/[^a-z0-9]+/i', '-', $title));
    $slug = trim($slug, '-');
    $chk = $conn->prepare("SELECT id FROM `{$prefix}services` WHERE slug = ? LIMIT 1");
    $chk->bind_param("s", $slug);
    $chk->execute();
    if ($chk->get_result()->fetch_assoc()) {
        $chk->close();
        $skipped++;
        continue;
    }
    $chk->close();

    $cid = $cat_ids[$cat_slug];
    $sid = $sub_ids[$sub_slug];
    $bid = $brand_ids[$key];
    $stmt_p->bind_param("ssssiii", $title, $desc, $img, $slug, $cid, $sid, $bid);
    if ($stmt_p->execute()) {
        $pid = (int)$conn->insert_id;
        $base_specs = [
            'Brand' => $brand_name,
            'Category' => $tree[$cat_slug]['name'],
            'Sub-category' => $tree[$cat_slug]['subs'][$sub_slug]['name'],
            'Support' => '24/7 Installation & Service (Bangladesh)',
        ];
        $all_specs = array_merge($base_specs, $specs);
        foreach ($all_specs as $k => $v) {
            $k = (string)$k;
            $v = (string)$v;
            $stmt_sp->bind_param("iss", $pid, $k, $v);
            $stmt_sp->execute();
        }
        $added++;
    }
}
$stmt_p->close();
$stmt_sp->close();
$messages[] = "✓ Client products যোগ: {$added}টি (skip/existing: {$skipped})";
?>
<!DOCTYPE html>
<html lang="bn">
<head><meta charset="UTF-8"><title>Install Seven</title>
<style>body{font-family:sans-serif;background:#0f172a;padding:40px;color:#cbd5e1}.box{max-width:640px;margin:0 auto;background:#1e293b;padding:30px;border-radius:12px;border:1px solid #334155}h2{color:#38bdf8}li{padding:6px;font-size:13px}.ok{color:#4ade80;font-weight:bold;margin-top:16px}</style>
</head>
<body><div class="box">
<h2>Phase 7 — Category → Sub → Brand → Full Products</h2>
<ul><?php foreach ($messages as $m) echo '<li>'.htmlspecialchars($m).'</li>'; ?></ul>
<?php if ($success): ?>
<p class="ok">সফল! পুরনো ডেমো মুছে রিয়েল প্রডাক্ট ক্যাটালগ লোড হয়েছে।</p>
<p style="font-size:12px;color:#94a3b8">একই ফোল্ডারে রাখুন: <code>install-seven.php</code> + <code>install-seven-products.php</code></p>
<?php endif; ?>
</div></body></html>
