<?php
/**
 * Core Functions File
 * Master settings, hybrid database fallback system (Elevators & Generators), 
 * customer review submission, and advanced Gemini AI integration.
 */

require_once 'config.php';

// =========================================================================
// ১. ডিফল্ট সাইট সেটিংস (ডাটাবেজে না থাকলে ফলব্যাক)
// =========================================================================
$master_settings = [
    'site_name'     => 'STAR ELEVATOR BD',
    'site_subtitle' => '& Power Engineering',
    'site_logo'     => 'https://www.nextgensoftwaretech.com/power_engineering/new_logo.jpeg',
    'site_phone'    => '+88 01711-701747',
    'site_email'    => 'starelevator@gmail.com',
    'site_address'  => 'House No. 398, (3rd Floor), Road No. 29, New DOSH Mohakhali, Dhaka-1206',
    'site_about'    => 'Your trusted partner for certified passenger lifts, standby diesel generators, sub-station electrical transformers, VRF air conditioning systems, and automated car parking lifts in Bangladesh.',
    'stat_years'    => '20+ Years',
    'stat_units'    => '2,700+',
    'stat_response' => '24/7/365',
];


/**
 * সেটিংস পাওয়ার মূল ফাংশন — ডাটাবেজ আগে, তারপর ডিফল্ট
 */
function get_site_setting($key) {
    global $conn, $master_settings;

    if ($conn) {
        $prefix = DB_PREFIX;
        $stmt = $conn->prepare("SELECT meta_value FROM `{$prefix}settings` WHERE meta_key = ? LIMIT 1");
        if ($stmt) {
            $stmt->bind_param("s", $key);
            $stmt->execute();
            $result = $stmt->get_result();
            if ($row = $result->fetch_assoc()) {
                $val = trim($row['meta_value']);
                $stmt->close();
                if ($val !== '') {
                    return $val;
                }
            } else {
                $stmt->close();
            }
        }
    }

    return $master_settings[$key] ?? '';
}

/**
 * লোকাল/রিমোট অ্যাসেট URL রিসলভ
 */
function site_asset_url($url, $from_admin = false) {
    if (empty($url)) return '';
    if (preg_match('#^https?://#i', $url)) return $url;
    $path = ltrim($url, '/');
    return $from_admin ? '../' . $path : $path;
}

/**
 * লোগো পাওয়ার শর্টকাট ফাংশন
 */
function get_site_logo($from_admin = false) {
    return site_asset_url(get_site_setting('site_logo'), $from_admin);
}

/**
 * লোগো লোকালি আপলোড
 */
function handle_logo_upload($file) {
    if (!isset($file['tmp_name']) || empty($file['tmp_name']) || $file['error'] !== UPLOAD_ERR_OK) {
        return ['success' => false, 'error' => 'লোগো আপলোড ব্যর্থ হয়েছে।'];
    }
    $allowed = ['image/jpeg', 'image/png', 'image/webp', 'image/gif', 'image/svg+xml'];
    $finfo = finfo_open(FILEINFO_MIME_TYPE);
    $mime  = finfo_file($finfo, $file['tmp_name']);
    finfo_close($finfo);
    if (!in_array($mime, $allowed)) {
        return ['success' => false, 'error' => 'শুধু JPG, PNG, WEBP, GIF, SVG অনুমোদিত।'];
    }
    $upload_dir = __DIR__ . '/uploads/logos/';
    if (!is_dir($upload_dir)) mkdir($upload_dir, 0755, true);
    $ext = pathinfo($file['name'], PATHINFO_EXTENSION) ?: 'png';
    $filename = 'logo_' . time() . '_' . bin2hex(random_bytes(4)) . '.' . strtolower($ext);
    $dest = $upload_dir . $filename;
    if (move_uploaded_file($file['tmp_name'], $dest)) {
        return ['success' => true, 'path' => 'uploads/logos/' . $filename];
    }
    return ['success' => false, 'error' => 'লোগো সেভ করা যায়নি।'];
}

/**
 * সেটিংস সেভ করার ফাংশন
 */
function save_site_setting($key, $value) {
    global $conn;
    if (!$conn) return false;
    $prefix = DB_PREFIX;
    $stmt = $conn->prepare("INSERT INTO `{$prefix}settings` (meta_key, meta_value) VALUES (?, ?) ON DUPLICATE KEY UPDATE meta_value = VALUES(meta_value)");
    if (!$stmt) return false;
    $stmt->bind_param("ss", $key, $value);
    $ok = $stmt->execute();
    $stmt->close();
    return $ok;
}


// ==========================================
// ২. হাইব্রিড ডাইনামিক ডেটা ফাংশনসমূহ (ফলব্যাক ডাটাসহ)
// ==========================================

/**
 * সার্ভিস ডেটা পাওয়ার ফাংশন
 */
function get_services() {
    global $conn;
    
    if ($conn) {
        $prefix = DB_PREFIX;
        $sql = "SELECT * FROM `{$prefix}services` ORDER BY id ASC";
        $result = $conn->query($sql);
        if ($result && $result->num_rows > 0) {
            $services = [];
            while ($row = $result->fetch_assoc()) {
                $services[] = $row;
            }
            return $services;
        }
    }
    
    return [
        [
            'title' => 'Elevator Maintenance & Repair',
            'description' => 'Certified preventative maintenance and safety checks for commercial and residential elevators.',
            'image_url' => 'https://images.unsplash.com/photo-1518156677180-95a2893f3e9f?w=600',
            'slug' => 'elevator-maintenance'
        ],
        [
            'title' => 'Generator Maintenance & Testing',
            'description' => 'Comprehensive standby generator servicing, annual load bank testing, and diesel fuel polishing.',
            'image_url' => 'https://images.unsplash.com/photo-1581092160607-ee22621dd758?w=600',
            'slug' => 'generator-maintenance'
        ],
        [
            'title' => 'Elevator Modernization',
            'description' => 'Upgrade outdated cab electronics, drive controllers, and interiors to modern standards.',
            'image_url' => 'https://images.unsplash.com/photo-1486406146926-c627a92ad1ab?w=600',
            'slug' => 'elevator-modernization'
        ],
        [
            'title' => 'Emergency Generator Repairs',
            'description' => '24/7 urgent dispatch for generator failure repairs, cooling system fixes, and backup power deploy.',
            'image_url' => 'https://images.unsplash.com/photo-1504307651254-35680f356dfd?w=600',
            'slug' => 'emergency-generator-repair'
        ],
        [
            'title' => 'ATS & Switchgear Controls',
            'description' => 'Expert inspection and repair of Automatic Transfer Switches (ATS) to guarantee smooth power transition.',
            'image_url' => 'https://images.unsplash.com/photo-1600585154340-be6161a56a0c?w=600',
            'slug' => 'ats-controls'
        ]
    ];
}

/**
 * কাস্টমার রিভিউ পাওয়ার ফাংশন (বাংলাদেশী নাম দিয়ে সাজানো ফলব্যাক)
 */
function get_testimonials() {
    global $conn;
    
    if ($conn) {
        $prefix = DB_PREFIX;
        // সর্বশেষ জমা পড়া রিভিউগুলো আগে দেখানোর জন্য ORDER BY id DESC ব্যবহার করা হয়েছে
        $sql = "SELECT * FROM `{$prefix}testimonials` ORDER BY id DESC";
        $result = $conn->query($sql);
        if ($result && $result->num_rows > 0) {
            $testimonials = [];
            while ($row = $result->fetch_assoc()) {
                $testimonials[] = $row;
            }
            return $testimonials;
        }
    }
    
    // বাংলাদেশী নাম সহ ডিফল্ট ফলব্যাক রিভিউ (ইংরেজিতে)
    return [
        [
            'client_name' => 'Tanvir Ahmed (Facility Manager)',
            'rating' => 5,
            'review_text' => 'Power Engineering handles both our lift and backup generator load testing in Gulshan. Excellent service and responsive team.',
            'source' => 'Google',
            'review_date' => '2024-03-15'
        ],
        [
            'client_name' => 'Farhana Yasmin (Director, Hotel Crown)',
            'rating' => 5,
            'review_text' => 'We passed our annual diesel generator testing requirements seamlessly. Highly recommend their routine service plans.',
            'source' => 'Website',
            'review_date' => '2024-03-10'
        ],
        [
            'client_name' => 'Kazi Rahman (President, Uttara Apartment Society)',
            'rating' => 5,
            'review_text' => 'Direct communication with technicians. No corporate call center delays. Fixed our residential lift within hours.',
            'source' => 'Google',
            'review_date' => '2024-02-28'
        ]
    ];
}

