<?php
session_start();
include 'layouts/dbconn.php';

// Set timezone to East Africa Time
date_default_timezone_set('Africa/Nairobi');

// Check if user is logged in
if (!isset($_SESSION['user_id'])) {
    header('Location: login.php');
    exit();
}

// Fetch user details
$user_id = $_SESSION['user_id'];
$sql_user_details = "SELECT u.*, ur.role_name FROM users u 
                     INNER JOIN user_roles ur ON u.role_id = ur.role_id 
                     WHERE u.user_id = ?";
$stmt_user = $conn->prepare($sql_user_details);
$stmt_user->bind_param("i", $user_id);
$stmt_user->execute();
$user_details = $stmt_user->get_result()->fetch_assoc();

if ($user_details) {
    $username = $user_details['username'];
    $role_name = $user_details['role_name'];
    $full_name = $user_details['full_name'];
    $email = $user_details['email'];
}

// Get search parameters
$search = $_GET['search'] ?? '';
$company_filter = $_GET['company_filter'] ?? '';
$status_filter = $_GET['status_filter'] ?? '';
$date_from = $_GET['date_from'] ?? '';
$date_to = $_GET['date_to'] ?? '';
$company_type = $_GET['type'] ?? '';
$page = $_GET['page'] ?? 1;
$limit = 20;
$offset = ($page - 1) * $limit;

// Build query with filters for COMPANY category type only
$where_conditions = array("ec.category_type = 'COMPANY'");
$params = array();
$param_types = "";

if (!empty($search)) {
    $where_conditions[] = "(e.expense_title LIKE ? OR e.description LIKE ? OR e.expense_number LIKE ? OR e.vendor_name LIKE ?)";
    $search_param = "%$search%";
    $params = array_merge($params, [$search_param, $search_param, $search_param, $search_param]);
    $param_types .= "ssss";
}

if (!empty($company_filter)) {
    $where_conditions[] = "e.category_id = ?";
    $params[] = $company_filter;
    $param_types .= "i";
}

// Filter by company type if specified
if (!empty($company_type)) {
    switch($company_type) {
        case 'online':
            $where_conditions[] = "ec.category_name LIKE '%Online%'";
            break;
        case 'mobile':
            $where_conditions[] = "ec.category_name LIKE '%Mobile%'";
            break;
        case 'office':
            $where_conditions[] = "(ec.category_name LIKE '%Office%' OR ec.category_name LIKE '%Rent%')";
            break;
        case 'salary':
            $where_conditions[] = "(ec.category_name LIKE '%Salary%' OR ec.category_name LIKE '%Salaries%' OR ec.category_name LIKE '%Benefits%')";
            break;
        case 'marketing':
            $where_conditions[] = "(ec.category_name LIKE '%Marketing%' OR ec.category_name LIKE '%Advertising%')";
            break;
    }
}

if (!empty($status_filter)) {
    $where_conditions[] = "e.status = ?";
    $params[] = $status_filter;
    $param_types .= "s";
}

if (!empty($date_from)) {
    $where_conditions[] = "e.expense_date >= ?";
    $params[] = $date_from;
    $param_types .= "s";
}

if (!empty($date_to)) {
    $where_conditions[] = "e.expense_date <= ?";
    $params[] = $date_to;
    $param_types .= "s";
}

$where_clause = implode(" AND ", $where_conditions);

// Get total count for pagination
$count_query = "SELECT COUNT(*) as total FROM expenses e 
                LEFT JOIN expense_categories ec ON e.category_id = ec.category_id 
                WHERE $where_clause";
$count_stmt = $conn->prepare($count_query);
if (!empty($params)) {
    $count_stmt->bind_param($param_types, ...$params);
}
$count_stmt->execute();
$total_expenses = $count_stmt->get_result()->fetch_assoc()['total'];
$total_pages = ceil($total_expenses / $limit);

// Get company expenses with pagination
$query = "SELECT e.*, ec.category_name, ec.category_type, ec.color_code,
                 u_created.full_name as created_by_name,
                 u_approved.full_name as approved_by_name
          FROM expenses e 
          LEFT JOIN expense_categories ec ON e.category_id = ec.category_id 
          LEFT JOIN users u_created ON e.created_by = u_created.user_id 
          LEFT JOIN users u_approved ON e.approved_by = u_approved.user_id 
          WHERE $where_clause
          ORDER BY e.expense_date DESC, e.created_at DESC 
          LIMIT ? OFFSET ?";

