<?php
/**
 * GlowSpace Database Helper Functions
 * Replaces JSON-based gsRead/gsWrite with MySQL queries
 */

require_once __DIR__ . '/../config.php';

class Database {
    private static $instance = null;
    private $pdo;

    private function __construct() {
        try {
            $this->pdo = new PDO(
                "mysql:host=" . DB_HOST . ";dbname=" . DB_NAME . ";charset=utf8mb4",
                DB_USER,
                DB_PASS,
                [
                    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                    PDO::ATTR_EMULATE_PREPARES => false
                ]
            );
        } catch (PDOException $e) {
            error_log("Database connection failed: " . $e->getMessage());
            die("Database connection failed. Please check your configuration.");
        }
    }

    public static function getInstance() {
        if (self::$instance === null) {
            self::$instance = new self();
        }
        return self::$instance;
    }

    public function getConnection() {
        return $this->pdo;
    }

    public function query($sql, $params = []) {
        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute($params);
            return $stmt;
        } catch (PDOException $e) {
            error_log("Database query error: " . $e->getMessage());
            return false;
        }
    }

    public function fetchAll($sql, $params = []) {
        $stmt = $this->query($sql, $params);
        return $stmt ? $stmt->fetchAll() : [];
    }

    public function fetchOne($sql, $params = []) {
        $stmt = $this->query($sql, $params);
        return $stmt ? $stmt->fetch() : null;
    }

    public function insert($table, $data) {
        $columns = array_keys($data);
        $placeholders = array_fill(0, count($columns), '?');
        
        $sql = "INSERT INTO $table (" . implode(', ', $columns) . ") 
                VALUES (" . implode(', ', $placeholders) . ")";
        
        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute(array_values($data));
            return $this->pdo->lastInsertId();
        } catch (PDOException $e) {
            error_log("Insert error: " . $e->getMessage());
            return false;
        }
    }

    public function update($table, $data, $where, $whereParams = []) {
        $setParts = [];
        foreach (array_keys($data) as $column) {
            $setParts[] = "$column = ?";
        }
        
        $sql = "UPDATE $table SET " . implode(', ', $setParts) . " WHERE $where";
        
        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute(array_merge(array_values($data), $whereParams));
            return $stmt->rowCount();
        } catch (PDOException $e) {
            error_log("Update error: " . $e->getMessage());
            return false;
        }
    }

    public function delete($table, $where, $params = []) {
        $sql = "DELETE FROM $table WHERE $where";
        
        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute($params);
            return $stmt->rowCount();
        } catch (PDOException $e) {
            error_log("Delete error: " . $e->getMessage());
            return false;
        }
    }
}

// Global database instance
function db() {
    return Database::getInstance()->getConnection();
}

// ============================================
// PRODUCT FUNCTIONS (Replaces gsRead/gsWrite)
// ============================================

function getProducts($filters = []) {
    $sql = "SELECT p.*, 
            (SELECT GROUP_CONCAT(image_path ORDER BY image_order SEPARATOR ',') 
             FROM product_images 
             WHERE product_id = p.product_id) as images
            FROM products p 
            WHERE 1=1";

    $params = [];

    if (!empty($filters['category'])) {
        $sql .= " AND p.category_id = ?";
        $params[] = $filters['category'];
    }

    if (!empty($filters['status'])) {
        $sql .= " AND p.status = ?";
        $params[] = $filters['status'];
    }

    if (!empty($filters['featured'])) {
        $sql .= " AND p.featured = 1";
    }

    if (!empty($filters['trending'])) {
        $sql .= " AND p.trending = 1";
    }

    if (!empty($filters['best_seller'])) {
        $sql .= " AND p.best_seller = 1";
    }

    if (!empty($filters['search'])) {
        $sql .= " AND (
            p.title LIKE ? 
            OR p.description LIKE ? 
            OR p.features LIKE ?
        )";

        $searchTerm = "%" . $filters['search'] . "%";

        $params[] = $searchTerm;
        $params[] = $searchTerm;
        $params[] = $searchTerm;
    }

    $sql .= " ORDER BY p.created_at DESC";

    if (!empty($filters['limit'])) {
        $sql .= " LIMIT " . (int)$filters['limit'];
    }

    $stmt = db()->prepare($sql);
    $stmt->execute($params);
    $products = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    // Decode images and process
    foreach ($products as &$product) {
        if (!empty($product['images'])) {
            $product['images'] = explode(',', $product['images']);
        } else {
            $product['images'] = [];
        }
        
        // Decode HTML entities
        $product['title'] = html_entity_decode($product['title'], ENT_QUOTES | ENT_HTML5, 'UTF-8');
        $product['description'] = html_entity_decode($product['description'], ENT_QUOTES | ENT_HTML5, 'UTF-8');
        $product['features'] = html_entity_decode($product['features'], ENT_QUOTES | ENT_HTML5, 'UTF-8');
        $product['sizes'] = html_entity_decode($product['sizes'], ENT_QUOTES | ENT_HTML5, 'UTF-8');
        
        // Decode specifications if JSON
        if (!empty($product['specifications'])) {
            $product['specifications'] = json_decode($product['specifications'], true);
        }
    }

    return $products;
}