/**
 * নতুন রিভিউ ডাটাবেজে সাবমিট করার ফাংশন
 */
function save_customer_review($name, $rating, $text) {
    global $conn;
    
    // ডাটাবেজ কানেক্টেড থাকলে সরাসরি সেভ হবে
    if ($conn) {
        $prefix = DB_PREFIX;
        $rating = intval($rating);
        $source = 'Website';
        $date = date('Y-m-d');
        
        $stmt = $conn->prepare("INSERT INTO `{$prefix}testimonials` (client_name, rating, review_text, source, review_date) VALUES (?, ?, ?, ?, ?)");
        if ($stmt) {
            $stmt->bind_param("sisss", $name, $rating, $text, $source, $date);
            $success = $stmt->execute();
            $stmt->close();
            return $success;
        }
    }
    
    return false; // ডাটাবেজ কানেক্টেড না থাকলে ফলস রিটার্ন করবে
}

/**
 * এফএকিউ ডেটা পাওয়ার ফাংশন
 */
function get_faqs() {
    global $conn;
    
    if ($conn) {
        $prefix = DB_PREFIX;
        $sql = "SELECT * FROM `{$prefix}faqs` ORDER BY sort_order ASC, id ASC";
        $result = $conn->query($sql);
        if ($result && $result->num_rows > 0) {
            $faqs = [];
            while ($row = $result->fetch_assoc()) {
                $faqs[] = $row;
            }
            return $faqs;
        }
    }
    
    return [
        [
            'question' => 'How often should a commercial standby generator be tested?',
            'answer' => 'We recommend weekly no-load automated tests and annual load bank testing (at 100% capacity) to guarantee emergency power during unexpected blackouts.'
        ],
        [
            'question' => 'What is the required frequency for elevator safety checks?',
            'answer' => 'Commercial passenger elevators generally require monthly inspections and annual weight-load safety testing to comply with state and local codes.'
        ],
        [
            'question' => 'Do you provide 24/7 support for both elevators and generators?',
            'answer' => 'Yes! We have certified rapid-dispatch teams available 24/7/365 to handle emergency elevator trap-ins or standby generator start-up failures.'
        ]
    ];
}

/**
 * ব্লগ বা নিউজ পোস্ট পাওয়ার ফাংশন
 */
function get_blogs($limit = 8) {
    global $conn;
    
    if ($conn) {
        $prefix = DB_PREFIX;
        $limit_sql = intval($limit);
        $sql = "SELECT * FROM `{$prefix}blogs` ORDER BY is_featured DESC, id DESC LIMIT {$limit_sql}";
        $result = $conn->query($sql);
        if ($result && $result->num_rows > 0) {
            $blogs = [];
            while ($row = $result->fetch_assoc()) {
                $blogs[] = $row;
            }
            return $blogs;
        }
    }
    
    return [
        [
            'title' => 'Critical Emergency Backup Power Checklist for Storm Seasons',
            'excerpt' => 'A step-by-step guide on how to prepare your commercial generator before major power grid interruptions.',
            'thumbnail_url' => 'https://images.unsplash.com/photo-1581092160607-ee22621dd758?w=600',
            'is_featured' => 1,
            'video_url' => 'https://youtu.be/7758KUaWxQk?si=5_G2pRhjAsW6-IYD'
        ],
        [
            'title' => 'Modernizing Commercial Elevators: Costs vs long-term Safety',
            'excerpt' => 'An operational cost analysis showing how updating elevator switchgear controls reduces building electric bills.',
            'thumbnail_url' => 'https://images.unsplash.com/photo-1486406146926-c627a92ad1ab?w=600',
            'is_featured' => 0,
            'video_url' => ''
        ],
        [
            'title' => 'Why Standby Generator Fuel Polishing is Essential',
            'excerpt' => 'Microbial growth in diesel tanks can clog filters during emergency runs. Learn how fuel polishing prevents failures.',
            'thumbnail_url' => 'https://images.unsplash.com/photo-1504307651254-35680f356dfd?w=600',
            'is_featured' => 0,
            'video_url' => ''
        ],
        [
            'title' => 'Understanding National Fire Protection (NFPA) Standard 110',
            'excerpt' => 'A straightforward breakdown of safety mandates for emergency and standby generator system operations.',
            'thumbnail_url' => 'https://images.unsplash.com/photo-1584622650111-993a426fbf0a?w=600',
            'is_featured' => 0,
            'video_url' => ''
        ]
    ];
}

function get_blog_by_id($id) {
    global $conn;
    $id = intval($id);
    if (!$conn || $id <= 0) return null;
    $prefix = DB_PREFIX;
    $stmt = $conn->prepare("SELECT * FROM `{$prefix}blogs` WHERE id = ? LIMIT 1");
    $stmt->bind_param("i", $id);
    $stmt->execute();
    $row = $stmt->get_result()->fetch_assoc();
    $stmt->close();
    return $row ?: null;
}


// =========================================================================
// ৩. জেমিনি ২.৫ ফ্ল্যাশ এআই চ্যাটবট ইন্টিগ্রেশন
// =========================================================================

function ask_gemini_bot($user_message) {
    if (!defined('GEMINI_API_KEY') || GEMINI_API_KEY === 'YOUR_GEMINI_API_KEY' || empty(GEMINI_API_KEY)) {
        return "Chatbot ready. Please set your actual Gemini API Key in 'config.php' to activate the bot.";
    }

    $api_key = GEMINI_API_KEY;
    $model = GEMINI_MODEL;
    $api_url = "https://generativelanguage.googleapis.com/v1beta/models/{$model}:generateContent?key={$api_key}";

    $site_name    = get_site_setting('site_name');
    $site_phone   = get_site_setting('site_phone');
    $site_email   = get_site_setting('site_email');
    $site_address = get_site_setting('site_address');
    $site_about   = get_site_setting('site_about');

    $products_list = "";
    $products = get_products_filtered(null, null, 50);
    foreach ($products as $p) {
        $slug = $p['slug'] ?? '';
        $img  = $p['image_url'] ?? '';
        $products_list .= "- {$p['title']} | slug: {$slug} | image: {$img}\n";
    }

    $faqs_list = "";
    $faqs = get_faqs();
    foreach ($faqs as $faq) {
        $faqs_list .= "Q: " . $faq['question'] . "\nA: " . $faq['answer'] . "\n\n";
    }

    $training_list = "";
    $training = get_chatbot_training();
    foreach ($training as $t) {
        $training_list .= "Q: {$t['question']}\nA: {$t['answer']}\n\n";
    }

    $context = "
Company: {$site_name}
About: {$site_about}
Phone: {$site_phone}
Email: {$site_email}
Address: {$site_address}

Products (use slug for links, image for display):
{$products_list}

FAQs:
{$faqs_list}

Trained Q&A:
{$training_list}
";

    $system_instruction = "You are 'Power Bot', AI assistant for '{$site_name}' in Bangladesh.

STRICT RULES:
1. ALWAYS respond in natural standard Bengali (বাংলা).
2. NEVER greet, say hello, namaskar, or any salutation. Start directly with the answer.
3. Use ONLY the Context below. Do not invent facts.
4. When mentioning a product, format as: [PRODUCT:product-slug|Product Name] so the system can show image+link.
5. If unknown, say you don't have that info and give phone {$site_phone} or address {$site_address}.

Context:
{$context}";

    $payload = [
        "contents" => [
            [
                "parts" => [
                    ["text" => $user_message]
                ]
            ]
        ],
        "systemInstruction" => [
            "parts" => [
                ["text" => $system_instruction]
            ]
        ],
        "generationConfig" => [
            "temperature" => 0.6,
            "maxOutputTokens" => 350
        ]
    ];

    $ch = curl_init($api_url);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
    curl_setopt($ch, CURLOPT_POST, true);
    curl_setopt($ch, CURLOPT_POSTFIELDS, json_encode($payload));
    curl_setopt($ch, CURLOPT_HTTPHEADER, [
        'Content-Type: application/json'
    ]);
    
    curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
    curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, false);
    curl_setopt($ch, CURLOPT_TIMEOUT, 15);

    $response = curl_exec($ch);
    $http_code = curl_getinfo($ch, CURLINFO_HTTP_CODE);
    $curl_error = curl_error($ch);
    curl_close($ch);

    if ($http_code === 200) {
        $result = json_decode($response, true);
        if (isset($result['candidates'][0]['content']['parts'][0]['text'])) {
            $raw = $result['candidates'][0]['content']['parts'][0]['text'];
            $parsed = parse_chatbot_product_tags($raw);
            save_chat_history($user_message, $parsed);
            return $parsed;
        }
    }

    if (!empty($curl_error)) {
        $err = "দুঃখিত, জেমিনি এপিআই সংযোগে সমস্যা হচ্ছে। (Error: " . htmlspecialchars($curl_error) . ")";
        save_chat_history($user_message, $err);
        return $err;
    }

    $fallback = "দুঃখিত, এই মুহূর্তে আমি আপনার প্রশ্নের সঠিক উত্তর দিতে পারছি না। অনুগ্রহ করে আমাদের হটলাইন নম্বর " . htmlspecialchars($site_phone) . "-এ কল করুন অথবা আমাদের অফিস: " . htmlspecialchars($site_address) . "-এ সরাসরি যোগাযোগ করুন।";
    save_chat_history($user_message, $fallback);
    return $fallback;
}


