<?php
/**
 * Installation Script - Phase 6
 * Auto-seed product specifications for ALL products (run once)
 * Path: install-six.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;
$messages = [];
$success  = true;
$seeded   = 0;
$skipped  = 0;
$errors   = 0;

// ─── product_specs টেবিল নিশ্চিত করুন ───────────────────────────────
$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;";

if ($conn->query($sql_specs) === TRUE) {
    $messages[] = "✓ টেবিল নিশ্চিত: `{$prefix}product_specs`";
} else {
    $messages[] = "✗ টেবিল ত্রুটি: " . $conn->error;
    $success = false;
}

// ─── হেল্পার: টাইটেল থেকে সংখ্যা বের করা ─────────────────────────────
function spec_first_match($title, $patterns) {
    foreach ($patterns as $pattern => $format) {
        if (preg_match($pattern, $title, $m)) {
            if (is_callable($format)) return $format($m);
            return sprintf($format, ...array_slice($m, 1));
        }
    }
    return null;
}

function spec_detect_category($title, $slug, $cat_slug) {
    if (!empty($cat_slug)) return $cat_slug;

    $hay = strtolower($title . ' ' . $slug);
    $map = [
        'passenger-elevator'   => ['passenger', 'mrl', 'stretcher', 'freight-passenger', 'gearless pm'],
        'residential-elevator' => ['residential', 'home lift', 'home elevator', 'vacuum home', 'pve'],
        'car-elevator'         => ['car elevator', 'car lift', 'suv car'],
        'car-parking-lift'     => ['parking', 'stacker', 'puzzle parking', 'rotary car', 'tower parking'],
        'diesel-generator'     => ['generator', 'kva', 'diesel', 'cummins', 'perkins', 'doosan'],
        'power-transformer'    => ['transformer', 'substation', '11kv', 'dry type transformer'],
        'solar-system'         => ['solar', 'kw', 'on-grid', 'off-grid', 'hybrid solar', 'rooftop solar'],
    ];
    foreach ($map as $slug_key => $keywords) {
        foreach ($keywords as $kw) {
            if (strpos($hay, $kw) !== false) return $slug_key;
        }
    }
    return 'general';
}

// ─── ক্যাটাগরি অনুযায়ী স্পেসিফিকেশন জেনারেট ───────────────────────
function generate_product_specs($title, $cat_slug, $company_name) {
    $brand = $company_name ?: 'STAR ELEVATOR BD';
    $t     = $title;
    $specs = [];

    switch ($cat_slug) {

        case 'passenger-elevator':
            $persons = spec_first_match($t, [
                '/(\d+)\s*[-]?\s*Person/i' => '%d-Person',
            ]) ?: '8-Person';
            $kg = spec_first_match($t, [
                '/(\d+)\s*Kg/i' => '%d Kg',
            ]);
            if (!$kg) {
                $num = (int) filter_var($persons, FILTER_SANITIZE_NUMBER_INT);
                $kg  = ($num > 0 ? ($num * 75) : 630) . ' Kg';
            }
            $speed = (stripos($t, 'High-Speed') !== false || stripos($t, 'High Speed') !== false) ? '1.75 m/s' : '1.0 m/s';
            $drive = (stripos($t, 'Gearless') !== false || stripos($t, 'PM') !== false) ? 'Gearless PM Synchronous' : 'Geared Traction';
            $type  = 'Passenger Elevator';
            if (stripos($t, 'MRL') !== false || stripos($t, 'Machine Room Less') !== false) $type .= ' (MRL)';
            if (stripos($t, 'Hospital') !== false || stripos($t, 'Stretcher') !== false) $type = 'Hospital / Stretcher Elevator';
            if (stripos($t, 'Panoramic') !== false || stripos($t, 'Glass') !== false) $type = 'Panoramic Glass Passenger Elevator';
            if (stripos($t, 'Freight') !== false) $type = 'Freight-Passenger Combined Lift';

            $specs = [
                'Equipment Type'  => $type,
                'Brand'           => $brand,
                'Capacity'        => "{$persons} ({$kg})",
                'Rated Speed'     => $speed,
                'Drive System'    => $drive,
                'Control System'  => 'VVVF Dual 32-bit Microprocessor',
                'Door Type'       => 'Center Opening Automatic Sliding',
                'Safety Features' => 'ARD, Overload Sensor, Multi-beam Door Protection',
                'Power Supply'    => '3-Phase 380V / 415V',
                'Warranty'        => '1 Year Parts & Labour',
                'Installation'    => 'Full supply, install & commissioning (Dhaka & nationwide)',
            ];
            break;

        case 'residential-elevator':
            $persons = spec_first_match($t, [
                '/(\d+)\s*[-]?\s*Person/i' => '%d-Person',
            ]) ?: '3-Person';
            $stops = spec_first_match($t, [
                '/(\d+)\s*[-]?\s*Stop/i' => '%d Stops',
            ]) ?: '2-3 Stops';
            $drive = 'Hydraulic Power Unit';
            if (stripos($t, 'MRL') !== false) $drive = 'Machine Room Less (MRL) Traction';
            if (stripos($t, 'Vacuum') !== false || stripos($t, 'PVE') !== false) $drive = 'Pneumatic Vacuum Drive (PVE)';
            $cabin = (stripos($t, 'Luxury') !== false || stripos($t, 'Glass') !== false) ? 'Luxury Glass / Designer Cabin' : 'Compact Residential Cabin';
            $outdoor = (stripos($t, 'Outdoor') !== false || stripos($t, 'Weatherproof') !== false) ? 'IP55 Weatherproof Outdoor Rated' : 'Indoor Installation';

            $specs = [
                'Equipment Type'  => 'Residential Home Elevator',
                'Brand'           => $brand,
                'Capacity'        => "{$persons} (approx. " . ((int)filter_var($persons, FILTER_SANITIZE_NUMBER_INT) * 75 ?: 225) . " Kg)",
                'Stops / Floors'  => $stops,
                'Drive System'    => $drive,
                'Cabin Finish'    => $cabin,
                'Installation'    => $outdoor,
                'Power Supply'    => 'Single Phase 220V / 3-Phase 380V',
                'Safety Features' => 'Emergency lowering, battery backup, telephone',
                'Warranty'        => '1 Year Parts & Labour',
                'Service Support' => '24/7 emergency support available',
            ];
            break;

        case 'car-elevator':
            $kg = spec_first_match($t, [
                '/(\d[\d,]*)\s*Kg/i' => '%s Kg',
            ]) ?: '2700 Kg';
            $speed = (stripos($t, 'Traction') !== false) ? '0.63 m/s' : '0.5 m/s';
            $type  = (stripos($t, 'Underground') !== false) ? 'Underground Car Elevator' : 'Hydraulic / Traction Car Elevator';

            $specs = [
                'Equipment Type'  => $type,
                'Brand'           => $brand,
                'Load Capacity'   => $kg,
                'Rated Speed'     => $speed,
                'Platform Size'   => 'Custom (SUV / Sedan compatible)',
                'Door Type'       => 'Vertical Sliding / Automatic',
                'Drive System'    => (stripos($t, 'Traction') !== false) ? 'Traction with Counterweight' : 'Hydraulic Cylinder',
                'Safety Features' => 'Overload sensor, anti-fall lock, emergency stop',
                'Control'         => 'Key-switch / PLC with remote monitoring',
                'Warranty'        => '1 Year Parts & Labour',
                'Installation'    => 'Basement / multi-level car lift solution',
            ];
            break;

        case 'car-parking-lift':
            $levels = spec_first_match($t, [
                '/(\d+)\s*[-]?\s*Level/i' => '%d Levels',
            ]) ?: (stripos($t, 'Tower') !== false ? 'Multi-Level Tower' : '2 Levels');
            $cars = spec_first_match($t, [
                '/(\d+)\s*[-]?\s*Car/i' => 'Up to %d Cars',
            ]);
            $type = 'Hydraulic Car Parking Stacker';
            if (stripos($t, 'Puzzle') !== false) $type = 'Puzzle Parking System';
            if (stripos($t, 'Rotary') !== false) $type = 'Rotary Car Parking Platform';
            if (stripos($t, 'Tower') !== false) $type = 'Tower Automated Parking System';
            if (stripos($t, 'Underground') !== false) $type = 'Underground Car Parking Lift';

            $specs = [
                'Equipment Type'  => $type,
                'Brand'           => $brand,
                'Parking Levels'  => $levels,
                'Vehicle Capacity'=> $cars ?: '1 Car per platform (SUV compatible)',
                'Load Capacity'   => '2,700 Kg per platform',
                'Lift Height'     => '2,100 mm clear height',
                'Drive System'    => 'High-pressure Hydraulic Power Pack',
                'Control System'  => 'PLC Auto / Key-switch manual override',
                'Safety Features' => 'Multi-stage mechanical fall locks, limit switches',
                'Warranty'        => '1 Year Parts & Labour',
                'Application'     => 'Residential basement & commercial parking',
            ];
            break;

        case 'diesel-generator':
            $kva = spec_first_match($t, [
                '/(\d+)\s*kVA/i' => '%d kVA',
            ]) ?: '150 kVA';
            $kva_num = (int) filter_var($kva, FILTER_SANITIZE_NUMBER_INT);
            $kw  = round($kva_num * 0.8);
            $canopy = (stripos($t, 'Soundproof') !== false || stripos($t, 'Silent') !== false) ? 'Super Soundproof (65 dBA @ 7m)' : 'Weatherproof Acoustic Canopy';
            $type = (stripos($t, 'Containerized') !== false) ? 'Containerized Industrial Genset' : 'Standby Diesel Generator';
            if (stripos($t, 'Prime Power') !== false) $type = 'Prime Power Diesel Generator';

            $specs = [
                'Equipment Type'  => $type,
                'Brand'           => $brand,
                'Rated Power'     => "{$kva} / {$kw} kW",
                'Engine Type'     => '4-Stroke Turbocharged Diesel',
                'Alternator'      => 'Brushless Stamford / Leroy Somer Type',
                'Controller'      => 'Deep Sea AMF Auto Start (DSE 7320)',
                'Fuel Type'       => 'High-Speed Diesel (HSD)',
                'Fuel Tank'       => 'Built-in 8–12 Hours running capacity',
                'Canopy'          => $canopy,
                'Warranty'        => '1 Year or 1,000 Running Hours',
                'Application'     => 'Factory, hospital, commercial backup power',
            ];
            break;

        case 'power-transformer':
            $kva = spec_first_match($t, [
                '/(\d+)\s*kVA/i' => '%d kVA',
            ]) ?: '500 kVA';
            $cooling = (stripos($t, 'Dry Type') !== false || stripos($t, 'Dry-Type') !== false) ? 'AN (Dry Type Air Natural)' : 'ONAN (Oil Natural Air Natural)';
            $type = (stripos($t, 'Dry') !== false) ? 'Dry Type Distribution Transformer' : 'Oil-Immersed Distribution Transformer';
            if (stripos($t, 'Pad Mounted') !== false) $type = 'Pad Mounted Transformer';
            if (stripos($t, 'Sub-station') !== false || stripos($t, 'Substation') !== false) $type = 'HT Sub-station Transformer';
            if (stripos($t, 'Compact') !== false) $type = 'Compact Package Substation';

            $specs = [
                'Equipment Type'  => $type,
                'Brand'           => $brand,
                'Rated Capacity'  => $kva,
                'Primary Voltage' => '11 kV',
                'Secondary Voltage'=> '0.415 kV (415V / 240V)',
                'Vector Group'    => 'Dyn11',
                'Cooling Method'  => $cooling,
                'Winding Material'=> '99.9% Electrolytic Copper',
                'Standard'        => 'IEC 60076 / BSTI Certified',
                'Warranty'        => '1 Year Manufacturing Warranty',
                'Application'     => 'Industrial & commercial power distribution',
            ];
            break;

        case 'solar-system':
            $kw = spec_first_match($t, [
                '/(\d+)\s*kW/i' => '%d kW',
            ]) ?: '10 kW';
            $sys_type = 'On-Grid Solar System';
            if (stripos($t, 'Hybrid') !== false) $sys_type = 'Hybrid Solar System (Battery Backup)';
            if (stripos($t, 'Off-Grid') !== false) $sys_type = 'Off-Grid Standalone Solar System';
            if (stripos($t, 'Industrial') !== false) $sys_type = 'Industrial Solar Power Plant';
            if (stripos($t, 'Residential') !== false) $sys_type = 'Residential Rooftop Solar Package';
            if (stripos($t, 'Commercial') !== false) $sys_type = 'Commercial Rooftop Solar System';

            $specs = [
                'Equipment Type'  => $sys_type,
                'Brand'           => $brand,
                'System Capacity' => $kw,
                'Solar Panel'     => 'Mono PERC Tier-1 (LONGi / Jinko / Trina class)',
                'Inverter'        => (stripos($t, 'Hybrid') !== false) ? 'Hybrid MPPT Inverter with Battery' : 'Grid-Tie String Inverter',
                'Battery Backup'  => (stripos($t, 'Hybrid') !== false) ? 'Lithium Battery Bank (optional sizing)' : 'Not required (On-Grid)',
                'Mounting'        => 'Rooftop / Ground Mount Aluminium Structure',
                'Generation'      => 'Approx. ' . ((int)filter_var($kw, FILTER_SANITIZE_NUMBER_INT) * 4) . '–' . ((int)filter_var($kw, FILTER_SANITIZE_NUMBER_INT) * 5) . ' units/day (avg.)',
                'Warranty'        => '10 Years Panel + 2–5 Years Inverter',
                'Net Metering'    => 'BPDB / DESCO compliant design support',
            ];
            break;

        default:
            $specs = [
                'Equipment Type'  => 'Engineering Product',
                'Brand'           => $brand,
                'Origin'          => 'Premium International Standard',
                'Warranty'        => '1 Year Official Warranty',
                'Installation'    => 'Professional installation & commissioning',
                'Delivery'        => 'Dhaka & nationwide delivery available',
                'Support'         => '24/7 technical support',
            ];
            break;
    }

    return $specs;
}

// ─── সব প্রডাক্ট লোড ─────────────────────────────────────────────────
$sql = "SELECT s.id, s.title, s.slug, s.description,
               cat.slug AS cat_slug, co.name AS company_name
        FROM `{$prefix}services` s
        LEFT JOIN `{$prefix}categories` cat ON s.category_id = cat.id
        LEFT JOIN `{$prefix}companies` co ON s.company_id = co.id
        ORDER BY s.id ASC";

$result = $conn->query($sql);
if (!$result) {
    die("<div style='color:red;padding:20px;'>প্রডাক্ট লোড ব্যর্থ: " . htmlspecialchars($conn->error) . "</div>");
}

$stmt_check = $conn->prepare("SELECT COUNT(*) AS cnt FROM `{$prefix}product_specs` WHERE product_id = ?");
$stmt_ins   = $conn->prepare("INSERT INTO `{$prefix}product_specs` (product_id, spec_key, spec_value) VALUES (?, ?, ?)");

while ($product = $result->fetch_assoc()) {
    $pid = (int) $product['id'];

    $stmt_check->bind_param("i", $pid);
    $stmt_check->execute();
    $existing = (int) $stmt_check->get_result()->fetch_assoc()['cnt'];

    if ($existing > 0) {
        $skipped++;
        continue;
    }

    $cat_slug = spec_detect_category(
        $product['title'],
        $product['slug'],
        $product['cat_slug'] ?? ''
    );

    $specs = generate_product_specs(
        $product['title'],
        $cat_slug,
        $product['company_name'] ?? ''
    );

    $ok = true;
    foreach ($specs as $key => $val) {
        $stmt_ins->bind_param("iss", $pid, $key, $val);
        if (!$stmt_ins->execute()) {
            $ok = false;
            $errors++;
            $messages[] = "✗ প্রডাক্ট #{$pid}: " . $conn->error;
            break;
        }
    }

    if ($ok) {
        $seeded++;
        $messages[] = "✓ স্পেক যোগ: " . $product['title'] . " ({$cat_slug})";
    }
}

$stmt_check->close();
$stmt_ins->close();

$total = $seeded + $skipped + $errors;
?>
<!DOCTYPE html>
<html lang="bn">
<head>
    <meta charset="UTF-8">
    <title>Install Six — Product Specs</title>
    <style>
        body { font-family: sans-serif; background: #0f172a; padding: 40px; color: #cbd5e1; }
        .box { max-width: 680px; margin: 0 auto; background: #1e293b; padding: 30px; border-radius: 12px; border: 1px solid #334155; }
        h2 { color: #38bdf8; margin-bottom: 8px; }
        .stats { display: grid; grid-template-columns: repeat(3,1fr); gap: 10px; margin: 20px 0; }
        .stat { background: #0f172a; padding: 14px; border-radius: 8px; text-align: center; border: 1px solid #334155; }
        .stat strong { display: block; font-size: 22px; color: #38bdf8; }
        .stat span { font-size: 11px; color: #94a3b8; text-transform: uppercase; }
        ul { max-height: 320px; overflow-y: auto; font-size: 12px; padding-left: 18px; }
        li { padding: 4px 0; }
        .ok { color: #4ade80; font-weight: bold; margin-top: 16px; }
        .note { font-size: 12px; color: #94a3b8; margin-top: 12px; line-height: 1.6; }
    </style>
</head>
<body>
<div class="box">
    <h2>Phase 6 — Product Specifications Auto-Seed</h2>
    <p>সব প্রডাক্টে ক্যাটাগরি অনুযায়ী স্বয়ংক্রিয় specification যোগ করা হয়েছে।</p>

    <div class="stats">
        <div class="stat"><strong><?php echo $seeded; ?></strong><span>নতুন স্পেক যোগ</span></div>
        <div class="stat"><strong><?php echo $skipped; ?></strong><span>আগে থেকেই ছিল</span></div>
        <div class="stat"><strong><?php echo $errors; ?></strong><span>ত্রুটি</span></div>
    </div>

    <?php if ($seeded > 0): ?>
    <p class="ok">সফল! <?php echo $seeded; ?>টি প্রডাক্টে specification যোগ হয়েছে।</p>
    <?php elseif ($skipped > 0 && $errors === 0): ?>
    <p class="ok">সব প্রডাক্টে আগে থেকেই specification আছে — কিছু করার দরকার নেই।</p>
    <?php endif; ?>

    <ul>
        <?php foreach ($messages as $m): ?>
        <li><?php echo htmlspecialchars($m); ?></li>
        <?php endforeach; ?>
    </ul>

    <p class="note">
        <strong>নোট:</strong> যেসব প্রডাক্টে আগে থেকেই spec ছিল সেগুলো স্কিপ করা হয়েছে (নিরাপদ re-run)।
        প্রতিটি প্রডাক্টের details পেজে specification টেবিল দেখা যাবে।
        Admin → Products থেকে পরে যেকোনো spec এডিট/ডিলিট করতে পারবেন।
    </p>
</div>
</body>
</html>
