<?php

header('Content-Type: application/json; charset=utf-8');
header('Access-Control-Allow-Origin: *');
header('Access-Control-Allow-Methods: GET, POST, DELETE, OPTIONS');
header('Access-Control-Allow-Headers: Content-Type');

if ($_SERVER['REQUEST_METHOD'] === 'OPTIONS') {
    http_response_code(200);
    exit();
}
// ===== INCREASE UPLOAD LIMITS =====
@ini_set('upload_max_filesize', '20M');
@ini_set('post_max_size', '25M');
@ini_set('max_execution_time', 60);
@ini_set('memory_limit', '128M');


// ===== DATABASE CONNECTION =====
$host = 'demo.shelltechnologiesbd.com';
$dbname = 'demoshelltech_database';
$username = 'demoshelltech_user';
$password = '-Bd19wi4~jgD7((d';

try {
    $pdo = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8mb4", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    echo json_encode(['error' => 'Database connection failed: ' . $e->getMessage()]);
    exit();
}

$entity = $_GET['entity'] ?? '';
$method = $_SERVER['REQUEST_METHOD'];

// ===== UPLOAD DIRECTORY =====
$uploadDir = __DIR__ . '/uploads/';
if (!is_dir($uploadDir)) {
    mkdir($uploadDir, 0755, true);
}
$htaccess = $uploadDir . '.htaccess';
if (!file_exists($htaccess)) {
    file_put_contents($htaccess, "Options -Indexes\n<FilesMatch \"\\.(php|phtml|php3|php4|php5|php7|pht)$\">\nDeny from all\n</FilesMatch>");
}

// =============================================
// COLUMN DETECTION — Auto-detect which DB columns exist
// Prevents "Unknown column" errors on INSERT/UPDATE/SELECT
// =============================================
$_columnCache = [];

function detectColumns($pdo, $table)
{
    global $_columnCache;
    if (isset($_columnCache[$table])) {
        return $_columnCache[$table];
    }
    try {
        $stmt = $pdo->query("SHOW COLUMNS FROM `$table`");
        $cols = [];
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            $cols[] = $row['Field'];
        }
        $_columnCache[$table] = $cols;
        return $cols;
    } catch (Exception $e) {
        $_columnCache[$table] = [];
        return [];
    }
}

function colExists($pdo, $table, $col)
{
    return in_array($col, detectColumns($pdo, $table));
}

/**
 * Returns the first candidate column that actually exists in the table.
 * Returns null if none found.
 */
function pickColumn($pdo, $table, $candidates)
{
    $cols = detectColumns($pdo, $table);
    foreach ($candidates as $c) {
        if (in_array($c, $cols)) {
            return $c;
        }
    }
    return null;
}

/**
 * Returns all candidate columns that exist in the table.
 * Used for dual-column writes (img + image_url).
 */
function pickColumns($pdo, $table, $candidates)
{
    $cols = detectColumns($pdo, $table);
    return array_values(array_intersect($candidates, $cols));
}

// ===== ROUTING =====
switch ($method) {
    case 'GET':
        handleGet($pdo, $entity);
        break;
    case 'POST':
        handlePost($pdo, $entity, $uploadDir);
        break;
    case 'DELETE':
        handleDelete($pdo, $entity);
        break;
    default:
        echo json_encode(['error' => 'Method not allowed']);
}

// =============================================
// CONFIG: Valid entities for CRUD
// =============================================
function getValidCrudEntities()
{
    return ['trainings', 'testings', 'notices', 'officers', 'trainees', 'equipment',
            'gallery', 'videos', 'facilities', 'sliders', 'employees', 'enrollments'];
}

