<?php
/**
 * Helper Functions
 * Kayal Kalmeen Kadai - Fish Stall Management System
 */

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

// Log user activity
function logActivity($user_id, $action, $table_name = null, $record_id = null) {
    $query = "INSERT INTO activity_logs (user_id, action, table_name, record_id, ip_address, user_agent) 
              VALUES (?, ?, ?, ?, ?, ?)";
    
    $ip = getUserIP();
    $user_agent = getUserAgent();
    
    executeQuery($query, "issiis", [$user_id, $action, $table_name, $record_id, $ip, $user_agent]);
}

// Get user details by ID
function getUserById($user_id) {
    $query = "SELECT user_id, username, full_name, email, phone, role, status 
              FROM users WHERE user_id = ?";
    return fetchSingle($query, "i", [$user_id]);
}

// Get user preferences
function getUserPreferences($user_id) {
    $query = "SELECT primary_color, secondary_color, font_size 
              FROM user_preferences WHERE user_id = ?";
    $prefs = fetchSingle($query, "i", [$user_id]);
    
    if (!$prefs) {
        // Return defaults
        return [
            'primary_color' => '#008B8B',
            'secondary_color' => '#20B2AA',
            'font_size' => 16
        ];
    }
    
    return $prefs;
}

// Save user preferences
function saveUserPreferences($user_id, $primary_color, $secondary_color, $font_size) {
    // Check if preferences exist
    $existing = fetchSingle("SELECT pref_id FROM user_preferences WHERE user_id = ?", "i", [$user_id]);
    
    if ($existing) {
        $query = "UPDATE user_preferences 
                  SET primary_color = ?, secondary_color = ?, font_size = ? 
                  WHERE user_id = ?";
        executeQuery($query, "ssii", [$primary_color, $secondary_color, $font_size, $user_id]);
    } else {
        $query = "INSERT INTO user_preferences (user_id, primary_color, secondary_color, font_size) 
                  VALUES (?, ?, ?, ?)";
        executeQuery($query, "issi", [$user_id, $primary_color, $secondary_color, $font_size]);
    }
}

// Get all active fish types
function getActiveFishTypes() {
    $query = "SELECT fish_id, fish_name, fish_name_tamil 
              FROM fish_types WHERE status = 'active' ORDER BY fish_name";
    return fetchAll($query);
}

// Get pending payment amounts for all suppliers (including manager)
function getAllSuppliersPendingAmounts() {
    $query = "
        SELECT 
            u.user_id,
            u.full_name as supplier_name,
            u.role,
            COALESCE(SUM(s.total_amount), 0) as total_supply,
            COALESCE(SUM(ps.amount), 0) as total_paid,
            (COALESCE(SUM(s.total_amount), 0) - COALESCE(SUM(ps.amount), 0)) as pending_amount
        FROM users u
        LEFT JOIN supply s ON u.user_id = s.supplier_id
        LEFT JOIN payment_settlements ps ON u.user_id = ps.supplier_id
        WHERE (u.role = 'supplier' OR u.role = 'manager') AND u.status = 'active'
        GROUP BY u.user_id, u.full_name, u.role
        HAVING pending_amount > 0 OR total_supply > 0
        ORDER BY u.full_name
    ";
    
    return fetchAll($query);
}

// Check if manager already exists
function managerExists($exclude_user_id = null) {
    $query = "SELECT COUNT(*) as count FROM users WHERE role = 'manager' AND status = 'active'";
    $params = [];
    $types = "";
    
    if ($exclude_user_id !== null) {
        $query .= " AND user_id != ?";
        $types = "i";
        $params = [$exclude_user_id];
    }
    
    $result = fetchSingle($query, $types, $params);
    return $result && $result['count'] > 0;
}

// Get expense categories
function getExpenseCategories() {
    $query = "SELECT category_id, category_name 
              FROM expense_categories WHERE status = 'active' ORDER BY category_name";
    return fetchAll($query);
}

// Get expense subcategories by category
function getExpenseSubcategories($category_id) {
    $query = "SELECT subcategory_id, subcategory_name 
              FROM expense_subcategories 
              WHERE category_id = ? AND status = 'active' 
              ORDER BY subcategory_name";
    return fetchAll($query, "i", [$category_id]);
}

// Calculate daily sales total
function getDailySalesTotal($date) {
    $query = "SELECT COALESCE(SUM(total_amount), 0) as total 
              FROM sales WHERE sale_date = ?";
    $result = fetchSingle($query, "s", [$date]);
    return $result ? floatval($result['total']) : 0;
}

// Calculate daily expenses total
function getDailyExpensesTotal($date) {
    $query = "SELECT COALESCE(SUM(total_amount), 0) as total 
              FROM expenses WHERE expense_date = ?";
    $result = fetchSingle($query, "s", [$date]);
    return $result ? floatval($result['total']) : 0;
}

// Get fish stock status
function getFishStockStatus() {
    $query = "SELECT * FROM fish_stock_status ORDER BY fish_name";
    return fetchAll($query);
}

// Get sales report by date range
function getSalesReport($start_date, $end_date, $user_id = null) {
    $query = "SELECT 
                s.sale_id, s.sale_date, f.fish_name, f.fish_name_tamil,
                s.weight_kg, s.rate_per_kg, s.total_amount,
                s.customer_name, s.payment_mode, u.full_name as added_by_name
              FROM sales s
              JOIN fish_types f ON s.fish_id = f.fish_id
              JOIN users u ON s.added_by = u.user_id
              WHERE s.sale_date BETWEEN ? AND ?";
    
    $params = [$start_date, $end_date];
    $types = "ss";
    
    if ($user_id !== null) {
        $query .= " AND s.added_by = ?";
        $types .= "i";
        $params[] = $user_id;
    }
    
    $query .= " ORDER BY s.sale_date DESC, s.sale_id DESC";
    
    return fetchAll($query, $types, $params);
}

