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

if (!isset($_SESSION['user_id'])) {
    header('Location: login.php');
    exit();
}

$allowed_roles = ['SUPER_ADMIN', 'HOSPITAL_ADMIN', 'BILLING_CLERK'];
if (!in_array($_SESSION['role_code'], $allowed_roles)) {
    header('Location: dashboard.php');
    exit();
}

$report_type = $_GET['report'] ?? 'trial_balance';
$fiscal_year = $_GET['fiscal_year'] ?? date('Y');
$fiscal_period = $_GET['fiscal_period'] ?? date('n');

// Update trial balance cache for current period
$conn->query("CALL UpdateTrialBalanceCache($fiscal_year, $fiscal_period)");

// Generate requested report
$report_data = [];
switch ($report_type) {
    case 'trial_balance':
        $stmt = $conn->prepare("CALL GenerateTrialBalance(?, ?)");
        $stmt->bind_param("ii", $fiscal_year, $fiscal_period);
        $stmt->execute();
        $result = $stmt->get_result();
        while ($row = $result->fetch_assoc()) {
            $report_data[] = $row;
        }
        $stmt->close();
        break;
        
    case 'income_statement':
        $period_start = $_GET['period_start'] ?? 1;
        $period_end = $_GET['period_end'] ?? date('n');
        
        $stmt = $conn->prepare("CALL GenerateIncomeStatement(?, ?, ?)");
        $stmt->bind_param("iii", $fiscal_year, $period_start, $period_end);
        $stmt->execute();
        
        // Get revenue
        $revenue_result = $stmt->get_result();
        $revenue_data = [];
        while ($row = $revenue_result->fetch_assoc()) {
            $revenue_data[] = $row;
        }
        $stmt->next_result();
        
        // Get expenses
        $expense_result = $stmt->get_result();
        $expense_data = [];
        while ($row = $expense_result->fetch_assoc()) {
            $expense_data[] = $row;
        }
        $stmt->next_result();
        
        // Get net income
        $net_result = $stmt->get_result();
        $net_income = $net_result->fetch_assoc()['net_income'] ?? 0;
        
        $report_data = [
            'revenue' => $revenue_data,
            'expenses' => $expense_data,
            'net_income' => $net_income
        ];
        $stmt->close();
        break;
        
    case 'balance_sheet':
        $stmt = $conn->prepare("CALL GenerateBalanceSheet(?, ?)");
        $stmt->bind_param("ii", $fiscal_year, $fiscal_period);
        $stmt->execute();
        
        // Get assets
        $assets_result = $stmt->get_result();
        $assets_data = [];
        while ($row = $assets_result->fetch_assoc()) {
            $assets_data[] = $row;
        }
        $stmt->next_result();
        
        // Get liabilities
        $liabilities_result = $stmt->get_result();
        $liabilities_data = [];
        while ($row = $liabilities_result->fetch_assoc()) {
            $liabilities_data[] = $row;
        }
        $stmt->next_result();
        
        // Get equity
        $equity_result = $stmt->get_result();
        $equity_data = [];
        while ($row = $equity_result->fetch_assoc()) {
            $equity_data[] = $row;
        }
        $stmt->next_result();
        
        // Get totals
        $totals_result = $stmt->get_result();
        $totals = $totals_result->fetch_assoc();
        
        $report_data = [
            'assets' => $assets_data,
            'liabilities' => $liabilities_data,
            'equity' => $equity_data,
            'totals' => $totals
        ];
        $stmt->close();
        break;
}

$months = [
    1 => 'January', 2 => 'February', 3 => 'March', 4 => 'April',
    5 => 'May', 6 => 'June', 7 => 'July', 8 => 'August',
    9 => 'September', 10 => 'October', 11 => 'November', 12 => 'December'
];
?>

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

