<?php
require_once __DIR__ . '/../config/database.php';

function getTotalSalesToday() {
    $pdo = getDB();
    $stmt = $pdo->query("SELECT COALESCE(SUM(total_amount), 0) FROM sales_invoices WHERE DATE(invoice_date) = CURDATE() AND invoice_status != 'Cancelled'");
    return $stmt->fetchColumn();
}

function getTotalRepairIncomeToday() {
    $pdo = getDB();
    $stmt = $pdo->query("SELECT COALESCE(SUM(amount), 0) FROM payments WHERE payment_type = 'repair' AND DATE(payment_date) = CURDATE()");
    return $stmt->fetchColumn();
}

function getPendingRepairs() {
    $pdo = getDB();
    $stmt = $pdo->query("SELECT COUNT(*) FROM repair_jobs WHERE repair_status NOT IN ('Delivered','Cancelled','Cannot Repair')");
    return $stmt->fetchColumn();
}

function getCompletedRepairs() {
    $pdo = getDB();
    $stmt = $pdo->query("SELECT COUNT(*) FROM repair_jobs WHERE repair_status = 'Delivered'");
    return $stmt->fetchColumn();
}

function getLowStockCount() {
    $pdo = getDB();
    $stmt = $pdo->query("SELECT COUNT(*) FROM products WHERE current_stock <= low_stock_qty AND status = 1");
    return $stmt->fetchColumn();
}

function getTotalCustomers() {
    $pdo = getDB();
    $stmt = $pdo->query("SELECT COUNT(*) FROM customers WHERE status = 1");
    return $stmt->fetchColumn();
}

function getTotalInvoices() {
    $pdo = getDB();
    $stmt = $pdo->query("SELECT COUNT(*) FROM sales_invoices WHERE invoice_status != 'Cancelled'");
    return $stmt->fetchColumn();
}

function getPaymentSummary() {
    $pdo = getDB();
    $stmt = $pdo->query("SELECT payment_method, COALESCE(SUM(amount), 0) as total FROM payments WHERE DATE(payment_date) = CURDATE() GROUP BY payment_method");
    return $stmt->fetchAll();
}

