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

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

// Get user details
$user_id = $_SESSION['user_id'];
$sql_user_details = "SELECT username, user_role, full_name, email, role_id FROM users WHERE user_id = $user_id";
$result_user_details = $conn->query($sql_user_details);

if ($result_user_details && $row_user_details = $result_user_details->fetch_assoc()) {
    $username = $row_user_details['username'];
    $user_role = $row_user_details['user_role'];
    $full_name = $row_user_details['full_name'];
    $email = $row_user_details['email'];
    $role_id = $row_user_details['role_id'];
}

// Set default date range to current month if not specified
$current_month_start = date('Y-m-01');
$current_month_end = date('Y-m-t');

$start_date = isset($_GET['start_date']) ? $_GET['start_date'] : $current_month_start;
$end_date = isset($_GET['end_date']) ? $_GET['end_date'] : $current_month_end;
$currency = isset($_GET['currency']) ? $_GET['currency'] : 'USD'; // Default to USD

// Function to get summary data
function getSummaryData($conn, $start_date, $end_date, $currency) {
    $summary = [];
    
    // Get total revenue (from invoices)
    $sql_revenue = "SELECT COALESCE(SUM(total_amount), 0) as total_revenue 
                   FROM invoices 
                   WHERE issue_date BETWEEN ? AND ? 
                   AND status != 'cancelled'";
    $stmt = $conn->prepare($sql_revenue);
    $stmt->bind_param("ss", $start_date, $end_date);
    $stmt->execute();
    $revenue_result = $stmt->get_result();
    $summary['revenue'] = $revenue_result->fetch_assoc()['total_revenue'];
    
    // Get total deposits
    $sql_deposits = "SELECT COALESCE(SUM(amount), 0) as total_deposits 
                    FROM deposits 
                    WHERE deposit_date BETWEEN ? AND ? 
                    AND currency = ?
                    AND status != 'rejected'";
    $stmt = $conn->prepare($sql_deposits);
    $stmt->bind_param("sss", $start_date, $end_date, $currency);
    $stmt->execute();
    $deposits_result = $stmt->get_result();
    $summary['deposits'] = $deposits_result->fetch_assoc()['total_deposits'];
    
    // Get total withdrawals (cheque_withdrawals table)
    $sql_withdrawals = "SELECT COALESCE(SUM(amount), 0) as total_withdrawals 
                       FROM cheque_withdrawals 
                       WHERE withdrawal_date BETWEEN ? AND ? 
                       AND currency = ?
                       AND status != 'rejected'";
    $stmt = $conn->prepare($sql_withdrawals);
    $stmt->bind_param("sss", $start_date, $end_date, $currency);
    $stmt->execute();
    $withdrawals_result = $stmt->get_result();
    $summary['withdrawals'] = $withdrawals_result->fetch_assoc()['total_withdrawals'];
    
    // Get total fund requests
    $sql_fund_requests = "SELECT COALESCE(SUM(amount), 0) as total_fund_requests 
                         FROM fund_requests 
                         WHERE request_date BETWEEN ? AND ? 
                         AND currency = ?
                         AND status != 'Rejected'
                         AND status != 'Cancelled'";
    $stmt = $conn->prepare($sql_fund_requests);
    $stmt->bind_param("sss", $start_date, $end_date, $currency);
    $stmt->execute();
    $fund_requests_result = $stmt->get_result();
    $summary['fund_requests'] = $fund_requests_result->fetch_assoc()['total_fund_requests'];
    
    // Get total expenses
    $sql_expenses = "SELECT COALESCE(SUM(amount), 0) as total_expenses 
                    FROM expenses 
                    WHERE created_date BETWEEN ? AND ? 
                    AND currency = ?
                    AND status != 'Rejected'";
    $stmt = $conn->prepare($sql_expenses);
    $stmt->bind_param("sss", $start_date, $end_date, $currency);
    $stmt->execute();
    $expenses_result = $stmt->get_result();
    $summary['expenses'] = $expenses_result->fetch_assoc()['total_expenses'];
    
    // Calculate net position
    $summary['net_position'] = $summary['revenue'] + $summary['deposits'] - $summary['withdrawals'] - $summary['fund_requests'] - $summary['expenses'];
    
    return $summary;
}

