<?php
ob_start();
include 'db.php';
include 'layout.php';
include 'financial_helpers.php';

$store_id = $_SESSION['store_id'] ?? 1;

// Get filter parameters
$from_date = $_GET['from_date'] ?? date('Y-m-01'); // First day of current month
$to_date = $_GET['to_date'] ?? date('Y-m-d');
$compare_with_previous = $_GET['compare_with_previous'] ?? '0';

// Calculate previous period dates for comparison
$from_date_obj = new DateTime($from_date);
$to_date_obj = new DateTime($to_date);
$period_days = $from_date_obj->diff($to_date_obj)->days + 1;

$prev_from_date = clone $from_date_obj;
$prev_from_date->modify("-{$period_days} days");
$prev_to_date = clone $from_date_obj;
$prev_to_date->modify('-1 day');

// Get fiscal periods for dropdown (if table exists)
$fiscal_periods = null;
try {
    $stmt = $conn->prepare("SELECT id, period_name, start_date, end_date FROM fiscal_periods WHERE store_id = ? ORDER BY start_date DESC");
    $stmt->bind_param("i", $store_id);
    $stmt->execute();
    $fiscal_periods = $stmt->get_result();
} catch (Exception $e) {
    // Table doesn't exist yet, that's okay
    $fiscal_periods = null;
}

// Function to get account balances for a period
function getAccountBalancesForPeriod($conn, $store_id, $from_date, $to_date) {
    $query = "
        SELECT 
            coa.id,
            coa.account_code,
            coa.account_name,
            coa.account_type,
            coa.account_category,
            coa.opening_balance,
            COALESCE(SUM(CASE WHEN jel.debit_amount > 0 THEN jel.debit_amount ELSE 0 END), 0) as total_debits,
            COALESCE(SUM(CASE WHEN jel.credit_amount > 0 THEN jel.credit_amount ELSE 0 END), 0) as total_credits
        FROM chart_of_accounts coa
        LEFT JOIN journal_entry_lines jel ON coa.id = jel.account_id
        LEFT JOIN journal_entries je ON jel.journal_entry_id = je.id
        WHERE coa.store_id = ?
        AND (je.status = 'posted' OR je.status IS NULL)
        AND (je.status != 'void' OR je.status IS NULL)
        AND (je.entry_date BETWEEN ? AND ? OR je.entry_date IS NULL)
        GROUP BY coa.id, coa.account_code, coa.account_name, coa.account_type, coa.account_category, coa.opening_balance
        ORDER BY coa.account_code
    ";
    
    $stmt = $conn->prepare($query);
    $stmt->bind_param("iss", $store_id, $from_date, $to_date);
    $stmt->execute();
    return $stmt->get_result();
}

// Get current period data
$current_period_data = getAccountBalancesForPeriod($conn, $store_id, $from_date, $to_date);
$current_revenue = 0;
$current_expenses = 0;
$current_cost_of_sales = 0;

$revenue_accounts = [];
$expense_accounts = [];
$cost_of_sales_accounts = [];

while ($row = $current_period_data->fetch_assoc()) {
    $balance = $row['opening_balance'] + $row['total_debits'] - $row['total_credits'];
    
    // For liability, equity, and revenue accounts, reverse the balance
    if (in_array($row['account_type'], ['liability', 'equity', 'revenue'])) {
        $balance = -$balance;
    }
    
    if ($row['account_type'] === 'revenue') {
        // Only include revenue accounts with non-zero balance
        if (abs($balance) > 0.01) {
            $revenue_accounts[] = [
                'code' => $row['account_code'],
                'name' => $row['account_name'],
                'balance' => $balance,
                'debits' => $row['total_debits'],
                'credits' => $row['total_credits']
            ];
            $current_revenue += $balance;
        }
    } elseif ($row['account_type'] === 'expense') {
        if ($row['account_category'] === 'cost_of_sales') {
            // Only include cost of sales accounts with non-zero balance
            if (abs($balance) > 0.01) {
                $cost_of_sales_accounts[] = [
                    'code' => $row['account_code'],
                    'name' => $row['account_name'],
                    'balance' => $balance,
                    'debits' => $row['total_debits'],
                    'credits' => $row['total_credits']
                ];
                $current_cost_of_sales += $balance;
            }
        } else {
            // Only include operating expense accounts with non-zero balance
            if (abs($balance) > 0.01) {
                $expense_accounts[] = [
                    'code' => $row['account_code'],
                    'name' => $row['account_name'],
                    'balance' => $balance,
                    'debits' => $row['total_debits'],
                    'credits' => $row['total_credits']
                ];
                $current_expenses += $balance;
            }
        }
    }
}