// =============================================
// CONFIG: SELECT columns — built dynamically using detected columns
// =============================================
function getSelectColumns($pdo, $entity)
{
    switch ($entity) {
        case 'trainings':
            $descCol  = pickColumn($pdo, 'trainings', ['desc', 'description']) ?? 'description';
            $imgCol   = pickColumn($pdo, 'trainings', ['img', 'image_url']) ?? 'img';
            return "id, title, `$descCol` AS `desc`, category, status,
                    start_date AS `start`, end_date AS `end`, fee, duration,
                    `$imgCol` AS `img`, capacity, venue";

        case 'testings':
            $descCol   = pickColumn($pdo, 'testings', ['desc', 'description']) ?? 'description';
            $testTypeCol = pickColumn($pdo, 'testings', ['testType', 'test_type']) ?? 'testType';
            $imgCol    = pickColumn($pdo, 'testings', ['img', 'image_url']) ?? 'img';
            return "id, title, `$descCol` AS `desc`,
                    `$testTypeCol` AS `testType`,
                    turnaround, fee, `$imgCol` AS `img`, standard";

        case 'notices':
            $dateCol = pickColumn($pdo, 'notices', ['notice_date', 'date']) ?? 'notice_date';
            return "id, title, content,
            DATE_FORMAT(`$dateCol`, '%Y-%m-%d') AS `date`,
            type, category";

        case 'officers':
            $imgCol = pickColumn($pdo, 'officers', ['img', 'image_url']) ?? 'img';
            return "id, name, designation, office, phone, mobile, email,
                    `$imgCol` AS `img`, display_order, department";

        case 'employees':
            $imgCol      = pickColumn($pdo, 'employees', ['img', 'image_url', 'photo']) ?? 'img';
            $joinDateCol = pickColumn($pdo, 'employees', ['joinDate', 'join_date', 'joining_date']) ?? 'joinDate';
            $activeCol   = pickColumn($pdo, 'employees', ['active', 'is_active', 'status']) ?? 'active';
            return "id, name, designation, department, email, phone,
            `$imgCol` AS `img`,
            `$joinDateCol` AS `joinDate`,
            `$activeCol` AS `active`";

        case 'trainees':
            $orgCol = pickColumn($pdo, 'trainees', ['org', 'organization']) ?? 'organization';
            return "id, user_id, name, email, phone,
            `$orgCol` AS `org`,
            designation, status";

        case 'equipment':
            $descCol = pickColumn($pdo, 'equipment', ['desc', 'description']) ?? 'desc';
            $imgCol  = pickColumn($pdo, 'equipment', ['img', 'image_url']) ?? 'img';
            return "id, name, capacity, standard,
                    `$imgCol` AS `img`,
                    `$descCol` AS `desc`";

        case 'gallery':
            $imgCol = pickColumn($pdo, 'gallery', ['img', 'image_url']) ?? 'img';
            return "id, title, category, `$imgCol` AS `img`, `date`";

        case 'videos':
            $thumbCol = pickColumn($pdo, 'videos', ['thumb', 'thumbnail_url']) ?? 'thumb';
            $descCol  = pickColumn($pdo, 'videos', ['desc', 'description']) ?? 'desc';
            return "id, title, url, `$thumbCol` AS `thumb`,
                    category, `$descCol` AS `desc`, duration";

        case 'facilities':
            $descCol = pickColumn($pdo, 'facilities', ['desc', 'description']) ?? 'description';
            $imgCol  = pickColumn($pdo, 'facilities', ['img', 'image_url']) ?? 'img';
            return "id, type, title, `$descCol` AS `desc`,
                    `$imgCol` AS `img`, capacity, area, features";

        case 'sliders':
            $btnTextCol = pickColumn($pdo, 'sliders', ['btnText', 'button_text']) ?? 'btnText';
            $btnLinkCol = pickColumn($pdo, 'sliders', ['btnLink', 'button_link']) ?? 'btnLink';
            $orderCol   = pickColumn($pdo, 'sliders', ['order', 'display_order']) ?? 'order';
            $activeCol  = pickColumn($pdo, 'sliders', ['active', 'is_active']) ?? 'active';
            $imgCol     = pickColumn($pdo, 'sliders', ['img', 'image_url']) ?? 'img';
            return "id, title, subtitle,
                    `$btnTextCol` AS `btnText`,
                    `$btnLinkCol` AS `btnLink`,
                    `$orderCol` AS `order`,
                    `$activeCol` AS `active`,
                    `$imgCol` AS `img`";

        case 'enrollments':
            return "id, entity_type, entity_id, name, phone, email, organization, status, created_at";

        default:
            return "*";
    }
}

// =============================================
// CONFIG: ORDER BY — uses detected columns
// =============================================
function getOrderBy($pdo, $entity)
{
    switch ($entity) {
        case 'trainings':
            return 'start_date DESC, id DESC';
        case 'officers':
            return 'display_order ASC, id ASC';
        case 'sliders':
            $orderCol = pickColumn($pdo, 'sliders', ['order', 'display_order']) ?? 'order';
            return "`$orderCol` ASC, id ASC";
        case 'gallery':
            return 'display_order ASC, id DESC';
        case 'videos':
            return 'display_order ASC, id DESC';
        case 'notices':
            $dateCol = pickColumn($pdo, 'notices', ['notice_date', 'date']) ?? 'notice_date';
            return "`$dateCol` DESC, id DESC";
        default:
            return 'id DESC';
    }
}