// =========================================================================
// ৪. ক্যাটাগরি, কোম্পানি ও প্রডাক্ট ফিল্টারিং
// =========================================================================

function make_slug($text) {
    $slug = strtolower(trim(preg_replace('/[^a-z0-9]+/i', '-', $text), '-'));
    return $slug !== '' ? $slug : 'item-' . time();
}

function get_categories($active_only = true) {
    global $conn;
    if ($conn) {
        ensure_product_hierarchy();
        $prefix = DB_PREFIX;
        $sql = "SELECT * FROM `{$prefix}categories`";
        if ($active_only) $sql .= " WHERE is_active = 1";
        $sql .= " ORDER BY sort_order ASC, id ASC";
        $result = $conn->query($sql);
        if ($result && $result->num_rows > 0) {
            $rows = [];
            while ($row = $result->fetch_assoc()) $rows[] = $row;
            return $rows;
        }
    }
    return [
        ['id'=>1,'name'=>'Passenger Elevator','slug'=>'passenger-elevator','icon'=>'fa-elevator'],
        ['id'=>2,'name'=>'Residential Elevator','slug'=>'residential-elevator','icon'=>'fa-house'],
        ['id'=>3,'name'=>'Car Elevator','slug'=>'car-elevator','icon'=>'fa-car'],
        ['id'=>4,'name'=>'Car Parking Lift','slug'=>'car-parking-lift','icon'=>'fa-square-parking'],
        ['id'=>5,'name'=>'Diesel Generator','slug'=>'diesel-generator','icon'=>'fa-bolt'],
        ['id'=>6,'name'=>'Power Transformer','slug'=>'power-transformer','icon'=>'fa-plug'],
        ['id'=>7,'name'=>'Solar System','slug'=>'solar-system','icon'=>'fa-solar-panel'],
    ];
}

function get_category_by_id($id) {
    global $conn;
    $id = intval($id);
    if (!$conn || $id <= 0) return null;
    $prefix = DB_PREFIX;
    $stmt = $conn->prepare("SELECT * FROM `{$prefix}categories` WHERE id = ? LIMIT 1");
    $stmt->bind_param("i", $id);
    $stmt->execute();
    $row = $stmt->get_result()->fetch_assoc();
    $stmt->close();
    return $row ?: null;
}

function save_category($data, $id = 0) {
    global $conn;
    if (!$conn) return false;
    ensure_product_hierarchy();
    $prefix = DB_PREFIX;
    $name       = trim($data['name'] ?? '');
    $slug       = trim($data['slug'] ?? '') ?: make_slug($name);
    $icon       = trim($data['icon'] ?? 'fa-folder');
    $image_url  = trim($data['image_url'] ?? '');
    $sort_order = intval($data['sort_order'] ?? 0);
    $is_active  = isset($data['is_active']) ? intval($data['is_active']) : 1;
    if ($name === '') return false;

    if ($id > 0) {
        $stmt = $conn->prepare("UPDATE `{$prefix}categories` SET name=?, slug=?, icon=?, image_url=?, sort_order=?, is_active=? WHERE id=?");
        $stmt->bind_param("ssssiii", $name, $slug, $icon, $image_url, $sort_order, $is_active, $id);
    } else {
        $stmt = $conn->prepare("INSERT INTO `{$prefix}categories` (name, slug, icon, image_url, sort_order, is_active) VALUES (?,?,?,?,?,?)");
        $stmt->bind_param("ssssii", $name, $slug, $icon, $image_url, $sort_order, $is_active);
    }
    $ok = $stmt->execute();
    $stmt->close();
    return $ok;
}

function delete_category($id) {
    global $conn;
    $id = intval($id);
    if (!$conn || $id <= 0) return false;
    $prefix = DB_PREFIX;
    $stmt = $conn->prepare("DELETE FROM `{$prefix}categories` WHERE id = ?");
    $stmt->bind_param("i", $id);
    $ok = $stmt->execute();
    $stmt->close();
    return $ok;
}

function get_companies_by_category($category_id, $active_only = true) {
    global $conn;
    $category_id = intval($category_id);
    if ($conn && $category_id > 0) {
        ensure_product_hierarchy();
        $prefix = DB_PREFIX;
        $sql = "SELECT * FROM `{$prefix}companies` WHERE category_id = ?";
        if ($active_only) $sql .= " AND is_active = 1";
        $sql .= " ORDER BY name ASC, id ASC";
        $stmt = $conn->prepare($sql);
        $stmt->bind_param("i", $category_id);
        $stmt->execute();
        $result = $stmt->get_result();
        $rows = [];
        while ($row = $result->fetch_assoc()) $rows[] = $row;
        $stmt->close();
        return dedupe_companies_by_name($rows);
    }
    return [];
}

/**
 * Brand list for a sub-category = all unique companies of that parent category
 * (one company / one catalog shared across all sub-categories)
 */
function get_companies_by_subcategory($subcategory_id, $active_only = true) {
    $subcategory_id = intval($subcategory_id);
    if ($subcategory_id <= 0) return [];
    $sub = get_subcategory_by_id($subcategory_id);
    if (!$sub || empty($sub['category_id'])) return [];
    return get_companies_by_category((int)$sub['category_id'], $active_only);
}

/**
 * Keep one row per company name (prefer row that has catalog_url)
 */
function dedupe_companies_by_name($rows) {
    $unique = [];
    foreach ($rows as $row) {
        $key = mb_strtolower(trim($row['name'] ?? ''));
        if ($key === '') continue;
        if (!isset($unique[$key])) {
            $unique[$key] = $row;
            continue;
        }
        $have_cat = !empty($unique[$key]['catalog_url']);
        $new_cat  = !empty($row['catalog_url']);
        if (!$have_cat && $new_cat) {
            $unique[$key] = $row;
        }
    }
    return array_values($unique);
}

function get_all_companies() {
    global $conn;
    if (!$conn) return [];
    ensure_product_hierarchy();
    $prefix = DB_PREFIX;
    $sql = "SELECT co.*, cat.name AS category_name
            FROM `{$prefix}companies` co
            LEFT JOIN `{$prefix}categories` cat ON co.category_id = cat.id
            ORDER BY cat.sort_order ASC, co.name ASC, co.id ASC";
    $result = $conn->query($sql);
    $rows = [];
    if ($result) {
        while ($row = $result->fetch_assoc()) $rows[] = $row;
    }
    // Dedupe across list by category_id + name
    $unique = [];
    foreach ($rows as $row) {
        $key = (int)$row['category_id'] . '|' . mb_strtolower(trim($row['name'] ?? ''));
        if (!isset($unique[$key])) {
            $unique[$key] = $row;
            continue;
        }
        if (empty($unique[$key]['catalog_url']) && !empty($row['catalog_url'])) {
            $unique[$key] = $row;
        }
    }
    return array_values($unique);
}

function get_company_by_id($id) {
    global $conn;
    $id = intval($id);
    if (!$conn || $id <= 0) return null;
    $prefix = DB_PREFIX;
    $stmt = $conn->prepare("SELECT * FROM `{$prefix}companies` WHERE id = ? LIMIT 1");
    $stmt->bind_param("i", $id);
    $stmt->execute();
    $row = $stmt->get_result()->fetch_assoc();
    $stmt->close();
    return $row ?: null;
}