// Get supply report by date range
function getSupplyReport($start_date, $end_date, $supplier_id = null) {
    $query = "SELECT 
                sup.supply_id, sup.supply_date, f.fish_name, f.fish_name_tamil,
                sup.weight_kg, sup.rate_per_kg, sup.total_amount,
                u.full_name as supplier_name, adder.full_name as added_by_name
              FROM supply sup
              JOIN fish_types f ON sup.fish_id = f.fish_id
              JOIN users u ON sup.supplier_id = u.user_id
              JOIN users adder ON sup.added_by = adder.user_id
              WHERE sup.supply_date BETWEEN ? AND ?";
    
    $params = [$start_date, $end_date];
    $types = "ss";
    
    if ($supplier_id !== null) {
        $query .= " AND sup.supplier_id = ?";
        $types .= "i";
        $params[] = $supplier_id;
    }
    
    $query .= " ORDER BY sup.supply_date DESC, sup.supply_id DESC";
    
    return fetchAll($query, $types, $params);
}

// Get expense report by date range
function getExpenseReport($start_date, $end_date, $user_id = null) {
    $query = "SELECT 
                e.expense_id, e.expense_date, c.category_name,
                COALESCE(sc.subcategory_name, '-') as subcategory_name,
                e.description, e.units, e.unit_cost, e.total_amount,
                e.payment_mode, u.full_name as added_by_name
              FROM expenses e
              JOIN expense_categories c ON e.category_id = c.category_id
              LEFT JOIN expense_subcategories sc ON e.subcategory_id = sc.subcategory_id
              JOIN users u ON e.added_by = u.user_id
              WHERE e.expense_date BETWEEN ? AND ?";
    
    $params = [$start_date, $end_date];
    $types = "ss";
    
    if ($user_id !== null) {
        $query .= " AND e.added_by = ?";
        $types .= "i";
        $params[] = $user_id;
    }
    
    $query .= " ORDER BY e.expense_date DESC, e.expense_id DESC";
    
    return fetchAll($query, $types, $params);
}

// Calculate profit/loss for date range
function calculateProfitLoss($start_date, $end_date) {
    // Get total sales
    $sales_query = "SELECT COALESCE(SUM(total_amount), 0) as total FROM sales WHERE sale_date BETWEEN ? AND ?";
    $sales_result = fetchSingle($sales_query, "ss", [$start_date, $end_date]);
    $total_sales = $sales_result ? floatval($sales_result['total']) : 0;
    
    // Get total supply cost
    $supply_query = "SELECT COALESCE(SUM(total_amount), 0) as total FROM supply WHERE supply_date BETWEEN ? AND ?";
    $supply_result = fetchSingle($supply_query, "ss", [$start_date, $end_date]);
    $total_supply = $supply_result ? floatval($supply_result['total']) : 0;
    
    // Get total expenses
    $expense_query = "SELECT COALESCE(SUM(total_amount), 0) as total FROM expenses WHERE expense_date BETWEEN ? AND ?";
    $expense_result = fetchSingle($expense_query, "ss", [$start_date, $end_date]);
    $total_expenses = $expense_result ? floatval($expense_result['total']) : 0;
    
    $total_cost = $total_supply + $total_expenses;
    $profit_loss = $total_sales - $total_cost;
    
    return [
        'total_sales' => $total_sales,
        'total_supply' => $total_supply,
        'total_expenses' => $total_expenses,
        'total_cost' => $total_cost,
        'profit_loss' => $profit_loss
    ];
}

// Generate pagination HTML
function generatePagination($current_page, $total_records, $records_per_page, $base_url) {
    $total_pages = ceil($total_records / $records_per_page);
    
    if ($total_pages <= 1) {
        return '';
    }
    
    $html = '<div class="pagination">';
    
    // Previous button
    if ($current_page > 1) {
        $prev_page = $current_page - 1;
        $html .= '<a href="' . $base_url . '&page=' . $prev_page . '" class="page-link">« Previous</a>';
    }
    
    // Page numbers
    $start_page = max(1, $current_page - 2);
    $end_page = min($total_pages, $current_page + 2);
    
    if ($start_page > 1) {
        $html .= '<a href="' . $base_url . '&page=1" class="page-link">1</a>';
        if ($start_page > 2) {
            $html .= '<span class="page-dots">...</span>';
        }
    }
    
    for ($i = $start_page; $i <= $end_page; $i++) {
        $active = ($i == $current_page) ? ' active' : '';
        $html .= '<a href="' . $base_url . '&page=' . $i . '" class="page-link' . $active . '">' . $i . '</a>';
    }
    
    if ($end_page < $total_pages) {
        if ($end_page < $total_pages - 1) {
            $html .= '<span class="page-dots">...</span>';
        }
        $html .= '<a href="' . $base_url . '&page=' . $total_pages . '" class="page-link">' . $total_pages . '</a>';
    }
    
    // Next button
    if ($current_page < $total_pages) {
        $next_page = $current_page + 1;
        $html .= '<a href="' . $base_url . '&page=' . $next_page . '" class="page-link">Next »</a>';
    }
    
    $html .= '</div>';
    
    return $html;
}

?>