// =============================================
// CONFIG: Frontend field → DB column mapping
// Uses column detection to map to the CORRECT column name
// =============================================
function getFieldMap($pdo, $entity)
{
    switch ($entity) {
        case 'trainings':
            return [
                'title'    => 'title',
                'desc'     => pickColumn($pdo, 'trainings', ['desc', 'description']) ?? 'desc',
                'category' => 'category',
                'status'   => 'status',
                'start'    => 'start_date',
                'end'      => 'end_date',
                'fee'      => 'fee',
                'duration' => 'duration',
                'capacity' => 'capacity',
                'venue'    => 'venue'
            ];

        case 'testings':
            return [
                'title'      => 'title',
                'desc'       => pickColumn($pdo, 'testings', ['desc', 'description']) ?? 'desc',
                'testType'   => pickColumn($pdo, 'testings', ['testType', 'test_type']) ?? 'testType',
                'turnaround' => 'turnaround',
                'fee'        => 'fee',
                'standard'   => 'standard'
            ];

        case 'notices':
            $map = [
                'title'    => 'title',
                'content'  => 'content',
                'date'     => pickColumn($pdo, 'notices', ['notice_date', 'date']) ?? 'notice_date',
                'type'     => 'type',
                'category' => 'category'
            ];
            if (colExists($pdo, 'notices', 'is_active')) {
                $map['is_active'] = 'is_active';
            }
            return $map;

        case 'officers':
            return [
                'name'          => 'name',
                'designation'   => 'designation',
                'office'        => 'office',
                'phone'         => 'phone',
                'mobile'        => 'mobile',
                'email'         => 'email',
                'display_order' => 'display_order',
                'department'    => 'department'
            ];

        case 'employees':
            return [
                'name'        => 'name',
                'designation' => 'designation',
                'department'  => 'department',
                'email'       => 'email',
                'phone'       => 'phone',
                'joinDate'    => pickColumn($pdo, 'employees', ['joinDate', 'join_date', 'joining_date']) ?? 'joinDate',
                'active'      => pickColumn($pdo, 'employees', ['active', 'is_active', 'status']) ?? 'active'
            ];

        case 'trainees':
            return [
                'name'        => 'name',
                'email'       => 'email',
                'phone'       => 'phone',
                'org'         => pickColumn($pdo, 'trainees', ['org', 'organization']) ?? 'org',
                'designation' => 'designation',
                'status'      => 'status'
            ];

        case 'equipment':
            return [
                'name'     => 'name',
                'capacity' => 'capacity',
                'standard' => 'standard',
                'desc'     => pickColumn($pdo, 'equipment', ['desc', 'description']) ?? 'desc'
            ];

        case 'gallery':
            return [
                'title'    => 'title',
                'category' => 'category',
                'date'     => 'date'
            ];

        case 'videos':
            return [
                'title'    => 'title',
                'url'      => 'url',
                'thumb'    => pickColumn($pdo, 'videos', ['thumb', 'thumbnail_url']) ?? 'thumb',
                'category' => 'category',
                'desc'     => pickColumn($pdo, 'videos', ['desc', 'description']) ?? 'desc',
                'duration' => 'duration'
            ];

        case 'facilities':
            return [
                'type'     => 'type',
                'title'    => 'title',
                'desc'     => pickColumn($pdo, 'facilities', ['desc', 'description']) ?? 'desc',
                'capacity' => 'capacity',
                'area'     => 'area',
                'features' => 'features'
            ];

        case 'sliders':
            return [
                'title'    => 'title',
                'subtitle' => 'subtitle',
                'btnText'  => pickColumn($pdo, 'sliders', ['btnText', 'button_text']) ?? 'btnText',
                'btnLink'  => pickColumn($pdo, 'sliders', ['btnLink', 'button_link']) ?? 'btnLink',
                'order'    => pickColumn($pdo, 'sliders', ['order', 'display_order']) ?? 'order',
                'active'   => pickColumn($pdo, 'sliders', ['active', 'is_active']) ?? 'active'
            ];

        case 'enrollments':
            return [
                'entity_type' => 'entity_type',
                'entity_id'   => 'entity_id',
                'name'        => 'name',
                'phone'       => 'phone',
                'email'       => 'email',
                'organization' => 'organization',
                'status'      => 'status'
            ];

        default:
            return [];
    }
}

// Fields that should become NULL when empty (SQL strict mode safety)
function getNullableFields()
{
    return ['start_date', 'end_date', 'date', 'notice_date', 'joinDate', 'capacity', 'area',
            'display_order', 'order', 'turnaround', 'active', 'duration', 'fee', 'entity_id',
            'desc', 'description', 'organization', 'org'];
}