// Function to get monthly data for charts
function getMonthlyData($conn, $start_date, $end_date, $currency) {
    $months = [];
    $revenues = [];
    $expenses = [];
    $deposits = [];
    $withdrawals = [];
    
    // Get the list of months between start and end dates
    $start = new DateTime($start_date);
    $end = new DateTime($end_date);
    $interval = DateInterval::createFromDateString('1 month');
    $period = new DatePeriod($start, $interval, $end);
    
    foreach ($period as $dt) {
        $month_start = $dt->format('Y-m-01');
        $month_end = $dt->format('Y-m-t');
        $month_label = $dt->format('M Y');
        
        $months[] = $month_label;
        
        // Get revenue for this month
        $sql_revenue = "SELECT COALESCE(SUM(total_amount), 0) as month_revenue 
                       FROM invoices 
                       WHERE issue_date BETWEEN ? AND ? 
                       AND status != 'cancelled'";
        $stmt = $conn->prepare($sql_revenue);
        $stmt->bind_param("ss", $month_start, $month_end);
        $stmt->execute();
        $result = $stmt->get_result();
        $revenues[] = floatval($result->fetch_assoc()['month_revenue']);
        
        // Get expenses for this month
        $sql_expenses = "SELECT COALESCE(SUM(amount), 0) as month_expenses 
                        FROM expenses 
                        WHERE created_date BETWEEN ? AND ? 
                        AND currency = ?
                        AND status != 'Rejected'";
        $stmt = $conn->prepare($sql_expenses);
        $stmt->bind_param("sss", $month_start, $month_end, $currency);
        $stmt->execute();
        $result = $stmt->get_result();
        $expenses[] = floatval($result->fetch_assoc()['month_expenses']);
        
        // Get deposits for this month
        $sql_deposits = "SELECT COALESCE(SUM(amount), 0) as month_deposits 
                        FROM deposits 
                        WHERE deposit_date BETWEEN ? AND ? 
                        AND currency = ?
                        AND status != 'rejected'";
        $stmt = $conn->prepare($sql_deposits);
        $stmt->bind_param("sss", $month_start, $month_end, $currency);
        $stmt->execute();
        $result = $stmt->get_result();
        $deposits[] = floatval($result->fetch_assoc()['month_deposits']);
        
        // Get withdrawals for this month
        $sql_withdrawals = "SELECT COALESCE(SUM(amount), 0) as month_withdrawals 
                           FROM cheque_withdrawals 
                           WHERE withdrawal_date BETWEEN ? AND ? 
                           AND currency = ?
                           AND status != 'rejected'";
        $stmt = $conn->prepare($sql_withdrawals);
        $stmt->bind_param("sss", $month_start, $month_end, $currency);
        $stmt->execute();
        $result = $stmt->get_result();
        $withdrawals[] = floatval($result->fetch_assoc()['month_withdrawals']);
    }
    
    return [
        'months' => $months,
        'revenues' => $revenues,
        'expenses' => $expenses,
        'deposits' => $deposits,
        'withdrawals' => $withdrawals
    ];
}

// Function to get expense breakdown by category
function getExpenseBreakdown($conn, $start_date, $end_date, $currency) {
    $categories = [];
    $amounts = [];
    
    $sql = "SELECT 
                ec.name as category_name, 
                COALESCE(SUM(e.amount), 0) as total_amount
            FROM 
                expenses e
            JOIN 
                expense_categories ec ON e.office_cost = SUBSTRING_INDEX(ec.name, ' - ', 1)
            WHERE 
                e.created_date BETWEEN ? AND ?
                AND e.currency = ?
                AND e.status != 'Rejected'
            GROUP BY 
                ec.name
            ORDER BY 
                total_amount DESC
            LIMIT 10";
    
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("sss", $start_date, $end_date, $currency);
    $stmt->execute();
    $result = $stmt->get_result();
    
    while ($row = $result->fetch_assoc()) {
        // Simplify category name by removing code
        $category = preg_replace('/ - [0-9\-]+$/', '', $row['category_name']);
        $categories[] = $category;
        $amounts[] = floatval($row['total_amount']);
    }
    
    return [
        'categories' => $categories,
        'amounts' => $amounts
    ];
}