function getMonthlySales($year = null) {
    if (!$year) $year = date('Y');
    $pdo = getDB();
    $stmt = $pdo->prepare("
        SELECT MONTH(invoice_date) as month, COALESCE(SUM(total_amount), 0) as total
        FROM sales_invoices WHERE YEAR(invoice_date) = ? AND invoice_status != 'Cancelled'
        GROUP BY MONTH(invoice_date) ORDER BY month
    ");
    $stmt->execute([$year]);
    return $stmt->fetchAll();
}

function getRepairStatusStats() {
    $pdo = getDB();
    $stmt = $pdo->query("SELECT repair_status as status, COUNT(*) as count FROM repair_jobs GROUP BY repair_status");
    return $stmt->fetchAll();
}

function getTopSellingProducts($limit = 10) {
    $pdo = getDB();
    $stmt = $pdo->prepare("
        SELECT p.product_name as name, SUM(sii.quantity) as total_qty, SUM(sii.total_price) as total_amount
        FROM sales_invoice_items sii
        JOIN products p ON sii.product_id = p.id
        JOIN sales_invoices si ON sii.sales_invoice_id = si.id
        WHERE si.invoice_status != 'Cancelled' AND MONTH(si.invoice_date) = MONTH(CURDATE()) AND YEAR(si.invoice_date) = YEAR(CURDATE())
        GROUP BY p.id ORDER BY total_qty DESC LIMIT ?
    ");
    $stmt->execute([$limit]);
    return $stmt->fetchAll();
}

function getTechnicianPerformance() {
    $pdo = getDB();
    $stmt = $pdo->query("
        SELECT u.name as full_name, COUNT(rj.id) as total_jobs,
               SUM(CASE WHEN rj.repair_status = 'Delivered' THEN 1 ELSE 0 END) as completed_jobs,
               COALESCE(SUM(rj.final_amount), 0) as total_value
        FROM users u
        JOIN repair_jobs rj ON u.id = rj.assigned_technician_id
        WHERE u.role_id = 4
        GROUP BY u.id ORDER BY completed_jobs DESC
    ");
    return $stmt->fetchAll();
}

function getStatusBadge($status) {
    $map = [
        'Pending' => 'badge-pending',
        'Completed' => 'badge-completed',
        'Cancelled' => 'badge-cancelled',
        'Delivered' => 'badge-delivered',
        'Received' => 'badge-pending',
        'Checking' => 'badge-waiting',
        'Waiting for Customer Approval' => 'badge-waiting',
        'Waiting for Parts' => 'badge-waiting',
        'Repairing' => 'badge-repairing',
        'Cannot Repair' => 'badge-cancelled',
        'Paid' => 'badge-completed',
        'Due' => 'badge-pending',
        'Partial' => 'badge-waiting',
        'Active' => 'badge-completed',
        'Expired' => 'badge-cancelled',
        'Claimed' => 'badge-waiting',
        'Available' => 'badge-completed',
        'Sold' => 'badge-pending',
        'Returned' => 'badge-waiting',
        'Damaged' => 'badge-cancelled',
        'Warranty Claim' => 'badge-waiting',
    ];
    $class = $map[$status] ?? 'badge-primary';
    return "<span class='badge {$class}'>" . htmlspecialchars($status) . "</span>";
}

function createStockMovement($product_id, $movement_type, $reference_type, $reference_id, $quantity_in, $quantity_out, $note = '', $created_by = null) {
    $pdo = getDB();
    $valid_types = ['purchase','sale','repair_usage','return','adjustment','damaged'];
    if (!in_array($movement_type, $valid_types)) return false;

    try {
        $pdo->beginTransaction();

        $stmt = $pdo->prepare("SELECT current_stock FROM products WHERE id = ? FOR UPDATE");
        $stmt->execute([$product_id]);
        $product = $stmt->fetch();

        if (!$product) { $pdo->rollBack(); return false; }

        $current = (int)$product['current_stock'];
        $new_balance = $current + (int)$quantity_in - (int)$quantity_out;

        if ($new_balance < 0) {
            $pdo->rollBack();
            return false;
        }

        $stmt = $pdo->prepare("INSERT INTO stock_movements (product_id, movement_type, reference_type, reference_id, quantity_in, quantity_out, balance_after, note, created_by) VALUES (?,?,?,?,?,?,?,?,?)");
        $stmt->execute([$product_id, $movement_type, $reference_type, $reference_id, (int)$quantity_in, (int)$quantity_out, $new_balance, $note, $created_by]);

        $stmt = $pdo->prepare("UPDATE products SET current_stock = ? WHERE id = ?");
        $stmt->execute([$new_balance, $product_id]);

        $pdo->commit();
        return true;
    } catch (Exception $e) {
        $pdo->rollBack();
        return false;
    }
}

function getStockBadge($current_stock, $low_stock_qty) {
    if ($current_stock <= 0) return '<span class="badge badge-cancelled">Out of Stock</span>';
    if ($current_stock <= $low_stock_qty) return '<span class="badge badge-waiting">Low Stock</span>';
    return '<span class="badge badge-completed">In Stock</span>';
}

if (!function_exists('getSetting')) {
    function getSetting($key, $default = '') {
        $pdo = getDB();
        try {
            $stmt = $pdo->prepare("SELECT setting_value FROM settings WHERE setting_key = ? LIMIT 1");
            $stmt->execute([$key]);
            $row = $stmt->fetch(PDO::FETCH_ASSOC);
            if ($row && isset($row['setting_value'])) {
                return $row['setting_value'];
            }
        } catch (Exception $e) {
            // Fall through to default
        }
        return $default;
    }
}

function getShopLogoUrl() {
    $logo = getSetting('shop_logo', '');
    if (!empty($logo)) {
        $logoPath = BASE_PATH . $logo;
        if (file_exists($logoPath)) {
            return BASE_URL . $logo;
        }
    }
    return BASE_URL . 'assets/images/logo.png';
}

function generateUniqueBarcode($product_type) {
    $pdo = getDB();

    $prefix_map = [
        'phone' => 'PHN',
        'accessory' => 'ACC',
        'spare_part' => 'SPR',
    ];
    $prefix = $prefix_map[$product_type] ?? 'PRD';
    $date_part = date('Ymd');

    $max_attempts = 20;
    for ($i = 0; $i < $max_attempts; $i++) {
        $random = str_pad(random_int(1, 99999), 5, '0', STR_PAD_LEFT);
        $barcode = $prefix . $date_part . $random;

        $stmt = $pdo->prepare("SELECT COUNT(*) FROM products WHERE barcode = ?");
        $stmt->execute([$barcode]);
        if ($stmt->fetchColumn() == 0) {
            return $barcode;
        }
    }

    $barcode = $prefix . $date_part . '00001';
    $stmt = $pdo->prepare("SELECT COUNT(*) FROM products WHERE barcode = ?");
    $stmt->execute([$barcode]);
    if ($stmt->fetchColumn() == 0) return $barcode;

    $suffix = 1;
    while ($suffix < 1000) {
        $bc = $prefix . $date_part . str_pad($suffix, 5, '0', STR_PAD_LEFT);
        $stmt = $pdo->prepare("SELECT COUNT(*) FROM products WHERE barcode = ?");
        $stmt->execute([$bc]);
        if ($stmt->fetchColumn() == 0) return $bc;
        $suffix++;
    }

    return $prefix . $date_part . uniqid();
}