// =============================================
// GET HANDLER
// =============================================
function handleGet($pdo, $entity)
{
    $id = $_GET['id'] ?? null;

    if ($entity === 'stats') {
        echo json_encode(getStats($pdo));
        return;
    }

    if ($entity === 'settings') {
        handleGetSettings($pdo);
        return;
    }

    $validEntities = getValidCrudEntities();
    if (!in_array($entity, $validEntities)) {
        echo json_encode(['error' => 'Invalid entity']);
        return;
    }

    try {
        $selectColumns = getSelectColumns($pdo, $entity);
        $orderBy = getOrderBy($pdo, $entity);

        // Filter active notices if is_active column exists
        $whereClause = '';
        if ($entity === 'notices' && colExists($pdo, 'notices', 'is_active')) {
            $whereClause = 'WHERE is_active = 1';
        }

        if ($id) {
            $stmt = $pdo->prepare("SELECT $selectColumns FROM `$entity` WHERE id = ?");
            $stmt->execute([$id]);
            $result = $stmt->fetch(PDO::FETCH_ASSOC);
            echo json_encode($result ?: ['error' => 'Not found']);
        } else {
            $stmt = $pdo->query("SELECT $selectColumns FROM `$entity` $whereClause ORDER BY $orderBy");
            echo json_encode($stmt->fetchAll(PDO::FETCH_ASSOC));
        }
    } catch (PDOException $e) {
        echo json_encode(['error' => $e->getMessage()]);
    }
}
// =============================================
// POST HANDLER (FIXED — multi-field update bug)
// =============================================
function handlePost($pdo, $entity, $uploadDir)
{
    $validEntities = array_merge(getValidCrudEntities(), ['contact', 'login', 'register', 'settings']);

    if (!in_array($entity, $validEntities)) {
        echo json_encode(['error' => 'Invalid entity']);
        return;
    }

    if ($entity === 'login') {
        handleLogin($pdo);
        return;
    }
    if ($entity === 'register') {
        handleRegister($pdo);
        return;
    }
    if ($entity === 'contact') {
        handleContact($pdo);
        return;
    }
    if ($entity === 'settings') {
        handleSaveSettings($pdo, $uploadDir);
        return;
    }

    // ===== Parse input data =====
    $id = null;
    $contentType = $_SERVER['CONTENT_TYPE'] ?? '';
    $data = [];

    if (strpos($contentType, 'multipart/form-data') !== false) {
        $data = $_POST;
        $id = $data['id'] ?? null;
    } elseif (strpos($contentType, 'application/json') !== false) {
        $raw = file_get_contents('php://input');
        $data = json_decode($raw, true) ?: [];
        $id = $data['id'] ?? null;
    } else {
        $data = $_POST;
        $id = $data['id'] ?? null;
    }

    if ($id === '' || $id === '0' || $id === 'null') {
        $id = null;
    }
    if ($id !== null) {
        $id = intval($id);
    }

    // ===== FACILITIES UPSERT by type =====
    if ($entity === 'facilities' && empty($id)) {
        $checkType = $data['type'] ?? null;
        if ($checkType && $checkType !== '') {
            try {
                $stmt = $pdo->prepare("SELECT id FROM facilities WHERE type = ?");
                $stmt->execute([$checkType]);
                $existing = $stmt->fetch(PDO::FETCH_ASSOC);
                if ($existing) {
                    $id = intval($existing['id']);
                }
            } catch (PDOException $e) {
                error_log("Facility check error: " . $e->getMessage());
            }
        }
    }

    // ===== Handle image upload (FIXED with error reporting) =====
    $uploadResult = handleImageUpload($uploadDir);
    $imagePath = $uploadResult['path'];
    $uploadError = $uploadResult['error'];
    $isNewImage = false;

    if ($imagePath !== null) {
        $isNewImage = true;
    } elseif (isset($data['img']) && !empty($data['img'])) {
        if (strpos($data['img'], 'data:') === 0) {
            $savedPath = saveBase64Image($data['img'], $uploadDir);
            if ($savedPath !== null) {
                $imagePath = $savedPath;
                $isNewImage = true;
            } else {
                $uploadError = 'Failed to process image data';
            }
        }
    }
    unset($data['img']);

    // আপলোড ত্রুটি থাকলে এরর রিটার্ন করুন
    if ($uploadError !== null) {
        echo json_encode(['error' => 'Image upload failed: ' . $uploadError]);
        return;
    }

    // ===== Map frontend keys to DB columns =====
    $mappedData = mapPostData($pdo, $entity, $data);

    // ===== Sync dual columns (FIXED) =====
    // যখন desc/description এর একটাতে লেখা হয়, অন্যটাতেও লেখা হবে
    syncDualColumns($pdo, $entity, $mappedData);

    // Default values for new records
    if ($entity === 'notices' && !$id && colExists($pdo, 'notices', 'is_active')) {
        if (!isset($mappedData['is_active']) || $mappedData['is_active'] === '' || $mappedData['is_active'] === null) {
            $mappedData['is_active'] = 1;
        }
    }

    // ===== Detect which image columns exist =====
    $imgColumns = pickColumns($pdo, $entity, ['img', 'image_url']);

    // ===== Build and execute SQL =====
    try {
        $nullableFields = getNullableFields();

        if ($id) {
            // ==================== UPDATE ====================
            $fields = [];
            $values = [];

            // পুরানো ছবি ফাইল ডিলিট করুন শুধু তখনই যখন সত্যিই নতুন ছবি আপলোড হয়েছে
            if ($isNewImage && !empty($imgColumns)) {
                try {
                    $colList = implode(',', array_map(function ($c) { return "`$c`"; }, $imgColumns));
                    $stmt = $pdo->prepare("SELECT $colList FROM `$entity` WHERE id = ?");
                    $stmt->execute([$id]);
                    $oldRow = $stmt->fetch(PDO::FETCH_ASSOC);
                    if ($oldRow) {
                        foreach ($imgColumns as $col) {
                            if (!empty($oldRow[$col])) {
                                $oldFile = __DIR__ . '/' . $oldRow[$col];
                                if (file_exists($oldFile) && strpos($oldRow[$col], 'uploads/') === 0) {
                                    @unlink($oldFile);
                                }
                            }
                        }
                    }
                } catch (PDOException $e) {
                    // Ignore
                }
            }

            foreach ($mappedData as $key => $value) {
                if (!preg_match('/^[a-zA-Z_][a-zA-Z0-9_]*$/', $key)) {
                    continue;
                }
                if (!colExists($pdo, $entity, $key)) {
                    continue;
                }
                $fields[] = "`$key` = ?";
                $values[] = ($value === '' && in_array($key, $nullableFields)) ? null : $value;
            }

            // ইমেজ কলাম আপডেট শুধু তখনই যখন নতুন ছবি আছে
            if ($isNewImage && $imagePath !== null) {
                foreach ($imgColumns as $col) {
                    // চেক করুন এই কলাম ইতিমধ্যে fields এ আছে কিনা
                    $fieldStr = "`$col` = ?";
                    if (!in_array($fieldStr, $fields)) {
                        $fields[] = $fieldStr;
                        $values[] = $imagePath;
                    }
                }
            }

            if (empty($fields)) {
                echo json_encode(['success' => true, 'message' => 'Nothing to update']);
                return;
            }

            $values[] = $id;
            $sql = "UPDATE `$entity` SET " . implode(', ', $fields) . " WHERE id = ?";
            $stmt = $pdo->prepare($sql);
            $stmt->execute($values);

            echo json_encode(['success' => true, 'message' => 'Updated successfully']);

        } else {
            // ==================== INSERT ====================
            $columns = [];
            $placeholders = [];
            $values = [];

            foreach ($mappedData as $key => $value) {
                if (!preg_match('/^[a-zA-Z_][a-zA-Z0-9_]*$/', $key)) {
                    continue;
                }
                if (!colExists($pdo, $entity, $key)) {
                    continue;
                }
                if ($value === '' || $value === null) {
                    continue;
                }
                $columns[] = "`$key`";
                $placeholders[] = "?";
                $values[] = $value;
            }

            // ইমেজ পাথ সব existing ইমেজ কলামে লিখুন
            if ($isNewImage && $imagePath !== null) {
                foreach ($imgColumns as $col) {
                    if (!in_array("`$col`", $columns)) {
                        $columns[] = "`$col`";
                        $placeholders[] = "?";
                        $values[] = $imagePath;
                    }
                }
            }

            if (empty($columns)) {
                echo json_encode(['error' => 'No data to insert']);
                return;
            }

            $sql = "INSERT INTO `$entity` (" . implode(', ', $columns) . ") VALUES (" . implode(', ', $placeholders) . ")";
            $stmt = $pdo->prepare($sql);
            $stmt->execute($values);

            echo json_encode(['success' => true, 'id' => $pdo->lastInsertId(), 'message' => 'Created successfully']);
        }
    } catch (PDOException $e) {
        echo json_encode(['error' => $e->getMessage()]);
    }
}