// Get previous period data for comparison
$previous_period_data = null;
$previous_revenue = 0;
$previous_expenses = 0;
$previous_cost_of_sales = 0;

if ($compare_with_previous == '1') {
    $previous_period_data = getAccountBalancesForPeriod($conn, $store_id, $prev_from_date->format('Y-m-d'), $prev_to_date->format('Y-m-d'));
    
    while ($row = $previous_period_data->fetch_assoc()) {
        $balance = $row['opening_balance'] + $row['total_debits'] - $row['total_credits'];
        
        if (in_array($row['account_type'], ['liability', 'equity', 'revenue'])) {
            $balance = -$balance;
        }
        
        if ($row['account_type'] === 'revenue') {
            $previous_revenue += $balance;
        } elseif ($row['account_type'] === 'expense') {
            if ($row['account_category'] === 'cost_of_sales') {
                $previous_cost_of_sales += $balance;
            } else {
                $previous_expenses += $balance;
            }
        }
    }
}

// Calculate totals
$current_gross_profit = $current_revenue - $current_cost_of_sales;
$current_net_profit = $current_gross_profit - $current_expenses;

$previous_gross_profit = $previous_revenue - $previous_cost_of_sales;
$previous_net_profit = $previous_gross_profit - $previous_expenses;

// Calculate percentage changes
$revenue_change = $previous_revenue != 0 ? (($current_revenue - $previous_revenue) / $previous_revenue) * 100 : 0;
$expenses_change = $previous_expenses != 0 ? (($current_expenses - $previous_expenses) / $previous_expenses) * 100 : 0;
$net_profit_change = $previous_net_profit != 0 ? (($current_net_profit - $previous_net_profit) / $previous_net_profit) * 100 : 0;
?>