function getProductById($productId) {
    $sql = "SELECT p.*, 
            (SELECT GROUP_CONCAT(image_path ORDER BY image_order SEPARATOR ',') 
             FROM product_images WHERE product_id = p.product_id) as images
            FROM products p WHERE product_id = ?";
    $stmt = db()->prepare($sql);
    $stmt->execute([$productId]);
    $product = $stmt->fetch(PDO::FETCH_ASSOC);
    
    if ($product) {
        if (!empty($product['images'])) {
            $product['images'] = explode(',', $product['images']);
        } else {
            $product['images'] = [];
        }
        
        // Decode HTML entities
        $product['title'] = html_entity_decode($product['title'], ENT_QUOTES | ENT_HTML5, 'UTF-8');
        $product['description'] = html_entity_decode($product['description'], ENT_QUOTES | ENT_HTML5, 'UTF-8');
        $product['features'] = html_entity_decode($product['features'], ENT_QUOTES | ENT_HTML5, 'UTF-8');
        $product['sizes'] = html_entity_decode($product['sizes'], ENT_QUOTES | ENT_HTML5, 'UTF-8');
        
        // Decode specifications if JSON
        if (!empty($product['specifications'])) {
            $product['specifications'] = json_decode($product['specifications'], true);
        }
        
        // Increment view count
        $stmt = db()->prepare("UPDATE products SET views = views + 1 WHERE product_id = ?");
        $stmt->execute([$productId]);
    }

    return $product;
}

function getProductBySlug($slug) {
    $sql = "SELECT product_id FROM products WHERE slug = ?";
    $stmt = db()->prepare($sql);
    $stmt->execute([$slug]);
    $result = $stmt->fetch(PDO::FETCH_ASSOC);
    return $result ? getProductById($result['product_id']) : null;
}

// ============================================
// CATEGORY FUNCTIONS
// ============================================