// =============================================
// Map frontend field names to DB columns
// No more sync blocks — field map already resolves to correct column
// =============================================
function mapPostData($pdo, $entity, $data)
{
    $map = getFieldMap($pdo, $entity);
    $mapped = [];
    $seen = [];

    foreach ($data as $key => $value) {
        if ($key === 'id') {
            continue;
        }

        if (isset($map[$key])) {
            $dbKey = $map[$key];

            // Prevent duplicate column writes
            if (isset($seen[$dbKey])) {
                continue;
            }
            $seen[$dbKey] = true;

            $mapped[$dbKey] = $value;
        }
    }

    return $mapped;
}

// =============================================
// SYNC DUAL COLUMNS — desc↔description, img↔image_url etc.
// যখন একটা কলামে ভ্যালু লেখা হয়, ডুয়াল কলামেও লেখা হবে
// =============================================
function syncDualColumns($pdo, $entity, &$mappedData)
{
    $syncPairs = [
        ['desc', 'description'],
        ['img', 'image_url'],
        ['testType', 'test_type'],
        ['btnText', 'button_text'],
        ['btnLink', 'button_link'],
        ['org', 'organization'],
        ['thumb', 'thumbnail_url'],
        ['joinDate', 'join_date', 'joining_date'],
        ['active', 'is_active'],
    ];

    foreach ($syncPairs as $pair) {
        $foundKey = null;
        $foundValue = null;

        // mappedData তে পেয়ারের কোন কলামে ভ্যালু আছে খুঁজুন
        foreach ($pair as $col) {
            if (isset($mappedData[$col])) {
                $foundKey = $col;
                $foundValue = $mappedData[$col];
                break;
            }
        }

        if ($foundKey !== null) {
            // বাকি সব কলামেও সেই ভ্যালু লিখুন যদি টেবিলে থাকে
            foreach ($pair as $col) {
                if ($col !== $foundKey && colExists($pdo, $entity, $col) && !isset($mappedData[$col])) {
                    $mappedData[$col] = $foundValue;
                }
            }
        }
    }
}

// =============================================
// DELETE HANDLER
// =============================================
function handleDelete($pdo, $entity)
{
    $validEntities = getValidCrudEntities();

    if (!in_array($entity, $validEntities)) {
        echo json_encode(['error' => 'Invalid entity']);
        return;
    }

    $id = $_GET['id'] ?? null;
    if (!$id) {
        $raw = file_get_contents('php://input');
        $data = json_decode($raw, true) ?: [];
        $id = $data['id'] ?? null;
    }

    if (!$id) {
        echo json_encode(['error' => 'ID required']);
        return;
    }

    try {
        // Delete associated image files from any existing image columns
        $imgColumns = pickColumns($pdo, $entity, ['img', 'image_url']);
        if (!empty($imgColumns)) {
            try {
                $colList = implode(',', array_map(function ($c) { return "`$c`"; }, $imgColumns));
                $stmt = $pdo->prepare("SELECT $colList FROM `$entity` WHERE id = ?");
                $stmt->execute([$id]);
                $row = $stmt->fetch(PDO::FETCH_ASSOC);

                if ($row) {
                    foreach ($imgColumns as $col) {
                        if (!empty($row[$col])) {
                            $filePath = __DIR__ . '/' . $row[$col];
                            if (file_exists($filePath) && strpos($row[$col], 'uploads/') === 0) {
                                unlink($filePath);
                            }
                        }
                    }
                }
            } catch (PDOException $e) {
                // Ignore
            }
        }

        $stmt = $pdo->prepare("DELETE FROM `$entity` WHERE id = ?");
        $stmt->execute([$id]);
        echo json_encode(['success' => true]);
    } catch (PDOException $e) {
        echo json_encode(['error' => $e->getMessage()]);
    }
}

// =============================================
// STATS
// =============================================
function getStats($pdo)
{
    $stats = [];
    $tables = [
        'trainings' => 'trainings', 'testings' => 'testings', 'notices' => 'notices',
        'officers' => 'officers', 'trainees' => 'trainees', 'equipment' => 'equipment',
        'gallery' => 'gallery', 'videos' => 'videos', 'facilities' => 'facilities',
        'sliders' => 'sliders', 'employees' => 'employees'
    ];
    foreach ($tables as $key => $table) {
        try {
            $stmt = $pdo->query("SELECT COUNT(*) as c FROM `$table`");
            $stats[$key] = (int)$stmt->fetch(PDO::FETCH_ASSOC)['c'];
        } catch (Exception $e) {
            $stats[$key] = 0;
        }
    }
    return $stats;
}