// Get summary data
$summary = getSummaryData($conn, $start_date, $end_date, $currency);

// Get monthly data for charts
$monthly_data = getMonthlyData($conn, $start_date, $end_date, $currency);

// Get expense breakdown
$expense_breakdown = getExpenseBreakdown($conn, $start_date, $end_date, $currency);

// Function to format currency
function formatCurrency($amount, $currency) {
    if ($currency == 'USD') {
        return '$' . number_format($amount, 2);
    } else if ($currency == 'Tsh' || $currency == 'TSH') {
        return 'Tsh ' . number_format($amount, 2);
    } else {
        return $currency . ' ' . number_format($amount, 2);
    }
}

// Function to determine status indicator class
function getStatusIndicator($value) {
    if ($value > 0) {
        return 'text-success';
    } else if ($value < 0) {
        return 'text-danger';
    } else {
        return 'text-warning';
    }
}

// Generate PDF report if requested
if (isset($_GET['export']) && $_GET['export'] == 'pdf') {
    require_once('fpdf/fpdf.php');
    
    class FinancialReportPDF extends FPDF {
        // Page header
        function Header() {
            // Arial bold 15
            $this->SetFont('Arial', 'B', 15);
            // Move to the right
            $this->Cell(80);
            // Title
            $this->Cell(30, 10, 'Royal Freight Ltd - Financial Position Report', 0, 0, 'C');
            // Line break
            $this->Ln(20);
        }

        // Page footer
        function Footer() {
            // Position at 1.5 cm from bottom
            $this->SetY(-15);
            // Arial italic 8
            $this->SetFont('Arial', 'I', 8);
            // Page number
            $this->Cell(0, 10, 'Page '.$this->PageNo().'/{nb}', 0, 0, 'C');
        }
    }

    // Create PDF instance
    $pdf = new FinancialReportPDF();
    $pdf->AliasNbPages();
    $pdf->AddPage();
    $pdf->SetFont('Arial', '', 12);
    
    // Date range
    $pdf->SetFont('Arial', 'B', 12);
    $pdf->Cell(0, 10, 'Period: ' . date('d M Y', strtotime($start_date)) . ' to ' . date('d M Y', strtotime($end_date)), 0, 1);
    $pdf->Cell(0, 10, 'Currency: ' . $currency, 0, 1);
    $pdf->Ln(5);
    
    // Summary section
    $pdf->SetFont('Arial', 'B', 14);
    $pdf->Cell(0, 10, 'Financial Summary', 0, 1);
    $pdf->SetFont('Arial', '', 12);
    
    $pdf->Cell(60, 10, 'Total Revenue:', 0);
    $pdf->Cell(60, 10, formatCurrency($summary['revenue'], $currency), 0, 1);
    
    $pdf->Cell(60, 10, 'Total Deposits:', 0);
    $pdf->Cell(60, 10, formatCurrency($summary['deposits'], $currency), 0, 1);
    
    $pdf->Cell(60, 10, 'Total Withdrawals:', 0);
    $pdf->Cell(60, 10, formatCurrency($summary['withdrawals'], $currency), 0, 1);
    
    $pdf->Cell(60, 10, 'Total Fund Requests:', 0);
    $pdf->Cell(60, 10, formatCurrency($summary['fund_requests'], $currency), 0, 1);
    
    $pdf->Cell(60, 10, 'Total Expenses:', 0);
    $pdf->Cell(60, 10, formatCurrency($summary['expenses'], $currency), 0, 1);
    
    $pdf->SetFont('Arial', 'B', 12);
    $pdf->Cell(60, 10, 'Net Position:', 0);
    $pdf->Cell(60, 10, formatCurrency($summary['net_position'], $currency), 0, 1);
    
    $pdf->Output('D', 'Financial_Position_Report.pdf');
    exit;
}
?>

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