<head>
    <?php includeFileWithVariables('layouts/title-meta.php', array('title' => 'Financial Reports')); ?>
    <?php include 'layouts/head-css.php'; ?>
    
    <style>
        .report-card {
            background: white;
            border-radius: 12px;
            box-shadow: 0 1px 3px 0 rgba(0, 0, 0, 0.1);
            border: 1px solid #e2e8f0;
        }
        
        .report-header {
            padding: 1.5rem;
            border-bottom: 1px solid #e2e8f0;
            background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
            color: white;
            border-radius: 12px 12px 0 0;
        }
        
        .report-tabs {
            display: flex;
            gap: 1rem;
            padding: 1rem 1.5rem;
            background: #f8fafc;
            border-bottom: 1px solid #e2e8f0;
        }
        
        .report-tab {
            padding: 0.75rem 1.5rem;
            border-radius: 8px;
            background: white;
            border: 1px solid #e2e8f0;
            cursor: pointer;
            transition: all 0.3s;
            text-decoration: none;
            color: #475569;
            font-weight: 500;
        }
        
        .report-tab:hover {
            border-color: #667eea;
            color: #667eea;
        }
        
        .report-tab.active {
            background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
            color: white;
            border-color: transparent;
        }
        
        .report-table {
            width: 100%;
            border-collapse: collapse;
        }
        
        .report-table th {
            background: #f8fafc;
            padding: 1rem;
            text-align: left;
            font-weight: 600;
            border-bottom: 2px solid #e2e8f0;
        }
        
        .report-table td {
            padding: 1rem;
            border-bottom: 1px solid #f1f5f9;
        }
        
        .report-table .section-header {
            background: #f1f5f9;
            font-weight: 700;
            color: #1e293b;
        }
        
        .report-table .total-row {
            background: #f8fafc;
            font-weight: 700;
            border-top: 2px solid #cbd5e1;
        }
        
        .amount-positive {
            color: #059669;
        }
        
        .amount-negative {
            color: #dc2626;
        }
        
        .report-footer {
            padding: 1.5rem;
            background: #f8fafc;
            border-top: 1px solid #e2e8f0;
            display: flex;
            justify-content: space-between;
            align-items: center;
        }
    </style>
</head>