function getCategories($activeOnly = true) {
    $sql = "SELECT * FROM categories";

    if ($activeOnly) {
        $sql .= " WHERE status = 1";
    }

    $sql .= " ORDER BY name ASC";

    $stmt = db()->query($sql);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function getCategoryById($categoryId) {
    $sql = "SELECT * FROM categories WHERE category_id = ?";
    $stmt = db()->prepare($sql);
    $stmt->execute([$categoryId]);
    return $stmt->fetch(PDO::FETCH_ASSOC);
}

// ============================================
// ORDER FUNCTIONS
// ============================================

function getOrders($filters = []) {
    $sql = "SELECT o.*, 
            (SELECT COUNT(*) FROM order_items WHERE order_id = o.order_id) as item_count
            FROM orders o WHERE 1=1";
    $params = [];

    if (!empty($filters['user_id'])) {
        $sql .= " AND user_id = ?";
        $params[] = $filters['user_id'];
    }

    if (!empty($filters['status'])) {
        $sql .= " AND status = ?";
        $params[] = $filters['status'];
    }

    if (!empty($filters['order_id'])) {
        $sql .= " AND order_id = ?";
        $params[] = $filters['order_id'];
    }

    $sql .= " ORDER BY created_at DESC";

    if (!empty($filters['limit'])) {
        $sql .= " LIMIT ?";
        $params[] = (int)$filters['limit'];
    }

    $stmt = db()->prepare($sql);
    $stmt->execute($params);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function getOrderById($orderId) {
    $sql = "SELECT o.* FROM orders o WHERE order_id = ?";
    $stmt = db()->prepare($sql);
    $stmt->execute([$orderId]);
    $order = $stmt->fetch(PDO::FETCH_ASSOC);
    
    if ($order) {
        // Get order items
        $sql = "SELECT * FROM order_items WHERE order_id = ?";
        $stmt = db()->prepare($sql);
        $stmt->execute([$orderId]);
        $order['items'] = $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    return $order;
}

function createOrder($orderData, $items) {
    $db = Database::getInstance();
    
    // Insert order
    $orderId = $db->insert('orders', $orderData);
    
    if ($orderId) {
        // Insert order items
        foreach ($items as $item) {
            $item['order_id'] = $orderData['order_id'];
            $db->insert('order_items', $item);
        }
        
        return true;
    }
    
    return false;
}

function updateOrderStatus($orderId, $status, $trackingNumber = null) {
    $data = ['status' => $status];
    if ($trackingNumber !== null) {
        $data['tracking_number'] = $trackingNumber;
    }
    
    $stmt = db()->prepare("UPDATE orders SET status = ?, tracking_number = COALESCE(?, tracking_number) WHERE order_id = ?");
    return $stmt->execute([$status, $trackingNumber, $orderId]);
}

// ============================================
// LANDING PAGE FUNCTIONS
// ============================================

function getLandingPages($enabledOnly = true) {
    $sql = "SELECT * FROM landing_pages";
    if ($enabledOnly) {
        $sql .= " WHERE enabled = 1";
    }
    $sql .= " ORDER BY created_at DESC";
    $stmt = db()->query($sql);
    $pages = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    // Decode JSON fields
    foreach ($pages as &$page) {
        $page['headline'] = html_entity_decode($page['headline'], ENT_QUOTES | ENT_HTML5, 'UTF-8');
        $page['description'] = html_entity_decode($page['description'], ENT_QUOTES | ENT_HTML5, 'UTF-8');
        $page['cta_text'] = html_entity_decode($page['cta_text'], ENT_QUOTES | ENT_HTML5, 'UTF-8');
        
        if (!empty($page['benefits'])) {
            $page['benefits'] = json_decode($page['benefits'], true);
        }
        if (!empty($page['testimonials'])) {
            $page['testimonials'] = json_decode($page['testimonials'], true);
        }
        if (!empty($page['faq'])) {
            $page['faq'] = json_decode($page['faq'], true);
        }
    }
    
    return $pages;
}

function getLandingPageBySlug($slug) {
    $sql = "SELECT * FROM landing_pages WHERE slug = ?";
    $stmt = db()->prepare($sql);
    $stmt->execute([$slug]);
    $page = $stmt->fetch(PDO::FETCH_ASSOC);
    
    if ($page) {
        $page['headline'] = html_entity_decode($page['headline'], ENT_QUOTES | ENT_HTML5, 'UTF-8');
        $page['description'] = html_entity_decode($page['description'], ENT_QUOTES | ENT_HTML5, 'UTF-8');
        $page['cta_text'] = html_entity_decode($page['cta_text'], ENT_QUOTES | ENT_HTML5, 'UTF-8');
        
        if (!empty($page['benefits'])) {
            $page['benefits'] = json_decode($page['benefits'], true);
        }
        if (!empty($page['testimonials'])) {
            $page['testimonials'] = json_decode($page['testimonials'], true);
        }
        if (!empty($page['faq'])) {
            $page['faq'] = json_decode($page['faq'], true);
        }
    }
    
    return $page;
}

function incrementLandingPageStats($slug, $field) {
    $allowedFields = ['views', 'clicks', 'add_to_cart', 'orders'];
    if (in_array($field, $allowedFields)) {
        $stmt = db()->prepare("UPDATE landing_pages SET $field = $field + 1 WHERE slug = ?");
        $stmt->execute([$slug]);
    }
}

// ============================================
// BANNER FUNCTIONS
// ============================================

function getBanners($activeOnly = true) {
    $sql = "SELECT * FROM banners";
    if ($activeOnly) {
        $sql .= " WHERE status = 1";
    }
    $sql .= " ORDER BY display_order ASC";
    $stmt = db()->query($sql);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

// ============================================
// COUPON FUNCTIONS
// ============================================

function getCoupons($activeOnly = true) {
    $sql = "SELECT * FROM coupons";
    if ($activeOnly) {
        $sql .= " WHERE status = 1 AND (expiry_date IS NULL OR expiry_date > NOW())";
    }
    $stmt = db()->query($sql);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function getCouponByCode($code) {
    $sql = "SELECT * FROM coupons WHERE code = ? AND status = 1 
            AND (expiry_date IS NULL OR expiry_date > NOW())";
    $stmt = db()->prepare($sql);
    $stmt->execute([$code]);
    return $stmt->fetch(PDO::FETCH_ASSOC);
}

// ============================================
// SETTINGS FUNCTIONS
// ============================================

function getSettings() {
    $sql = "SELECT * FROM settings";
    
    $stmt = db()->query($sql);
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    $settings = [];

    foreach ($rows as $row) {
        $value = $row['setting_value'];
        
        // Convert based on type
        switch ($row['setting_type']) {
            case 'boolean':
                $value = filter_var($value, FILTER_VALIDATE_BOOLEAN);
                break;

            case 'number':
                $value = is_numeric($value) 
                    ? (strpos($value, '.') !== false ? (float)$value : (int)$value) 
                    : $value;
                break;

            case 'json':
                $value = json_decode($value, true);
                break;
        }
        
        $settings[$row['setting_key']] = $value;
    }
    
    return $settings;
}

function getSetting($key, $default = null) {
    $settings = getSettings();
    return $settings[$key] ?? $default;
}

function updateSetting($key, $value, $type = 'string') {
    $sql = "INSERT INTO settings (setting_key, setting_value, setting_type, updated_at) 
            VALUES (?, ?, ?, NOW()) 
            ON DUPLICATE KEY UPDATE setting_value = VALUES(setting_value), 
            setting_type = VALUES(setting_type), updated_at = NOW()";
    
    $stmt = db()->prepare($sql);
    return $stmt->execute([$key, $value, $type]);
}

// ============================================
// REVIEW FUNCTIONS
// ============================================

function getReviews($productId = null, $approvedOnly = true) {
    $sql = "SELECT r.* FROM reviews r WHERE 1=1";
    $params = [];

    if ($productId) {
        $sql .= " AND product_id = ?";
        $params[] = $productId;
    }

    if ($approvedOnly) {
        $sql .= " AND status = 1";
    }

    $sql .= " ORDER BY created_at DESC";

    $stmt = db()->prepare($sql);
    $stmt->execute($params);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

// ============================================
// ACTIVITY LOG FUNCTIONS
// ============================================

function logActivity($action, $detail = '', $userId = null, $adminId = null) {
    $data = [
        'action' => $action,
        'detail' => $detail,
        'user_id' => $userId,
        'admin_id' => $adminId,
        'ip_address' => $_SERVER['REMOTE_ADDR'] ?? '',
        'user_agent' => $_SERVER['HTTP_USER_AGENT'] ?? ''
    ];
    
    return Database::getInstance()->insert('activity_logs', $data);
}

// ============================================
// UTILITY FUNCTIONS
// ============================================

function sanitize($str) {
    $str = html_entity_decode((string)$str, ENT_QUOTES, 'UTF-8');
    return htmlspecialchars(strip_tags(trim($str)), ENT_QUOTES, 'UTF-8');
}

function generateId($prefix = 'GS') {
    return $prefix . '-' . strtoupper(substr(md5(uniqid(mt_rand(), true)), 0, 8));
}

function imgUrl($path) {
    if (empty($path)) return '';
    $path = str_replace('\\', '/', (string)$path);
    if (preg_match('#^(https?:)?//#i', $path)) return $path;
    $pos = strpos($path, 'uploads/');
    if ($pos !== false) return substr($path, $pos);
    return ltrim($path, '/');
}

function validIcon($icon, $default = 'fas fa-box') {
    $icon = trim((string)$icon);
    if ($icon === '') return $default;
    if (preg_match('/^(fa[srlbdt]?|fa-solid|fa-regular|fa-brands|fa-light|fa-duotone|fa-thin)(\s+fa-[a-z0-9-]+)+$/i', $icon)) {
        return $icon;
    }
    return $default;
}

function redirect($url) {
    header('Location: ' . $url);
    exit;
}