function save_company($data, $id = 0) {
    global $conn;
    if (!$conn) return false;
    ensure_product_hierarchy();
    $prefix = DB_PREFIX;
    $category_id    = intval($data['category_id'] ?? 0);
    // Companies are category-level (shared across all sub-categories)
    $subcategory_id = 0;
    $name           = trim($data['name'] ?? '');
    $slug           = trim($data['slug'] ?? '') ?: make_slug($name);
    $logo_url       = trim($data['logo_url'] ?? '');
    $catalog_url    = trim($data['catalog_url'] ?? '');
    $is_active      = isset($data['is_active']) ? intval($data['is_active']) : 1;
    if ($name === '' || $category_id <= 0) return false;

    if ($id > 0) {
        $stmt = $conn->prepare("UPDATE `{$prefix}companies` SET category_id=?, subcategory_id=NULLIF(?,0), name=?, slug=?, logo_url=?, catalog_url=?, is_active=? WHERE id=?");
        $stmt->bind_param("iissssii", $category_id, $subcategory_id, $name, $slug, $logo_url, $catalog_url, $is_active, $id);
    } else {
        $stmt = $conn->prepare("INSERT INTO `{$prefix}companies` (category_id, subcategory_id, name, slug, logo_url, catalog_url, is_active) VALUES (?,NULLIF(?,0),?,?,?,?,?)");
        $stmt->bind_param("iissssi", $category_id, $subcategory_id, $name, $slug, $logo_url, $catalog_url, $is_active);
    }
    $ok = $stmt->execute();
    $stmt->close();
    return $ok;
}

/**
 * Merge duplicate companies (same category + same name) into one.
 * Keeps catalog/logo from any duplicate, re-points products, deletes extras.
 * @return array{merged:int, deleted:int, kept:int}
 */
function merge_duplicate_companies() {
    global $conn;
    $stats = ['merged' => 0, 'deleted' => 0, 'kept' => 0];
    if (!$conn) return $stats;
    ensure_product_hierarchy();
    $prefix = DB_PREFIX;

    $result = $conn->query("SELECT id, category_id, name, catalog_url, logo_url, is_active FROM `{$prefix}companies` ORDER BY category_id ASC, name ASC, id ASC");
    if (!$result) return $stats;

    $groups = [];
    while ($row = $result->fetch_assoc()) {
        $key = (int)$row['category_id'] . '|' . mb_strtolower(trim($row['name']));
        $groups[$key][] = $row;
    }

    foreach ($groups as $rows) {
        if (count($rows) < 2) {
            $stats['kept']++;
            // still clear subcategory_id for singles
            $id = (int)$rows[0]['id'];
            $conn->query("UPDATE `{$prefix}companies` SET subcategory_id = NULL WHERE id = {$id}");
            continue;
        }

        // Prefer row that has catalog, else lowest id
        usort($rows, function ($a, $b) {
            $ac = !empty($a['catalog_url']) ? 0 : 1;
            $bc = !empty($b['catalog_url']) ? 0 : 1;
            if ($ac !== $bc) return $ac - $bc;
            return (int)$a['id'] - (int)$b['id'];
        });
        $keep = $rows[0];
        $keep_id = (int)$keep['id'];
        $stats['kept']++;
        $stats['merged']++;

        $catalog = $keep['catalog_url'] ?? '';
        $logo    = $keep['logo_url'] ?? '';
        foreach ($rows as $r) {
            if ($catalog === '' && !empty($r['catalog_url'])) $catalog = $r['catalog_url'];
            if ($logo === '' && !empty($r['logo_url'])) $logo = $r['logo_url'];
        }
        $cat_esc = $conn->real_escape_string($catalog);
        $logo_esc = $conn->real_escape_string($logo);
        $conn->query("UPDATE `{$prefix}companies` SET subcategory_id = NULL, catalog_url = '{$cat_esc}', logo_url = '{$logo_esc}' WHERE id = {$keep_id}");

        for ($i = 1; $i < count($rows); $i++) {
            $dup_id = (int)$rows[$i]['id'];
            $conn->query("UPDATE `{$prefix}services` SET company_id = {$keep_id} WHERE company_id = {$dup_id}");
            $conn->query("DELETE FROM `{$prefix}companies` WHERE id = {$dup_id}");
            $stats['deleted']++;
        }
    }

    return $stats;
}

function delete_company($id) {
    global $conn;
    $id = intval($id);
    if (!$conn || $id <= 0) return false;
    $prefix = DB_PREFIX;
    $stmt = $conn->prepare("DELETE FROM `{$prefix}companies` WHERE id = ?");
    $stmt->bind_param("i", $id);
    $ok = $stmt->execute();
    $stmt->close();
    return $ok;
}