<body>
    <div id="layout-wrapper">
        <?php include 'layouts/menu.php'; ?>
        
        <div class="main-content">
            <div class="page-content">
                <div class="container-fluid">
                    <div class="row">
                        <div class="col-12">
                            <div class="page-title-box d-sm-flex align-items-center justify-content-between">
                                <h4 class="mb-sm-0">Financial Reports</h4>
                                <div class="page-title-right">
                                    <ol class="breadcrumb m-0">
                                        <li class="breadcrumb-item"><a href="dashboard.php">Dashboard</a></li>
                                        <li class="breadcrumb-item active">Financial Reports</li>
                                    </ol>
                                </div>
                            </div>
                        </div>
                    </div>

                    <div class="report-card">
                        <div class="report-header">
                            <h5 class="mb-0">
                                <i class="ri-file-chart-line me-2"></i>
                                <?php 
                                echo match($report_type) {
                                    'trial_balance' => 'Trial Balance',
                                    'income_statement' => 'Income Statement',
                                    'balance_sheet' => 'Balance Sheet',
                                    default => 'Financial Report'
                                };
                                ?>
                            </h5>
                        </div>
                        
                        <!-- Report Tabs -->
                        <div class="report-tabs">
                            <a href="?report=trial_balance&fiscal_year=<?php echo $fiscal_year; ?>&fiscal_period=<?php echo $fiscal_period; ?>" 
                               class="report-tab <?php echo $report_type === 'trial_balance' ? 'active' : ''; ?>">
                                Trial Balance
                            </a>
                            <a href="?report=income_statement&fiscal_year=<?php echo $fiscal_year; ?>&fiscal_period=<?php echo $fiscal_period; ?>" 
                               class="report-tab <?php echo $report_type === 'income_statement' ? 'active' : ''; ?>">
                                Income Statement
                            </a>
                            <a href="?report=balance_sheet&fiscal_year=<?php echo $fiscal_year; ?>&fiscal_period=<?php echo $fiscal_period; ?>" 
                               class="report-tab <?php echo $report_type === 'balance_sheet' ? 'active' : ''; ?>">
                                Balance Sheet
                            </a>
                        </div>
                        
                        <!-- Filters -->
                        <div class="p-3 bg-light border-bottom">
                            <form method="GET" action="" class="row g-3">
                                <input type="hidden" name="report" value="<?php echo $report_type; ?>">
                                <div class="col-md-3">
                                    <label class="form-label">Fiscal Year</label>
                                    <select class="form-select" name="fiscal_year">
                                        <?php for ($y = date('Y') - 2; $y <= date('Y') + 1; $y++): ?>
                                            <option value="<?php echo $y; ?>" <?php echo $y == $fiscal_year ? 'selected' : ''; ?>>
                                                <?php echo $y; ?>
                                            </option>
                                        <?php endfor; ?>
                                    </select>
                                </div>
                                <div class="col-md-3">
                                    <label class="form-label">Period (Month)</label>
                                    <select class="form-select" name="fiscal_period">
                                        <?php foreach ($months as $num => $name): ?>
                                            <option value="<?php echo $num; ?>" <?php echo $num == $fiscal_period ? 'selected' : ''; ?>>
                                                <?php echo $name; ?>
                                            </option>
                                        <?php endforeach; ?>
                                    </select>
                                </div>
                                <?php if ($report_type === 'income_statement'): ?>
                                <div class="col-md-2">
                                    <label class="form-label">From Period</label>
                                    <select class="form-select" name="period_start">
                                        <?php for ($i = 1; $i <= 12; $i++): ?>
                                            <option value="<?php echo $i; ?>" <?php echo $i == 1 ? 'selected' : ''; ?>>
                                                <?php echo $months[$i]; ?>
                                            </option>
                                        <?php endfor; ?>
                                    </select>
                                </div>
                                <div class="col-md-2">
                                    <label class="form-label">To Period</label>
                                    <select class="form-select" name="period_end">
                                        <?php for ($i = 1; $i <= 12; $i++): ?>
                                            <option value="<?php echo $i; ?>" <?php echo $i == date('n') ? 'selected' : ''; ?>>
                                                <?php echo $months[$i]; ?>
                                            </option>
                                        <?php endfor; ?>
                                    </select>
                                </div>
                                <?php endif; ?>
                                <div class="col-md-2">
                                    <label class="form-label">&nbsp;</label>
                                    <button type="submit" class="btn btn-primary w-100">
                                        <i class="ri-refresh-line me-1"></i>Generate
                                    </button>
                                </div>
                            </form>
                        </div>

                        <!-- Report Content -->
                        <div class="card-body p-0">
                            <?php if ($report_type === 'trial_balance'): ?>
                                <!-- Trial Balance Report -->
                                <div class="table-responsive">
                                    <table class="report-table">
                                        <thead>
                                            <tr>
                                                <th>Account Code</th>
                                                <th>Account Name</th>
                                                <th>Type</th>
                                                <th style="text-align: right;">Debit</th>
                                                <th style="text-align: right;">Credit</th>
                                                <th style="text-align: right;">Balance</th>
                                            </tr>
                                        </thead>
                                        <tbody>
                                            <?php 
                                            $total_debits = 0;
                                            $total_credits = 0;
                                            $current_type = '';
                                            
                                            foreach ($report_data as $row): 
                                                if ($current_type !== $row['account_type']) {
                                                    $current_type = $row['account_type'];
                                            ?>
                                                <tr class="section-header">
                                                    <td colspan="6"><?php echo $current_type; ?></td>
                                                </tr>
                                            <?php 
                                                }
                                                $total_debits += $row['debit_total'];
                                                $total_credits += $row['credit_total'];
                                            ?>
                                            <tr>
                                                <td><?php echo htmlspecialchars($row['account_code']); ?></td>
                                                <td><?php echo htmlspecialchars($row['account_name']); ?></td>
                                                <td>
                                                    <span class="badge bg-secondary">
                                                        <?php echo $row['normal_balance']; ?>
                                                    </span>
                                                </td>
                                                <td style="text-align: right;">
                                                    <?php echo $row['debit_total'] > 0 ? 'KES ' . number_format($row['debit_total'], 2) : '-'; ?>
                                                </td>
                                                <td style="text-align: right;">
                                                    <?php echo $row['credit_total'] > 0 ? 'KES ' . number_format($row['credit_total'], 2) : '-'; ?>
                                                </td>
                                                <td style="text-align: right;">
                                                    <strong>KES <?php echo number_format(abs($row['balance']), 2); ?></strong>
                                                </td>
                                            </tr>
                                            <?php endforeach; ?>
                                            <tr class="total-row">
                                                <td colspan="3"><strong>TOTALS</strong></td>
                                                <td style="text-align: right;"><strong>KES <?php echo number_format($total_debits, 2); ?></strong></td>
                                                <td style="text-align: right;"><strong>KES <?php echo number_format($total_credits, 2); ?></strong></td>
                                                <td style="text-align: right;">
                                                    <strong class="<?php echo $total_debits == $total_credits ? 'amount-positive' : 'amount-negative'; ?>">
                                                        <?php echo $total_debits == $total_credits ? 'BALANCED' : 'OUT OF BALANCE'; ?>
                                                    </strong>
                                                </td>
                                            </tr>
                                        </tbody>
                                    </table>
                                </div>
                                
                            <?php elseif ($report_type === 'income_statement'): ?>
                                <!-- Income Statement Report -->
                                <div class="table-responsive">
                                    <table class="report-table">
                                        <thead>
                                            <tr>
                                                <th>Account</th>
                                                <th style="text-align: right;">Amount</th>
                                            </tr>
                                        </thead>
                                        <tbody>
                                            <tr class="section-header">
                                                <td colspan="2"><strong>REVENUE</strong></td>
                                            </tr>
                                            <?php 
                                            $total_revenue = 0;
                                            foreach ($report_data['revenue'] as $row): 
                                                $total_revenue += $row['amount'];
                                            ?>
                                            <tr>
                                                <td><?php echo htmlspecialchars($row['account_name']); ?></td>
                                                <td style="text-align: right;">KES <?php echo number_format($row['amount'], 2); ?></td>
                                            </tr>
                                            <?php endforeach; ?>
                                            <tr class="total-row">
                                                <td><strong>Total Revenue</strong></td>
                                                <td style="text-align: right;">
                                                    <strong class="amount-positive">KES <?php echo number_format($total_revenue, 2); ?></strong>
                                                </td>
                                            </tr>
                                            
                                            <tr class="section-header">
                                                <td colspan="2"><strong>EXPENSES</strong></td>
                                            </tr>
                                            <?php 
                                            $total_expenses = 0;
                                            foreach ($report_data['expenses'] as $row): 
                                                $total_expenses += $row['amount'];
                                            ?>
                                            <tr>
                                                <td><?php echo htmlspecialchars($row['account_name']); ?></td>
                                                <td style="text-align: right;">KES <?php echo number_format($row['amount'], 2); ?></td>
                                            </tr>
                                            <?php endforeach; ?>
                                            <tr class="total-row">
                                                <td><strong>Total Expenses</strong></td>
                                                <td style="text-align: right;">
                                                    <strong class="amount-negative">KES <?php echo number_format($total_expenses, 2); ?></strong>
                                                </td>
                                            </tr>
                                            
                                            <tr class="total-row" style="background: #e0e7ff; border-top: 3px solid #667eea;">
                                                <td><strong>NET INCOME</strong></td>
                                                <td style="text-align: right;">
                                                    <strong class="<?php echo $report_data['net_income'] >= 0 ? 'amount-positive' : 'amount-negative'; ?>" 
                                                            style="font-size: 1.25rem;">
                                                        KES <?php echo number_format($report_data['net_income'], 2); ?>
                                                    </strong>
                                                </td>
                                            </tr>
                                        </tbody>
                                    </table>
                                </div>
                                
                            <?php elseif ($report_type === 'balance_sheet'): ?>
                                <!-- Balance Sheet Report -->
                                <div class="table-responsive">
                                    <table class="report-table">
                                        <thead>
                                            <tr>
                                                <th>Account</th>
                                                <th>Category</th>
                                                <th style="text-align: right;">Amount</th>
                                            </tr>
                                        </thead>
                                        <tbody>
                                            <tr class="section-header">
                                                <td colspan="3"><strong>ASSETS</strong></td>
                                            </tr>
                                            <?php 
                                            $current_category = '';
                                            foreach ($report_data['assets'] as $row): 
                                                if ($current_category !== $row['account_category']) {
                                                    $current_category = $row['account_category'];
                                            ?>
                                                <tr style="background: #f8fafc;">
                                                    <td colspan="3" style="padding-left: 2rem; font-weight: 600;">
                                                        <?php echo $current_category; ?>
                                                    </td>
                                                </tr>
                                            <?php } ?>
                                            <tr>
                                                <td style="padding-left: 3rem;"><?php echo htmlspecialchars($row['account_name']); ?></td>
                                                <td><?php echo htmlspecialchars($row['account_code']); ?></td>
                                                <td style="text-align: right;">KES <?php echo number_format($row['balance'], 2); ?></td>
                                            </tr>
                                            <?php endforeach; ?>
                                            <tr class="total-row">
                                                <td colspan="2"><strong>Total Assets</strong></td>
                                                <td style="text-align: right;">
                                                    <strong>KES <?php echo number_format($report_data['totals']['total_assets'], 2); ?></strong>
                                                </td>
                                            </tr>
                                            
                                            <tr class="section-header">
                                                <td colspan="3"><strong>LIABILITIES</strong></td>
                                            </tr>
                                            <?php 
                                            $current_category = '';
                                            foreach ($report_data['liabilities'] as $row): 
                                                if ($current_category !== $row['account_category']) {
                                                    $current_category = $row['account_category'];
                                            ?>
                                                <tr style="background: #f8fafc;">
                                                    <td colspan="3" style="padding-left: 2rem; font-weight: 600;">
                                                        <?php echo $current_category; ?>
                                                    </td>
                                                </tr>
                                            <?php } ?>
                                            <tr>
                                                <td style="padding-left: 3rem;"><?php echo htmlspecialchars($row['account_name']); ?></td>
                                                <td><?php echo htmlspecialchars($row['account_code']); ?></td>
                                                <td style="text-align: right;">KES <?php echo number_format($row['balance'], 2); ?></td>
                                            </tr>
                                            <?php endforeach; ?>
                                            <tr class="total-row">
                                                <td colspan="2"><strong>Total Liabilities</strong></td>
                                                <td style="text-align: right;">
                                                    <strong>KES <?php echo number_format($report_data['totals']['total_liabilities'], 2); ?></strong>
                                                </td>
                                            </tr>
                                            
                                            <tr class="section-header">
                                                <td colspan="3"><strong>EQUITY</strong></td>
                                            </tr>
                                            <?php foreach ($report_data['equity'] as $row): ?>
                                            <tr>
                                                <td style="padding-left: 3rem;"><?php echo htmlspecialchars($row['account_name']); ?></td>
                                                <td><?php echo htmlspecialchars($row['account_code']); ?></td>
                                                <td style="text-align: right;">KES <?php echo number_format($row['balance'], 2); ?></td>
                                            </tr>
                                            <?php endforeach; ?>
                                            <tr class="total-row">
                                                <td colspan="2"><strong>Total Equity</strong></td>
                                                <td style="text-align: right;">
                                                    <strong>KES <?php echo number_format($report_data['totals']['total_equity'], 2); ?></strong>
                                                </td>
                                            </tr>
                                            
                                            <tr class="total-row" style="background: #e0e7ff; border-top: 3px solid #667eea;">
                                                <td colspan="2"><strong>TOTAL LIABILITIES & EQUITY</strong></td>
                                                <td style="text-align: right;">
                                                    <strong style="font-size: 1.25rem;">
                                                        KES <?php echo number_format($report_data['totals']['total_liabilities'] + $report_data['totals']['total_equity'], 2); ?>
                                                    </strong>
                                                </td>
                                            </tr>
                                            
                                            <tr style="background: <?php echo abs($report_data['totals']['total_assets'] - ($report_data['totals']['total_liabilities'] + $report_data['totals']['total_equity'])) < 0.01 ? '#d1fae5' : '#fee2e2'; ?>;">
                                                <td colspan="2"><strong>BALANCE CHECK</strong></td>
                                                <td style="text-align: right;">
                                                    <strong class="<?php echo abs($report_data['totals']['total_assets'] - ($report_data['totals']['total_liabilities'] + $report_data['totals']['total_equity'])) < 0.01 ? 'amount-positive' : 'amount-negative'; ?>">
                                                        <?php 
                                                        $difference = $report_data['totals']['total_assets'] - ($report_data['totals']['total_liabilities'] + $report_data['totals']['total_equity']);
                                                        echo abs($difference) < 0.01 ? 'BALANCED ✓' : 'OUT OF BALANCE: KES ' . number_format(abs($difference), 2);
                                                        ?>
                                                    </strong>
                                                </td>
                                            </tr>
                                        </tbody>
                                    </table>
                                </div>
                            <?php endif; ?>
                        </div>
                        
                        <!-- Report Footer -->
                        <div class="report-footer">
                            <div>
                                <small class="text-muted">
                                    Report generated on <?php echo date('d M Y H:i:s'); ?>
                                </small>
                            </div>
                            <div>
                                <button class="btn btn-secondary" onclick="window.print();">
                                    <i class="ri-printer-line me-1"></i>Print Report
                                </button>
                                <button class="btn btn-success" onclick="exportToExcel();">
                                    <i class="ri-file-excel-line me-1"></i>Export to Excel
                                </button>
                            </div>
                        </div>
                    </div>
                </div>
            </div>
            <?php include 'layouts/footer.php'; ?>
        </div>
    </div>

    <?php include 'layouts/vendor-scripts.php'; ?>
    <script src="assets/js/app.js"></script>
    
    <script>
    function exportToExcel() {
        Swal.fire({
            title: 'Export to Excel',
            text: 'This feature will be available soon',
            icon: 'info'
        });
    }
    
    // Print styles
    window.addEventListener('beforeprint', function() {
        document.querySelector('.report-tabs').style.display = 'none';
        document.querySelector('.report-footer').style.display = 'none';
    });
    
    window.addEventListener('afterprint', function() {
        document.querySelector('.report-tabs').style.display = 'flex';
        document.querySelector('.report-footer').style.display = 'flex';
    });
    </script>
</body>
</html>