$stmt = $conn->prepare($query);
$params[] = $limit;
$params[] = $offset;
$param_types .= "ii";

if (!empty($params)) {
    $stmt->bind_param($param_types, ...$params);
}
$stmt->execute();
$expenses = $stmt->get_result();

// Get company categories for filter dropdown
$company_categories_query = "SELECT * FROM expense_categories WHERE category_type = 'COMPANY' AND is_active = 1 ORDER BY category_name";
$company_categories_result = $conn->query($company_categories_query);

// Get company expense statistics
$stats_query = "SELECT 
    COUNT(*) as total_count,
    SUM(CASE WHEN status = 'PAID' THEN amount ELSE 0 END) as total_paid,
    SUM(CASE WHEN status = 'PENDING' THEN amount ELSE 0 END) as total_pending,
    SUM(CASE WHEN DATE(expense_date) = CURDATE() THEN amount ELSE 0 END) as today_total,
    SUM(CASE WHEN YEAR(expense_date) = YEAR(CURDATE()) AND MONTH(expense_date) = MONTH(CURDATE()) AND status = 'PAID' THEN amount ELSE 0 END) as month_total,
    COUNT(CASE WHEN status = 'PENDING' THEN 1 END) as pending_count
    FROM expenses e 
    LEFT JOIN expense_categories ec ON e.category_id = ec.category_id
    WHERE ec.category_type = 'COMPANY'";
$stats = $conn->query($stats_query)->fetch_assoc();

// Get individual company category totals
$company_totals_query = "SELECT 
    ec.category_name,
    COUNT(e.expense_id) as count,
    SUM(CASE WHEN e.status = 'PAID' THEN e.amount ELSE 0 END) as total_amount,
    SUM(CASE WHEN YEAR(e.expense_date) = YEAR(CURDATE()) AND MONTH(e.expense_date) = MONTH(CURDATE()) AND e.status = 'PAID' THEN e.amount ELSE 0 END) as month_amount
    FROM expense_categories ec
    LEFT JOIN expenses e ON ec.category_id = e.category_id
    WHERE ec.category_type = 'COMPANY' AND ec.is_active = 1
    GROUP BY ec.category_id, ec.category_name
    ORDER BY total_amount DESC";
$company_totals = $conn->query($company_totals_query);
?>

<?php include 'layouts/main.php'; ?>