// =============================================
// SETTINGS: GET
// =============================================
function handleGetSettings($pdo)
{
    try {
        $stmt = $pdo->query("SELECT * FROM settings WHERE id = 1");
        $settings = $stmt->fetch(PDO::FETCH_ASSOC);

        $stmt2 = $pdo->query("SELECT setting_key, setting_value FROM site_settings");
        $keyValues = [];
        while ($row = $stmt2->fetch(PDO::FETCH_ASSOC)) {
            $keyValues[$row['setting_key']] = $row['setting_value'];
        }

        echo json_encode([
            'success' => true,
            'settings' => $settings ?: [],
            'site_settings' => $keyValues
        ]);
    } catch (PDOException $e) {
        echo json_encode(['error' => $e->getMessage()]);
    }
}

// =============================================
// SETTINGS: SAVE
// =============================================
function handleSaveSettings($pdo, $uploadDir)
{
    $data = $_POST;

    try {
        $fields = [];
        $values = [];
        $allowed = ['site_name', 'phone', 'email', 'address', 'facebook', 'youtube'];

        foreach ($allowed as $col) {
            if (isset($data[$col])) {
                $fields[] = "`$col` = ?";
                $values[] = $data[$col];
            }
        }

        // FIXED: শুধু নতুন আপলোড বা base64 কে লোগো হিসেবে ধরুন
        $logoPath = handleImageUpload($uploadDir);
        $isNewLogo = false;

        if ($logoPath !== null) {
            $isNewLogo = true;
        } elseif (isset($data['img']) && !empty($data['img'])) {
            if (strpos($data['img'], 'data:') === 0) {
                $logoPath = saveBase64Image($data['img'], $uploadDir);
                if ($logoPath !== null) {
                    $isNewLogo = true;
                }
            }
            // পুরানো path ignore করুন
        }

        if ($isNewLogo && $logoPath !== null) {
            $fields[] = "`logo` = ?";
            $values[] = $logoPath;
        }

        if (!empty($fields)) {
            $values[] = 1;
            $sql = "UPDATE settings SET " . implode(', ', $fields) . " WHERE id = ?";
            $stmt = $pdo->prepare($sql);
            $stmt->execute($values);
        }

        $kvMap = [
            'site_name' => 'academy_name', 'phone' => 'phone', 'email' => 'email',
            'address' => 'address', 'facebook' => 'facebook_url', 'youtube' => 'youtube_url'
        ];

        foreach ($kvMap as $dataKey => $settingKey) {
            if (isset($data[$dataKey])) {
                $stmt = $pdo->prepare(
                    "INSERT INTO site_settings (setting_key, setting_value) VALUES (?, ?)
                     ON DUPLICATE KEY UPDATE setting_value = ?"
                );
                $stmt->execute([$settingKey, $data[$dataKey], $data[$dataKey]]);
            }
        }

        echo json_encode(['success' => true, 'message' => 'Settings saved successfully']);
    } catch (PDOException $e) {
        echo json_encode(['error' => $e->getMessage()]);
    }
}

// =============================================
// LOGIN
// =============================================
function handleLogin($pdo)
{
    $raw = file_get_contents('php://input');
    $data = json_decode($raw, true) ?: [];
    $email = trim($data['email'] ?? '');
    $password = $data['password'] ?? '';

    if (empty($email) || empty($password)) {
        echo json_encode(['success' => false, 'message' => 'Email and password required']);
        return;
    }

    // ===== Ensure default admin exists =====
    ensureDefaultAdmin($pdo);

    // ===== Check admins table =====
    try {
        $stmt = $pdo->prepare("SELECT * FROM admins WHERE email = ?");
        $stmt->execute([$email]);
        $admin = $stmt->fetch(PDO::FETCH_ASSOC);
    } catch (PDOException $e) {
        $admin = false;
    }

    if ($admin) {
        $passwordValid = false;

        // Method 1: Proper bcrypt hash verification
        if (password_verify($password, $admin['password'])) {
            $passwordValid = true;
        }
        // Method 2: Plain text fallback
        elseif ($admin['password'] === $password) {
            $passwordValid = true;
            try {
                $newHash = password_hash($password, PASSWORD_DEFAULT);
                $stmt = $pdo->prepare("UPDATE admins SET password = ? WHERE id = ?");
                $stmt->execute([$newHash, $admin['id']]);
            } catch (PDOException $e) {
            }
        }
        // Method 3: md5 fallback
        elseif (md5($password) === $admin['password']) {
            $passwordValid = true;
            try {
                $newHash = password_hash($password, PASSWORD_DEFAULT);
                $stmt = $pdo->prepare("UPDATE admins SET password = ? WHERE id = ?");
                $stmt->execute([$newHash, $admin['id']]);
            } catch (PDOException $e) {
            }
        }

        if ($passwordValid) {
            unset($admin['password']);
            $admin['role'] = $admin['role'] ?? 'admin';
            $admin['organization'] = $admin['organization'] ?? 'PWD';
            $admin['org'] = $admin['org'] ?? $admin['organization'] ?? 'PWD';
            $admin['phone'] = $admin['phone'] ?? '';
            $admin['designation'] = $admin['designation'] ?? 'Administrator';
            echo json_encode(['success' => true, 'user' => $admin]);
            return;
        }
    }

    // ===== Check users table =====
    try {
        $stmt = $pdo->prepare("SELECT * FROM users WHERE email = ?");
        $stmt->execute([$email]);
        $user = $stmt->fetch(PDO::FETCH_ASSOC);
    } catch (PDOException $e) {
        $user = false;
    }

    if ($user) {
        $passwordValid = false;

        if (password_verify($password, $user['password'])) {
            $passwordValid = true;
        } elseif ($user['password'] === $password) {
            $passwordValid = true;
            try {
                $newHash = password_hash($password, PASSWORD_DEFAULT);
                $stmt = $pdo->prepare("UPDATE users SET password = ? WHERE id = ?");
                $stmt->execute([$newHash, $user['id']]);
            } catch (PDOException $e) {
            }
        } elseif (md5($password) === $user['password']) {
            $passwordValid = true;
            try {
                $newHash = password_hash($password, PASSWORD_DEFAULT);
                $stmt = $pdo->prepare("UPDATE users SET password = ? WHERE id = ?");
                $stmt->execute([$newHash, $user['id']]);
            } catch (PDOException $e) {
            }
        }

        if ($passwordValid) {
            unset($user['password']);
            $user['org'] = $user['organization'] ?? $user['org'] ?? '';
            echo json_encode(['success' => true, 'user' => $user]);
            return;
        }
    }

    echo json_encode(['success' => false, 'message' => 'Invalid credentials']);
}