<head>
    <?php includeFileWithVariables('layouts/title-meta.php', array('title' => 'Financial Position Report')); ?>
    <?php include 'layouts/head-css.php'; ?>
    <style>
        .card-stats {
            border-radius: 0.25rem;
            margin-bottom: 20px;
        }
        .card-stats .card-body {
            padding: 1rem;
        }
        .stat-title {
            font-size: 1rem;
            margin-bottom: 0.5rem;
        }
        .stat-value {
            font-size: 1.5rem;
            font-weight: 600;
        }
        .chart-container {
            position: relative; 
            height: 100%;
            width: 100%;
        }
    </style>
</head>

<body>
    <!-- Begin page -->
    <div id="layout-wrapper">
        <?php include 'layouts/menu.php'; ?>

        <!-- ============================================================== -->
        <!-- Start right Content here -->
        <!-- ============================================================== -->
        <div class="main-content">
            <div class="page-content">
                <div class="container-fluid">
                    <?php includeFileWithVariables('layouts/page-title.php', array('pagetitle' => 'Reports', 'title' => 'Financial Position Report')); ?>

                    <div class="row">
                        <div class="col-lg-12">
                            <div class="card">
                                <div class="card-header align-items-center d-flex">
                                    <h4 class="card-title mb-0 flex-grow-1">Financial Position Report</h4>
                                </div>
                                
                                <div class="card-body">
                                    <form method="GET" class="mb-4">
                                        <div class="row">
                                            <div class="col-md-3">
                                                <div class="mb-3">
                                                    <label for="start_date" class="form-label">Start Date</label>
                                                    <input type="date" name="start_date" id="start_date" class="form-control" value="<?php echo $start_date; ?>">
                                                </div>
                                            </div>
                                            <div class="col-md-3">
                                                <div class="mb-3">
                                                    <label for="end_date" class="form-label">End Date</label>
                                                    <input type="date" name="end_date" id="end_date" class="form-control" value="<?php echo $end_date; ?>">
                                                </div>
                                            </div>
                                            <div class="col-md-3">
                                                <div class="mb-3">
                                                    <label for="currency" class="form-label">Currency</label>
                                                    <select name="currency" id="currency" class="form-select">
                                                        <option value="USD" <?php echo ($currency == 'USD') ? 'selected' : ''; ?>>USD</option>
                                                        <option value="Tsh" <?php echo ($currency == 'Tsh') ? 'selected' : ''; ?>>Tsh</option>
                                                    </select>
                                                </div>
                                            </div>
                                            <div class="col-md-3">
                                                <div class="mt-4">
                                                    <button type="submit" class="btn btn-primary">Apply Filters</button>
                                                    <a href="?export=pdf&start_date=<?php echo $start_date; ?>&end_date=<?php echo $end_date; ?>&currency=<?php echo $currency; ?>" class="btn btn-secondary ms-2">Export PDF</a>
                                                </div>
                                            </div>
                                        </div>
                                    </form>
                                    
                                    <div class="alert alert-info">
                                        <strong>Report Period:</strong> <?php echo date('d M Y', strtotime($start_date)); ?> to <?php echo date('d M Y', strtotime($end_date)); ?> | 
                                        <strong>Currency:</strong> <?php echo $currency; ?>
                                    </div>
                                    
                                    <!-- Financial Summary Cards -->
                                    <div class="row">
                                        <div class="col-md-4">
                                            <div class="card card-stats bg-light">
                                                <div class="card-body">
                                                    <h5 class="stat-title">Total Revenue</h5>
                                                    <div class="stat-value <?php echo getStatusIndicator($summary['revenue']); ?>">
                                                        <?php echo formatCurrency($summary['revenue'], $currency); ?>
                                                    </div>
                                                </div>
                                            </div>
                                        </div>
                                        <div class="col-md-4">
                                            <div class="card card-stats bg-light">
                                                <div class="card-body">
                                                    <h5 class="stat-title">Total Deposits</h5>
                                                    <div class="stat-value <?php echo getStatusIndicator($summary['deposits']); ?>">
                                                        <?php echo formatCurrency($summary['deposits'], $currency); ?>
                                                    </div>
                                                </div>
                                            </div>
                                        </div>
                                        <div class="col-md-4">
                                            <div class="card card-stats bg-light">
                                                <div class="card-body">
                                                    <h5 class="stat-title">Net Position</h5>
                                                    <div class="stat-value <?php echo getStatusIndicator($summary['net_position']); ?>">
                                                        <?php echo formatCurrency($summary['net_position'], $currency); ?>
                                                    </div>
                                                </div>
                                            </div>
                                        </div>
                                    </div>
                                    
                                    <div class="row">
                                        <div class="col-md-4">
                                            <div class="card card-stats bg-light">
                                                <div class="card-body">
                                                    <h5 class="stat-title">Total Withdrawals</h5>
                                                    <div class="stat-value <?php echo getStatusIndicator(-1 * $summary['withdrawals']); ?>">
                                                        <?php echo formatCurrency($summary['withdrawals'], $currency); ?>
                                                    </div>
                                                </div>
                                            </div>
                                        </div>
                                        <div class="col-md-4">
                                            <div class="card card-stats bg-light">
                                                <div class="card-body">
                                                    <h5 class="stat-title">Total Fund Requests</h5>
                                                    <div class="stat-value <?php echo getStatusIndicator(-1 * $summary['fund_requests']); ?>">
                                                        <?php echo formatCurrency($summary['fund_requests'], $currency); ?>
                                                    </div>
                                                </div>
                                            </div>
                                        </div>
                                        <div class="col-md-4">
                                            <div class="card card-stats bg-light">
                                                <div class="card-body">
                                                    <h5 class="stat-title">Total Expenses</h5>
                                                    <div class="stat-value <?php echo getStatusIndicator(-1 * $summary['expenses']); ?>">
                                                        <?php echo formatCurrency($summary['expenses'], $currency); ?>
                                                    </div>
                                                </div>
                                            </div>
                                        </div>
                                    </div>
                                    
                                    <!-- Charts -->
                                    <div class="row mt-4">
                                        <div class="col-md-8">
                                            <div class="card">
                                                <div class="card-header">
                                                    <h5 class="card-title mb-0">Revenue and Expenses Trend</h5>
                                                </div>
                                                <div class="card-body">
                                                    <div class="chart-container">
                                                        <canvas id="revenueExpensesChart"></canvas>
                                                    </div>
                                                </div>
                                            </div>
                                        </div>
                                        <div class="col-md-4">
                                            <div class="card">
                                                <div class="card-header">
                                                    <h5 class="card-title mb-0">Expense Breakdown</h5>
                                                </div>
                                                <div class="card-body">
                                                    <div class="chart-container">
                                                        <canvas id="expenseBreakdownChart"></canvas>
                                                    </div>
                                                </div>
                                            </div>
                                        </div>
                                    </div>
                                    
                                    <div class="row mt-4">
                                        <div class="col-md-12">
                                            <div class="card">
                                                <div class="card-header">
                                                    <h5 class="card-title mb-0">Cash Flow Analysis</h5>
                                                </div>
                                                <div class="card-body">
                                                    <div class="chart-container">
                                                        <canvas id="cashFlowChart"></canvas>
                                                    </div>
                                                </div>
                                            </div>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                </div>
                <!-- container-fluid -->
            </div>
            <!-- End Page-content -->

            <?php include 'layouts/footer.php'; ?>
        </div>
        <!-- end main content-->
    </div>
    <!-- END layout-wrapper -->

    <?php include 'layouts/vendor-scripts.php'; ?>