function ensure_product_hierarchy() {
    static $done = null;
    if ($done !== null) return $done;
    global $conn;
    if (!$conn) return $done = false;
    $prefix = DB_PREFIX;

    $conn->query("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`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

    $check = function($table, $col) use ($conn, $prefix) {
        $r = $conn->query("SHOW COLUMNS FROM `{$prefix}{$table}` LIKE '{$col}'");
        return $r && $r->num_rows > 0;
    };
    if (!$check('companies', 'subcategory_id')) {
        $conn->query("ALTER TABLE `{$prefix}companies` ADD `subcategory_id` INT NULL AFTER `category_id`");
    }
    if (!$check('companies', 'catalog_url')) {
        $conn->query("ALTER TABLE `{$prefix}companies` ADD `catalog_url` VARCHAR(500) NULL AFTER `logo_url`");
    }
    if (!$check('categories', 'image_url')) {
        $conn->query("ALTER TABLE `{$prefix}categories` ADD `image_url` VARCHAR(500) NULL AFTER `icon`");
    }
    if (!$check('services', 'subcategory_id')) {
        $conn->query("ALTER TABLE `{$prefix}services` ADD `subcategory_id` INT NULL AFTER `category_id`");
    }
    return $done = true;
}

function get_subcategories($category_id = null, $active_only = true) {
    global $conn;
    if (!$conn) return [];
    ensure_product_hierarchy();
    $prefix = DB_PREFIX;
    $sql = "SELECT sc.*, cat.name AS category_name FROM `{$prefix}subcategories` sc
            LEFT JOIN `{$prefix}categories` cat ON sc.category_id = cat.id WHERE 1=1";
    if ($active_only) $sql .= " AND sc.is_active = 1";
    if ($category_id) $sql .= " AND sc.category_id = " . intval($category_id);
    $sql .= " ORDER BY sc.sort_order ASC, sc.name ASC";
    $result = $conn->query($sql);
    $rows = [];
    if ($result) while ($row = $result->fetch_assoc()) $rows[] = $row;
    return $rows;
}

function get_subcategory_by_id($id) {
    global $conn;
    $id = intval($id);
    if (!$conn || $id <= 0) return null;
    ensure_product_hierarchy();
    $prefix = DB_PREFIX;
    $stmt = $conn->prepare("SELECT * FROM `{$prefix}subcategories` WHERE id = ? LIMIT 1");
    $stmt->bind_param("i", $id);
    $stmt->execute();
    $row = $stmt->get_result()->fetch_assoc();
    $stmt->close();
    return $row ?: null;
}

function save_subcategory($data, $id = 0) {
    global $conn;
    if (!$conn) return false;
    ensure_product_hierarchy();
    $prefix = DB_PREFIX;
    $category_id = intval($data['category_id'] ?? 0);
    $name        = trim($data['name'] ?? '');
    $slug        = trim($data['slug'] ?? '') ?: make_slug($name);
    $sort_order  = intval($data['sort_order'] ?? 0);
    $is_active   = isset($data['is_active']) ? intval($data['is_active']) : 1;
    if ($name === '' || $category_id <= 0) return false;

    if ($id > 0) {
        $stmt = $conn->prepare("UPDATE `{$prefix}subcategories` SET category_id=?, name=?, slug=?, sort_order=?, is_active=? WHERE id=?");
        $stmt->bind_param("issiii", $category_id, $name, $slug, $sort_order, $is_active, $id);
    } else {
        $stmt = $conn->prepare("INSERT INTO `{$prefix}subcategories` (category_id, name, slug, sort_order, is_active) VALUES (?,?,?,?,?)");
        $stmt->bind_param("issii", $category_id, $name, $slug, $sort_order, $is_active);
    }
    $ok = $stmt->execute();
    $stmt->close();
    return $ok;
}

function delete_subcategory($id) {
    global $conn;
    $id = intval($id);
    if (!$conn || $id <= 0) return false;
    $prefix = DB_PREFIX;
    $stmt = $conn->prepare("DELETE FROM `{$prefix}subcategories` WHERE id = ?");
    $stmt->bind_param("i", $id);
    $ok = $stmt->execute();
    $stmt->close();
    return $ok;
}

function get_product_menu_tree() {
    $tree = [];
    $categories = get_categories(true);
    foreach ($categories as $cat) {
        $subs = get_subcategories($cat['id'], true);
        $sub_list = [];
        foreach ($subs as $sub) {
            $brands = get_companies_by_subcategory($sub['id'], true);
            $sub_list[] = [
                'id' => (int)$sub['id'],
                'name' => $sub['name'],
                'slug' => $sub['slug'],
                'brands' => array_map(function ($b) {
                    return [
                        'id' => (int)$b['id'],
                        'name' => $b['name'],
                        'slug' => $b['slug'],
                        'catalog_url' => trim($b['catalog_url'] ?? ''),
                    ];
                }, $brands),
            ];
        }
        $tree[] = [
            'id' => (int)$cat['id'],
            'name' => $cat['name'],
            'slug' => $cat['slug'],
            'icon' => $cat['icon'] ?? 'fa-box',
            'image_url' => trim($cat['image_url'] ?? ''),
            'subcategories' => $sub_list,
        ];
    }
    return $tree;
}

/**
 * Convert Google Drive / local PDF link to embeddable viewer URL
 */
function catalog_embed_url($url) {
    $url = trim((string)$url);
    if ($url === '') return '';

    // Google Drive: /file/d/ID/
    if (preg_match('#drive\.google\.com/file/d/([^/]+)#i', $url, $m)) {
        return 'https://drive.google.com/file/d/' . $m[1] . '/preview';
    }
    // Google Drive: open?id=ID
    if (preg_match('#[?&]id=([a-zA-Z0-9_-]+)#', $url, $m) && stripos($url, 'drive.google') !== false) {
        return 'https://drive.google.com/file/d/' . $m[1] . '/preview';
    }
    // Google Docs
    if (preg_match('#docs\.google\.com/document/d/([^/]+)#i', $url, $m)) {
        return 'https://docs.google.com/document/d/' . $m[1] . '/preview';
    }

    return $url;
}

function company_has_catalog($company) {
    return !empty($company['catalog_url']);
}

function get_admin_password() {
    $stored = get_site_setting('admin_password');
    return $stored !== '' ? $stored : 'admin123';
}

function verify_admin_password($password) {
    return hash_equals(get_admin_password(), (string)$password);
}

function change_admin_password($current, $new, $confirm) {
    if (!verify_admin_password($current)) {
        return ['success' => false, 'error' => 'বর্তমান পাসওয়ার্ড ভুল।'];
    }
    $new = trim((string)$new);
    $confirm = trim((string)$confirm);
    if (strlen($new) < 6) {
        return ['success' => false, 'error' => 'নতুন পাসওয়ার্ড কমপক্ষে ৬ অক্ষরের হতে হবে।'];
    }
    if ($new !== $confirm) {
        return ['success' => false, 'error' => 'নতুন পাসওয়ার্ড ও কনফার্ম পাসওয়ার্ড মিলছে না।'];
    }
    if (!save_site_setting('admin_password', $new)) {
        return ['success' => false, 'error' => 'পাসওয়ার্ড সেভ করা যায়নি।'];
    }
    return ['success' => true];
}

function get_products_filtered($category_id = null, $company_id = null, $limit = 0, $subcategory_id = null) {
    global $conn;
    if ($conn) {
        ensure_product_hierarchy();
        $prefix = DB_PREFIX;
        $sql = "SELECT s.*, c.name as category_name, sc.name as subcategory_name, co.name as company_name
                FROM `{$prefix}services` s
                LEFT JOIN `{$prefix}categories` c ON s.category_id = c.id
                LEFT JOIN `{$prefix}subcategories` sc ON s.subcategory_id = sc.id
                LEFT JOIN `{$prefix}companies` co ON s.company_id = co.id
                WHERE 1=1";
        $types = '';
        $params = [];

        if ($category_id) {
            $sql .= " AND s.category_id = ?";
            $types .= 'i';
            $params[] = intval($category_id);
        }
        if ($subcategory_id) {
            $sql .= " AND s.subcategory_id = ?";
            $types .= 'i';
            $params[] = intval($subcategory_id);
        }
        if ($company_id) {
            $sql .= " AND s.company_id = ?";
            $types .= 'i';
            $params[] = intval($company_id);
        }
        $sql .= " ORDER BY s.id ASC";
        if ($limit > 0) $sql .= " LIMIT " . intval($limit);

        $stmt = $conn->prepare($sql);
        if ($stmt) {
            if (!empty($params)) $stmt->bind_param($types, ...$params);
            $stmt->execute();
            $result = $stmt->get_result();
            $rows = [];
            while ($row = $result->fetch_assoc()) $rows[] = $row;
            $stmt->close();
            if (!empty($rows)) return $rows;
            return [];
        }
    }
    return get_services();
}

function get_product_by_slug($slug) {
    global $conn;
    if ($conn) {
        ensure_product_hierarchy();
        $prefix = DB_PREFIX;
        $stmt = $conn->prepare("SELECT s.*, c.name as category_name, sc.name as subcategory_name, co.name as company_name
            FROM `{$prefix}services` s
            LEFT JOIN `{$prefix}categories` c ON s.category_id = c.id
            LEFT JOIN `{$prefix}subcategories` sc ON s.subcategory_id = sc.id
            LEFT JOIN `{$prefix}companies` co ON s.company_id = co.id
            WHERE s.slug = ? LIMIT 1");
        $stmt->bind_param("s", $slug);
        $stmt->execute();
        $result = $stmt->get_result();
        $row = $result->fetch_assoc();
        $stmt->close();
        return $row ?: null;
    }
    return null;
}


// =========================================================================
// ৫. ব্যানার ম্যানেজমেন্ট
// =========================================================================

function banner_get_desktop_image($banner) {
    if (!$banner) return '';
    if (!empty($banner['desktop_image_url'])) return $banner['desktop_image_url'];
    if (in_array($banner['device_type'] ?? '', ['desktop', 'both'], true)) return $banner['image_url'] ?? '';
    return '';
}

function banner_get_mobile_image($banner) {
    if (!$banner) return '';
    if (!empty($banner['mobile_image_url'])) return $banner['mobile_image_url'];
    if (in_array($banner['device_type'] ?? '', ['mobile', 'both'], true)) return $banner['image_url'] ?? '';
    return '';
}

function banner_column_exists($col) {
    global $conn;
    if (!$conn) return false;
    $prefix = DB_PREFIX;
    $table  = "{$prefix}banners";
    $col    = $conn->real_escape_string($col);
    $r = $conn->query("SHOW COLUMNS FROM `{$table}` LIKE '{$col}'");
    return $r && $r->num_rows > 0;
}

/**
 * install-five.php ছাড়াই অটো মাইগ্রেশন — admin/banner.php বা সাইট লোড হলে চলে
 */
function ensure_banner_columns() {
    global $conn;
    if (!$conn) return false;
    $prefix = DB_PREFIX;
    $table  = "{$prefix}banners";

    if (banner_column_exists('desktop_image_url')) return true;

    $columns = [
        'desktop_image_url'    => "ALTER TABLE `{$table}` ADD `desktop_image_url` VARCHAR(500) NULL AFTER `image_url`",
        'desktop_image_source' => "ALTER TABLE `{$table}` ADD `desktop_image_source` ENUM('upload','url') NULL DEFAULT 'url' AFTER `desktop_image_url`",
        'mobile_image_url'     => "ALTER TABLE `{$table}` ADD `mobile_image_url` VARCHAR(500) NULL AFTER `desktop_image_source`",
        'mobile_image_source'  => "ALTER TABLE `{$table}` ADD `mobile_image_source` ENUM('upload','url') NULL DEFAULT 'url' AFTER `mobile_image_url`",
    ];

    foreach ($columns as $name => $sql) {
        if (banner_column_exists($name)) continue;
        if ($conn->query($sql) !== TRUE) return false;
    }

    $conn->query("UPDATE `{$table}` SET desktop_image_url = image_url, desktop_image_source = image_source WHERE device_type = 'desktop' AND (desktop_image_url IS NULL OR desktop_image_url = '')");
    $conn->query("UPDATE `{$table}` SET mobile_image_url = image_url, mobile_image_source = image_source WHERE device_type = 'mobile' AND (mobile_image_url IS NULL OR mobile_image_url = '')");
    $conn->query("UPDATE `{$table}` SET desktop_image_url = image_url, desktop_image_source = image_source, mobile_image_url = image_url, mobile_image_source = image_source WHERE device_type = 'both' AND (desktop_image_url IS NULL OR desktop_image_url = '')");

    return true;
}

function banner_has_columns() {
    static $has = null;
    if ($has !== null) return $has;
    ensure_banner_columns();
    $has = banner_column_exists('desktop_image_url');
    return $has;
}

function get_banners($device_type = null, $active_only = true) {
    global $conn;
    if ($conn) {
        $prefix = DB_PREFIX;
        $sql = "SELECT * FROM `{$prefix}banners` WHERE 1=1";
        if ($active_only) $sql .= " AND is_active = 1";
        if ($device_type && in_array($device_type, ['desktop','mobile'])) {
            $sql .= " AND (device_type = '{$device_type}' OR device_type = 'both')";
        }
        $sql .= " ORDER BY sort_order ASC, id ASC";
        $result = $conn->query($sql);
        if ($result && $result->num_rows > 0) {
            $rows = [];
            while ($row = $result->fetch_assoc()) $rows[] = $row;
            return $rows;
        }
    }
    return [];
}

function get_primary_banner() {
    $banners = get_banners(null, true);
    return $banners[0] ?? null;
}

function get_banner_by_id($id) {
    global $conn;
    $id = intval($id);
    if ($conn && $id > 0) {
        $prefix = DB_PREFIX;
        $stmt = $conn->prepare("SELECT * FROM `{$prefix}banners` WHERE id = ? LIMIT 1");
        $stmt->bind_param("i", $id);
        $stmt->execute();
        $row = $stmt->get_result()->fetch_assoc();
        $stmt->close();
        return $row ?: null;
    }
    return null;
}

function save_banner($data, $id = 0) {
    global $conn;
    if (!$conn) return false;
    $prefix = DB_PREFIX;

    $title       = $data['title'] ?? '';
    $subtitle    = $data['subtitle'] ?? '';
    $button_text = $data['button_text'] ?? '';
    $button_link = $data['button_link'] ?? '';
    $desktop_image_url    = $data['desktop_image_url'] ?? '';
    $desktop_image_source = $data['desktop_image_source'] ?? 'url';
    $mobile_image_url     = $data['mobile_image_url'] ?? '';
    $mobile_image_source  = $data['mobile_image_source'] ?? 'url';
    $image_url   = $desktop_image_url ?: $mobile_image_url;
    $image_source= $desktop_image_url ? $desktop_image_source : $mobile_image_source;
    $device_type = 'both';
    $is_active   = isset($data['is_active']) ? intval($data['is_active']) : 1;
    $sort_order  = isset($data['sort_order']) ? intval($data['sort_order']) : 0;

    if (banner_has_columns()) {
        if ($id > 0) {
            $stmt = $conn->prepare("UPDATE `{$prefix}banners` SET title=?, subtitle=?, button_text=?, button_link=?, image_url=?, image_source=?, desktop_image_url=?, desktop_image_source=?, mobile_image_url=?, mobile_image_source=?, device_type=?, is_active=?, sort_order=? WHERE id=?");
            $stmt->bind_param("sssssssssssiii", $title, $subtitle, $button_text, $button_link, $image_url, $image_source, $desktop_image_url, $desktop_image_source, $mobile_image_url, $mobile_image_source, $device_type, $is_active, $sort_order, $id);
        } else {
            $stmt = $conn->prepare("INSERT INTO `{$prefix}banners` (title, subtitle, button_text, button_link, image_url, image_source, desktop_image_url, desktop_image_source, mobile_image_url, mobile_image_source, device_type, is_active, sort_order) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)");
            $stmt->bind_param("sssssssssssii", $title, $subtitle, $button_text, $button_link, $image_url, $image_source, $desktop_image_url, $desktop_image_source, $mobile_image_url, $mobile_image_source, $device_type, $is_active, $sort_order);
        }
    } else {
        if ($id > 0) {
            $stmt = $conn->prepare("UPDATE `{$prefix}banners` SET title=?, subtitle=?, button_text=?, button_link=?, image_url=?, image_source=?, device_type=?, is_active=?, sort_order=? WHERE id=?");
            $stmt->bind_param("sssssssiii", $title, $subtitle, $button_text, $button_link, $image_url, $image_source, $device_type, $is_active, $sort_order, $id);
        } else {
            $stmt = $conn->prepare("INSERT INTO `{$prefix}banners` (title, subtitle, button_text, button_link, image_url, image_source, device_type, is_active, sort_order) VALUES (?,?,?,?,?,?,?,?,?)");
            $stmt->bind_param("sssssssii", $title, $subtitle, $button_text, $button_link, $image_url, $image_source, $device_type, $is_active, $sort_order);
        }
    }
    $ok = $stmt->execute();
    $stmt->close();
    return $ok;
}

function delete_banner_upload_file($path) {
    if (empty($path) || strpos($path, 'uploads/') !== 0) return;
    $full = __DIR__ . '/' . $path;
    if (file_exists($full)) @unlink($full);
}

function delete_banner($id) {
    global $conn;
    $id = intval($id);
    if (!$conn || $id <= 0) return false;
    $prefix = DB_PREFIX;
    $banner = get_banner_by_id($id);
    if ($banner) {
        if (($banner['image_source'] ?? '') === 'upload') delete_banner_upload_file($banner['image_url'] ?? '');
        if (($banner['desktop_image_source'] ?? '') === 'upload') delete_banner_upload_file($banner['desktop_image_url'] ?? '');
        if (($banner['mobile_image_source'] ?? '') === 'upload') delete_banner_upload_file($banner['mobile_image_url'] ?? '');
    }
    $stmt = $conn->prepare("DELETE FROM `{$prefix}banners` WHERE id = ?");
    $stmt->bind_param("i", $id);
    $ok = $stmt->execute();
    $stmt->close();
    return $ok;
}

function handle_banner_upload($file) {
    if (!isset($file['tmp_name']) || empty($file['tmp_name']) || $file['error'] !== UPLOAD_ERR_OK) {
        return ['success' => false, 'error' => 'ফাইল আপলোড ব্যর্থ হয়েছে।'];
    }
    $allowed = ['image/jpeg','image/png','image/webp','image/gif'];
    $finfo = finfo_open(FILEINFO_MIME_TYPE);
    $mime  = finfo_file($finfo, $file['tmp_name']);
    finfo_close($finfo);
    if (!in_array($mime, $allowed)) {
        return ['success' => false, 'error' => 'শুধু JPG, PNG, WEBP, GIF অনুমোদিত।'];
    }
    $upload_dir = __DIR__ . '/uploads/banners/';
    if (!is_dir($upload_dir)) mkdir($upload_dir, 0755, true);
    $ext = pathinfo($file['name'], PATHINFO_EXTENSION) ?: 'jpg';
    $filename = time() . '_' . bin2hex(random_bytes(4)) . '.' . strtolower($ext);
    $dest = $upload_dir . $filename;
    if (move_uploaded_file($file['tmp_name'], $dest)) {
        return ['success' => true, 'path' => 'uploads/banners/' . $filename];
    }
    return ['success' => false, 'error' => 'ফাইল সেভ করা যায়নি।'];
}


// =========================================================================
// ৬. মারকিউ টেক্সট
// =========================================================================

function get_marquee() {
    global $conn;
    if ($conn) {
        $prefix = DB_PREFIX;
        $result = $conn->query("SELECT * FROM `{$prefix}marquee` WHERE is_active = 1 ORDER BY id DESC LIMIT 1");
        if ($result && $row = $result->fetch_assoc()) return $row;
    }
    return [
        'text' => '★ ISO Certified Engineering Solutions ★ 24/7 Emergency Support ★',
        'speed' => 'normal',
        'is_active' => 1
    ];
}

function get_marquee_all() {
    global $conn;
    if ($conn) {
        $prefix = DB_PREFIX;
        $result = $conn->query("SELECT * FROM `{$prefix}marquee` ORDER BY id DESC LIMIT 1");
        if ($result && $row = $result->fetch_assoc()) return $row;
    }
    return get_marquee();
}

function save_marquee($text, $speed = 'normal', $is_active = 1) {
    global $conn;
    if (!$conn) return false;
    $prefix = DB_PREFIX;
    $existing = $conn->query("SELECT id FROM `{$prefix}marquee` ORDER BY id DESC LIMIT 1");
    $is_active = intval($is_active);
    $allowed_speeds = ['slow','normal','fast'];
    if (!in_array($speed, $allowed_speeds)) $speed = 'normal';

    if ($existing && $row = $existing->fetch_assoc()) {
        $id = (int)$row['id'];
        $stmt = $conn->prepare("UPDATE `{$prefix}marquee` SET text=?, speed=?, is_active=? WHERE id=?");
        $stmt->bind_param("ssii", $text, $speed, $is_active, $id);
    } else {
        $stmt = $conn->prepare("INSERT INTO `{$prefix}marquee` (text, speed, is_active) VALUES (?,?,?)");
        $stmt->bind_param("ssi", $text, $speed, $is_active);
    }
    $ok = $stmt->execute();
    $stmt->close();
    return $ok;
}


// =========================================================================
// ৭. চ্যাটবট ট্রেইনিং ও হিস্টরি
// =========================================================================

function get_chatbot_training($active_only = true) {
    global $conn;
    if ($conn) {
        $prefix = DB_PREFIX;
        $sql = "SELECT * FROM `{$prefix}chatbot_training`";
        if ($active_only) $sql .= " WHERE is_active = 1";
        $sql .= " ORDER BY sort_order ASC, id ASC";
        $result = $conn->query($sql);
        if ($result && $result->num_rows > 0) {
            $rows = [];
            while ($row = $result->fetch_assoc()) $rows[] = $row;
            return $rows;
        }
    }
    return [];
}

function get_chatbot_training_grouped() {
    $all = get_chatbot_training();
    $grouped = [];
    foreach ($all as $item) {
        $title = $item['title'] ?: 'সাধারণ';
        $grouped[$title][] = $item;
    }
    return $grouped;
}

function save_chatbot_training($data, $id = 0) {
    global $conn;
    if (!$conn) return false;
    $prefix = DB_PREFIX;
    $title = $data['title'] ?? '';
    $question = $data['question'] ?? '';
    $answer = $data['answer'] ?? '';
    $category_tag = $data['category_tag'] ?? '';
    $sort_order = intval($data['sort_order'] ?? 0);
    $is_active = intval($data['is_active'] ?? 1);

    if ($id > 0) {
        $stmt = $conn->prepare("UPDATE `{$prefix}chatbot_training` SET title=?, question=?, answer=?, category_tag=?, sort_order=?, is_active=? WHERE id=?");
        $stmt->bind_param("ssssiii", $title, $question, $answer, $category_tag, $sort_order, $is_active, $id);
    } else {
        $stmt = $conn->prepare("INSERT INTO `{$prefix}chatbot_training` (title, question, answer, category_tag, sort_order, is_active) VALUES (?,?,?,?,?,?)");
        $stmt->bind_param("ssssii", $title, $question, $answer, $category_tag, $sort_order, $is_active);
    }
    $ok = $stmt->execute();
    $stmt->close();
    return $ok;
}

function delete_chatbot_training($id) {
    global $conn;
    $id = intval($id);
    if (!$conn || $id <= 0) return false;
    $prefix = DB_PREFIX;
    $stmt = $conn->prepare("DELETE FROM `{$prefix}chatbot_training` WHERE id = ?");
    $stmt->bind_param("i", $id);
    $ok = $stmt->execute();
    $stmt->close();
    return $ok;
}

function save_chat_history($user_message, $bot_response) {
    global $conn;
    if (!$conn) return false;
    $prefix = DB_PREFIX;
    $ip = $_SERVER['REMOTE_ADDR'] ?? '';
    $session_id = session_id() ?: '';
    $stmt = $conn->prepare("INSERT INTO `{$prefix}chat_history` (user_ip, user_message, bot_response, session_id) VALUES (?,?,?,?)");
    $stmt->bind_param("ssss", $ip, $user_message, $bot_response, $session_id);
    $ok = $stmt->execute();
    $stmt->close();
    return $ok;
}

function get_chat_history($limit = 50, $offset = 0) {
    global $conn;
    if ($conn) {
        $prefix = DB_PREFIX;
        $limit = intval($limit);
        $offset = intval($offset);
        $sql = "SELECT * FROM `{$prefix}chat_history` ORDER BY created_at DESC LIMIT {$limit} OFFSET {$offset}";
        $result = $conn->query($sql);
        if ($result) {
            $rows = [];
            while ($row = $result->fetch_assoc()) $rows[] = $row;
            return $rows;
        }
    }
    return [];
}

function get_recent_chat_history($limit = 5) {
    return get_chat_history($limit, 0);
}

function parse_chatbot_product_tags($text) {
    return preg_replace_callback(
        '/\[PRODUCT:([^\|]+)\|([^\]]+)\]/',
        function ($m) {
            $slug = trim($m[1]);
            $name = trim($m[2]);
            $product = get_product_by_slug($slug);
            $img = ($product && !empty($product['image_url']))
                ? htmlspecialchars($product['image_url'])
                : 'https://images.unsplash.com/photo-1581092160607-ee22621dd758?w=200';
            $link = 'products-details.php?slug=' . urlencode($slug);
            return '<a href="' . $link . '" class="chat-product-card inline-flex items-center gap-2 bg-white/10 border border-white/20 rounded-lg p-2 my-1 hover:bg-white/20 transition-all">'
                . '<img src="' . $img . '" alt="' . htmlspecialchars($name) . '" class="w-12 h-12 rounded object-cover">'
                . '<span class="text-sm font-semibold">' . htmlspecialchars($name) . '</span></a>';
        },
        $text
    );
}


// =========================================================================
// ৮. ড্যাশবোর্ড কাউন্টার
// =========================================================================

function count_products() {
    global $conn;
    if ($conn) {
        $prefix = DB_PREFIX;
        $r = $conn->query("SELECT COUNT(*) as cnt FROM `{$prefix}services`");
        if ($r) return (int)$r->fetch_assoc()['cnt'];
    }
    return count(get_services());
}

function count_reviews() {
    global $conn;
    if ($conn) {
        $prefix = DB_PREFIX;
        $r = $conn->query("SELECT COUNT(*) as cnt FROM `{$prefix}testimonials`");
        if ($r) return (int)$r->fetch_assoc()['cnt'];
    }
    return count(get_testimonials());
}

function count_chat_messages() {
    global $conn;
    if ($conn) {
        $prefix = DB_PREFIX;
        $r = $conn->query("SELECT COUNT(*) as cnt FROM `{$prefix}chat_history`");
        if ($r) return (int)$r->fetch_assoc()['cnt'];
    }
    return 0;
}

function count_faqs() {
    global $conn;
    if ($conn) {
        $prefix = DB_PREFIX;
        $r = $conn->query("SELECT COUNT(*) as cnt FROM `{$prefix}faqs`");
        if ($r) return (int)$r->fetch_assoc()['cnt'];
    }
    return count(get_faqs());
}

function count_categories() {
    global $conn;
    if ($conn) {
        $prefix = DB_PREFIX;
        $r = $conn->query("SELECT COUNT(*) as cnt FROM `{$prefix}categories`");
        if ($r) return (int)$r->fetch_assoc()['cnt'];
    }
    return count(get_categories());
}

/**
 * YouTube URL কে embed URL-এ রূপান্তর
 */
function get_youtube_embed_url($url) {
    $video_id = '';
    if (preg_match('%(?:youtube(?:-nocookie)?\.com/(?:[^/]+/.+/|(?:v|e(?:mbed)?)/|.*[?&]v=)|youtu\.be/)([^"&?/\s]{11})%i', $url, $match)) {
        $video_id = $match[1];
    }
    return $video_id ? "https://www.youtube.com/embed/{$video_id}" : $url;
}


// =========================================================================
// ৯. কন্টাক্ট / কোটেশন ইনকোয়ারি
// =========================================================================

function save_contact_inquiry($name, $phone, $email, $service = '', $message = '') {
    global $conn;
    if (!$conn) return false;
    $prefix = DB_PREFIX;
    $ip = $_SERVER['REMOTE_ADDR'] ?? '';
    $stmt = $conn->prepare("INSERT INTO `{$prefix}contact_inquiries` (name, phone, email, service, message, ip_address) VALUES (?,?,?,?,?,?)");
    $stmt->bind_param("ssssss", $name, $phone, $email, $service, $message, $ip);
    $ok = $stmt->execute();
    $stmt->close();
    return $ok;
}

function get_contact_inquiries($limit = 50, $offset = 0, $unread_only = false) {
    global $conn;
    if (!$conn) return [];
    $prefix = DB_PREFIX;
    $limit = intval($limit);
    $offset = intval($offset);
    $sql = "SELECT * FROM `{$prefix}contact_inquiries`";
    if ($unread_only) $sql .= " WHERE is_read = 0";
    $sql .= " ORDER BY created_at DESC LIMIT {$limit} OFFSET {$offset}";
    $result = $conn->query($sql);
    $rows = [];
    if ($result) while ($row = $result->fetch_assoc()) $rows[] = $row;
    return $rows;
}

function get_contact_inquiry_by_id($id) {
    global $conn;
    $id = intval($id);
    if (!$conn || $id <= 0) return null;
    $prefix = DB_PREFIX;
    $stmt = $conn->prepare("SELECT * FROM `{$prefix}contact_inquiries` WHERE id = ? LIMIT 1");
    $stmt->bind_param("i", $id);
    $stmt->execute();
    $row = $stmt->get_result()->fetch_assoc();
    $stmt->close();
    return $row ?: null;
}

function count_contact_inquiries($unread_only = false) {
    global $conn;
    if (!$conn) return 0;
    $prefix = DB_PREFIX;
    $sql = "SELECT COUNT(*) as cnt FROM `{$prefix}contact_inquiries`";
    if ($unread_only) $sql .= " WHERE is_read = 0";
    $r = $conn->query($sql);
    return $r ? (int)$r->fetch_assoc()['cnt'] : 0;
}

function mark_inquiry_read($id) {
    global $conn;
    $id = intval($id);
    if (!$conn || $id <= 0) return false;
    $prefix = DB_PREFIX;
    $stmt = $conn->prepare("UPDATE `{$prefix}contact_inquiries` SET is_read = 1 WHERE id = ?");
    $stmt->bind_param("i", $id);
    $ok = $stmt->execute();
    $stmt->close();
    return $ok;
}

function delete_contact_inquiry($id) {
    global $conn;
    $id = intval($id);
    if (!$conn || $id <= 0) return false;
    $prefix = DB_PREFIX;
    $stmt = $conn->prepare("DELETE FROM `{$prefix}contact_inquiries` WHERE id = ?");
    $stmt->bind_param("i", $id);
    $ok = $stmt->execute();
    $stmt->close();
    return $ok;
}


// =========================================================================
// ১০. ডিভাইস-স্পেসিফিক হিরো ব্যানার (Desktop 16:9 / Mobile 4:3 + Fallback)
// =========================================================================

/**
 * হিরো ব্যানার — ডিভাইস অনুযায়ী আলাদা ইমেজ
 * Desktop খালি থাকলে Mobile ইমেজ দেখাবে
 * Mobile খালি থাকলে Desktop ইমেজ দেখাবে
 */
function get_device_banner($device = 'desktop') {
    $banners = get_device_banners($device);
    return $banners[0] ?? null;
}

/**
 * সব অ্যাকটিভ ব্যানার — স্লাইডারের জন্য (desktop/mobile ইমেজ রিজল্ভড)
 */
function get_device_banners($device = 'desktop') {
    $list = get_banners(null, true);
    $out  = [];
    foreach ($list as $banner) {
        $desktop_img = banner_get_desktop_image($banner);
        $mobile_img  = banner_get_mobile_image($banner);
        $image = ($device === 'desktop')
            ? ($desktop_img ?: $mobile_img)
            : ($mobile_img ?: $desktop_img);
        if (empty($image)) continue;
        $banner['image_url'] = $image;
        $out[] = $banner;
    }
    return $out;
}
// =========================================================================
// 11. PROJECTS (company work gallery)
// =========================================================================

function ensure_projects_table() {
    global $conn;
    static $done = false;
    if ($done || !$conn) return;
    $done = true;
    $prefix = DB_PREFIX;
    $conn->query("CREATE TABLE IF NOT EXISTS `{$prefix}projects` (
        `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        `company_name` VARCHAR(255) NOT NULL,
        `category_id` INT UNSIGNED DEFAULT NULL,
        `description` TEXT,
        `image_urls` TEXT,
        `video_urls` TEXT,
        `is_active` TINYINT(1) NOT NULL DEFAULT 1,
        `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        INDEX (`category_id`),
        INDEX (`is_active`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
}

function project_decode_urls($raw) {
    if (empty($raw)) return [];
    $decoded = json_decode($raw, true);
    if (is_array($decoded)) {
        return array_values(array_filter(array_map('trim', $decoded)));
    }
    // newline / comma separated fallback
    $parts = preg_split('/[\r\n,]+/', $raw);
    return array_values(array_filter(array_map('trim', $parts)));
}

function project_encode_urls($list) {
    if (!is_array($list)) {
        $list = preg_split('/[\r\n,]+/', (string)$list);
    }
    $list = array_values(array_filter(array_map('trim', $list)));
    return json_encode($list, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
}

function get_projects($limit = 0, $active_only = true) {
    global $conn;
    ensure_projects_table();
    if (!$conn) return [];
    $prefix = DB_PREFIX;
    $where = $active_only ? 'WHERE p.is_active = 1' : '';
    $limit_sql = $limit > 0 ? 'LIMIT ' . intval($limit) : '';
    $sql = "SELECT p.*, c.name AS category_name, c.icon AS category_icon
            FROM `{$prefix}projects` p
            LEFT JOIN `{$prefix}categories` c ON c.id = p.category_id
            $where
            ORDER BY p.id DESC
            $limit_sql";
    $result = $conn->query($sql);
    $rows = [];
    if ($result) {
        while ($row = $result->fetch_assoc()) {
            $row['images'] = project_decode_urls($row['image_urls'] ?? '');
            $row['videos'] = project_decode_urls($row['video_urls'] ?? '');
            $rows[] = $row;
        }
    }
    return $rows;
}

function get_project_by_id($id) {
    global $conn;
    ensure_projects_table();
    $id = intval($id);
    if (!$conn || $id <= 0) return null;
    $prefix = DB_PREFIX;
    $stmt = $conn->prepare("SELECT p.*, c.name AS category_name, c.icon AS category_icon
        FROM `{$prefix}projects` p
        LEFT JOIN `{$prefix}categories` c ON c.id = p.category_id
        WHERE p.id = ? LIMIT 1");
    $stmt->bind_param('i', $id);
    $stmt->execute();
    $row = $stmt->get_result()->fetch_assoc();
    $stmt->close();
    if (!$row) return null;
    $row['images'] = project_decode_urls($row['image_urls'] ?? '');
    $row['videos'] = project_decode_urls($row['video_urls'] ?? '');
    return $row;
}

function save_project($data, $id = 0) {
    global $conn;
    ensure_projects_table();
    if (!$conn) return false;
    $prefix = DB_PREFIX;
    $company = trim($data['company_name'] ?? '');
    $cat_id  = !empty($data['category_id']) ? intval($data['category_id']) : 0;
    $desc    = trim($data['description'] ?? '');
    $images  = project_encode_urls($data['image_urls'] ?? []);
    $videos  = project_encode_urls($data['video_urls'] ?? []);
    $active  = !empty($data['is_active']) ? 1 : 0;
    $id      = intval($id);

    if ($company === '') return false;

    if ($id > 0) {
        $stmt = $conn->prepare("UPDATE `{$prefix}projects` SET company_name=?, category_id=NULLIF(?,0), description=?, image_urls=?, video_urls=?, is_active=? WHERE id=?");
        $stmt->bind_param('sisssii', $company, $cat_id, $desc, $images, $videos, $active, $id);
    } else {
        $stmt = $conn->prepare("INSERT INTO `{$prefix}projects` (company_name, category_id, description, image_urls, video_urls, is_active) VALUES (?,NULLIF(?,0),?,?,?,?)");
        $stmt->bind_param('sisssi', $company, $cat_id, $desc, $images, $videos, $active);
    }
    $ok = $stmt->execute();
    $stmt->close();
    return $ok;
}

function delete_project($id) {
    global $conn;
    ensure_projects_table();
    $id = intval($id);
    if (!$conn || $id <= 0) return false;
    $prefix = DB_PREFIX;
    $stmt = $conn->prepare("DELETE FROM `{$prefix}projects` WHERE id = ?");
    $stmt->bind_param('i', $id);
    $ok = $stmt->execute();
    $stmt->close();
    return $ok;
}

function project_cover($project) {
    if (!empty($project['images'][0])) return $project['images'][0];
    return 'https://images.unsplash.com/photo-1504307651254-35680f356dfd?w=800';
}

function youtube_embed_url($url) {
    $video_id = '';
    if (preg_match('%(?:youtube(?:-nocookie)?\.com/(?:[^/]+/.+/|(?:v|e(?:mbed)?)/|.*[?&]v=)|youtu\.be/)([^"&?/\s]{11})%i', $url, $match)) {
        $video_id = $match[1];
    }
    return $video_id ? "https://www.youtube.com/embed/{$video_id}" : $url;
}