<div class="page-wrapper">
    <div class="content">
        <div class="page-header">
            <div class="page-title">
                <h4>Profit & Loss Statement</h4>
                <h6>For the period <?php echo date('F j, Y', strtotime($from_date)); ?> to <?php echo date('F j, Y', strtotime($to_date)); ?></h6>
            </div>
            <div class="page-btn">
                <button class="btn btn-added" onclick="window.print()">
                    <i class="fa fa-print"></i> Print
                </button>
            </div>
        </div>

        <!-- Filters -->
        <div class="card">
            <div class="card-body">
                <form method="GET" class="row g-3">
                    <div class="col-md-3">
                        <label for="from_date" class="form-label">From Date</label>
                        <input type="date" class="form-control" id="from_date" name="from_date" value="<?php echo htmlspecialchars($from_date); ?>">
                    </div>
                    <div class="col-md-3">
                        <label for="to_date" class="form-label">To Date</label>
                        <input type="date" class="form-control" id="to_date" name="to_date" value="<?php echo htmlspecialchars($to_date); ?>">
                    </div>
                    <div class="col-md-3">
                        <label for="compare_with_previous" class="form-label">Compare with Previous Period</label>
                        <select class="form-select" id="compare_with_previous" name="compare_with_previous">
                            <option value="0" <?php echo $compare_with_previous == '0' ? 'selected' : ''; ?>>No</option>
                            <option value="1" <?php echo $compare_with_previous == '1' ? 'selected' : ''; ?>>Yes</option>
                        </select>
                    </div>
                    <div class="col-md-3">
                        <label class="form-label">&nbsp;</label>
                        <div>
                            <button type="submit" class="btn btn-primary">
                                <i class="fa fa-search"></i> Generate Report
                            </button>
                            <a href="profit_loss.php" class="btn btn-secondary">
                                <i class="fa fa-refresh"></i> Reset
                            </a>
                        </div>
                    </div>
                </form>
            </div>
        </div>

        <!-- Summary Cards -->
        <div class="row">
            <div class="col-md-3">
                <div class="card">
                    <div class="card-body">
                        <div class="d-flex align-items-center">
                            <div class="flex-grow-1">
                                <h6 class="text-muted mb-2">Total Revenue</h6>
                                <h4 class="text-success mb-0"><?php echo number_format($current_revenue, 2); ?></h4>
                                <?php if ($compare_with_previous == '1'): ?>
                                    <small class="text-<?php echo $revenue_change >= 0 ? 'success' : 'danger'; ?>">
                                        <?php echo $revenue_change >= 0 ? '+' : ''; ?><?php echo number_format($revenue_change, 1); ?>% vs previous
                                    </small>
                                <?php endif; ?>
                            </div>
                            <div class="ms-3">
                                <i class="fa fa-arrow-up fa-2x text-success"></i>
                            </div>
                        </div>
                    </div>
                </div>
            </div>
            <div class="col-md-3">
                <div class="card">
                    <div class="card-body">
                        <div class="d-flex align-items-center">
                            <div class="flex-grow-1">
                                <h6 class="text-muted mb-2">Cost of Sales</h6>
                                <h4 class="text-danger mb-0"><?php echo number_format($current_cost_of_sales, 2); ?></h4>
                                <?php if ($compare_with_previous == '1'): ?>
                                    <small class="text-<?php echo $previous_cost_of_sales != 0 ? (($current_cost_of_sales - $previous_cost_of_sales) / $previous_cost_of_sales) * 100 >= 0 ? 'danger' : 'success' : 'muted'; ?>">
                                        <?php 
                                        if ($previous_cost_of_sales != 0) {
                                            $change = (($current_cost_of_sales - $previous_cost_of_sales) / $previous_cost_of_sales) * 100;
                                            echo ($change >= 0 ? '+' : '') . number_format($change, 1) . '% vs previous';
                                        } else {
                                            echo 'No previous data';
                                        }
                                        ?>
                                    </small>
                                <?php endif; ?>
                            </div>
                            <div class="ms-3">
                                <i class="fa fa-arrow-down fa-2x text-danger"></i>
                            </div>
                        </div>
                    </div>
                </div>
            </div>
            <div class="col-md-3">
                <div class="card">
                    <div class="card-body">
                        <div class="d-flex align-items-center">
                            <div class="flex-grow-1">
                                <h6 class="text-muted mb-2">Operating Expenses</h6>
                                <h4 class="text-warning mb-0"><?php echo number_format($current_expenses, 2); ?></h4>
                                <?php if ($compare_with_previous == '1'): ?>
                                    <small class="text-<?php echo $expenses_change >= 0 ? 'danger' : 'success'; ?>">
                                        <?php echo $expenses_change >= 0 ? '+' : ''; ?><?php echo number_format($expenses_change, 1); ?>% vs previous
                                    </small>
                                <?php endif; ?>
                            </div>
                            <div class="ms-3">
                                <i class="fa fa-arrow-down fa-2x text-warning"></i>
                            </div>
                        </div>
                    </div>
                </div>
            </div>
            <div class="col-md-3">
                <div class="card">
                    <div class="card-body">
                        <div class="d-flex align-items-center">
                            <div class="flex-grow-1">
                                <h6 class="text-muted mb-2">Net Profit/Loss</h6>
                                <h4 class="text-<?php echo $current_net_profit >= 0 ? 'success' : 'danger'; ?> mb-0">
                                    <?php echo number_format($current_net_profit, 2); ?>
                                </h4>
                                <?php if ($compare_with_previous == '1'): ?>
                                    <small class="text-<?php echo $net_profit_change >= 0 ? 'success' : 'danger'; ?>">
                                        <?php echo $net_profit_change >= 0 ? '+' : ''; ?><?php echo number_format($net_profit_change, 1); ?>% vs previous
                                    </small>
                                <?php endif; ?>
                            </div>
                            <div class="ms-3">
                                <i class="fa fa-<?php echo $current_net_profit >= 0 ? 'arrow-up' : 'arrow-down'; ?> fa-2x text-<?php echo $current_net_profit >= 0 ? 'success' : 'danger'; ?>"></i>
                            </div>
                        </div>
                    </div>
                </div>
            </div>
        </div>

        <!-- Profit & Loss Statement -->
        <div class="card">
            <div class="card-body">
                <h5 class="card-title">Profit & Loss Statement</h5>
                
                <div class="table-responsive">
                    <table class="table table-borderless">
                        <tbody>
                            <!-- Revenue Section -->
                            <tr class="table-primary">
                                <td colspan="3"><strong>REVENUE</strong></td>
                                <td class="text-end"><strong><?php echo number_format($current_revenue, 2); ?></strong></td>
                                <?php if ($compare_with_previous == '1'): ?>
                                    <td class="text-end"><strong><?php echo number_format($previous_revenue, 2); ?></strong></td>
                                    <td class="text-end"><strong><?php echo number_format($current_revenue - $previous_revenue, 2); ?></strong></td>
                                <?php endif; ?>
                            </tr>
                            
                            <?php foreach ($revenue_accounts as $account): ?>
                                <tr>
                                    <td style="padding-left: 30px;"><?php echo htmlspecialchars($account['code']); ?></td>
                                    <td><?php echo htmlspecialchars($account['name']); ?></td>
                                    <td></td>
                                    <td class="text-end"><?php echo number_format($account['balance'], 2); ?></td>
                                    <?php if ($compare_with_previous == '1'): ?>
                                        <td class="text-end">-</td>
                                        <td class="text-end">-</td>
                                    <?php endif; ?>
                                </tr>
                            <?php endforeach; ?>
                            
                            <!-- Cost of Sales Section -->
                            <tr class="table-danger">
                                <td colspan="3"><strong>COST OF SALES</strong></td>
                                <td class="text-end"><strong>(<?php echo number_format($current_cost_of_sales, 2); ?>)</strong></td>
                                <?php if ($compare_with_previous == '1'): ?>
                                    <td class="text-end"><strong>(<?php echo number_format($previous_cost_of_sales, 2); ?>)</strong></td>
                                    <td class="text-end"><strong>(<?php echo number_format($current_cost_of_sales - $previous_cost_of_sales, 2); ?>)</strong></td>
                                <?php endif; ?>
                            </tr>
                            
                            <?php foreach ($cost_of_sales_accounts as $account): ?>
                                <tr>
                                    <td style="padding-left: 30px;"><?php echo htmlspecialchars($account['code']); ?></td>
                                    <td><?php echo htmlspecialchars($account['name']); ?></td>
                                    <td></td>
                                    <td class="text-end">(<?php echo number_format($account['balance'], 2); ?>)</td>
                                    <?php if ($compare_with_previous == '1'): ?>
                                        <td class="text-end">-</td>
                                        <td class="text-end">-</td>
                                    <?php endif; ?>
                                </tr>
                            <?php endforeach; ?>
                            
                            <!-- Gross Profit -->
                            <tr class="table-success">
                                <td colspan="3"><strong>GROSS PROFIT</strong></td>
                                <td class="text-end"><strong><?php echo number_format($current_gross_profit, 2); ?></strong></td>
                                <?php if ($compare_with_previous == '1'): ?>
                                    <td class="text-end"><strong><?php echo number_format($previous_gross_profit, 2); ?></strong></td>
                                    <td class="text-end"><strong><?php echo number_format($current_gross_profit - $previous_gross_profit, 2); ?></strong></td>
                                <?php endif; ?>
                            </tr>
                            
                            <!-- Operating Expenses Section -->
                            <tr class="table-warning">
                                <td colspan="3"><strong>OPERATING EXPENSES</strong></td>
                                <td class="text-end"><strong>(<?php echo number_format($current_expenses, 2); ?>)</strong></td>
                                <?php if ($compare_with_previous == '1'): ?>
                                    <td class="text-end"><strong>(<?php echo number_format($previous_expenses, 2); ?>)</strong></td>
                                    <td class="text-end"><strong>(<?php echo number_format($current_expenses - $previous_expenses, 2); ?>)</strong></td>
                                <?php endif; ?>
                            </tr>
                            
                            <?php foreach ($expense_accounts as $account): ?>
                                <tr>
                                    <td style="padding-left: 30px;"><?php echo htmlspecialchars($account['code']); ?></td>
                                    <td><?php echo htmlspecialchars($account['name']); ?></td>
                                    <td></td>
                                    <td class="text-end">(<?php echo number_format($account['balance'], 2); ?>)</td>
                                    <?php if ($compare_with_previous == '1'): ?>
                                        <td class="text-end">-</td>
                                        <td class="text-end">-</td>
                                    <?php endif; ?>
                                </tr>
                            <?php endforeach; ?>
                            
                            <!-- Net Profit/Loss -->
                            <tr class="table-<?php echo $current_net_profit >= 0 ? 'success' : 'danger'; ?>">
                                <td colspan="3"><strong>NET <?php echo $current_net_profit >= 0 ? 'PROFIT' : 'LOSS'; ?></strong></td>
                                <td class="text-end"><strong><?php echo number_format($current_net_profit, 2); ?></strong></td>
                                <?php if ($compare_with_previous == '1'): ?>
                                    <td class="text-end"><strong><?php echo number_format($previous_net_profit, 2); ?></strong></td>
                                    <td class="text-end"><strong><?php echo number_format($current_net_profit - $previous_net_profit, 2); ?></strong></td>
                                <?php endif; ?>
                            </tr>
                        </tbody>
                    </table>
                </div>
                
                <?php if ($compare_with_previous == '1'): ?>
                    <div class="mt-3">
                        <small class="text-muted">
                            <strong>Previous Period:</strong> <?php echo $prev_from_date->format('F j, Y'); ?> to <?php echo $prev_to_date->format('F j, Y'); ?>
                        </small>
                    </div>
                <?php endif; ?>
            </div>
        </div>
    </div>
</div>

<script>
// Auto-submit form when date changes
document.getElementById('from_date').addEventListener('change', function() {
    this.form.submit();
});

document.getElementById('to_date').addEventListener('change', function() {
    this.form.submit();
});

document.getElementById('compare_with_previous').addEventListener('change', function() {
    this.form.submit();
});
</script>

</body>
</html>