<head>
    <?php includeFileWithVariables('layouts/title-meta.php', array('title' => 'Company Expenses - Personal Fund Management System')); ?>
    <?php include 'layouts/head-css.php'; ?>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
    
    <style>
        body {
            background-color: #f8fafc;
            font-family: -apple-system, BlinkMacSystemFont, 'Segoe UI', Roboto, 'Helvetica Neue', Arial, sans-serif;
        }
        
        .page-content { padding: 1.5rem; }
        
        .page-header {
            background: linear-gradient(135deg, #3498db 0%, #2980b9 100%);
            color: white;
            border-radius: 12px;
            padding: 2rem;
            margin-bottom: 2rem;
            box-shadow: 0 4px 6px -1px rgba(0, 0, 0, 0.1);
        }
        
        .page-title {
            font-size: 1.75rem;
            font-weight: 600;
            margin-bottom: 0.5rem;
        }
        
        .page-subtitle { opacity: 0.9; margin: 0; }
        
        .stats-row {
            display: grid;
            grid-template-columns: repeat(auto-fit, minmax(200px, 1fr));
            gap: 1.5rem;
            margin-bottom: 2rem;
        }
        
        .stat-card {
            background: white;
            border-radius: 12px;
            padding: 1.5rem;
            box-shadow: 0 1px 3px 0 rgba(0, 0, 0, 0.1);
            border: 1px solid #e2e8f0;
            text-align: center;
        }
        
        .stat-value {
            font-size: 2rem;
            font-weight: 700;
            color: #1e293b;
            margin-bottom: 0.5rem;
        }
        
        .stat-label {
            font-size: 0.875rem;
            color: #64748b;
            text-transform: uppercase;
            letter-spacing: 0.05em;
        }
        
        .quick-filters, .filter-card, .table-card {
            background: white;
            border-radius: 12px;
            padding: 1.5rem;
            box-shadow: 0 1px 3px 0 rgba(0, 0, 0, 0.1);
            border: 1px solid #e2e8f0;
            margin-bottom: 2rem;
        }
        
        .filter-btn {
            padding: 0.5rem 1rem;
            border: 1px solid #e2e8f0;
            border-radius: 6px;
            background: white;
            color: #64748b;
            text-decoration: none;
            margin: 0.25rem;
            display: inline-block;
            font-size: 0.875rem;
            transition: all 0.2s ease;
        }
        
        .filter-btn:hover, .filter-btn.active {
            background: #3498db;
            color: white;
            border-color: #3498db;
        }
        
        .table-header {
            padding: 1.5rem;
            border-bottom: 1px solid #e2e8f0;
            background: #f8fafc;
            display: flex;
            justify-content: space-between;
            align-items: center;
        }
        
        .table-title {
            font-size: 1.125rem;
            font-weight: 600;
            color: #1e293b;
            margin: 0;
        }
        
        .data-table {
            width: 100%;
            border-collapse: collapse;
            margin: 0;
        }
        
        .data-table th {
            background: #f8fafc;
            padding: 1rem;
            text-align: left;
            font-weight: 600;
            font-size: 0.875rem;
            color: #64748b;
            border-bottom: 1px solid #e2e8f0;
        }
        
        .data-table td {
            padding: 1rem;
            border-bottom: 1px solid #f1f5f9;
            font-size: 0.875rem;
        }
        
        .data-table tbody tr:hover { background: #f8fafc; }
        
        .status-badge {
            padding: 0.25rem 0.75rem;
            border-radius: 9999px;
            font-size: 0.75rem;
            font-weight: 600;
            text-transform: uppercase;
            letter-spacing: 0.05em;
        }
        
        .status-draft { background: #f1f5f9; color: #64748b; }
        .status-pending { background: #fef3c7; color: #92400e; }
        .status-approved { background: #dcfce7; color: #166534; }
        .status-paid { background: #dcfce7; color: #166534; }
        .status-rejected { background: #fee2e2; color: #991b1b; }
        
        .payment-badge {
            padding: 0.25rem 0.5rem;
            border-radius: 4px;
            font-size: 0.75rem;
            font-weight: 500;
        }
        
        .payment-cash { background: #dcfce7; color: #166534; }
        .payment-card { background: #ddd6fe; color: #5b21b6; }
        .payment-bank-transfer { background: #e0e7ff; color: #3730a3; }
        .payment-mpesa { background: #fef3c7; color: #92400e; }
        
        .btn-action {
            padding: 0.375rem 0.75rem;
            border-radius: 6px;
            font-size: 0.75rem;
            font-weight: 600;
            text-decoration: none;
            display: inline-block;
            margin: 0 0.25rem;
            transition: all 0.2s ease;
        }
        
        .btn-primary { background: #3b82f6; color: white; }
        .btn-primary:hover { background: #1d4ed8; color: white; }
        .btn-warning { background: #d97706; color: white; }
        .btn-warning:hover { background: #b45309; color: white; }
        .btn-company { background: #3498db; color: white; }
        .btn-company:hover { background: #2980b9; color: white; }
        
        .expense-avatar {
            width: 40px;
            height: 40px;
            border-radius: 8px;
            background: #3498db;
            color: white;
            display: flex;
            align-items: center;
            justify-content: center;
            font-weight: 600;
            font-size: 0.875rem;
        }
        
        .form-control, .form-select {
            border: 1px solid #e2e8f0;
            border-radius: 8px;
            padding: 0.75rem;
            font-size: 0.875rem;
        }
        
        .btn {
            padding: 0.75rem 1.5rem;
            border-radius: 8px;
            font-weight: 600;
            font-size: 0.875rem;
            border: none;
            cursor: pointer;
            transition: all 0.2s ease;
        }
    </style>
</head>

<body>
    <div id="layout-wrapper">
        <?php include 'layouts/menu.php'; ?>
        
        <div class="main-content">
            <div class="page-content">
                <div class="container-fluid">
                    <!-- Page Header -->
                    <div class="page-header">
                        <h1 class="page-title">Company Expenses</h1>
                        <p class="page-subtitle">Track and manage business expenses for online, mobile, and office operations</p>
                    </div>

                    <!-- Stats Row -->
                    <div class="stats-row">
                        <div class="stat-card">
                            <div class="stat-value"><?php echo $stats['total_count'] ?? 0; ?></div>
                            <div class="stat-label">Total Company Expenses</div>
                        </div>
                        <div class="stat-card">
                            <div class="stat-value">KES <?php echo number_format($stats['total_paid'] ?? 0); ?></div>
                            <div class="stat-label">Total Paid</div>
                        </div>
                        <div class="stat-card">
                            <div class="stat-value">KES <?php echo number_format($stats['month_total'] ?? 0); ?></div>
                            <div class="stat-label">This Month</div>
                        </div>
                        <div class="stat-card">
                            <div class="stat-value"><?php echo $stats['pending_count'] ?? 0; ?></div>
                            <div class="stat-label">Pending</div>
                        </div>
                    </div>

                    <!-- Quick Filters -->
                    <div class="quick-filters">
                        <h6 style="margin-bottom: 1rem; color: #1e293b;">Quick Filters by Company Type:</h6>
                        <a href="company_expenses.php" class="filter-btn <?php echo empty($company_type) ? 'active' : ''; ?>">All Company</a>
                        <a href="company_expenses.php?type=online" class="filter-btn <?php echo $company_type === 'online' ? 'active' : ''; ?>">Online Company</a>
                        <a href="company_expenses.php?type=mobile" class="filter-btn <?php echo $company_type === 'mobile' ? 'active' : ''; ?>">Mobile Company</a>
                        <a href="company_expenses.php?type=office" class="filter-btn <?php echo $company_type === 'office' ? 'active' : ''; ?>">Office & Rent</a>
                        <a href="company_expenses.php?type=salary" class="filter-btn <?php echo $company_type === 'salary' ? 'active' : ''; ?>">Salaries & Benefits</a>
                        <a href="company_expenses.php?type=marketing" class="filter-btn <?php echo $company_type === 'marketing' ? 'active' : ''; ?>">Marketing</a>
                    </div>

                    <!-- Filter Card -->
                    <div class="filter-card">
                        <form method="GET" class="row g-3">
                            <div class="col-md-3">
                                <label class="form-label">Search</label>
                                <input type="text" class="form-control" name="search" value="<?php echo htmlspecialchars($search); ?>" placeholder="Search company expenses...">
                            </div>
                            <div class="col-md-3">
                                <label class="form-label">Company Category</label>
                                <select class="form-select" name="company_filter">
                                    <option value="">All Categories</option>
                                    <?php 
                                    $company_categories_result->data_seek(0);
                                    while ($company = $company_categories_result->fetch_assoc()): 
                                    ?>
                                        <option value="<?php echo $company['category_id']; ?>" <?php echo $company_filter == $company['category_id'] ? 'selected' : ''; ?>>
                                            <?php echo htmlspecialchars($company['category_name']); ?>
                                        </option>
                                    <?php endwhile; ?>
                                </select>
                            </div>
                            <div class="col-md-2">
                                <label class="form-label">Status</label>
                                <select class="form-select" name="status_filter">
                                    <option value="">All Status</option>
                                    <option value="PAID" <?php echo $status_filter == 'PAID' ? 'selected' : ''; ?>>Paid</option>
                                    <option value="PENDING" <?php echo $status_filter == 'PENDING' ? 'selected' : ''; ?>>Pending</option>
                                </select>
                            </div>
                            <div class="col-md-2">
                                <label class="form-label">From Date</label>
                                <input type="date" class="form-control" name="date_from" value="<?php echo htmlspecialchars($date_from); ?>">
                            </div>
                            <div class="col-md-2">
                                <label class="form-label">To Date</label>
                                <input type="date" class="form-control" name="date_to" value="<?php echo htmlspecialchars($date_to); ?>">
                            </div>
                            <div class="col-md-12">
                                <button type="submit" class="btn btn-primary">Filter</button>
                                <a href="company_expenses.php" class="btn btn-outline-secondary">Clear</a>
                            </div>
                        </form>
                    </div>

                    <!-- Company Expenses Table -->
                    <div class="table-card">
                        <div class="table-header">
                            <h3 class="table-title">Company Expenses (<?php echo $total_expenses; ?> total)</h3>
                            <div class="d-flex gap-2">
                                <a href="add_expense.php?type=COMPANY" class="btn btn-company">
                                    <i class="ph-plus me-1"></i> Add Company Expense
                                </a>
                            </div>
                        </div>
                        
                        <table class="data-table">
                            <thead>
                                <tr>
                                    <th>Expense Details</th>
                                    <th>Category</th>
                                    <th>Amount</th>
                                    <th>Payment Method</th>
                                    <th>Status</th>
                                    <th>Date</th>
                                    <th>Actions</th>
                                </tr>
                            </thead>
                            <tbody>
                                <?php while ($expense = $expenses->fetch_assoc()): ?>
                                <tr>
                                    <td>
                                        <div class="d-flex align-items-center">
                                            <div class="expense-avatar me-3">CO</div>
                                            <div>
                                                <div style="font-weight: 600; color: #1e293b;">
                                                    <?php echo htmlspecialchars($expense['expense_title']); ?>
                                                </div>
                                                <div style="color: #64748b; font-size: 0.75rem;">
                                                    #<?php echo htmlspecialchars($expense['expense_number']); ?>
                                                </div>
                                                <?php if ($expense['vendor_name']): ?>
                                                    <div style="color: #3498db; font-size: 0.75rem;">
                                                        Vendor: <?php echo htmlspecialchars($expense['vendor_name']); ?>
                                                    </div>
                                                <?php endif; ?>
                                            </div>
                                        </div>
                                    </td>
                                    <td>
                                        <span style="font-weight: 500; color: #1e293b;">
                                            <?php echo htmlspecialchars($expense['category_name']); ?>
                                        </span>
                                    </td>
                                    <td>
                                        <span style="font-weight: 600; color: #1e293b; font-size: 1rem;">
                                            KES <?php echo number_format($expense['amount'], 2); ?>
                                        </span>
                                    </td>
                                    <td>
                                        <?php 
                                        $payment_class = match($expense['payment_method']) {
                                            'CASH' => 'payment-cash',
                                            'CARD' => 'payment-card',
                                            'BANK_TRANSFER' => 'payment-bank-transfer',
                                            'MPESA' => 'payment-mpesa',
                                            default => 'payment-cash'
                                        };
                                        ?>
                                        <span class="payment-badge <?php echo $payment_class; ?>">
                                            <?php echo ucfirst(str_replace('_', ' ', strtolower($expense['payment_method']))); ?>
                                        </span>
                                    </td>
                                    <td>
                                        <?php 
                                        $status_class = match($expense['status']) {
                                            'PENDING' => 'status-pending',
                                            'PAID' => 'status-paid',
                                            default => 'status-draft'
                                        };
                                        ?>
                                        <span class="status-badge <?php echo $status_class; ?>">
                                            <?php echo ucfirst(strtolower($expense['status'])); ?>
                                        </span>
                                    </td>
                                    <td>
                                        <div style="font-weight: 500; color: #1e293b;">
                                            <?php echo date('M j, Y', strtotime($expense['expense_date'])); ?>
                                        </div>
                                        <div style="color: #64748b; font-size: 0.75rem;">
                                            <?php echo date('g:i A', strtotime($expense['created_at'])); ?>
                                        </div>
                                    </td>
                                    <td>
                                        <div class="d-flex gap-1">
                                            <a href="view_expense.php?id=<?php echo $expense['expense_id']; ?>" class="btn-action btn-primary" title="View">
                                                <i class="ph-eye"></i>
                                            </a>
                                            <a href="edit_expense.php?id=<?php echo $expense['expense_id']; ?>" class="btn-action btn-warning" title="Edit">
                                                <i class="ph-pencil"></i>
                                            </a>
                                        </div>
                                    </td>
                                </tr>
                                <?php endwhile; ?>
                            </tbody>
                        </table>
                    </div>
                </div>
            </div>
            <?php include 'layouts/footer.php'; ?>
        </div>
    </div>

    <?php include 'layouts/vendor-scripts.php'; ?>
    <script src="assets/js/app.js"></script>
</body>
</html>

<?php $conn->close(); ?>