// =============================================
// AUTO-CREATE DEFAULT ADMIN
// =============================================
function ensureDefaultAdmin($pdo)
{
    try {
        $stmt = $pdo->query("SHOW TABLES LIKE 'admins'");
        $tableExists = $stmt->fetch();

        if (!$tableExists) {
            $pdo->exec("CREATE TABLE IF NOT EXISTS `admins` (
                `id` INT AUTO_INCREMENT PRIMARY KEY,
                `name` VARCHAR(255) NOT NULL DEFAULT 'Administrator',
                `email` VARCHAR(255) NOT NULL UNIQUE,
                `password` VARCHAR(255) NOT NULL,
                `role` VARCHAR(50) DEFAULT 'admin',
                `phone` VARCHAR(50) DEFAULT '',
                `designation` VARCHAR(255) DEFAULT 'Administrator',
                `organization` VARCHAR(255) DEFAULT 'PWD',
                `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
        }

        $stmt = $pdo->query("SELECT COUNT(*) as c FROM admins");
        $count = (int)$stmt->fetch(PDO::FETCH_ASSOC)['c'];

        if ($count === 0) {
            $hashedPassword = password_hash('admin123', PASSWORD_DEFAULT);
            $stmt = $pdo->prepare("INSERT INTO admins (name, email, password, role, designation, organization) VALUES (?, ?, ?, ?, ?, ?)");
            $stmt->execute([
                'System Administrator',
                'admin@pwd.training.gov.bd',
                $hashedPassword,
                'admin',
                'Administrator',
                'PWD'
            ]);
        }
    } catch (PDOException $e) {
        error_log("ensureDefaultAdmin error: " . $e->getMessage());
    }
}

// =============================================
// REGISTER
// =============================================
function handleRegister($pdo)
{
    $raw = file_get_contents('php://input');
    $data = json_decode($raw, true) ?: [];

    $name = trim($data['name'] ?? '');
    $email = trim($data['email'] ?? '');
    $password = $data['password'] ?? '';
    $phone = trim($data['phone'] ?? '');
    $org = trim($data['org'] ?? '');
    $designation = trim($data['designation'] ?? '');

    if (empty($email) || empty($password)) {
        echo json_encode(['error' => 'Email and password required']);
        return;
    }

    if (strlen($password) < 6) {
        echo json_encode(['error' => 'Password must be at least 6 characters']);
        return;
    }

    // Check if email already exists
    try {
        $stmt = $pdo->prepare("SELECT id FROM users WHERE email = ?");
        $stmt->execute([$email]);
        if ($stmt->fetch()) {
            echo json_encode(['error' => 'Email already exists']);
            return;
        }
    } catch (PDOException $e) {
        echo json_encode(['error' => 'Database error checking email']);
        return;
    }

    $hashed = password_hash($password, PASSWORD_DEFAULT);

    try {
        $pdo->beginTransaction();

        // Insert into users table
        $stmtUser = $pdo->prepare("INSERT INTO users (name, email, password, phone, organization, designation, role, status) VALUES (?, ?, ?, ?, ?, ?, 'user', 'active')");
        $stmtUser->execute([$name, $email, $hashed, $phone, $org, $designation]);
        $userId = $pdo->lastInsertId();

        // Insert into trainees table
        $stmtTrainee = $pdo->prepare("INSERT INTO trainees (user_id, name, email, phone, organization, designation, status, enrollment_date) VALUES (?, ?, ?, ?, ?, ?, 'active', CURDATE())");
        $stmtTrainee->execute([$userId, $name, $email, $phone, $org, $designation]);

        $pdo->commit();

        $userData = [
            'id' => (int)$userId,
            'name' => $name,
            'email' => $email,
            'phone' => $phone,
            'organization' => $org,
            'org' => $org,
            'designation' => $designation,
            'role' => 'user',
            'status' => 'active'
        ];

        echo json_encode(['success' => true, 'user' => $userData, 'message' => 'Registration successful']);

    } catch (PDOException $e) {
        $pdo->rollBack();

        if ($e->getCode() == 23000) {
            echo json_encode(['error' => 'Email already exists']);
        } else {
            echo json_encode(['error' => 'Registration failed: ' . $e->getMessage()]);
        }
    }
}

// =============================================
// CONTACT
// =============================================
function handleContact($pdo)
{
    $raw = file_get_contents('php://input');
    $data = json_decode($raw, true) ?: [];

    $name = trim($data['name'] ?? '');
    $email = trim($data['email'] ?? '');
    $subject = trim($data['subject'] ?? '');
    $message = trim($data['message'] ?? '');

    if (empty($name) || empty($email) || empty($message)) {
        echo json_encode(['error' => 'Name, email and message are required']);
        return;
    }

    try {
        $stmt = $pdo->prepare("INSERT INTO contact_messages (name, email, subject, message) VALUES (?, ?, ?, ?)");
        $stmt->execute([$name, $email, $subject, $message]);
        echo json_encode(['success' => true, 'message' => 'Message sent successfully']);
    } catch (PDOException $e) {
        echo json_encode(['error' => $e->getMessage()]);
    }
}

// =============================================
// FILE UPLOAD HELPERS
// =============================================
function handleImageUpload($uploadDir)
{
    // ডিরেক্টরি লেখাযোগ্য কিনা চেক করুন
    if (!is_dir($uploadDir)) {
        mkdir($uploadDir, 0755, true);
    }
    if (!is_writable($uploadDir)) {
        @chmod($uploadDir, 0777);
        if (!is_writable($uploadDir)) {
            error_log("UPLOAD ERROR: Directory not writable: $uploadDir");
            return ['path' => null, 'error' => 'Upload directory is not writable. Contact admin.'];
        }
    }

    if (!isset($_FILES['img']) || $_FILES['img']['error'] === UPLOAD_ERR_NO_FILE) {
        return ['path' => null, 'error' => null]; // No file = not an error
    }

    // সুনির্দিষ্ট এরর কোড চেক
    $errorCode = $_FILES['img']['error'];
    if ($errorCode !== UPLOAD_ERR_OK) {
        $errorMessages = [
            UPLOAD_ERR_INI_SIZE   => 'File exceeds server upload_max_filesize limit',
            UPLOAD_ERR_FORM_SIZE  => 'File exceeds form MAX_FILE_SIZE limit',
            UPLOAD_ERR_PARTIAL    => 'File was only partially uploaded',
            UPLOAD_ERR_NO_TMP_DIR => 'Missing temporary folder on server',
            UPLOAD_ERR_CANT_WRITE => 'Failed to write file to disk',
            UPLOAD_ERR_EXTENSION  => 'Upload blocked by PHP extension',
        ];
        $msg = $errorMessages[$errorCode] ?? "Unknown upload error (code: $errorCode)";
        error_log("UPLOAD ERROR: $msg");
        return ['path' => null, 'error' => $msg];
    }

    $file = $_FILES['img'];

    // সাইজ চেক (10MB পর্যন্ত)
    if ($file['size'] > 10 * 1024 * 1024) {
        return ['path' => null, 'error' => 'Image must be smaller than 10MB'];
    }

    // MIME টাইপ চেক
    $finfo = new finfo(FILEINFO_MIME_TYPE);
    $mime = $finfo->file($file['tmp_name']);
    $allowed = ['image/jpeg', 'image/png', 'image/gif', 'image/webp'];

    if (!in_array($mime, $allowed)) {
        return ['path' => null, 'error' => 'Invalid file type. Only JPG, PNG, GIF, WebP allowed.'];
    }

    // এক্সটেনশন চেক
    $ext = strtolower(pathinfo($file['name'], PATHINFO_EXTENSION));
    if (!in_array($ext, ['jpg', 'jpeg', 'png', 'gif', 'webp'])) {
        $ext = 'jpg';
    }

    $filename = bin2hex(random_bytes(8)) . '_' . time() . '.' . $ext;
    $filepath = $uploadDir . $filename;

    if (move_uploaded_file($file['tmp_name'], $filepath)) {
        return ['path' => 'uploads/' . $filename, 'error' => null];
    }

    error_log("UPLOAD ERROR: move_uploaded_file failed for $filepath");
    return ['path' => null, 'error' => 'Failed to save file. Check directory permissions.'];
}

function saveBase64Image($base64String, $uploadDir)
{
    if (!is_writable($uploadDir)) {
        @chmod($uploadDir, 0777);
    }

    if (preg_match('/^data:image\/(\w+);base64,/', $base64String, $type)) {
        $imageType = strtolower($type[1]);
        $allowed = ['jpeg', 'jpg', 'png', 'gif', 'webp'];

        if (!in_array($imageType, $allowed)) {
            return null;
        }

        $base64String = substr($base64String, strpos($base64String, ',') + 1);
        $imageData = base64_decode($base64String);

        if ($imageData === false) {
            error_log("BASE64 ERROR: base64_decode failed");
            return null;
        }

        if (strlen($imageData) > 10 * 1024 * 1024) {
            error_log("BASE64 ERROR: Image data too large (" . strlen($imageData) . " bytes)");
            return null;
        }

        $ext = $imageType === 'jpeg' ? 'jpg' : $imageType;
        $filename = bin2hex(random_bytes(8)) . '_' . time() . '.' . $ext;
        $filepath = $uploadDir . $filename;

        if (file_put_contents($filepath, $imageData)) {
            return 'uploads/' . $filename;
        }

        error_log("BASE64 ERROR: file_put_contents failed for $filepath");
    }
    return null;
}