<script src="assets/js/app.js"></script>
<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
<link href="assets/libs/sweetalert2/sweetalert2.min.css" rel="stylesheet">
<script src="assets/libs/sweetalert2/sweetalert2.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/select2@4.1.0-rc.0/dist/js/select2.min.js"></script>
    <!-- Chart.js -->
    <script src="https://cdn.jsdelivr.net/npm/chart.js@3.9.1/dist/chart.min.js"></script>
    
    <script>
    document.addEventListener('DOMContentLoaded', function() {
        // Revenue and Expenses Chart
        const revenueExpensesCtx = document.getElementById('revenueExpensesChart').getContext('2d');
        const revenueExpensesChart = new Chart(revenueExpensesCtx, {
            type: 'line',
            data: {
                labels: <?php echo json_encode($monthly_data['months']); ?>,
                datasets: [{
                    label: 'Revenue',
                    data: <?php echo json_encode($monthly_data['revenues']); ?>,
                    backgroundColor: 'rgba(54, 162, 235, 0.2)',
                    borderColor: 'rgba(54, 162, 235, 1)',
                    borderWidth: 2,
                    tension: 0.1
                }, {
                    label: 'Expenses',
                    data: <?php echo json_encode($monthly_data['expenses']); ?>,
                    backgroundColor: 'rgba(255, 99, 132, 0.2)',
                    borderColor: 'rgba(255, 99, 132, 1)',
                    borderWidth: 2,
                    tension: 0.1
                }]
            },
            options: {
                responsive: true,
                maintainAspectRatio: false,
                scales: {
                    y: {
                        beginAtZero: true
                    }
                }
            }
        });
        
        // Expense Breakdown Chart (Pie chart)
        const expenseBreakdownCtx = document.getElementById('expenseBreakdownChart').getContext('2d');
        const expenseBreakdownChart = new Chart(expenseBreakdownCtx, {
            type: 'doughnut',
            data: {
                labels: <?php echo json_encode($expense_breakdown['categories']); ?>,
                datasets: [{
                    label: 'Expense Breakdown',
                    data: <?php echo json_encode($expense_breakdown['amounts']); ?>,
                    backgroundColor: [
                        'rgba(255, 99, 132, 0.7)',
                        'rgba(54, 162, 235, 0.7)',
                        'rgba(255, 206, 86, 0.7)',
                        'rgba(75, 192, 192, 0.7)',
                        'rgba(153, 102, 255, 0.7)',
                        'rgba(255, 159, 64, 0.7)',
                        'rgba(199, 199, 199, 0.7)',
                        'rgba(83, 102, 255, 0.7)',
                        'rgba(40, 159, 64, 0.7)',
                        'rgba(210, 199, 199, 0.7)'
                    ],
                    borderWidth: 1
                }]
            },
            options: {
                responsive: true,
                maintainAspectRatio: false,
                plugins: {
                    legend: {
                        position: 'right',
                        labels: {
                            boxWidth: 12
                        }
                    }
                }
            }
        });
        
        // Cash Flow Chart (Deposits vs Withdrawals)
        const cashFlowCtx = document.getElementById('cashFlowChart').getContext('2d');
        const cashFlowChart = new Chart(cashFlowCtx, {
            type: 'bar',
            data: {
                labels: <?php echo json_encode($monthly_data['months']); ?>,
                datasets: [{
                    label: 'Deposits',
                    data: <?php echo json_encode($monthly_data['deposits']); ?>,
                    backgroundColor: 'rgba(75, 192, 192, 0.7)',
                    borderColor: 'rgba(75, 192, 192, 1)',
                    borderWidth: 1
                }, {
                    label: 'Withdrawals',
                    data: <?php echo json_encode($monthly_data['withdrawals']); ?>,
                    backgroundColor: 'rgba(255, 159, 64, 0.7)',
                    borderColor: 'rgba(255, 159, 64, 1)',
                    borderWidth: 1
                }]
            },
            options: {
                responsive: true,
                maintainAspectRatio: false,
                scales: {
                    y: {
                        beginAtZero: true
                    }
                }
            }
        });
    });
    </script>
</body>
</html>