// modules/dashboard/analytics.php
<?php
require_once '../../config/config.php';
require_once '../../config/database.php';
require_once '../../includes/header.php';
?>

<div class="row">
    <!-- Sales Analytics Filters -->
    <div class="col-12 mb-4">
        <div class="card">
            <div class="card-body">
                <form id="analyticsFilterForm" class="row g-3">
                    <div class="col-md-3">
                        <label class="form-label">Date Range</label>
                        <input type="text" class="form-control daterange" name="date_range">
                    </div>
                    <div class="col-md-2">
                        <label class="form-label">Group By</label>
                        <select class="form-select" name="group_by">
                            <option value="day">Daily</option>
                            <option value="week">Weekly</option>
                            <option value="month">Monthly</option>
                        </select>
                    </div>
                    <div class="col-md-2">
                        <label class="form-label">Compare With</label>
                        <select class="form-select" name="compare_with">
                            <option value="">None</option>
                            <option value="previous_period">Previous Period</option>
                            <option value="previous_year">Previous Year</option>
                        </select>
                    </div>
                    <div class="col-md-3">
                        <label class="form-label">Metrics</label>
                        <select class="form-select" name="metrics" multiple>
                            <option value="revenue" selected>Revenue</option>
                            <option value="invoices">Invoice Count</option>
                            <option value="average">Average Value</option>
                        </select>
                    </div>
                    <div class="col-md-2">
                        <label class="form-label">&nbsp;</label>
                        <button type="submit" class="btn btn-primary w-100">Apply</button>
                    </div>
                </form>
            </div>
        </div>
    </div>

    <!-- Analytics Overview -->
    <div class="col-12 mb-4">
        <div class="card">
            <div class="card-header">
                <h5 class="card-title">Sales Analytics</h5>
            </div>
            <div class="card-body">
                <div id="salesAnalyticsChart" style="height: 400px;"></div>
            </div>
        </div>
    </div>

    <!-- Key Performance Indicators -->
    <div class="col-md-6 mb-4">
        <div class="card">
            <div class="card-header">
                <h5 class="card-title">Key Metrics</h5>
            </div>
            <div class="card-body">
                <div class="row">
                    <div class="col-md-6 mb-3">
                        <div class="metric-card">
                            <h6>Revenue Growth</h6>
                            <h3 id="revenueGrowth">0%</h3>
                            <div class="trend"></div>
                        </div>
                    </div>
                    <div class="col-md-6 mb-3">
                        <div class="metric-card">
                            <h6>Average Invoice Value</h6>
                            <h3 id="avgInvoiceValue">$0</h3>
                            <div class="trend"></div>
                        </div>
                    </div>
                    <div class="col-md-6 mb-3">
                        <div class="metric-card">
                            <h6>Collection Rate</h6>
                            <h3 id="collectionRate">0%</h3>
                            <div class="trend"></div>
                        </div>
                    </div>
                    <div class="col-md-6 mb-3">
                        <div class="metric-card">
                            <h6>Customer Growth</h6>
                            <h3 id="customerGrowth">0%</h3>
                            <div class="trend"></div>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </div>

    <!-- Sales Distribution -->
    <div class="col-md-6 mb-4">
        <div class="card">
            <div class="card-header">
                <h5 class="card-title">Sales Distribution</h5>
            </div>
            <div class="card-body">
                <div id="salesDistributionChart" style="height: 300px;"></div>
            </div>
        </div>
    </div>

    <!-- Customer Segments -->
    <div class="col-md-12 mb-4">
        <div class="card">
            <div class="card-header d-flex justify-content-between align-items-center">
                <h5 class="card-title">Customer Segments</h5>
                <button class="btn btn-sm btn-primary" onclick="exportSegments()">
                    Export
                </button>
            </div>
            <div class="card-body">
                <div class="table-responsive">
                    <table id="customerSegmentsTable" class="table">
                        <thead>
                            <tr>
                                <th>Segment</th>
                                <th>Customers</th>
                                <th>Revenue</th>
                                <th>Avg. Value</th>
                                <th>Growth</th>
                                <th>Trend</th>
                            </tr>
                        </thead>
                    </table>
                </div>
            </div>
        </div>
    </div>
</div>

<!-- Custom SQL Views for Analytics -->
<?php
$sql = "
CREATE VIEW vw_sales_analytics AS
SELECT 
    DATE(invoice_date) as date,
    COUNT(*) as invoice_count,
    SUM(total_amount) as revenue,
    AVG(total_amount) as average_value,
    SUM(CASE WHEN status = 'paid' THEN total_amount ELSE 0 END) as collected_amount,
    COUNT(DISTINCT customer_id) as unique_customers
FROM invoices
GROUP BY DATE(invoice_date);

CREATE VIEW vw_customer_segments AS
SELECT 
    CASE 
        WHEN total_sales >= 10000 THEN 'High Value'
        WHEN total_sales >= 5000 THEN 'Medium Value'
        ELSE 'Low Value'
    END as segment,
    COUNT(*) as customer_count,
    SUM(total_sales) as total_revenue,
    AVG(total_sales) as avg_revenue,
    SUM(total_sales) - LAG(SUM(total_sales)) OVER (ORDER BY MIN(created_at)) as growth
FROM (
    SELECT 
        c.id,
        c.created_at,
        COALESCE(SUM(i.total_amount), 0) as total_sales
    FROM customers c
    LEFT JOIN invoices i ON c.id = i.customer_id
    GROUP BY c.id, c.created_at
) customer_stats
GROUP BY segment;
";

// Add JavaScript for Analytics
?>
<script src="https://cdn.jsdelivr.net/npm/apexcharts"></script>
<script src="https://cdn.jsdelivr.net/npm/daterangepicker/daterangepicker.min.js"></script>
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/daterangepicker/daterangepicker.css">

<script>
class DashboardAnalytics {
    constructor() {
        this.charts = {};
        this.filters = {
            dateRange: null,
            groupBy: 'day',
            compareWith: '',
            metrics: ['revenue']
        };
    }

    init() {
        this.initializeDateRangePicker();
        this.initializeCharts();
        this.bindEvents();
        this.loadData();
    }

    initializeDateRangePicker() {
        $('.daterange').daterangepicker({
            startDate: moment().subtract(30, 'days'),
            endDate: moment(),
            ranges: {
                'Today': [moment(), moment()],
                'Yesterday': [moment().subtract(1, 'days'), moment().subtract(1, 'days')],
                'Last 7 Days': [moment().subtract(6, 'days'), moment()],
                'Last 30 Days': [moment().subtract(29, 'days'), moment()],
                'This Month': [moment().startOf('month'), moment().endOf('month')],
                'Last Month': [moment().subtract(1, 'month').startOf('month'), moment().subtract(1, 'month').endOf('month')]
            }
        });
    }

    initializeCharts() {
        // Sales Analytics Chart
        this.charts.salesAnalytics = new ApexCharts(
            document.querySelector("#salesAnalyticsChart"),
            this.getSalesAnalyticsOptions()
        );
        this.charts.salesAnalytics.render();

        // Sales Distribution Chart
        this.charts.salesDistribution = new ApexCharts(
            document.querySelector("#salesDistributionChart"),
            this.getSalesDistributionOptions()
        );
        this.charts.salesDistribution.render();
    }

    getSalesAnalyticsOptions() {
        return {
            chart: {
                type: 'line',
                height: 400,
                toolbar: {
                    show: true
                }
            },
            stroke: {
                curve: 'smooth',
                width: 2
            },
            series: [{
                name: 'Revenue',
                data: []
            }],
            xaxis: {
                type: 'datetime'
            },
            yaxis: {
                labels: {
                    formatter: function(value) {
                        return '$' + value.toLocaleString();
                    }
                }
            },
            tooltip: {
                x: {
                    format: 'dd MMM yyyy'
                }
            }
        };
    }

    getSalesDistributionOptions() {
        return {
            chart: {
                type: 'donut',
                height: 300
            },
            series: [],
            labels: [],
            responsive: [{
                breakpoint: 480,
                options: {
                    chart: {
                        width: 200
                    },
                    legend: {
                        position: 'bottom'
                    }
                }
            }]
        };
    }

    bindEvents() {
        $('#analyticsFilterForm').submit((e) => {
            e.preventDefault();
            this.updateFilters();
            this.loadData();
        });
    }

    updateFilters() {
        const formData = new FormData($('#analyticsFilterForm')[0]);
        this.filters = {
            dateRange: formData.get('date_range'),
            groupBy: formData.get('group_by'),
            compareWith: formData.get('compare_with'),
            metrics: formData.getAll('metrics')
        };
    }

    loadData() {
        $.get('api.php', {
            action: 'get_analytics_data',
            ...this.filters
        }, (response) => {
            if(response.success) {
                this.updateCharts(response.data);
                this.updateMetrics(response.data.metrics);
                this.updateSegments(response.data.segments);
            }
        });
    }

    updateCharts(data) {
        // Update Sales Analytics Chart
        this.charts.salesAnalytics.updateSeries([{
            name: 'Revenue',
            data: data.sales_trend
        }]);

        // Update Sales Distribution Chart
        this.charts.salesDistribution.updateSeries(data.distribution.values);
        this.charts.salesDistribution.updateOptions({
            labels: data.distribution.labels
        });
    }

    updateMetrics(metrics) {
        $('#revenueGrowth').text(metrics.revenue_growth + '%');
        $('#avgInvoiceValue').text('$' + metrics.avg_invoice_value.toLocaleString());
        $('#collectionRate').text(metrics.collection_rate + '%');
        $('#customerGrowth').text(metrics.customer_growth + '%');

        // Update trends
        this.updateTrendIndicator('#revenueGrowth', metrics.revenue_growth);
        this.updateTrendIndicator('#avgInvoiceValue', metrics.avg_invoice_trend);
        this.updateTrendIndicator('#collectionRate', metrics.collection_rate_trend);
        this.updateTrendIndicator('#customerGrowth', metrics.customer_growth);
    }

    updateTrendIndicator(selector, value) {
        const element = $(selector).siblings('.trend');
        element.html(value >= 0 ? 
            '<i class="fas fa-arrow-up text-success"></i>' : 
            '<i class="fas fa-arrow-down text-danger"></i>'
        );
    }

    updateSegments(segments) {
        $('#customerSegmentsTable').DataTable({
            data: segments,
            columns: [
                { data: 'segment' },
                { data: 'customers' },
                { 
                    data: 'revenue',
                    render: function(data) {
                        return '$' + data.toLocaleString();
                    }
                },
                { 
                    data: 'avg_value',
                    render: function(data) {
                        return '$' + data.toLocaleString();
                    }
                },
                { 
                    data: 'growth',
                    render: function(data) {
                        return data + '%';
                    }
                },
                {
                    data: 'trend',
                    render: function(data) {
                        return `<div class="sparkline">${data.join(',')}</div>`;
                    }
                }
            ],
            destroy: true
        });

        // Initialize sparklines
        $('.sparkline').sparkline('html', {
            type: 'line',
            width: '100px',
            height: '30px'
        });
    }
}

// Initialize Analytics
document.addEventListener('DOMContentLoaded', () => {
    const analytics = new DashboardAnalytics();
    analytics.init();
});

function exportSegments() {
    window.location.href = 'export_segments.php?' + $.param(analytics.filters);
}
</script>

<?php require_once '../../includes/footer.php'; ?> dashboard-api.php // modules/dashboard/api.php
<?php
require_once '../../config/config.php';
require_once '../../config/database.php';

header('Content-Type: application/json');

$action = $_GET['action'] ?? '';

switch($action) {
    case 'revenue_data':
        $range = $_GET['range'] ?? 'month';
        $data = getRevenueData($db, $range);
        echo json_encode(['success' => true, 'data' => $data]);
        break;

    case 'payment_methods':
        $data = getPaymentMethodsData($db);
        echo json_encode(['success' => true, 'data' => $data]);
        break;

    case 'get_notifications':
        $data = getNotifications($db);
        echo json_encode(['success' => true, 'data' => $data]);
        break;

    case 'get_sales_summary':
        $data = getSalesSummary($db);
        echo json_encode(['success' => true, 'data' => $data]);
        break;
}

function getRevenueData($db, $range) {
    switch($range) {
        case 'week':
            $query = "
                SELECT 
                    DATE_FORMAT(invoice_date, '%W') as label,
                    SUM(total_amount) as value
                FROM invoices
                WHERE invoice_date >= DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY)
                GROUP BY DATE(invoice_date)
                ORDER BY invoice_date
            ";
            break;

        case 'month':
            $query = "
                SELECT 
                    DATE_FORMAT(invoice_date, '%d %b') as label,
                    SUM(total_amount) as value
                FROM invoices
                WHERE invoice_date >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)
                GROUP BY DATE(invoice_date)
                ORDER BY invoice_date
            ";
            break;

        case 'year':
            $query = "
                SELECT 
                    DATE_FORMAT(invoice_date, '%b %Y') as label,
                    SUM(total_amount) as value
                FROM invoices
                WHERE invoice_date >= DATE_SUB(CURRENT_DATE, INTERVAL 12 MONTH)
                GROUP BY MONTH(invoice_date), YEAR(invoice_date)
                ORDER BY invoice_date
            ";
            break;
    }

    $result = $db->query($query);
    $labels = [];
    $values = [];

    while($row = $result->fetch_assoc()) {
        $labels[] = $row['label'];
        $values[] = floatval($row['value']);
    }

    return [
        'labels' => $labels,
        'values' => $values
    ];
}

function getPaymentMethodsData($db) {
    $query = "
        SELECT 
            payment_method,
            COUNT(*) as count,
            SUM(amount) as total_amount
        FROM payments
        GROUP BY payment_method
        ORDER BY total_amount DESC
    ";

    $result = $db->query($query);
    $labels = [];
    $values = [];

    while($row = $result->fetch_assoc()) {
        $labels[] = ucfirst($row['payment_method']);
        $values[] = intval($row['count']);
    }

    return [
        'labels' => $labels,
        'values' => $values
    ];
}

function getNotifications($db) {
    $notifications = [];

    // Due Invoices
    $query = "
        SELECT 
            i.invoice_number,
            i.total_amount,
            i.due_date,
            c.customer_name
        FROM invoices i
        LEFT JOIN customers c ON i.customer_id = c.id
        WHERE i.status != 'paid' 
        AND i.due_date <= DATE_ADD(CURRENT_DATE, INTERVAL 7 DAY)
        ORDER BY i.due_date
        LIMIT 5
    ";
    
    $result = $db->query($query);
    while($row = $result->fetch_assoc()) {
        $notifications[] = [
            'type' => 'invoice_due',
            'message' => "Invoice {$row['invoice_number']} for {$row['customer_name']} is due on " . 
                        formatDate($row['due_date']),
            'amount' => $row['total_amount'],
            'date' => $row['due_date']
        ];
    }

    // Low Stock Alerts
    $query = "
        SELECT 
            product_name,
            stock_quantity
        FROM products
        WHERE stock_quantity <= minimum_stock
        LIMIT 5
    ";
    
    $result = $db->query($query);
    while($row = $result->fetch_assoc()) {
        $notifications[] = [
            'type' => 'low_stock',
            'message' => "Low stock alert: {$row['product_name']} ({$row['stock_quantity']} units remaining)",
            'quantity' => $row['stock_quantity']
        ];
    }

    // Recent Payments
    $query = "
        SELECT 
            p.amount,
            p.payment_date,
            c.customer_name,
            i.invoice_number
        FROM payments p
        LEFT JOIN invoices i ON p.invoice_id = i.id
        LEFT JOIN customers c ON i.customer_id = c.id
        ORDER BY p.payment_date DESC
        LIMIT 5
    ";
    
    $result = $db->query($query);
    while($row = $result->fetch_assoc()) {
        $notifications[] = [
            'type' => 'payment_received',
            'message' => "Payment received from {$row['customer_name']} for invoice {$row['invoice_number']}",
            'amount' => $row['amount'],
            'date' => $row['payment_date']
        ];
    }

    return $notifications;
}

function getSalesSummary($db) {
    $summary = [];

    // Today's Sales
    $query = "
        SELECT 
            COUNT(*) as invoice_count,
            COALESCE(SUM(total_amount), 0) as total_amount
        FROM invoices
        WHERE DATE(invoice_date) = CURRENT_DATE
    ";
    $result = $db->query($query)->fetch_assoc();
    $summary['today'] = $result;

    // This Week's Sales
    $query = "
        SELECT 
            COUNT(*) as invoice_count,
            COALESCE(SUM(total_amount), 0) as total_amount
        FROM invoices
        WHERE YEARWEEK(invoice_date) = YEARWEEK(CURRENT_DATE)
    ";
    $result = $db->query($query)->fetch_assoc();
    $summary['week'] = $result;

    // This Month's Sales
    $query = "
        SELECT 
            COUNT(*) as invoice_count,
            COALESCE(SUM(total_amount), 0) as total_amount
        FROM invoices
        WHERE MONTH(invoice_date) = MONTH(CURRENT_DATE)
        AND YEAR(invoice_date) = YEAR(CURRENT_DATE)
    ";
    $result = $db->query($query)->fetch_assoc();
    $summary['month'] = $result;

    return $summary;
}index.php <?php
require_once '../../config/config.php';
require_once '../../config/database.php';
require_once '../../includes/header.php';

// Function to map status to Bootstrap badge color
function getStatusColor($status) {
    switch ($status) {
        case 'paid':
            return 'success';
        case 'pending':
            return 'warning';
        case 'cancelled':
            return 'danger';
        default:
            return 'secondary';
    }
}

// Get dashboard statistics
$stats = getDashboardStats($db);
?>

<div class="row">
    <!-- Quick Stats Cards -->
    <div class="col-xl-3 col-md-6 mb-4">
        <div class="card border-left-primary h-100">
            <div class="card-body">
                <div class="row align-items-center">
                    <div class="col">
                        <div class="text-xs font-weight-bold text-primary text-uppercase mb-1">
                            Total Sales (Monthly)
                        </div>
                        <div class="h5 mb-0 font-weight-bold">
                            <?php echo formatCurrency($stats['monthly_sales']); ?>
                        </div>
                    </div>
                    <div class="col-auto">
                        <i class="fas fa-calendar fa-2x text-gray-300"></i>
                    </div>
                </div>
            </div>
        </div>
    </div>

    <div class="col-xl-3 col-md-6 mb-4">
        <div class="card border-left-success h-100">
            <div class="card-body">
                <div class="row align-items-center">
                    <div class="col">
                        <div class="text-xs font-weight-bold text-success text-uppercase mb-1">
                            Outstanding Invoices
                        </div>
                        <div class="h5 mb-0 font-weight-bold">
                            <?php echo formatCurrency($stats['outstanding_amount']); ?>
                        </div>
                    </div>
                    <div class="col-auto">
                        <i class="fas fa-dollar-sign fa-2x text-gray-300"></i>
                    </div>
                </div>
            </div>
        </div>
    </div>

    <div class="col-xl-3 col-md-6 mb-4">
        <div class="card border-left-info h-100">
            <div class="card-body">
                <div class="row align-items-center">
                    <div class="col">
                        <div class="text-xs font-weight-bold text-info text-uppercase mb-1">
                            Collection Rate
                        </div>
                        <div class="row align-items-center">
                            <div class="col-auto">
                                <div class="h5 mb-0 font-weight-bold">
                                    <?php echo number_format($stats['collection_rate'], 1); ?>%
                                </div>
                            </div>
                            <div class="col">
                                <div class="progress progress-sm">
                                    <div class="progress-bar bg-info" role="progressbar" 
                                         style="width: <?php echo $stats['collection_rate']; ?>%">
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                    <div class="col-auto">
                        <i class="fas fa-clipboard-list fa-2x text-gray-300"></i>
                    </div>
                </div>
            </div>
        </div>
    </div>

    <div class="col-xl-3 col-md-6 mb-4">
        <div class="card border-left-warning h-100">
            <div class="card-body">
                <div class="row align-items-center">
                    <div class="col">
                        <div class="text-xs font-weight-bold text-warning text-uppercase mb-1">
                            Pending Invoices
                        </div>
                        <div class="h5 mb-0 font-weight-bold">
                            <?php echo $stats['pending_invoices']; ?>
                        </div>
                    </div>
                    <div class="col-auto">
                        <i class="fas fa-file-invoice fa-2x text-gray-300"></i>
                    </div>
                </div>
            </div>
        </div>
    </div>
</div>

<!-- Revenue Chart & Payment Methods -->
<div class="row">
    <div class="col-xl-8 col-lg-7">
        <div class="card shadow mb-4">
            <div class="card-header py-3 d-flex justify-content-between align-items-center">
                <h6 class="m-0 font-weight-bold text-primary">Revenue Overview</h6>
                <div class="dropdown">
                    <button class="btn btn-sm btn-primary dropdown-toggle" data-bs-toggle="dropdown">
                        Time Range
                    </button>
                    <ul class="dropdown-menu">
                        <li><a class="dropdown-item" href="#" onclick="updateRevenueChart('week')">This Week</a></li>
                        <li><a class="dropdown-item" href="#" onclick="updateRevenueChart('month')">This Month</a></li>
                        <li><a class="dropdown-item" href="#" onclick="updateRevenueChart('year')">This Year</a></li>
                    </ul>
                </div>
            </div>
            <div class="card-body">
                <canvas id="revenueChart"></canvas>
            </div>
        </div>
    </div>

    <div class="col-xl-4 col-lg-5">
        <div class="card shadow mb-4">
            <div class="card-header py-3">
                <h6 class="m-0 font-weight-bold text-primary">Payment Methods</h6>
            </div>
            <div class="card-body">
                <canvas id="paymentMethodsChart"></canvas>
            </div>
        </div>
    </div>
</div>

<!-- Recent Invoices & Top Customers -->
<div class="row">
    <div class="col-lg-6 mb-4">
        <div class="card shadow">
            <div class="card-header py-3">
                <h6 class="m-0 font-weight-bold text-primary">Recent Invoices</h6>
            </div>
            <div class="card-body">
                <div class="table-responsive">
                    <table class="table">
                        <thead>
                            <tr>
                                <th>Invoice #</th>
                                <th>Customer</th>
                                <th>Amount</th>
                                <th>Status</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php foreach($stats['recent_invoices'] as $invoice): ?>
                            <tr>
                                <td>
                                    <a href="../invoices/view.php?id=<?php echo $invoice['id']; ?>">
                                        <?php echo $invoice['invoice_number']; ?>
                                    </a>
                                </td>
                                <td><?php echo $invoice['customer_name']; ?></td>
                                <td><?php echo formatCurrency($invoice['total_amount']); ?></td>
                                <td>
                                    <span class="badge bg-<?php echo getStatusColor($invoice['status']); ?>">
                                        <?php echo ucfirst($invoice['status']); ?>
                                    </span>
                                </td>
                            </tr>
                            <?php endforeach; ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
    </div>

    <div class="col-lg-6 mb-4">
        <div class="card shadow">
            <div class="card-header py-3">
                <h6 class="m-0 font-weight-bold text-primary">Top Customers</h6>
            </div>
            <div class="card-body">
                <div class="table-responsive">
                    <table class="table">
                        <thead>
                            <tr>
                                <th>Customer</th>
                                <th>Total Sales</th>
                                <th>Invoices</th>
                                <th>Status</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php foreach($stats['top_customers'] as $customer): ?>
                            <tr>
                                <td>
                                    <a href="../customers/profile.php?id=<?php echo $customer['id']; ?>">
                                        <?php echo $customer['customer_name']; ?>
                                    </a>
                                </td>
                                <td><?php echo formatCurrency($customer['total_sales']); ?></td>
                                <td><?php echo $customer['invoice_count']; ?></td>
                                <td>
                                    <div class="progress" style="height: 5px;">
                                        <div class="progress-bar bg-success" style="width: <?php echo $customer['payment_rate']; ?>%">
                                        </div>
                                    </div>
                                </td>
                            </tr>
                            <?php endforeach; ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
    </div>
</div>

<!-- Due Invoices Alert -->
<?php if(count($stats['due_invoices']) > 0): ?>
<div class="row">
    <div class="col-12">
        <div class="card bg-warning text-white shadow">
            <div class="card-body">
                <div class="text-white-50 small">Invoices Due This Week</div>
                <div class="table-responsive">
                    <table class="table table-sm text-white">
                        <thead>
                            <tr>
                                <th>Invoice #</th>
                                <th>Customer</th>
                                <th>Due Date</th>
                                <th>Amount</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php foreach($stats['due_invoices'] as $invoice): ?>
                            <tr>
                                <td><?php echo $invoice['invoice_number']; ?></td>
                                <td><?php echo $invoice['customer_name']; ?></td>
                                <td><?php echo formatDate($invoice['due_date']); ?></td>
                                <td><?php echo formatCurrency($invoice['total_amount']); ?></td>
                            </tr>
                            <?php endforeach; ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
    </div>
</div>
<?php endif; ?>

<!-- Initialize Charts -->
<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
<script>
document.addEventListener('DOMContentLoaded', function() {
    initializeCharts();
});

function initializeCharts() {
    // Revenue Chart
    $.get('api.php?action=revenue_data&range=month', function(response) {
        if(response.success) {
            createRevenueChart(response.data);
        }
    });

    // Payment Methods Chart
    $.get('api.php?action=payment_methods', function(response) {
        if(response.success) {
            createPaymentMethodsChart(response.data);
        }
    });
}

function createRevenueChart(data) {
    const ctx = document.getElementById('revenueChart').getContext('2d');
    new Chart(ctx, {
        type: 'line',
        data: {
            labels: data.labels,
            datasets: [{
                label: 'Revenue',
                data: data.values,
                borderColor: 'rgb(78, 115, 223)',
                tension: 0.1,
                fill: false
            }]
        },
        options: {
            responsive: true,
            maintainAspectRatio: false,
            scales: {
                y: {
                    beginAtZero: true,
                    ticks: {
                        callback: function(value) {
                            return '$' + value.toLocaleString();
                        }
                    }
                }
            }
        }
    });
}

function createPaymentMethodsChart(data) {
    const ctx = document.getElementById('paymentMethodsChart').getContext('2d');
    new Chart(ctx, {
        type: 'doughnut',
        data: {
            labels: data.labels,
            datasets: [{
                data: data.values,
                backgroundColor: [
                    '#4e73df',
                    '#1cc88a',
                    '#36b9cc',
                    '#f6c23e'
                ]
            }]
        },
        options: {
            responsive: true,
            maintainAspectRatio: false,
            plugins: {
                legend: {
                    position: 'bottom'
                }
            }
        }
    });
}

function updateRevenueChart(range) {
    $.get('api.php?action=revenue_data&range=' + range, function(response) {
        if(response.success) {
            // Update chart with new data
            const chart = Chart.getChart('revenueChart');
            chart.data.labels = response.data.labels;
            chart.data.datasets[0].data = response.data.values;
            chart.update();
        }
    });
}
</script>

<?php
function getDashboardStats($db) {
    $stats = [];
    
    // Monthly sales
    $query = "SELECT COALESCE(SUM(total_amount), 0) as total 
              FROM invoices 
              WHERE MONTH(invoice_date) = MONTH(CURRENT_DATE) 
              AND YEAR(invoice_date) = YEAR(CURRENT_DATE)";
    $result = $db->query($query)->fetch_assoc();
    $stats['monthly_sales'] = $result['total'];
    
    // Outstanding amount
    $query = "SELECT COALESCE(SUM(total_amount), 0) as total 
              FROM invoices 
              WHERE status != 'paid' AND status != 'cancelled'";
    $result = $db->query($query)->fetch_assoc();
    $stats['outstanding_amount'] = $result['total'];
    
    // Collection rate
    $query = "SELECT 
                (SUM(CASE WHEN status = 'paid' THEN total_amount ELSE 0 END) / 
                 SUM(total_amount) * 100) as rate
              FROM invoices 
              WHERE status != 'cancelled'";
    $result = $db->query($query)->fetch_assoc();
    $stats['collection_rate'] = $result['rate'];
    
    // Pending invoices
    $query = "SELECT COUNT(*) as total 
              FROM invoices 
              WHERE status = 'pending'";
    $result = $db->query($query)->fetch_assoc();
    $stats['pending_invoices'] = $result['total'];
    
    // Recent invoices
    $query = "SELECT i.*, c.customer_name 
              FROM invoices i 
              LEFT JOIN customers c ON i.customer_id = c.id 
              ORDER BY i.invoice_date DESC 
              LIMIT 5";
    $result = $db->query($query);
    $stats['recent_invoices'] = [];
    while($row = $result->fetch_assoc()) {
        $stats['recent_invoices'][] = $row;
    }
    
    // Top customers
    $query = "SELECT 
                c.id,
                c.customer_name,
                COUNT(i.id) as invoice_count,
                SUM(i.total_amount) as total_sales,
                (COUNT(CASE WHEN i.status = 'paid' THEN 1 END) / 
                 COUNT(*) * 100) as payment_rate
              FROM customers c
              LEFT JOIN invoices i ON c.id = i.customer_id
              GROUP BY c.id
              ORDER BY total_sales DESC
              LIMIT 5";
    $result = $db->query($query);
    $stats['top_customers'] = [];
    while($row = $result->fetch_assoc()) {
        $stats['top_customers'][] = $row;
    }
    
    // Due invoices
    $query = "SELECT i.*, c.customer_name 
              FROM invoices i
              LEFT JOIN customers c ON i.customer_id = c.id
              WHERE i.status != 'paid' 
              AND i.due_date BETWEEN CURRENT_DATE AND DATE_ADD(CURRENT_DATE, INTERVAL 7 DAY)
              ORDER BY i.due_date";
    $result = $db->query($query);
    $stats['due_invoices'] = [];
    while($row = $result->fetch_assoc()) {
        $stats['due_invoices'][] = $row;
    }
    
    return $stats;
}

require_once '../../includes/footer.php';
?> <?php
require_once '../../config/config.php';
require_once '../../config/database.php';

header('Content-Type: application/json');

$action = isset($_GET['action']) ? $_GET['action'] : '';

switch ($action) {
    case 'getCustomers':
        getCustomers();
        break;
    case 'addCustomer':
        addCustomer();
        break;
    case 'editCustomer':
        editCustomer();
        break;
    case 'deleteCustomer':
        deleteCustomer();
        break;
    case 'getCustomerDetails':
        getCustomerDetails();
        break;
    default:
        echo json_encode(['success' => false, 'message' => 'Invalid action']);
        break;
}

function getCustomers() {
    global $conn;
    $search = isset($_GET['search']) ? $_GET['search'] : '';
    $filter = isset($_GET['filter']) ? $_GET['filter'] : '';

    $query = "SELECT * FROM customers WHERE 1=1";
    if (!empty($search)) {
        $query .= " AND (name LIKE ? OR email LIKE ? OR phone LIKE ?)";
    }
    if (!empty($filter)) {
        $query .= " AND status = ?";
    }

    $stmt = $conn->prepare($query);
    $params = [];
    if (!empty($search)) {
        $searchTerm = '%' . $search . '%';
        $params[] = $searchTerm;
        $params[] = $searchTerm;
        $params[] = $searchTerm;
    }
    if (!empty($filter)) {
        $params[] = $filter;
    }

    if ($stmt->execute($params)) {
        $customers = $stmt->fetchAll(PDO::FETCH_ASSOC);
        echo json_encode(['success' => true, 'data' => $customers]);
    } else {
        echo json_encode(['success' => false, 'message' => 'Failed to fetch customers']);
    }
}

function addCustomer() {
    global $db; // Use the $db object from the Database class
    $data = json_decode(file_get_contents("php://input"), true);

    // Debugging: Print the received data
    error_log(print_r($data, true));

    $name = $data['customer_name'];
    $email = $data['email'];
    $phone = $data['phone'];
    $company = $data['company'];
    $address = $data['address'];
    $city = $data['city'];
    $state = $data['state'];
    $zip_code = $data['zip_code'];
    $notes = $data['notes'];

    $query = "INSERT INTO customers (name, email, phone, company, address, city, state, zip_code, notes, status) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, 'active')";

    $stmt = $db->prepare($query);
    if ($stmt->bind_param("sssssssssss", $name, $email, $phone, $company, $address, $city, $state, $zip_code, $notes)) {
        if ($stmt->execute()) {
            echo json_encode(['success' => true, 'message' => 'Customer added successfully']);
        } else {
            // Debugging: Print the error message
            error_log($stmt->error);
            echo json_encode(['success' => false, 'message' => 'Failed to add customer']);
        }
    } else {
        // Debugging: Print the error message
        error_log($stmt->error);
        echo json_encode(['success' => false, 'message' => 'Failed to prepare statement']);
    }
}
function editCustomer() {
    global $conn;
    $data = json_decode(file_get_contents("php://input"), true);

    $customer_id = $data['customer_id'];
    $name = $data['customer_name'];
    $email = $data['email'];
    $phone = $data['phone'];
    $company = $data['company'];
    $address = $data['address'];
    $city = $data['city'];
    $state = $data['state'];
    $zip_code = $data['zip_code'];
    $notes = $data['notes'];

    $query = "UPDATE customers SET name = ?, email = ?, phone = ?, company = ?, address = ?, city = ?, state = ?, zip_code = ?, notes = ? WHERE id = ?";

    $stmt = $conn->prepare($query);
    if ($stmt->execute([$name, $email, $phone, $company, $address, $city, $state, $zip_code, $notes, $customer_id])) {
        echo json_encode(['success' => true, 'message' => 'Customer updated successfully']);
    } else {
        echo json_encode(['success' => false, 'message' => 'Failed to update customer']);
    }
}

function deleteCustomer() {
    global $conn;
    $data = json_decode(file_get_contents("php://input"), true);

    $customer_id = $data['customer_id'];

    $query = "DELETE FROM customers WHERE id = ?";

    $stmt = $conn->prepare($query);
    if ($stmt->execute([$customer_id])) {
        echo json_encode(['success' => true, 'message' => 'Customer deleted successfully']);
    } else {
        echo json_encode(['success' => false, 'message' => 'Failed to delete customer']);
    }
}

function getCustomerDetails() {
    global $conn;
    $customer_id = isset($_GET['customer_id']) ? $_GET['customer_id'] : '';

    $query = "SELECT * FROM customers WHERE id = ?";

    $stmt = $conn->prepare($query);
    if ($stmt->execute([$customer_id])) {
        $customer = $stmt->fetch(PDO::FETCH_ASSOC);
        echo json_encode(['success' => true, 'data' => $customer]);
    } else {
        echo json_encode(['success' => false, 'message' => 'Failed to fetch customer details']);
    }
}
?><?php
require_once '../../config/config.php';
require_once '../../config/database.php';
require_once '../../includes/header.php';
?>

<div class="row">
    <div class="col-md-12">
        <div class="card">
            <div class="card-header d-flex justify-content-between align-items-center">
                <h4>Customer Management</h4>
                <button type="button" class="btn btn-primary" data-bs-toggle="modal" data-bs-target="#addCustomerModal">
                    <i class="fas fa-plus"></i> Add New Customer
                </button>
            </div>
            <div class="card-body">
                <!-- Search and Filter Section -->
                <div class="row mb-3">
                    <div class="col-md-3">
                        <input type="text" id="customerSearch" class="form-control" placeholder="Search customers...">
                    </div>
                    <div class="col-md-2">
                        <select id="customerFilter" class="form-select">
                            <option value="">All Customers</option>
                            <option value="active">Active</option>
                            <option value="inactive">Inactive</option>
                        </select>
                    </div>
                </div>

                <!-- Customers Table -->
                <div class="table-responsive">
                    <table id="customersTable" class="table table-striped">
                        <thead>
                            <tr>
                                <th>ID</th>
                                <th>Name</th>
                                <th>Email</th>
                                <th>Phone</th>
                                <th>Total Sales</th>
                                <th>Status</th>
                                <th>Actions</th>
                            </tr>
                        </thead>
                    </table>
                </div>
            </div>
        </div>
    </div>
</div>

<!-- Add Customer Modal -->
<div class="modal fade" id="addCustomerModal">
    <div class="modal-dialog modal-lg">
        <div class="modal-content">
            <div class="modal-header">
                <h5 class="modal-title">Add New Customer</h5>
                <button type="button" class="btn-close" data-bs-dismiss="modal"></button>
            </div>
            <form id="addCustomerForm">
                <div class="modal-body">
                    <div class="row">
                        <div class="col-md-6">
                            <div class="mb-3">
                                <label class="form-label">Customer Name *</label>
                                <input type="text" class="form-control" name="customer_name" required>
                            </div>
                            <div class="mb-3">
                                <label class="form-label">Email *</label>
                                <input type="email" class="form-control" name="email" required>
                            </div>
                            <div class="mb-3">
                                <label class="form-label">Phone</label>
                                <input type="tel" class="form-control" name="phone">
                            </div>
                            <div class="mb-3">
                                <label class="form-label">Company</label>
                                <input type="text" class="form-control" name="company">
                            </div>
                        </div>
                        <div class="col-md-6">
                            <div class="mb-3">
                                <label class="form-label">Address</label>
                                <textarea class="form-control" name="address" rows="2"></textarea>
                            </div>
                            <div class="mb-3">
                                <label class="form-label">City</label>
                                <input type="text" class="form-control" name="city">
                            </div>
                            <div class="mb-3">
                                <label class="form-label">State</label>
                                <input type="text" class="form-control" name="state">
                            </div>
                            <div class="mb-3">
                                <label class="form-label">ZIP Code</label>
                                <input type="text" class="form-control" name="zip_code">
                            </div>
                        </div>
                    </div>
                    <div class="mb-3">
                        <label class="form-label">Notes</label>
                        <textarea class="form-control" name="notes" rows="3"></textarea>
                    </div>
                </div>
                <div class="modal-footer">
                    <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Close</button>
                    <button type="submit" class="btn btn-primary">Save Customer</button>
                </div>
            </form>
        </div>
    </div>
</div>

<!-- Edit Customer Modal -->
<div class="modal fade" id="editCustomerModal">
    <div class="modal-dialog modal-lg">
        <div class="modal-content">
            <div class="modal-header">
                <h5 class="modal-title">Edit Customer</h5>
                <button type="button" class="btn-close" data-bs-dismiss="modal"></button>
            </div>
            <form id="editCustomerForm">
                <input type="hidden" name="customer_id">
                <div class="modal-body">
                    <div class="row">
                        <div class="col-md-6">
                            <div class="mb-3">
                                <label class="form-label">Customer Name *</label>
                                <input type="text" class="form-control" name="customer_name" required>
                            </div>
                            <div class="mb-3">
                                <label class="form-label">Email *</label>
                                <input type="email" class="form-control" name="email" required>
                            </div>
                            <div class="mb-3">
                                <label class="form-label">Phone</label>
                                <input type="tel" class="form-control" name="phone">
                            </div>
                            <div class="mb-3">
                                <label class="form-label">Company</label>
                                <input type="text" class="form-control" name="company">
                            </div>
                        </div>
                        <div class="col-md-6">
                            <div class="mb-3">
                                <label class="form-label">Address</label>
                                <textarea class="form-control" name="address" rows="2"></textarea>
                            </div>
                            <div class="mb-3">
                                <label class="form-label">City</label>
                                <input type="text" class="form-control" name="city">
                            </div>
                            <div class="mb-3">
                                <label class="form-label">State</label>
                                <input type="text" class="form-control" name="state">
                            </div>
                            <div class="mb-3">
                                <label class="form-label">ZIP Code</label>
                                <input type="text" class="form-control" name="zip_code">
                            </div>
                        </div>
                    </div>
                    <div class="mb-3">
                        <label class="form-label">Notes</label>
                        <textarea class="form-control" name="notes" rows="3"></textarea>
                    </div>
                </div>
                <div class="modal-footer">
                    <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Close</button>
                    <button type="submit" class="btn btn-primary">Update Customer</button>
                </div>
            </form>
        </div>
    </div>
</div>

<!-- View Customer Details Modal -->
<div class="modal fade" id="viewCustomerModal">
    <div class="modal-dialog modal-lg">
        <div class="modal-content">
            <div class="modal-header">
                <h5 class="modal-title">Customer Details</h5>
                <button type="button" class="btn-close" data-bs-dismiss="modal"></button>
            </div>
            <div class="modal-body">
                <div class="row">
                    <div class="col-md-6">
                        <h6>Basic Information</h6>
                        <table class="table table-borderless">
                            <tr>
                                <th>Name:</th>
                                <td id="view_customer_name"></td>
                            </tr>
                            <tr>
                                <th>Email:</th>
                                <td id="view_email"></td>
                            </tr>
                            <tr>
                                <th>Phone:</th>
                                <td id="view_phone"></td>
                            </tr>
                            <tr>
                                <th>Company:</th>
                                <td id="view_company"></td>
                            </tr>
                        </table>
                    </div>
                    <div class="col-md-6">
                        <h6>Address Information</h6>
                        <table class="table table-borderless">
                            <tr>
                                <th>Address:</th>
                                <td id="view_address"></td>
                            </tr>
                            <tr>
                                <th>City:</th>
                                <td id="view_city"></td>
                            </tr>
                            <tr>
                                <th>State:</th>
                                <td id="view_state"></td>
                            </tr>
                            <tr>
                                <th>ZIP Code:</th>
                                <td id="view_zip_code"></td>
                            </tr>
                        </table>
                    </div>
                </div>
                <div class="row mt-4">
                    <div class="col-md-12">
                        <h6>Customer Statistics</h6>
                        <div class="row">
                            <div class="col-md-3">
                                <div class="card bg-primary text-white">
                                    <div class="card-body">
                                        <h6>Total Sales</h6>
                                        <h4 id="view_total_sales">$0.00</h4>
                                    </div>
                                </div>
                            </div>
                            <div class="col-md-3">
                                <div class="card bg-success text-white">
                                    <div class="card-body">
                                        <h6>Total Invoices</h6>
                                        <h4 id="view_total_invoices">0</h4>
                                    </div>
                                </div>
                            </div>
                            <div class="col-md-3">
                                <div class="card bg-info text-white">
                                    <div class="card-body">
                                        <h6>Average Invoice</h6>
                                        <h4 id="view_avg_invoice">$0.00</h4>
                                    </div>
                                </div>
                            </div>
                            <div class="col-md-3">
                                <div class="card bg-warning text-white">
                                    <div class="card-body">
                                        <h6>Outstanding</h6>
                                        <h4 id="view_outstanding">$0.00</h4>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </div>
</div>

<!-- Customer JavaScript -->
<script src="<?php echo SITE_URL; ?>/assets/js/customers.js"></script>

<?php require_once '../../includes/footer.php'; ?><?php
require_once '../../config/config.php';
require_once '../../config/database.php';
require_once '../../includes/header.php';

$customer_id = isset($_GET['id']) ? intval($_GET['id']) : 0;
$customer = getCustomerDetails($customer_id);

if (!$customer) {
    echo "<div class='alert alert-danger'>Customer not found</div>";
    exit;
}
?>

<div class="row">
    <!-- Customer Overview -->
    <div class="col-md-3">
        <div class="card">
            <div class="card-body text-center">
                <div class="customer-avatar mb-3">
                    <img src="<?php echo getCustomerAvatar($customer["email"]); ?>" 
                         class="rounded-circle" style="width: 100px;">
                </div>
                <h4><?php echo htmlspecialchars($customer['customer_name']); ?></h4>
                <p class="text-muted"><?php echo htmlspecialchars($customer['company_name']); ?></p>
                <span class="badge bg-<?php echo $customer['status'] == 'active' ? 'success' : 'danger'; ?>">
                    <?php echo ucfirst($customer['status']); ?>
                </span>
                
                <hr>
                
                <div class="contact-info text-start">
                    <p><i class="fas fa-envelope"></i> <?php echo htmlspecialchars($customer['email']); ?></p>
                    <p><i class="fas fa-phone"></i> <?php echo htmlspecialchars($customer['phone']); ?></p>
                    <p><i class="fas fa-map-marker-alt"></i> <?php echo formatAddress($customer); ?></p>
                </div>
            </div>
        </div>

        <!-- Quick Actions -->
        <div class="card mt-3">
            <div class="card-header">
                <h5>Quick Actions</h5>
            </div>
            <div class="card-body">
                <div class="d-grid gap-2">
                    <button class="btn btn-primary" onclick="createInvoice(<?php echo $customer_id; ?>)">
                        <i class="fas fa-file-invoice"></i> New Invoice
                    </button>
                    <button class="btn btn-info" onclick="sendEmail(<?php echo $customer_id; ?>)">
                        <i class="fas fa-envelope"></i> Send Email
                    </button>
                    <button class="btn btn-success" onclick="addPayment(<?php echo $customer_id; ?>)">
                        <i class="fas fa-money-bill"></i> Record Payment
                    </button>
                </div>
            </div>
        </div>
    </div>

    <!-- Main Content -->
    <div class="col-md-9">
        <!-- Statistics Cards -->
        <div class="row">
            <div class="col-md-3">
                <div class="card bg-primary text-white">
                    <div class="card-body">
                        <h6>Total Sales</h6>
                        <h3><?php echo formatCurrency($customer['total_sales']); ?></h3>
                    </div>
                </div>
            </div>
            <div class="col-md-3">
                <div class="card bg-success text-white">
                    <div class="card-body">
                        <h6>Total Invoices</h6>
                        <h3><?php echo $customer['total_invoices']; ?></h3>
                    </div>
                </div>
            </div>
            <div class="col-md-3">
                <div class="card bg-info text-white">
                    <div class="card-body">
                        <h6>Average Invoice</h6>
                        <h3><?php echo formatCurrency($customer['avg_invoice_value']); ?></h3>
                    </div>
                </div>
            </div>
            <div class="col-md-3">
                <div class="card bg-warning text-white">
                    <div class="card-body">
                        <h6>Outstanding</h6>
                        <h3><?php echo formatCurrency($customer['outstanding_amount']); ?></h3>
                    </div>
                </div>
            </div>
        </div>

        <!-- Tabs Section -->
        <div class="card mt-3">
            <div class="card-body">
                <ul class="nav nav-tabs" id="customerTabs">
                    <li class="nav-item">
                        <a class="nav-link active" data-bs-toggle="tab" href="#invoices">Invoices</a>
                    </li>
                    <li class="nav-item">
                        <a class="nav-link" data-bs-toggle="tab" href="#transactions">Transactions</a>
                    </li>
                    <li class="nav-item">
                        <a class="nav-link" data-bs-toggle="tab" href="#contacts">Contacts</a>
                    </li>
                    <li class="nav-item">
                        <a class="nav-link" data-bs-toggle="tab" href="#documents">Documents</a>
                    </li>
                </ul>

                <div class="tab-content mt-3">
                    <!-- Invoices Tab -->
                    <div class="tab-pane fade show active" id="invoices">
                        <table class="table table-striped" id="invoicesTable">
                            <thead>
                                <tr>
                                    <th>Invoice #</th>
                                    <th>Date</th>
                                    <th>Due Date</th>
                                    <th>Amount</th>
                                    <th>Status</th>
                                    <th>Actions</th>
                                </tr>
                            </thead>
                        </table>
                    </div>

                    <!-- Transactions Tab -->
                    <div class="tab-pane fade" id="transactions">
                        <table class="table table-striped" id="transactionsTable">
                            <thead>
                                <tr>
                                    <th>Date</th>
                                    <th>Type</th>
                                    <th>Reference</th>
                                    <th>Amount</th>
                                    <th>Balance</th>
                                </tr>
                            </thead>
                        </table>
                    </div>

                    <!-- Contacts Tab -->
                    <div class="tab-pane fade" id="contacts">
                        <button class="btn btn-primary mb-3" onclick="addContact()">
                            <i class="fas fa-plus"></i> Add Contact
                        </button>
                        <div id="contactsList">
                            <!-- Will be populated via AJAX -->
                        </div>
                    </div>

                    <!-- Documents Tab -->
                    <div class="tab-pane fade" id="documents">
                        <form id="uploadDocumentForm" class="mb-3">
                            <div class="row">
                                <div class="col-md-4">
                                    <select class="form-select" name="document_type" required>
                                        <option value="">Select Document Type</option>
                                        <option value="contract">Contract</option>
                                        <option value="id_proof">ID Proof</option>
                                        <option value="tax_document">Tax Document</option>
                                        <option value="other">Other</option>
                                    </select>
                                </div>
                                <div class="col-md-6">
                                    <input type="file" class="form-control" name="document" required>
                                </div>
                                <div class="col-md-2">
                                    <button type="submit" class="btn btn-primary w-100">Upload</button>
                                </div>
                            </div>
                        </form>
                        <div id="documentsList">
                            <!-- Will be populated via AJAX -->
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </div>
</div>

<!-- Add Contact Modal -->
<div class="modal fade" id="addContactModal">
    <div class="modal-dialog">
        <div class="modal-content">
            <div class="modal-header">
                <h5 class="modal-title">Add Contact</h5>
                <button type="button" class="btn-close" data-bs-dismiss="modal"></button>
            </div>
            <form id="addContactForm">
                <div class="modal-body">
                    <div class="mb-3">
                        <label class="form-label">Contact Name</label>
                        <input type="text" class="form-control" name="contact_name" required>
                    </div>
                    <div class="mb-3">
                        <label class="form-label">Designation</label>
                        <input type="text" class="form-control" name="designation">
                    </div>
                    <div class="mb-3">
                        <label class="form-label">Email</label>
                        <input type="email" class="form-control" name="email" required>
                    </div>
                    <div class="mb-3">
                        <label class="form-label">Phone</label>
                        <input type="text" class="form-control" name="phone">
                    </div>
                    <div class="mb-3">
                        <div class="form-check">
                            <input type="checkbox" class="form-check-input" name="is_primary" value="1">
                            <label class="form-check-label">Primary Contact</label>
                        </div>
                    </div>
                </div>
                <div class="modal-footer">
                    <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Close</button>
                    <button type="submit" class="btn btn-primary">Save Contact</button>
                </div>
            </form>
        </div>
    </div>
</div>

<?php
function getCustomerDetails($id) {
    global $db;
    $query = "SELECT * FROM vw_customer_summary WHERE id = " . intval($id);
    $result = $db->query($query);
    return $result->fetch_assoc();
}

function formatAddress($customer) {
    $address = [];
    if ($customer['city']) $address[] = $customer['city'];
    if ($customer['state']) $address[] = $customer['state'];
    if ($customer['country']) $address[] = $customer['country'];
    if ($customer['postal_code']) $address[] = $customer['postal_code'];
    return implode(', ', $address);
}

function getCustomerAvatar($email) {
    $hash = md5(strtolower(trim($email)));
    return "https://www.gravatar.com/avatar/$hash?s=200&d=mp";
}
?>

<script src="<?php echo SITE_URL; ?>/assets/js/customer-profile.js"></script>

<?php require_once '../../includes/footer.php'; ?><?php
require_once '../../config/config.php';
require_once '../../config/database.php';
require_once '../../includes/header.php';
?>

<div class="row">
    <!-- Report Filters -->
    <div class="col-md-12 mb-4">
        <div class="card">
            <div class="card-header">
                <h4>Customer Analysis</h4>
            </div>
            <div class="card-body">
                <form id="reportFiltersForm">
                    <div class="row">
                        <div class="col-md-3">
                            <div class="mb-3">
                                <label class="form-label">Date Range</label>
                                <select class="form-select" name="date_range">
                                    <option value="7">Last 7 Days</option>
                                    <option value="30">Last 30 Days</option>
                                    <option value="90">Last 90 Days</option>
                                    <option value="365">Last Year</option>
                                    <option value="custom">Custom Range</option>
                                </select>
                            </div>
                        </div>
                        <div class="col-md-3">
                            <div class="mb-3">
                                <label class="form-label">Customer Type</label>
                                <select class="form-select" name="customer_type">
                                    <option value="">All Customers</option>
                                    <option value="active">Active</option>
                                    <option value="inactive">Inactive</option>
                                    <option value="new">New Customers</option>
                                </select>
                            </div>
                        </div>
                        <div class="col-md-3">
                            <div class="mb-3">
                                <label class="form-label">Sort By</label>
                                <select class="form-select" name="sort_by">
                                    <option value="sales">Total Sales</option>
                                    <option value="invoices">Number of Invoices</option>
                                    <option value="average">Average Invoice Value</option>
                                </select>
                            </div>
                        </div>
                        <div class="col-md-3">
                            <div class="mb-3">
                                <label class="form-label">&nbsp;</label>
                                <button type="submit" class="btn btn-primary d-block w-100">
                                    Generate Report
                                </button>
                            </div>
                        </div>
                    </div>
                </form>
            </div>
        </div>
    </div>

    <!-- Summary Cards -->
    <div class="col-md-12 mb-4">
        <div class="row">
            <div class="col-md-3">
                <div class="card bg-primary text-white">
                    <div class="card-body">
                        <h6>Total Customers</h6>
                        <h3 id="totalCustomers">0</h3>
                    </div>
                </div>
            </div>
            <div class="col-md-3">
                <div class="card bg-success text-white">
                    <div class="card-body">
                        <h6>Active Customers</h6>
                        <h3 id="activeCustomers">0</h3>
                    </div>
                </div>
            </div>
            <div class="col-md-3">
                <div class="card bg-info text-white">
                    <div class="card-body">
                        <h6>Total Sales</h6>
                        <h3 id="totalSales">$0.00</h3>
                    </div>
                </div>
            </div>
            <div class="col-md-3">
                <div class="card bg-warning text-white">
                    <div class="card-body">
                        <h6>Average Sale</h6>
                        <h3 id="averageSale">$0.00</h3>
                    </div>
                </div>
            </div>
        </div>
    </div>

    <!-- Charts Section -->
    <div class="col-md-6 mb-4">
        <div class="card">
            <div class="card-header">
                <h5>Sales Distribution</h5>
            </div>
            <div class="card-body">
                <canvas id="salesDistributionChart"></canvas>
            </div>
        </div>
    </div>

    <div class="col-md-6 mb-4">
        <div class="card">
            <div class="card-header">
                <h5>Customer Growth</h5>
            </div>
            <div class="card-body">
                <canvas id="customerGrowthChart"></canvas>
            </div>
        </div>
    </div>

    <!-- Customer Analysis Table -->
    <div class="col-md-12">
        <div class="card">
            <div class="card-header d-flex justify-content-between align-items-center">
                <h5>Customer Analysis</h5>
                <div class="btn-group">
                    <button class="btn btn-primary" onclick="exportReport('excel')">
                        <i class="fas fa-file-excel"></i> Export Excel
                    </button>
                    <button class="btn btn-danger" onclick="exportReport('pdf')">
                        <i class="fas fa-file-pdf"></i> Export PDF
                    </button>
                </div>
            </div>
            <div class="card-body">
                <table id="customerAnalysisTable" class="table table-striped">
                    <thead>
                        <tr>
                            <th>Customer</th>
                            <th>Total Sales</th>
                            <th>Invoices</th>
                            <th>Average Value</th>
                            <th>Last Purchase</th>
                            <th>Outstanding</th>
                            <th>Status</th>
                        </tr>
                    </thead>
                </table>
            </div>
        </div>
    </div>
</div>

<!-- Add Customer Analysis JavaScript -->
<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
<script>
$(document).ready(function() {
    // Initialize DataTable
    const analysisTable = $('#customerAnalysisTable').DataTable({
        ajax: {
            url: 'api.php?action=customer_analysis',
            data: function(d) {
                return $.extend({}, d, getReportFilters());
            }
        },
        columns: [
            { 
                data: 'customer_name',
                render: function(data, type, row) {
                    return `<a href="profile.php?id=${row.id}">${data}</a>`;
                }
            },
            { 
                data: 'total_sales',
                render: formatCurrency
            },
            { data: 'invoice_count' },
            { 
                data: 'average_value',
                render: formatCurrency
            },
            { data: 'last_purchase_date' },
            { 
                data: 'outstanding_amount',
                render: formatCurrency
            },
            {
                data: 'status',
                render: function(data) {
                    return `<span class="badge bg-${data === 'active' ? 'success' : 'danger'}">${data}</span>`;
                }
            }
        ],
        order: [[1, 'desc']]
    });

    // Handle Report Filters
    $('#reportFiltersForm').submit(function(e) {
        e.preventDefault();
        updateReport();
    });

    // Initial report load
    updateReport();
});

function updateReport() {
    const filters = getReportFilters();

    // Update summary cards
    $.get('api.php?action=customer_summary', filters, function(response) {
        if(response.success) {
            $('#totalCustomers').text(response.data.total_customers);
            $('#activeCustomers').text(response.data.active_customers);
            $('#totalSales').text(formatCurrency(response.data.total_sales));
            $('#averageSale').text(formatCurrency(response.data.average_sale));
        }
    });

    // Update sales distribution chart
    $.get('api.php?action=sales_distribution', filters, function(response) {
        if(response.success) {
            updateSalesDistributionChart(response.data);
        }
    });

    // Update customer growth chart
    $.get('api.php?action=customer_growth', filters, function(response) {
        if(response.success) {
            updateCustomerGrowthChart(response.data);
        }
    });

    // Refresh analysis table
    $('#customerAnalysisTable').DataTable().ajax.reload();
}

function updateSalesDistributionChart(data) {
    const ctx = document.getElementById('salesDistributionChart').getContext('2d');
    new Chart(ctx, {
        type: 'pie',
        data: {
            labels: data.labels,
            datasets: [{
                data: data.values,
                backgroundColor: [
                    '#4e73df',
                    '#1cc88a',
                    '#36b9cc',
                    '#f6c23e',
                    '#e74a3b'
                ]
            }]
        },
        options: {
            responsive: true,
            maintainAspectRatio: false
        }
    });
}

function updateCustomerGrowthChart(data) {
    const ctx = document.getElementById('customerGrowthChart').getContext('2d');
    new Chart(ctx, {
        type: 'line',
        data: {
            labels: data.labels,
            datasets: [{
                label: 'New Customers',
                data: data.values,
                borderColor: '#4e73df',
                tension: 0.1
            }]
        },
        options: {
            responsive: true,
            maintainAspectRatio: false
        }
    });
}

function getReportFilters() {
    return {
        date_range: $('select[name="date_range"]').val(),
        customer_type: $('select[name="customer_type"]').val(),
        sort_by: $('select[name="sort_by"]').val()
    };
}

function formatCurrency(amount) {
    return new Intl.NumberFormat('en-US', {
        style: 'currency',
        currency: 'USD'
    }).format(amount);
}

function exportReport(format) {
    const filters = getReportFilters();
    window.location.href = `export_report.php?format=${format}&${$.param(filters)}`;
}
</script>

<?php require_once '../../includes/footer.php'; ?><?php
require_once '../../config/config.php';
require_once '../../config/database.php';

header('Content-Type: application/json');

$action = $_POST['action'] ?? $_GET['action'] ?? '';

switch($action) {
    case 'list':
        $query = "SELECT i.*, c.customer_name 
                 FROM invoices i 
                 LEFT JOIN customers c ON i.customer_id = c.id 
                 ORDER BY i.invoice_date DESC";
        $result = $db->query($query);
        $invoices = [];
        
        while($row = $result->fetch_assoc()) {
            $invoices[] = $row;
        }
        
        echo json_encode(['data' => $invoices]);
        break;
        
    case 'create':
        try {
            $db->query("START TRANSACTION");
            
            // Create invoice header
            $customerId = intval($_POST['customer_id']);
            $invoiceDate = $db->escape($_POST['invoice_date']);
            $dueDate = $db->escape($_POST['due_date']);
            $notes = $db->escape($_POST['notes']);
            $taxRate = floatval($_POST['tax_rate']);
            
            // Generate invoice number
            $year = date('Y');
            $month = date('m');
            $query = "SELECT COALESCE(MAX(CAST(SUBSTRING(invoice_number, 9) AS UNSIGNED)), 0) + 1 AS next_number 
                     FROM invoices 
                     WHERE invoice_number LIKE 'INV$year$month%'";
            $result = $db->query($query);
            $row = $result->fetch_assoc();
            $nextNumber = str_pad($row['next_number'], 4, '0', STR_PAD_LEFT);
            $invoiceNumber = "INV$year$month$nextNumber";
            
            // Calculate totals
            $subtotal = 0;
            foreach($_POST['items'] as $item) {
                $subtotal += floatval($item['quantity']) * floatval($item['unit_price']);
            }
            
            $taxAmount = $subtotal * ($taxRate / 100);
            $total = $subtotal + $taxAmount;
            
            // Insert invoice
            $query = "INSERT INTO invoices (
                        invoice_number, customer_id, invoice_date, due_date,
                        subtotal, tax_rate, tax_amount, total_amount, notes, status
                     ) VALUES (
                        '$invoiceNumber', $customerId, '$invoiceDate', '$dueDate',
                        $subtotal, $taxRate, $taxAmount, $total, '$notes', 'draft'
                     )";
            
            $db->query($query);
            $invoiceId = $db->getLastId();
            
            // Insert invoice items
            foreach($_POST['items'] as $item) {
                $productId = intval($item['product_id']);
                $quantity = intval($item['quantity']);
                $unitPrice = floatval($item['unit_price']);
                $total = $quantity * $unitPrice;
                
                $query = "INSERT INTO invoice_items (
                            invoice_id, product_id, quantity, unit_price, total_price
                         ) VALUES (
                            $invoiceId, $productId, $quantity, $unitPrice, $total
                         )";
                
                $db->query($query);
                
                // Update product stock
                $query = "UPDATE products 
                         SET stock_quantity = stock_quantity - $quantity 
                         WHERE id = $productId";
                $db->query($query);
            }
            
            $db->query("COMMIT");
            echo json_encode(['success' => true, 'message' => 'Invoice created successfully', 'invoice_id' => $invoiceId]);
            
        } catch (Exception $e) {
            $db->query("ROLLBACK");
            echo json_encode(['success' => false, 'message' => 'Failed to create invoice: ' . $e->getMessage()]);
        }
        break;

    case 'bulk_action':
        $action = $_POST['bulk_action'];
        $invoiceIds = implode(',', array_map('intval', $_POST['invoice_ids']));
        
        switch($action) {
            case 'mark_paid':
            case 'mark_sent':
                $status = ($action == 'mark_paid') ? 'paid' : 'sent';
                $query = "CALL sp_batch_update_invoice_status('$invoiceIds', '$status')";
                $db->query($query);
                echo json_encode(['success' => true, 'message' => 'Status updated successfully']);
                break;
                
            case 'delete':
                // Check if invoices can be deleted
                $query = "SELECT COUNT(*) as count FROM invoice_items WHERE invoice_id IN ($invoiceIds)";
                $result = $db->query($query)->fetch_assoc();
                
                if($result['count'] > 0) {
                    echo json_encode(['success' => false, 'message' => 'Some invoices cannot be deleted']);
                    break;
                }
                
                $query = "DELETE FROM invoices WHERE id IN ($invoiceIds)";
                $db->query($query);
                echo json_encode(['success' => true, 'message' => 'Invoices deleted successfully']);
                break;
                
            case 'export':
                // Generate Excel file with selected invoices
                $excel = new ExcelExport();
                $excel->exportSelectedInvoices($invoiceIds);
                break;
                
            case 'email':
                // Send emails for selected invoices
                $emailHandler = new EmailHandler();
                $success = $emailHandler->sendBulkInvoices($invoiceIds);
                echo json_encode(['success' => $success, 'message' => $success ? 'Emails sent successfully' : 'Failed to send emails']);
                break;
        }
        break;

    case 'setup_recurring':
        $templateId = intval($_POST['template_id']);
        $frequency = $db->escape($_POST['frequency']);
        $nextDate = $db->escape($_POST['next_date']);
        
        $query = "CALL sp_setup_recurring_invoice($templateId, '$frequency', '$nextDate')";
        if($db->query($query)) {
            echo json_encode(['success' => true, 'message' => 'Recurring invoice setup successfully']);
        } else {
            echo json_encode(['success' => false, 'message' => 'Failed to setup recurring invoice']);
        }
        break;
}
<?php
require_once '../../config/config.php';
require_once '../../config/database.php';
require_once '../../includes/header.php';
?>

<div class="row">
    <div class="col-md-12">
        <div class="card">
            <div class="card-header">
                <h4>Batch Operations</h4>
            </div>
            <div class="card-body">
                <!-- Bulk Invoice Actions -->
                <form id="bulkActionForm" class="mb-4">
                    <div class="row align-items-end">
                        <div class="col-md-4">
                            <div class="mb-3">
                                <label class="form-label">Select Action</label>
                                <select class="form-select" name="bulk_action" required>
                                    <option value="">Choose Action</option>
                                    <option value="mark_paid">Mark as Paid</option>
                                    <option value="mark_sent">Mark as Sent</option>
                                    <option value="delete">Delete Selected</option>
                                    <option value="export">Export Selected</option>
                                    <option value="email">Email Selected</option>
                                </select>
                            </div>
                        </div>
                        <div class="col-md-4">
                            <button type="submit" class="btn btn-primary">
                                Apply to Selected
                            </button>
                        </div>
                    </div>
                
                    <!-- Invoice Selection Table -->
                    <div class="table-responsive mt-4">
                        <table class="table table-striped" id="invoiceSelectionTable">
                            <thead>
                                <tr>
                                    <th>
                                        <input type="checkbox" id="selectAll">
                                    </th>
                                    <th>Invoice #</th>
                                    <th>Customer</th>
                                    <th>Date</th>
                                    <th>Amount</th>
                                    <th>Status</th>
                                </tr>
                            </thead>
                            <tbody>
                                <!-- Will be populated via AJAX -->
                            </tbody>
                        </table>
                    </div>
                </form>
            </div>
        </div>
    </div>
</div>

<!-- Add Recurring Invoice Feature -->
<div class="row mt-4">
    <div class="col-md-12">
        <div class="card">
            <div class="card-header">
                <h4>Recurring Invoices</h4>
            </div>
            <div class="card-body">
                <form id="recurringInvoiceForm">
                    <div class="row">
                        <div class="col-md-4">
                            <div class="mb-3">
                                <label class="form-label">Select Invoice Template</label>
                                <select class="form-select" name="template_id" required>
                                    <!-- Will be populated via AJAX -->
                                </select>
                            </div>
                        </div>
                        <div class="col-md-4">
                            <div class="mb-3">
                                <label class="form-label">Frequency</label>
                                <select class="form-select" name="frequency" required>
                                    <option value="monthly">Monthly</option>
                                    <option value="quarterly">Quarterly</option>
                                    <option value="yearly">Yearly</option>
                                </select>
                            </div>
                        </div>
                        <div class="col-md-4">
                            <div class="mb-3">
                                <label class="form-label">Next Generation Date</label>
                                <input type="date" class="form-control" name="next_date" required>
                            </div>
                        </div>
                    </div>
                    <button type="submit" class="btn btn-success">
                        Setup Recurring Invoice
                    </button>
                </form>

                <!-- Recurring Invoices List -->
                <div class="table-responsive mt-4">
                    <table class="table table-striped" id="recurringInvoicesTable">
                        <thead>
                            <tr>
                                <th>Template</th>
                                <th>Customer</th>
                                <th>Frequency</th>
                                <th>Next Date</th>
                                <th>Amount</th>
                                <th>Actions</th>
                            </tr>
                        </thead>
                        <tbody>
                            <!-- Will be populated via AJAX -->
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
    </div>
</div>

<?php
// Add necessary SQL procedures for batch operations

$sql = "
DELIMITER //

CREATE PROCEDURE sp_batch_update_invoice_status(
    IN p_invoice_ids TEXT,
    IN p_status VARCHAR(20)
)
BEGIN
    SET @sql = CONCAT(
        'UPDATE invoices SET status = ? WHERE id IN (', p_invoice_ids, ')'
    );
    PREPARE stmt FROM @sql;
    EXECUTE stmt USING p_status;
    DEALLOCATE PREPARE stmt;
END //

CREATE PROCEDURE sp_setup_recurring_invoice(
    IN p_template_id INT,
    IN p_frequency VARCHAR(20),
    IN p_next_date DATE
)
BEGIN
    INSERT INTO recurring_invoices (
        template_id,
        frequency,
        next_date
    ) VALUES (
        p_template_id,
        p_frequency,
        p_next_date
    );
END //

CREATE PROCEDURE sp_generate_recurring_invoices()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_template_id, v_customer_id INT;
    DECLARE v_frequency VARCHAR(20);
    DECLARE v_next_date DATE;
    
    DECLARE cur CURSOR FOR 
        SELECT template_id, frequency, next_date
        FROM recurring_invoices
        WHERE next_date <= CURRENT_DATE;
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN cur;
    
    read_loop: LOOP
        FETCH cur INTO v_template_id, v_frequency, v_next_date;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        -- Create new invoice from template
        CALL sp_create_invoice_from_template(v_template_id);
        
        -- Update next date
        UPDATE recurring_invoices
        SET next_date = 
            CASE v_frequency
                WHEN 'monthly' THEN DATE_ADD(v_next_date, INTERVAL 1 MONTH)
                WHEN 'quarterly' THEN DATE_ADD(v_next_date, INTERVAL 3 MONTH)
                WHEN 'yearly' THEN DATE_ADD(v_next_date, INTERVAL 1 YEAR)
            END
        WHERE template_id = v_template_id;
    END LOOP;
    
    CLOSE cur;
END //

DELIMITER ;
";

// Add JavaScript for batch operations and recurring invoices
?>

<script>
$(document).ready(function() {
    // Initialize DataTable for invoice selection
    const invoiceTable = $('#invoiceSelectionTable').DataTable({
        ajax: 'api.php?action=list_invoices',
        columns: [
            {
                data: 'id',
                render: function(data) {
                    return `<input type="checkbox" name="invoice_ids[]" value="${data}">`;
                }
            },
            { data: 'invoice_number' },
            { data: 'customer_name' },
            { data: 'invoice_date' },
            { 
                data: 'total_amount',
                render: function(data) {
                    return formatCurrency(data);
                }
            },
            {
                data: 'status',
                render: function(data) {
                    return `<span class="badge bg-${getStatusColor(data)}">${data}</span>`;
                }
            }
        ]
    });

    // Select All checkbox
    $('#selectAll').change(function() {
        $('input[name="invoice_ids[]"]').prop('checked', $(this).prop('checked'));
    });

    // Bulk Action Form
    $('#bulkActionForm').submit(function(e) {
        e.preventDefault();
        
        const selectedIds = $('input[name="invoice_ids[]"]:checked').map(function() {
            return $(this).val();
        }).get();

        if(selectedIds.length === 0) {
            alert('Please select at least one invoice');
            return;
        }

        const action = $('select[name="bulk_action"]').val();
        
        if(confirm(`Are you sure you want to ${action} the selected invoices?`)) {
            $.post('api.php', {
                action: 'bulk_action',
                bulk_action: action,
                invoice_ids: selectedIds
            }, function(response) {
                if(response.success) {
                    toastr.success(response.message);
                    invoiceTable.ajax.reload();
                } else {
                    toastr.error(response.message);
                }
            });
        }
    });

    // Initialize DataTable for recurring invoices
    const recurringTable = $('#recurringInvoicesTable').DataTable({
        ajax: 'api.php?action=list_recurring',
        columns: [
            { data: 'template_name' },
            { data: 'customer_name' },
            { data: 'frequency' },
            { data: 'next_date' },
            {
                data: 'amount',
                render: function(data) {
                    return formatCurrency(data);
                }
            },
            {
                data: 'id',
                render: function(data) {
                    return `
                        <button class="btn btn-sm btn-primary edit-recurring" data-id="${data}">
                            Edit
                        </button>
                        <button class="btn btn-sm btn-danger delete-recurring" data-id="${data}">
                            Delete
                        </button>
                    `;
                }
            }
        ]
    });

    // Recurring Invoice Form
    $('#recurringInvoiceForm').submit(function(e) {
        e.preventDefault();
        
        $.post('api.php', $(this).serialize() + '&action=setup_recurring', function(response) {
            if(response.success) {
                toastr.success(response.message);
                recurringTable.ajax.reload();
                $(this)[0].reset();
            } else {
                toastr.error(response.message);
            }
        });
    });
});

// Helper functions
function formatCurrency(amount) {
    return new Intl.NumberFormat('en-US', {
        style: 'currency',
        currency: 'USD'
    }).format(amount);
}

function getStatusColor(status) {
    const colors = {
        'paid': 'success',
        'pending': 'warning',
        'cancelled': 'danger',
        'draft': 'secondary'
    };
    return colors[status] || 'primary';
}
</script>

<?php require_once '../../includes/footer.php'; ?><?php
require_once '../../config/config.php';
require_once '../../config/database.php';
require_once '../../includes/header.php';
?>

<div class="row">
    <div class="col-md-12">
        <div class="card">
            <div class="card-header">
                <h4>Create New Invoice</h4>
            </div>
            <div class="card-body">
                <form id="createInvoiceForm">
                    <!-- Invoice Header -->
                    <div class="row mb-4">
                        <div class="col-md-6">
                            <div class="mb-3">
                                <label class="form-label">Customer</label>
                                <select class="form-select" name="customer_id" required>
                                    <option value="">Select Customer</option>
                                </select>
                            </div>
                        </div>
                        <div class="col-md-3">
                            <div class="mb-3">
                                <label class="form-label">Invoice Date</label>
                                <input type="date" class="form-control" name="invoice_date" required>
                            </div>
                        </div>
                        <div class="col-md-3">
                            <div class="mb-3">
                                <label class="form-label">Due Date</label>
                                <input type="date" class="form-control" name="due_date" required>
                            </div>
                        </div>
                    </div>

                    <!-- Invoice Items -->
                    <div class="row mb-4">
                        <div class="col-md-12">
                            <h5>Invoice Items</h5>
                            <table class="table" id="invoiceItems">
                                <thead>
                                    <tr>
                                        <th>Product</th>
                                        <th>Quantity</th>
                                        <th>Unit Price</th>
                                        <th>Total</th>
                                        <th>Action</th>
                                    </tr>
                                </thead>
                                <tbody>
                                    <tr id="itemRow0">
                                        <td>
                                            <select class="form-select product-select" name="items[0][product_id]" required>
                                                <option value="">Select Product</option>
                                            </select>
                                        </td>
                                        <td>
                                            <input type="number" class="form-control quantity" name="items[0][quantity]" min="1" value="1">
                                        </td>
                                        <td>
                                            <input type="number" class="form-control unit-price" name="items[0][unit_price]" readonly>
                                        </td>
                                        <td>
                                            <input type="number" class="form-control item-total" readonly>
                                        </td>
                                        <td>
                                            <button type="button" class="btn btn-danger btn-sm remove-item">
                                                <i class="fas fa-trash"></i>
                                            </button>
                                        </td>
                                    </tr>
                                </tbody>
                            </table>
                            <button type="button" class="btn btn-success" id="addItem">
                                <i class="fas fa-plus"></i> Add Item
                            </button>
                        </div>
                    </div>

                    <!-- Invoice Totals -->
                    <div class="row">
                        <div class="col-md-6">
                            <div class="mb-3">
                                <label class="form-label">Notes</label>
                                <textarea class="form-control" name="notes" rows="3"></textarea>
                            </div>
                        </div>
                        <div class="col-md-6">
                            <div class="card">
                                <div class="card-body">
                                    <div class="row mb-2">
                                        <label class="col-sm-4 col-form-label">Subtotal:</label>
                                        <div class="col-sm-8">
                                            <input type="number" class="form-control" id="subtotal" readonly>
                                        </div>
                                    </div>
                                    <div class="row mb-2">
                                        <label class="col-sm-4 col-form-label">Tax Rate (%):</label>
                                        <div class="col-sm-8">
                                            <input type="number" class="form-control" name="tax_rate" value="0" min="0" max="100">
                                        </div>
                                    </div>
                                    <div class="row mb-2">
                                        <label class="col-sm-4 col-form-label">Tax Amount:</label>
                                        <div class="col-sm-8">
                                            <input type="number" class="form-control" id="taxAmount" readonly>
                                        </div>
                                    </div>
                                    <div class="row">
                                        <label class="col-sm-4 col-form-label">Total:</label>
                                        <div class="col-sm-8">
                                            <input type="number" class="form-control" id="total" readonly>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>

                    <div class="row mt-4">
                        <div class="col-md-12">
                            <button type="submit" class="btn btn-primary">
                                <i class="fas fa-save"></i> Save Invoice
                            </button>
                            <a href="index.php" class="btn btn-secondary">
                                <i class="fas fa-times"></i> Cancel
                            </a>
                        </div>
                    </div>
                </form>
            </div>
        </div>
    </div>
</div>

<!-- Add JavaScript for invoice handling -->
<script src="<?php echo SITE_URL; ?>/assets/js/invoice-create.js"></script><?php
require_once '../../config/config.php';
require_once '../../config/database.php';
require_once '../../includes/InvoicePDF.php';

$invoice_id = isset($_GET['id']) ? intval($_GET['id']) : 0;

// Get invoice details
$query = "SELECT i.*, c.* 
          FROM invoices i 
          LEFT JOIN customers c ON i.customer_id = c.id 
          WHERE i.id = $invoice_id";
$invoice = $db->query($query)->fetch_assoc();

// Get invoice items
$query = "SELECT ii.*, p.product_name 
          FROM invoice_items ii 
          LEFT JOIN products p ON ii.product_id = p.id 
          WHERE ii.invoice_id = $invoice_id";
$items = $db->query($query)->fetch_all(MYSQLI_ASSOC);

// Generate PDF
$pdf = new InvoicePDF($invoice, $items);
$pdfContent = $pdf->generate();

// Output PDF
header('Content-Type: application/pdf');
header('Content-Disposition: attachment; filename="Invoice_' . $invoice['invoice_number'] . '.pdf"');
echo $pdfContent;<?php
require_once '../../config/config.php';
require_once '../../config/database.php';
require_once '../../includes/header.php';
?>

<div class="row">
    <div class="col-md-12 mb-3">
        <div class="card">
            <div class="card-header d-flex justify-content-between align-items-center">
                <h4>Invoices</h4>
                <a href="create.php" class="btn btn-primary">
                    <i class="fas fa-plus"></i> Create New Invoice
                </a>
            </div>
            <div class="card-body">
                <table id="invoicesTable" class="table table-striped">
                    <thead>
                        <tr>
                            <th>Invoice #</th>
                            <th>Customer</th>
                            <th>Date</th>
                            <th>Due Date</th>
                            <th>Amount</th>
                            <th>Status</th>
                            <th>Actions</th>
                        </tr>
                    </thead>
                </table>
            </div>
        </div>
    </div>
</div>

<?php require_once '../../includes/footer.php'; ?><?php
require_once '../../config/config.php';
require_once '../../config/database.php';
require_once '../../includes/header.php';

// Function to get status color
function getStatusColor($status) {
    switch ($status) {
        case 'paid':
            return 'success';
        case 'pending':
            return 'warning';
        case 'overdue':
            return 'danger';
        case 'cancelled':
            return 'secondary';
        default:
            return 'info';
    }
}

$invoice_id = isset($_GET['id']) ? intval($_GET['id']) : 0;

// Get invoice details
$query = "SELECT i.*, c.* 
          FROM invoices i 
          LEFT JOIN customers c ON i.customer_id = c.id 
          WHERE i.id = $invoice_id";
$result = $db->query($query);
$invoice = $result->fetch_assoc();

// Get invoice items
$query = "SELECT ii.*, p.product_name 
          FROM invoice_items ii 
          LEFT JOIN products p ON ii.product_id = p.id 
          WHERE ii.invoice_id = $invoice_id";
$items = $db->query($query)->fetch_all(MYSQLI_ASSOC);
?>

<div class="row">
    <div class="col-md-12 mb-3">
        <div class="card">
            <div class="card-header d-flex justify-content-between align-items-center">
                <h4>Invoice #<?php echo $invoice['invoice_number']; ?></h4>
                <div class="btn-group">
                    <button type="button" class="btn btn-primary" onclick="printInvoice()">
                        <i class="fas fa-print"></i> Print
                    </button>
                    <button type="button" class="btn btn-success" onclick="downloadPDF()">
                        <i class="fas fa-file-pdf"></i> Download PDF
                    </button>
                    <button type="button" class="btn btn-info" onclick="emailInvoice()">
                        <i class="fas fa-envelope"></i> Email
                    </button>
                </div>
            </div>
            <div class="card-body" id="invoicePrint">
                <!-- Company Information -->
                <div class="row mb-4">
                    <div class="col-md-6">
                        <h2><?php echo COMPANY_NAME; ?></h2>
                        <p><?php echo COMPANY_ADDRESS; ?><br>
                           Phone: <?php echo COMPANY_PHONE; ?><br>
                           Email: <?php echo COMPANY_EMAIL; ?></p>
                    </div>
                    <div class="col-md-6 text-end">
                        <h1 class="text-primary">INVOICE</h1>
                        <p><strong>Invoice #:</strong> <?php echo $invoice['invoice_number']; ?><br>
                           <strong>Date:</strong> <?php echo date('d/m/Y', strtotime($invoice['invoice_date'])); ?><br>
                           <strong>Due Date:</strong> <?php echo date('d/m/Y', strtotime($invoice['due_date'])); ?></p>
                    </div>
                </div>

                <!-- Customer Information -->
                <div class="row mb-4">
                    <div class="col-md-6">
                        <h5>Bill To:</h5>
                        <p><strong><?php echo $invoice['customer_name']; ?></strong><br>
                           <?php echo $invoice['address']; ?><br>
                           <?php echo $invoice['city'] . ', ' . $invoice['state'] . ' ' . $invoice['zip_code']; ?><br>
                           Phone: <?php echo $invoice['phone']; ?><br>
                           Email: <?php echo $invoice['email']; ?></p>
                    </div>
                    <div class="col-md-6 text-end">
                        <h5>Status:</h5>
                        <span class="badge bg-<?php echo getStatusColor($invoice['status']); ?>">
                            <?php echo ucfirst($invoice['status']); ?>
                        </span>
                    </div>
                </div>

                <!-- Invoice Items -->
                <div class="table-responsive mb-4">
                    <table class="table table-striped">
                        <thead>
                            <tr>
                                <th>Item</th>
                                <th>Quantity</th>
                                <th>Unit Price</th>
                                <th class="text-end">Total</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php foreach($items as $item): ?>
                            <tr>
                                <td><?php echo $item['product_name']; ?></td>
                                <td><?php echo $item['quantity']; ?></td>
                                <td><?php echo number_format($item['unit_price'], 2); ?></td>
                                <td class="text-end"><?php echo number_format($item['total_price'], 2); ?></td>
                            </tr>
                            <?php endforeach; ?>
                        </tbody>
                        <tfoot>
                            <tr>
                                <td colspan="3" class="text-end"><strong>Subtotal:</strong></td>
                                <td class="text-end"><?php echo number_format($invoice['subtotal'], 2); ?></td>
                            </tr>
                            <tr>
                                <td colspan="3" class="text-end"><strong>Tax (<?php echo isset($invoice['tax_rate']) ? $invoice['tax_rate'] : 0; ?>%):</strong></td>
                                <td class="text-end"><?php echo number_format($invoice['tax_amount'], 2); ?></td>
                            </tr>
                            <tr>
                                <td colspan="3" class="text-end"><strong>Total:</strong></td>
                                <td class="text-end"><strong><?php echo number_format($invoice['total_amount'], 2); ?></strong></td>
                            </tr>
                        </tfoot>
                    </table>
                </div>

                <!-- Notes -->
                <?php if(!empty($invoice['notes'])): ?>
                <div class="row mb-4">
                    <div class="col-md-12">
                        <h5>Notes:</h5>
                        <p><?php echo nl2br($invoice['notes']); ?></p>
                    </div>
                </div>
                <?php endif; ?>
            </div>
        </div>
    </div>
</div>

<?php
// Add PDF Generation Class
require_once '../../includes/InvoicePDF.php';
?>

<script>
function printInvoice() {
    const printContent = document.getElementById('invoicePrint').innerHTML;
    const originalContent = document.body.innerHTML;
    
    document.body.innerHTML = printContent;
    window.print();
    document.body.innerHTML = originalContent;
}

function downloadPDF() {
    window.location.href = 'generate_pdf.php?id=<?php echo $invoice_id; ?>';
}

function emailInvoice() {
    if(confirm('Send invoice to <?php echo $invoice['email']; ?>?')) {
        $.post('api.php', {
            action: 'email_invoice',
            invoice_id: <?php echo $invoice_id; ?>
        }, function(response) {
            alert(response.message);
        });
    }
}
</script>

<?php require_once '../../includes/footer.php'; ?>
// modules/payments/api.php
<?php
require_once '../../config/config.php';
require_once '../../config/database.php';

header('Content-Type: application/json');

$action = $_POST['action'] ?? $_GET['action'] ?? '';

switch($action) {
    case 'list_payments':
        $query = "SELECT p.*, i.invoice_number, c.customer_name 
                 FROM payments p
                 LEFT JOIN invoices i ON p.invoice_id = i.id
                 LEFT JOIN customers c ON i.customer_id = c.id
                 ORDER BY p.payment_date DESC";
        
        $result = $db->query($query);
        $payments = [];
        
        while($row = $result->fetch_assoc()) {
            $payments[] = $row;
        }
        
        echo json_encode(['data' => $payments]);
        break;
        
    case 'get_unpaid_invoices':
        $query = "SELECT i.id, i.invoice_number, c.customer_name, 
                        (i.total_amount - COALESCE(SUM(p.amount), 0)) as balance
                 FROM invoices i
                 LEFT JOIN payments p ON i.id = p.invoice_id
                 LEFT JOIN customers c ON i.customer_id = c.id
                 WHERE i.status != 'paid'
                 GROUP BY i.id
                 HAVING balance > 0";
        
        $result = $db->query($query);
        $invoices = [];
        
        while($row = $result->fetch_assoc()) {
            $invoices[] = $row;
        }
        
        echo json_encode(['data' => $invoices]);
        break;
        
    case 'record_payment':
        try {
            $invoiceId = intval($_POST['invoice_id']);
            $amount = floatval($_POST['amount']);
            $paymentDate = $db->escape($_POST['payment_date']);
            $paymentMethod = $db->escape($_POST['payment_method']);
            $referenceNumber = $db->escape($_POST['reference_number']);
            $notes = $db->escape($_POST['notes']);
            
            $query = "CALL sp_record_payment($invoiceId, $amount, '$paymentDate', 
                                           '$paymentMethod', '$referenceNumber', '$notes')";
            
            $db->query($query);
            echo json_encode(['success' => true, 'message' => 'Payment recorded successfully']);
        } catch (Exception $e) {
            echo json_encode(['success' => false, 'message' => $e->getMessage()]);
        }
        break;
        
    case 'delete_payment':
        $paymentId = intval($_POST['payment_id']);
        
        // Start transaction
        $db->query("START TRANSACTION");
        
        try {
            // Get payment details
            $query = "SELECT invoice_id, amount FROM payments WHERE id = $paymentId";
            $payment = $db->query($query)->fetch_assoc();
            
            // Delete payment
            $query = "DELETE FROM payments WHERE id = $paymentId";
            $db->query($query);
            
            // Update invoice status
            $query = "UPDATE invoices SET status = 'pending' 
                     WHERE id = {$payment['invoice_id']}";
            $db->query($query);
            
            $db->query("COMMIT");
            echo json_encode(['success' => true, 'message' => 'Payment deleted successfully']);
        } catch (Exception $e) {
            $db->query("ROLLBACK");
            echo json_encode(['success' => false, 'message' => 'Failed to delete payment']);
        }
        break;
}<?php
require_once '../../config/config.php';
require_once '../../config/database.php';
require_once '../../includes/header.php';
?>

<div class="row">
    <div class="col-md-12">
        <div class="card">
            <div class="card-header d-flex justify-content-between align-items-center">
                <h4>Payment Tracking</h4>
                <button type="button" class="btn btn-primary" data-bs-toggle="modal" data-bs-target="#addPaymentModal">
                    <i class="fas fa-plus"></i> Record Payment
                </button>
            </div>
            <div class="card-body">
                <div class="table-responsive">
                    <table id="paymentsTable" class="table table-striped">
                        <thead>
                            <tr>
                                <th>Payment ID</th>
                                <th>Invoice #</th>
                                <th>Customer</th>
                                <th>Amount</th>
                                <th>Payment Date</th>
                                <th>Payment Method</th>
                                <th>Status</th>
                                <th>Actions</th>
                            </tr>
                        </thead>
                    </table>
                </div>
            </div>
        </div>
    </div>
</div>

<!-- Add Payment Modal -->
<div class="modal fade" id="addPaymentModal">
    <div class="modal-dialog">
        <div class="modal-content">
            <div class="modal-header">
                <h5 class="modal-title">Record Payment</h5>
                <button type="button" class="btn-close" data-bs-dismiss="modal"></button>
            </div>
            <form id="addPaymentForm">
                <div class="modal-body">
                    <div class="mb-3">
                        <label class="form-label">Select Invoice</label>
                        <select class="form-select" name="invoice_id" required>
                            <option value="">Choose Invoice</option>
                        </select>
                    </div>
                    <div class="mb-3">
                        <label class="form-label">Amount</label>
                        <input type="number" class="form-control" name="amount" step="0.01" required>
                    </div>
                    <div class="mb-3">
                        <label class="form-label">Payment Date</label>
                        <input type="date" class="form-control" name="payment_date" required>
                    </div>
                    <div class="mb-3">
                        <label class="form-label">Payment Method</label>
                        <select class="form-select" name="payment_method" required>
                            <option value="cash">Cash</option>
                            <option value="bank_transfer">Bank Transfer</option>
                            <option value="credit_card">Credit Card</option>
                            <option value="check">Check</option>
                        </select>
                    </div>
                    <div class="mb-3">
                        <label class="form-label">Reference Number</label>
                        <input type="text" class="form-control" name="reference_number">
                    </div>
                    <div class="mb-3">
                        <label class="form-label">Notes</label>
                        <textarea class="form-control" name="notes" rows="3"></textarea>
                    </div>
                </div>
                <div class="modal-footer">
                    <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Close</button>
                    <button type="submit" class="btn btn-primary">Save Payment</button>
                </div>
            </form>
        </div>
    </div>
</div>

<?php
// Create necessary database tables and procedures

$sql = "
-- Create payments table
CREATE TABLE payments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    invoice_id INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    payment_date DATE NOT NULL,
    payment_method VARCHAR(50) NOT NULL,
    reference_number VARCHAR(100),
    notes TEXT,
    status ENUM('pending', 'completed', 'failed') DEFAULT 'completed',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (invoice_id) REFERENCES invoices(id)
);

-- Create payment tracking views
CREATE VIEW vw_payment_summary AS
SELECT 
    i.id AS invoice_id,
    i.invoice_number,
    i.total_amount AS invoice_amount,
    COALESCE(SUM(p.amount), 0) AS paid_amount,
    (i.total_amount - COALESCE(SUM(p.amount), 0)) AS balance,
    CASE 
        WHEN i.total_amount <= COALESCE(SUM(p.amount), 0) THEN 'Paid'
        WHEN COALESCE(SUM(p.amount), 0) > 0 THEN 'Partial'
        ELSE 'Unpaid'
    END AS payment_status
FROM invoices i
LEFT JOIN payments p ON i.id = p.invoice_id
GROUP BY i.id;

-- Create stored procedures
DELIMITER //

CREATE PROCEDURE sp_record_payment(
    IN p_invoice_id INT,
    IN p_amount DECIMAL(10,2),
    IN p_payment_date DATE,
    IN p_payment_method VARCHAR(50),
    IN p_reference_number VARCHAR(100),
    IN p_notes TEXT
)
BEGIN
    DECLARE v_invoice_balance DECIMAL(10,2);
    
    -- Get current invoice balance
    SELECT (total_amount - COALESCE(SUM(p.amount), 0))
    INTO v_invoice_balance
    FROM invoices i
    LEFT JOIN payments p ON i.id = p.invoice_id
    WHERE i.id = p_invoice_id
    GROUP BY i.id;
    
    -- Check if payment amount is valid
    IF p_amount > v_invoice_balance THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Payment amount exceeds invoice balance';
    END IF;
    
    -- Record payment
    INSERT INTO payments (
        invoice_id,
        amount,
        payment_date,
        payment_method,
        reference_number,
        notes
    ) VALUES (
        p_invoice_id,
        p_amount,
        p_payment_date,
        p_payment_method,
        p_reference_number,
        p_notes
    );
    
    -- Update invoice status if fully paid
    IF v_invoice_balance - p_amount <= 0 THEN
        UPDATE invoices SET status = 'paid' WHERE id = p_invoice_id;
    END IF;
END //

CREATE PROCEDURE sp_get_payment_statistics(
    IN p_start_date DATE,
    IN p_end_date DATE
)
BEGIN
    SELECT 
        COUNT(*) as total_payments,
        SUM(amount) as total_amount,
        payment_method,
        DATE_FORMAT(payment_date, '%Y-%m') as payment_month
    FROM payments
    WHERE payment_date BETWEEN p_start_date AND p_end_date
    GROUP BY payment_method, payment_month
    ORDER BY payment_month, payment_method;
END //

DELIMITER ;
";

// Add JavaScript for payment management
?>

<script>
$(document).ready(function() {
    // Initialize DataTable
    const paymentsTable = $('#paymentsTable').DataTable({
        ajax: 'api.php?action=list_payments',
        columns: [
            { data: 'id' },
            { 
                data: 'invoice_number',
                render: function(data, type, row) {
                    return `<a href="../invoices/view.php?id=${row.invoice_id}">${data}</a>`;
                }
            },
            { data: 'customer_name' },
            { 
                data: 'amount',
                render: function(data) {
                    return formatCurrency(data);
                }
            },
            { data: 'payment_date' },
            { 
                data: 'payment_method',
                render: function(data) {
                    return data.replace('_', ' ').toUpperCase();
                }
            },
            { 
                data: 'status',
                render: function(data) {
                    return `<span class="badge bg-${getStatusColor(data)}">${data}</span>`;
                }
            },
            {
                data: 'id',
                render: function(data, type, row) {
                    return `
                        <button class="btn btn-sm btn-primary" onclick="viewPayment(${data})">
                            <i class="fas fa-eye"></i>
                        </button>
                        <button class="btn btn-sm btn-danger" onclick="deletePayment(${data})">
                            <i class="fas fa-trash"></i>
                        </button>
                    `;
                }
            }
        ]
    });

    // Load unpaid invoices for dropdown
    $.get('api.php?action=get_unpaid_invoices', function(response) {
        const select = $('select[name="invoice_id"]');
        response.data.forEach(invoice => {
            select.append(`
                <option value="${invoice.id}">
                    ${invoice.invoice_number} - ${invoice.customer_name} 
                    (Balance: ${formatCurrency(invoice.balance)})
                </option>
            `);
        });
    });

    // Handle payment form submission
    $('#addPaymentForm').submit(function(e) {
        e.preventDefault();
        
        $.post('api.php', $(this).serialize() + '&action=record_payment', function(response) {
            if(response.success) {
                $('#addPaymentModal').modal('hide');
                paymentsTable.ajax.reload();
                toastr.success(response.message);
            } else {
                toastr.error(response.message);
            }
        });
    });
});

function viewPayment(id) {
    // Implement payment details view
}

function deletePayment(id) {
    if(confirm('Are you sure you want to delete this payment?')) {
        $.post('api.php', {
            action: 'delete_payment',
            payment_id: id
        }, function(response) {
            if(response.success) {
                $('#paymentsTable').DataTable().ajax.reload();
                toastr.success(response.message);
            } else {
                toastr.error(response.message);
            }
        });
    }
}

function formatCurrency(amount) {
    return new Intl.NumberFormat('en-US', {
        style: 'currency',
        currency: 'USD'
    }).format(amount);
}

function getStatusColor(status) {
    const colors = {
        'completed': 'success',
        'pending': 'warning',
        'failed': 'danger'
    };
    return colors[status] || 'secondary';
}
</script>

<?php require_once '../../includes/footer.php'; ?><?php
require_once '../../config/config.php';
require_once '../../config/database.php';

header('Content-Type: application/json');

$action = $_POST['action'] ?? $_GET['action'] ?? '';

switch($action) {
    case 'list':
        $query = "SELECT * FROM products ORDER BY id DESC";
        $result = $db->query($query);
        $products = [];
        
        while($row = $result->fetch_assoc()) {
            $products[] = $row;
        }
        
        echo json_encode(['data' => $products]);
        break;
        
    case 'add':
        $name = $db->escape($_POST['product_name']);
        $description = $db->escape($_POST['description']);
        $price = floatval($_POST['unit_price']);
        $stock = intval($_POST['stock_quantity']);
        
        $query = "INSERT INTO products (product_name, description, unit_price, stock_quantity) 
                  VALUES ('$name', '$description', $price, $stock)";
        
        if($db->query($query)) {
            echo json_encode(['success' => true, 'message' => 'Product added successfully']);
        } else {
            echo json_encode(['success' => false, 'message' => 'Failed to add product']);
        }
        break;
        
    case 'edit':
        $id = intval($_POST['product_id']);
        $name = $db->escape($_POST['product_name']);
        $description = $db->escape($_POST['description']);
        $price = floatval($_POST['unit_price']);
        $stock = intval($_POST['stock_quantity']);
        
        $query = "UPDATE products SET 
                  product_name = '$name',
                  description = '$description',
                  unit_price = $price,
                  stock_quantity = $stock
                  WHERE id = $id";
        
        if($db->query($query)) {
            error_log("Update successful for product ID: $id"); // Log success
            echo json_encode(['success' => true, 'message' => 'Product updated successfully']);

        } else {
            echo json_encode(['success' => false, 'message' => 'Failed to update product']);
        }
        break;
        
    case 'delete':
        $id = intval($_POST['id']);
        
        // First check if product is used in any invoice
        $check_query = "SELECT COUNT(*) as count FROM invoice_items WHERE product_id = $id";
        $result = $db->query($check_query)->fetch_assoc();
        
        if($result['count'] > 0) {
            echo json_encode(['success' => false, 'message' => 'Cannot delete product. It is used in invoices.']);
            exit;
        }
        
        $query = "DELETE FROM products WHERE id = $id";
        
        if($db->query($query)) {
            echo json_encode(['success' => true, 'message' => 'Product deleted successfully']);
        } else {
            echo json_encode(['success' => false, 'message' => 'Failed to delete product']);
        }
        break;
        
    case 'get':
        $id = intval($_GET['id']);
        $query = "SELECT * FROM products WHERE id = $id";
        $result = $db->query($query);
        
        if($product = $result->fetch_assoc()) {
            echo json_encode(['success' => true, 'data' => $product]);
        } else {
            echo json_encode(['success' => false, 'message' => 'Product not found']);
        }
        break;
}
<?php
require_once '../../config/config.php';
require_once '../../config/database.php';
require_once '../../includes/header.php';
?>

<div class="row">
    <div class="col-md-12">
        <div class="card">
            <div class="card-header d-flex justify-content-between align-items-center">
                <h4>Products Management</h4>
                <button type="button" class="btn btn-primary" data-bs-toggle="modal" data-bs-target="#addProductModal">
                    <i class="fas fa-plus"></i> Add New Product
                </button>
            </div>
            <div class="card-body">
                <table id="productsTable" class="table table-striped">
                    <thead>
                        <tr>
                            <th>ID</th>
                            <th>Product Name</th>
                            <th>Description</th>
                            <th>Unit Price</th>
                            <th>Stock</th>
                            <th>Actions</th>
                        </tr>
                    </thead>
                </table>
            </div>
        </div>
    </div>
</div>

<!-- Add Product Modal -->
<div class="modal fade" id="addProductModal" tabindex="-1">
    <div class="modal-dialog">
        <div class="modal-content">
            <div class="modal-header">
                <h5 class="modal-title">Add New Product</h5>
                <button type="button" class="btn-close" data-bs-dismiss="modal"></button>
            </div>
            <form id="addProductForm">
                <div class="modal-body">
                    <div class="mb-3">
                        <label class="form-label">Product Name</label>
                        <input type="text" class="form-control" name="product_name" required>
                    </div>
                    <div class="mb-3">
                        <label class="form-label">Description</label>
                        <textarea class="form-control" name="description" rows="3"></textarea>
                    </div>
                    <div class="mb-3">
                        <label class="form-label">Unit Price</label>
                        <input type="number" class="form-control" name="unit_price" step="0.01" required>
                    </div>
                    <div class="mb-3">
                        <label class="form-label">Initial Stock</label>
                        <input type="number" class="form-control" name="stock_quantity" value="0">
                    </div>
                </div>
                <div class="modal-footer">
                    <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Close</button>
                    <button type="submit" class="btn btn-primary">Save Product</button>
                </div>
            </form>
        </div>
    </div>
</div>

<!-- Edit Product Modal -->
<div class="modal fade" id="editProductModal" tabindex="-1">
    <div class="modal-dialog">
        <div class="modal-content">
            <div class="modal-header">
                <h5 class="modal-title">Edit Product</h5>
                <button type="button" class="btn-close" data-bs-dismiss="modal"></button>
            </div>
            <form id="editProductForm">
                <input type="hidden" name="product_id">
                <div class="modal-body">
                    <!-- Same fields as Add Product Modal -->
                    <div class="mb-3">
                        <label class="form-label">Product Name</label>
                        <input type="text" class="form-control" name="product_name" required>
                    </div>
                    <div class="mb-3">
                        <label class="form-label">Description</label>
                        <textarea class="form-control" name="description" rows="3"></textarea>
                    </div>
                    <div class="mb-3">
                        <label class="form-label">Unit Price</label>
                        <input type="number" class="form-control" name="unit_price" step="0.01" required>
                    </div>
                    <div class="mb-3">
                        <label class="form-label">Stock Quantity</label>
                        <input type="number" class="form-control" name="stock_quantity">
                    </div>
                </div>
                <div class="modal-footer">
                    <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Close</button>
                    <button type="submit" class="btn btn-primary">Update Product</button>
                </div>
            </form>
        </div>
    </div>
</div>

<?php require_once '../../includes/footer.php'; ?><?php
require_once '../../config/config.php';
require_once '../../config/database.php';

header('Content-Type: application/json');

$action = $_POST['action'] ?? $_GET['action'] ?? '';

switch($action) {
    case 'list_recurring':
        $query = "SELECT r.*, 
                    c.customer_name,
                    COUNT(i.id) as generated_invoices,
                    MAX(i.invoice_date) as last_generated
                 FROM recurring_invoices r
                 LEFT JOIN customers c ON r.customer_id = c.id
                 LEFT JOIN invoices i ON i.recurring_id = r.id
                 GROUP BY r.id
                 ORDER BY r.created_at DESC";
        
        $result = $db->query($query);
        $recurring = [];
        while($row = $result->fetch_assoc()) {
            $recurring[] = $row;
        }
        
        echo json_encode(['data' => $recurring]);
        break;

    case 'create':
        try {
            $db->query("START TRANSACTION");

            $customerId = intval($_POST['customer_id']);
            $frequency = $db->escape($_POST['frequency']);
            $startDate = $db->escape($_POST['start_date']);
            $endDate = !empty($_POST['end_date']) ? "'" . $db->escape($_POST['end_date']) . "'" : "NULL";
            $notes = $db->escape($_POST['notes']);

            // Calculate total amount
            $totalAmount = 0;
            foreach($_POST['items'] as $item) {
                $totalAmount += floatval($item['quantity']) * floatval($item['price']);
            }

            // Insert recurring invoice
            $query = "INSERT INTO recurring_invoices (
                        customer_id, frequency, start_date, end_date,
                        next_date, total_amount, notes
                     ) VALUES (
                        $customerId, '$frequency', '$startDate', $endDate,
                        '$startDate', $totalAmount, '$notes'
                     )";
            
            $db->query($query);
            $recurringId = $db->getLastId();

            // Insert items
            foreach($_POST['items'] as $item) {
                $productId = intval($item['product_id']);
                $quantity = intval($item['quantity']);
                $price = floatval($item['price']);

                $query = "INSERT INTO recurring_items (
                            recurring_id, product_id, quantity, price
                         ) VALUES (
                            $recurringId, $productId, $quantity, $price
                         )";
                
                $db->query($query);
            }

            $db->query("COMMIT");

            echo json_encode([
                'success' => true,
                'message' => 'Recurring invoice created successfully'
            ]);
        } catch (Exception $e) {
            $db->query("ROLLBACK");
            echo json_encode([
                'success' => false,
                'message' => 'Failed to create recurring invoice: ' . $e->getMessage()
            ]);
        }
        break;

    case 'get_settings':
        $id = intval($_GET['id']);
        
        $query = "SELECT * FROM recurring_invoices WHERE id = $id";
        $result = $db->query($query);
        
        if($recurring = $result->fetch_assoc()) {
            echo json_encode([
                'success' => true,
                'data' => $recurring
            ]);
        } else {
            echo json_encode([
                'success' => false,
                'message' => 'Recurring invoice not found'
            ]);
        }
        break;

    case 'update_settings':
        try {
            $id = intval($_POST['recurring_id']);
            $status = $db->escape($_POST['status']);
            $nextDate = $db->escape($_POST['next_date']);

            $query = "UPDATE recurring_invoices 
                     SET status = '$status',
                         next_date = '$nextDate'
                     WHERE id = $id";

            if($db->query($query)) {
                echo json_encode([
                    'success' => true,
                    'message' => 'Settings updated successfully'
                ]);
            } else {
                throw new Exception('Failed to update settings');
            }
        } catch (Exception $e) {
            echo json_encode([
                'success' => false,
                'message' => $e->getMessage()
            ]);
        }
        break;

    case 'delete':
        try {
            $id = intval($_POST['id']);

            // Check if there are generated invoices
            $query = "SELECT COUNT(*) as count 
                     FROM invoices 
                     WHERE recurring_id = $id";
            $result = $db->query($query)->fetch_assoc();

            if($result['count'] > 0) {
                throw new Exception('Cannot delete: This recurring invoice has generated invoices');
            }

            $db->query("START TRANSACTION");

            // Delete items
            $query = "DELETE FROM recurring_items WHERE recurring_id = $id";
            $db->query($query);

            // Delete recurring invoice
            $query = "DELETE FROM recurring_invoices WHERE id = $id";
            $db->query($query);

            $db->query("COMMIT");

            echo json_encode([
                'success' => true,
                'message' => 'Recurring invoice deleted successfully'
            ]);
        } catch (Exception $e) {
            $db->query("ROLLBACK");
            echo json_encode([
                'success' => false,
                'message' => $e->getMessage()
            ]);
        }
        break;

    case 'get_history':
        $id = intval($_GET['id']);
        
        $query = "SELECT i.*, c.customer_name
                 FROM invoices i
                 LEFT JOIN customers c ON i.customer_id = c.id
                 WHERE i.recurring_id = $id
                 ORDER BY i.invoice_date DESC";
        
        $result = $db->query($query);
        $invoices = [];
        while($row = $result->fetch_assoc()) {
            $invoices[] = $row;
        }
        
        echo json_encode([
            'success' => true,
            'data' => $invoices
        ]);
        break;
}
?>

<!-- Create the history page -->
<?php
// modules/recurring/history.php
require_once '../../config/config.php';
require_once '../../config/database.php';
require_once '../../includes/header.php';

$recurring_id = intval($_GET['id']);

// Get recurring invoice details
$query = "SELECT r.*, c.customer_name
          FROM recurring_invoices r
          LEFT JOIN customers c ON r.customer_id = c.id
          WHERE r.id = $recurring_id";
$recurring = $db->query($query)->fetch_assoc();

if(!$recurring) {
    die('Recurring invoice not found');
}
?>

<div class="row">
    <div class="col-12 mb-4">
        <div class="card">
            <div class="card-header">
                <h4>Recurring Invoice History</h4>
            </div>
            <div class="card-body">
                <div class="row mb-4">
                    <div class="col-md-6">
                        <h5>Customer: <?php echo $recurring['customer_name']; ?></h5>
                        <p>Frequency: <?php echo ucfirst($recurring['frequency']); ?></p>
                        <p>Start Date: <?php echo formatDate($recurring['start_date']); ?></p>
                        <p>End Date: <?php echo $recurring['end_date'] ? formatDate($recurring['end_date']) : 'No end date'; ?></p>
                    </div>
                    <div class="col-md-6 text-end">
                        <h5>Status: <span class="badge bg-<?php echo getStatusColor($recurring['status']); ?>">
                            <?php echo ucfirst($recurring['status']); ?>
                        </span></h5>
                        <p>Next Generation: <?php echo formatDate($recurring['next_date']); ?></p>
                        <p>Amount: <?php echo formatCurrency($recurring['total_amount']); ?></p>
                    </div>
                </div>

                <table id="historyTable" class="table table-striped">
                    <thead>
                        <tr>
                            <th>Invoice #</th>
                            <th>Date</th>
                            <th>Due Date</th>
                            <th>Amount</th>
                            <th>Status</th>
                            <th>Actions</th>
                        </tr>
                    </thead>
                </table>
            </div>
        </div>
    </div>
</div>

<script>
$(document).ready(function() {
    $('#historyTable').DataTable({
        ajax: {
            url: 'api.php',
            data: {
                action: 'get_history',
                id: <?php echo $recurring_id; ?>
            }
        },
        columns: [
            { 
                data: 'invoice_number',
                render: function(data, type, row) {
                    return `<a href="../invoices/view.php?id=${row.id}">${data}</a>`;
                }
            },
            { data: 'invoice_date' },
            { data: 'due_date' },
            { 
                data: 'total_amount',
                render: formatCurrency
            },
            {
                data: 'status',
                render: function(data) {
                    return `<span class="badge bg-${getStatusColor(data)}">${data}</span>`;
                }
            },
            {
                data: 'id',
                render: function(data) {
                    return `
                        <button class="btn btn-sm btn-primary" onclick="viewInvoice(${data})">
                            <i class="fas fa-eye"></i>
                        </button>
                        <button class="btn btn-sm btn-success" onclick="downloadPDF(${data})">
                            <i class="fas fa-download"></i>
                        </button>
                    `;
                }
            }
        ],
        order: [[1, 'desc']]
    });
});

function viewInvoice(id) {
    window.location.href = `../invoices/view.php?id=${id}`;
}

function downloadPDF(id) {
    window.location.href = `../invoices/download.php?id=${id}`;
}

function formatCurrency(amount) {
    return new Intl.NumberFormat('en-US', {
        style: 'currency',
        currency: 'USD'
    }).format(amount);
}

function getStatusColor(status) {
    const colors = {
        'paid': 'success',
        'pending': 'warning',
        'overdue': 'danger',
        'cancelled': 'secondary'
    };
    return colors[status] || 'primary';
}
</script>

<?php require_once '../../includes/footer.php'; ?>// modules/recurring/cron.php
<?php
require_once '../../config/config.php';
require_once '../../config/database.php';
require_once '../../includes/EmailHandler.php';

class RecurringInvoiceGenerator {
    private $db;
    private $emailHandler;
    
    public function __construct() {
        $this->db = new Database();
        $this->emailHandler = new EmailHandler();
    }
    
    public function generateDueInvoices() {
        try {
            $this->db->query("START TRANSACTION");
            
            // Get due recurring invoices
            $query = "SELECT r.*, c.* 
                     FROM recurring_invoices r
                     LEFT JOIN customers c ON r.customer_id = c.id
                     WHERE r.status = 'active' 
                     AND r.next_date <= CURRENT_DATE
                     AND (r.end_date IS NULL OR r.end_date >= CURRENT_DATE)";
            
            $result = $this->db->query($query);
            
            while($recurring = $result->fetch_assoc()) {
                $this->generateInvoice($recurring);
                $this->updateNextDate($recurring);
            }
            
            $this->db->query("COMMIT");
            $this->logGeneration("Successfully generated recurring invoices");
            
        } catch (Exception $e) {
            $this->db->query("ROLLBACK");
            $this->logGeneration("Error generating invoices: " . $e->getMessage());
        }
    }
    
    private function generateInvoice($recurring) {
        // Generate invoice number
        $invoiceNumber = $this->generateInvoiceNumber();
        
        // Insert invoice
        $query = "INSERT INTO invoices (
                    invoice_number,
                    customer_id,
                    recurring_id,
                    invoice_date,
                    due_date,
                    subtotal,
                    tax_rate,
                    tax_amount,
                    total_amount,
                    notes,
                    status
                ) VALUES (
                    '$invoiceNumber',
                    {$recurring['customer_id']},
                    {$recurring['id']},
                    CURRENT_DATE,
                    DATE_ADD(CURRENT_DATE, INTERVAL 30 DAY),
                    {$recurring['total_amount']},
                    {$recurring['tax_rate']},
                    {$recurring['tax_amount']},
                    {$recurring['total_amount']},
                    '{$recurring['notes']}',
                    'pending'
                )";
        
        $this->db->query($query);
        $invoiceId = $this->db->getLastId();
        
        // Copy items
        $this->copyInvoiceItems($recurring['id'], $invoiceId);
        
        // Send email notification
        $this->sendInvoiceNotification($invoiceId, $recurring);
    }
    
    private function copyInvoiceItems($recurringId, $invoiceId) {
        $query = "INSERT INTO invoice_items (
                    invoice_id,
                    product_id,
                    quantity,
                    unit_price,
                    total_price
                ) SELECT 
                    $invoiceId,
                    product_id,
                    quantity,
                    price,
                    quantity * price
                 FROM recurring_items
                 WHERE recurring_id = $recurringId";
        
        $this->db->query($query);
    }
    
    private function updateNextDate($recurring) {
        $nextDate = $this->calculateNextDate($recurring['next_date'], $recurring['frequency']);
        
        $query = "UPDATE recurring_invoices 
                 SET next_date = '$nextDate',
                     last_generated = CURRENT_DATE
                 WHERE id = {$recurring['id']}";
        
        $this->db->query($query);
    }
    
    private function calculateNextDate($currentDate, $frequency) {
        switch($frequency) {
            case 'weekly':
                return date('Y-m-d', strtotime($currentDate . ' +1 week'));
            case 'monthly':
                return date('Y-m-d', strtotime($currentDate . ' +1 month'));
            case 'quarterly':
                return date('Y-m-d', strtotime($currentDate . ' +3 months'));
            case 'yearly':
                return date('Y-m-d', strtotime($currentDate . ' +1 year'));
            default:
                return $currentDate;
        }
    }
    
    private function generateInvoiceNumber() {
        $prefix = date('Ym');
        
        $query = "SELECT MAX(CAST(SUBSTRING(invoice_number, 9) AS UNSIGNED)) as last_number 
                 FROM invoices 
                 WHERE invoice_number LIKE 'INV$prefix%'";
        
        $result = $this->db->query($query)->fetch_assoc();
        $nextNumber = ($result['last_number'] ?? 0) + 1;
        
        return 'INV' . $prefix . str_pad($nextNumber, 4, '0', STR_PAD_LEFT);
    }
    
    private function sendInvoiceNotification($invoiceId, $recurring) {
        // Get invoice details
        $query = "SELECT i.*, c.*
                 FROM invoices i
                 LEFT JOIN customers c ON i.customer_id = c.id
                 WHERE i.id = $invoiceId";
        
        $invoice = $this->db->query($query)->fetch_assoc();
        
        // Generate PDF
        $pdf = new InvoicePDF($invoice);
        $pdfContent = $pdf->generate();
        
        // Send email
        $emailData = [
            'to' => $invoice['email'],
            'subject' => "New Invoice #{$invoice['invoice_number']}",
            'template' => 'recurring_invoice',
            'data' => [
                'customer_name' => $invoice['customer_name'],
                'invoice_number' => $invoice['invoice_number'],
                'amount' => formatCurrency($invoice['total_amount']),
                'due_date' => formatDate($invoice['due_date'])
            ],
            'attachments' => [
                [
                    'content' => $pdfContent,
                    'name' => "Invoice_{$invoice['invoice_number']}.pdf",
                    'type' => 'application/pdf'
                ]
            ]
        ];
        
        $this->emailHandler->send($emailData);
    }
    
    private function logGeneration($message) {
        $logFile = __DIR__ . '/../../logs/recurring.log';
        $timestamp = date('Y-m-d H:i:s');
        file_put_contents($logFile, "[$timestamp] $message\n", FILE_APPEND);
    }
}

// Add Email template for recurring invoices
class EmailTemplates {
    public static $templates = [
        'recurring_invoice' => [
            'subject' => 'New Invoice Generated - #{invoice_number}',
            'body' => '
                <div style="font-family: Arial, sans-serif; max-width: 600px; margin: 0 auto;">
                    <h2>New Invoice Generated</h2>
                    <p>Dear {customer_name},</p>
                    <p>A new invoice has been generated for your recurring billing:</p>
                    <div style="background: #f8f9fa; padding: 15px; margin: 20px 0;">
                        <p><strong>Invoice Number:</strong> {invoice_number}</p>
                        <p><strong>Amount:</strong> {amount}</p>
                        <p><strong>Due Date:</strong> {due_date}</p>
                    </div>
                    <p>Please find the invoice attached to this email.</p>
                    <p>For any questions, please don\'t hesitate to contact us.</p>
                    <p>Thank you for your business!</p>
                    <br>
                    <p>Best regards,<br>{company_name}</p>
                </div>
            '
        ]
    ];
}

// Add monitoring and reporting features
class RecurringMonitor {
    private $db;
    
    public function __construct() {
        $this->db = new Database();
    }
    
    public function getStatus() {
        $stats = [
            'total_active' => $this->getActiveCount(),
            'due_today' => $this->getDueTodayCount(),
            'failed_generation' => $this->getFailedCount(),
            'upcoming' => $this->getUpcomingInvoices()
        ];
        
        return $stats;
    }
    
    private function getActiveCount() {
        $query = "SELECT COUNT(*) as count 
                 FROM recurring_invoices 
                 WHERE status = 'active'";
        return $this->db->query($query)->fetch_assoc()['count'];
    }
    
    private function getDueTodayCount() {
        $query = "SELECT COUNT(*) as count 
                 FROM recurring_invoices 
                 WHERE status = 'active' 
                 AND next_date = CURRENT_DATE";
        return $this->db->query($query)->fetch_assoc()['count'];
    }
    
    private function getFailedCount() {
        $query = "SELECT COUNT(*) as count 
                 FROM recurring_invoices 
                 WHERE status = 'active' 
                 AND next_date < CURRENT_DATE";
        return $this->db->query($query)->fetch_assoc()['count'];
    }
    
    private function getUpcomingInvoices() {
        $query = "SELECT r.*, c.customer_name
                 FROM recurring_invoices r
                 LEFT JOIN customers c ON r.customer_id = c.id
                 WHERE r.status = 'active'
                 AND r.next_date BETWEEN CURRENT_DATE AND DATE_ADD(CURRENT_DATE, INTERVAL 7 DAY)
                 ORDER BY r.next_date";
        
        $result = $this->db->query($query);
        $upcoming = [];
        while($row = $result->fetch_assoc()) {
            $upcoming[] = $row;
        }
        return $upcoming;
    }
}

// Create monitoring page
if(isset($_GET['monitor'])) {
    $monitor = new RecurringMonitor();
    $status = $monitor->getStatus();
    
    header('Content-Type: application/json');
    echo json_encode($status);
    exit;
}

// Run generation if called via CLI or cron
if(php_sapi_name() === 'cli') {
    $generator = new RecurringInvoiceGenerator();
    $generator->generateDueInvoices();
}
?><?php
require_once '../../config/config.php';
require_once '../../config/database.php';
require_once '../../includes/header.php';
?>

<div class="row">
    <div class="col-12">
        <div class="card">
            <div class="card-header d-flex justify-content-between align-items-center">
                <h4>Recurring Invoices</h4>
                <button type="button" class="btn btn-primary" data-bs-toggle="modal" data-bs-target="#addRecurringModal">
                    <i class="fas fa-plus"></i> Create Recurring Invoice
                </button>
            </div>
            <div class="card-body">
                <!-- Recurring Invoices Table -->
                <table id="recurringTable" class="table table-striped">
                    <thead>
                        <tr>
                            <th>ID</th>
                            <th>Customer</th>
                            <th>Frequency</th>
                            <th>Amount</th>
                            <th>Next Due</th>
                            <th>Status</th>
                            <th>Actions</th>
                        </tr>
                    </thead>
                </table>
            </div>
        </div>
    </div>
</div>

<!-- Add Recurring Invoice Modal -->
<div class="modal fade" id="addRecurringModal" tabindex="-1">
    <div class="modal-dialog modal-lg">
        <div class="modal-content">
            <div class="modal-header">
                <h5 class="modal-title">Create Recurring Invoice</h5>
                <button type="button" class="btn-close" data-bs-dismiss="modal"></button>
            </div>
            <form id="recurringForm">
                <div class="modal-body">
                    <div class="row">
                        <div class="col-md-6">
                            <div class="mb-3">
                                <label class="form-label">Customer</label>
                                <select class="form-select" name="customer_id" required>
                                    <option value="">Select Customer</option>
                                </select>
                            </div>
                            <div class="mb-3">
                                <label class="form-label">Frequency</label>
                                <select class="form-select" name="frequency" required>
                                    <option value="weekly">Weekly</option>
                                    <option value="monthly">Monthly</option>
                                    <option value="quarterly">Quarterly</option>
                                    <option value="yearly">Yearly</option>
                                </select>
                            </div>
                            <div class="mb-3">
                                <label class="form-label">Start Date</label>
                                <input type="date" class="form-control" name="start_date" required>
                            </div>
                            <div class="mb-3">
                                <label class="form-label">End Date (Optional)</label>
                                <input type="date" class="form-control" name="end_date">
                            </div>
                        </div>
                        <div class="col-md-6">
                            <!-- Items Section -->
                            <div class="mb-3">
                                <label class="form-label">Items</label>
                                <div id="itemsContainer">
                                    <div class="item-row mb-2">
                                        <div class="row">
                                            <div class="col-6">
                                                <select class="form-select product-select" name="items[0][product_id]" required>
                                                    <option value="">Select Product</option>
                                                </select>
                                            </div>
                                            <div class="col-3">
                                                <input type="number" class="form-control" name="items[0][quantity]" 
                                                       placeholder="Qty" min="1" value="1" required>
                                            </div>
                                            <div class="col-3">
                                                <input type="number" class="form-control" name="items[0][price]" 
                                                       placeholder="Price" readonly>
                                            </div>
                                        </div>
                                    </div>
                                </div>
                                <button type="button" class="btn btn-sm btn-secondary mt-2" onclick="addItemRow()">
                                    Add Item
                                </button>
                            </div>
                            <div class="mb-3">
                                <label class="form-label">Notes</label>
                                <textarea class="form-control" name="notes" rows="3"></textarea>
                            </div>
                        </div>
                    </div>
                </div>
                <div class="modal-footer">
                    <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Close</button>
                    <button type="submit" class="btn btn-primary">Create Recurring Invoice</button>
                </div>
            </form>
        </div>
    </div>
</div>

<!-- Recurring Settings Modal -->
<div class="modal fade" id="settingsModal" tabindex="-1">
    <div class="modal-dialog">
        <div class="modal-content">
            <div class="modal-header">
                <h5 class="modal-title">Recurring Settings</h5>
                <button type="button" class="btn-close" data-bs-dismiss="modal"></button>
            </div>
            <form id="settingsForm">
                <div class="modal-body">
                    <div class="mb-3">
                        <label class="form-label">Status</label>
                        <select class="form-select" name="status" required>
                            <option value="active">Active</option>
                            <option value="paused">Paused</option>
                            <option value="completed">Completed</option>
                        </select>
                    </div>
                    <div class="mb-3">
                        <label class="form-label">Next Generation Date</label>
                        <input type="date" class="form-control" name="next_date" required>
                    </div>
                </div>
                <div class="modal-footer">
                    <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Close</button>
                    <button type="submit" class="btn btn-primary">Save Settings</button>
                </div>
            </form>
        </div>
    </div>
</div>

<?php
// Create necessary database tables and procedures
$sql = "
CREATE TABLE recurring_invoices (
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    frequency ENUM('weekly', 'monthly', 'quarterly', 'yearly') NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE,
    next_date DATE NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    notes TEXT,
    status ENUM('active', 'paused', 'completed') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

CREATE TABLE recurring_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    recurring_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (recurring_id) REFERENCES recurring_invoices(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

DELIMITER //

CREATE PROCEDURE sp_generate_recurring_invoices()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_recurring_id, v_customer_id INT;
    DECLARE v_next_date DATE;
    
    DECLARE cur CURSOR FOR 
        SELECT id, customer_id, next_date 
        FROM recurring_invoices 
        WHERE status = 'active' 
        AND next_date <= CURRENT_DATE
        AND (end_date IS NULL OR end_date >= CURRENT_DATE);
        
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN cur;
    
    read_loop: LOOP
        FETCH cur INTO v_recurring_id, v_customer_id, v_next_date;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        -- Generate invoice
        CALL sp_create_invoice_from_recurring(v_recurring_id);
        
        -- Update next date
        UPDATE recurring_invoices
        SET next_date = 
            CASE frequency
                WHEN 'weekly' THEN DATE_ADD(next_date, INTERVAL 1 WEEK)
                WHEN 'monthly' THEN DATE_ADD(next_date, INTERVAL 1 MONTH)
                WHEN 'quarterly' THEN DATE_ADD(next_date, INTERVAL 3 MONTH)
                WHEN 'yearly' THEN DATE_ADD(next_date, INTERVAL 1 YEAR)
            END
        WHERE id = v_recurring_id;
        
    END LOOP;
    
    CLOSE cur;
END //

CREATE PROCEDURE sp_create_invoice_from_recurring(IN p_recurring_id INT)
BEGIN
    DECLARE v_invoice_id INT;
    DECLARE v_customer_id INT;
    DECLARE v_total_amount DECIMAL(10,2);
    
    -- Get recurring invoice details
    SELECT customer_id, total_amount 
    INTO v_customer_id, v_total_amount
    FROM recurring_invoices 
    WHERE id = p_recurring_id;
    
    -- Create new invoice
    INSERT INTO invoices (
        customer_id,
        invoice_date,
        due_date,
        total_amount,
        notes,
        status
    ) SELECT 
        customer_id,
        CURRENT_DATE,
        DATE_ADD(CURRENT_DATE, INTERVAL 30 DAY),
        total_amount,
        notes,
        'pending'
    FROM recurring_invoices
    WHERE id = p_recurring_id;
    
    SET v_invoice_id = LAST_INSERT_ID();
    
    -- Copy items
    INSERT INTO invoice_items (
        invoice_id,
        product_id,
        quantity,
        price
    ) SELECT 
        v_invoice_id,
        product_id,
        quantity,
        price
    FROM recurring_items
    WHERE recurring_id = p_recurring_id;
    
END //

DELIMITER ;
";

// Add JavaScript for Recurring Invoice Management
?>

<script>
$(document).ready(function() {
    // Initialize DataTable
    const recurringTable = $('#recurringTable').DataTable({
        ajax: 'api.php?action=list_recurring',
        columns: [
            { data: 'id' },
            { data: 'customer_name' },
            { 
                data: 'frequency',
                render: function(data) {
                    return data.charAt(0).toUpperCase() + data.slice(1);
                }
            },
            { 
                data: 'total_amount',
                render: formatCurrency
            },
            { data: 'next_date' },
            {
                data: 'status',
                render: function(data) {
                    const colors = {
                        'active': 'success',
                        'paused': 'warning',
                        'completed': 'secondary'
                    };
                    return `<span class="badge bg-${colors[data]}">${data}</span>`;
                }
            },
            {
                data: 'id',
                render: function(data) {
                    return `
                        <button class="btn btn-sm btn-info" onclick="viewHistory(${data})">
                            <i class="fas fa-history"></i>
                        </button>
                        <button class="btn btn-sm btn-primary" onclick="editSettings(${data})">
                            <i class="fas fa-cog"></i>
                        </button>
                        <button class="btn btn-sm btn-danger" onclick="deleteRecurring(${data})">
                            <i class="fas fa-trash"></i>
                        </button>
                    `;
                }
            }
        ]
    });

    // Load customers for dropdown
    $.get('../customers/api.php?action=list', function(response) {
        const select = $('select[name="customer_id"]');
        response.data.forEach(customer => {
            select.append(`<option value="${customer.id}">${customer.customer_name}</option>`);
        });
    });

    // Load products for dropdown
    loadProducts($('.product-select').first());

    // Handle form submission
    $('#recurringForm').submit(function(e) {
        e.preventDefault();
        
        $.post('api.php', $(this).serialize() + '&action=create', function(response) {
            if(response.success) {
                $('#addRecurringModal').modal('hide');
                recurringTable.ajax.reload();
                toastr.success('Recurring invoice created successfully');
            } else {
                toastr.error(response.message);
            }
        });
    });

    // Handle settings form
    $('#settingsForm').submit(function(e) {
        e.preventDefault();
        
        $.post('api.php', $(this).serialize() + '&action=update_settings', function(response) {
            if(response.success) {
                $('#settingsModal').modal('hide');
                recurringTable.ajax.reload();
                toastr.success('Settings updated successfully');
            } else {
                toastr.error(response.message);
            }
        });
    });
});

function loadProducts(select) {
    $.get('../products/api.php?action=list', function(response) {
        response.data.forEach(product => {
            select.append(`
                <option value="${product.id}" data-price="${product.unit_price}">
                    ${product.product_name}
                </option>
            `);
        });
    });
}

function addItemRow() {
    const count = $('#itemsContainer .item-row').length;
    const newRow = `
        <div class="item-row mb-2">
            <div class="row">
                <div class="col-6">
                    <select class="form-select product-select" name="items[${count}][product_id]" required>
                        <option value="">Select Product</option>
                    </select>
                </div>
                <div class="col-3">
                    <input type="number" class="form-control" name="items[${count}][quantity]" 
                           placeholder="Qty" min="1" value="1" required>
                </div>
                <div class="col-3">
                    <input type="number" class="form-control" name="items[${count}][price]" 
                           placeholder="Price" readonly>
                </div>
            </div>
        </div>
    `;
    
    $('#itemsContainer').append(newRow);
    loadProducts($('#itemsContainer .item-row:last .product-select'));
}

function viewHistory(id) {
    window.location.href = `history.php?id=${id}`;
}

function editSettings(id) {
    $.get('api.php', { action: 'get_settings', id: id }, function(response) {
        if(response.success) {
            $('#settingsForm [name="status"]').val(response.data.status);
            $('#settingsForm [name="next_date"]').val(response.data.next_date);
            $('#settingsForm').append(`<input type="hidden" name="recurring_id" value="${id}">`);
            $('#settingsModal').modal('show');
        }
    });
}

function deleteRecurring(id) {
    if(confirm('Are you sure you want to delete this recurring invoice?')) {
        $.post('api.php', {
            action: 'delete',
            id: id
        }, function(response) {
            if(response.success) {
                $('#recurringTable').DataTable().ajax.reload();
                toastr.success('Recurring invoice deleted successfully');
            } else {
                toastr.error(response.message);
            }
        });
    }
}

function formatCurrency(amount) {
    return new Intl.NumberFormat('en-US', {
        style: 'currency',
        currency: 'USD'
    }).format(amount);
}
</script>

<?php require_once '../../includes/footer.php'; ?>// modules/recurring/template_api.php
<?php
require_once '../../config/config.php';
require_once '../../config/database.php';

header('Content-Type: application/json');

$action = $_POST['action'] ?? $_GET['action'] ?? '';

switch($action) {
    case 'list_templates':
        $query = "SELECT t.*,
                    COUNT(DISTINCT ri.id) as active_recurrences,
                    COUNT(ti.id) as items_count,
                    COALESCE(SUM(ti.quantity * ti.price), 0) as total_amount
                 FROM invoice_templates t
                 LEFT JOIN recurring_invoices ri ON t.id = ri.template_id AND ri.status = 'active'
                 LEFT JOIN template_items ti ON t.id = ti.template_id
                 GROUP BY t.id
                 ORDER BY t.created_at DESC";
        
        $result = $db->query($query);
        $templates = [];
        while($row = $result->fetch_assoc()) {
            $templates[] = $row;
        }
        
        echo json_encode(['data' => $templates]);
        break;

    case 'save_template':
        try {
            $db->query("START TRANSACTION");

            // Save template header
            $name = $db->escape($_POST['template_name']);
            $customerType = $db->escape($_POST['customer_type']);
            $description = $db->escape($_POST['description']);
            $paymentTerms = intval($_POST['payment_terms']);
            $taxRate = floatval($_POST['tax_rate']);
            $notes = $db->escape($_POST['notes']);

            $query = "INSERT INTO invoice_templates (
                        template_name, customer_type, description,
                        payment_terms, tax_rate, notes
                     ) VALUES (
                        '$name', '$customerType', '$description',
                        $paymentTerms, $taxRate, '$notes'
                     )";
            
            $db->query($query);
            $templateId = $db->getLastId();

            // Save template items
            foreach($_POST['items'] as $item) {
                $productId = intval($item['product_id']);
                $quantity = intval($item['quantity']);
                $price = floatval($item['price']);

                $query = "INSERT INTO template_items (
                            template_id, product_id, quantity, price
                         ) VALUES (
                            $templateId, $productId, $quantity, $price
                         )";
                
                $db->query($query);
            }

            $db->query("COMMIT");

            echo json_encode([
                'success' => true,
                'message' => 'Template saved successfully'
            ]);
        } catch (Exception $e) {
            $db->query("ROLLBACK");
            echo json_encode([
                'success' => false,
                'message' => 'Failed to save template: ' . $e->getMessage()
            ]);
        }
        break;

    case 'get_template':
        $id = intval($_GET['id']);
        
        // Get template details
        $query = "SELECT * FROM invoice_templates WHERE id = $id";
        $template = $db->query($query)->fetch_assoc();
        
        if(!$template) {
            echo json_encode([
                'success' => false,
                'message' => 'Template not found'
            ]);
            exit;
        }
        
        // Get template items
        $query = "SELECT ti.*, p.product_name 
                 FROM template_items ti
                 LEFT JOIN products p ON ti.product_id = p.id
                 WHERE ti.template_id = $id";
        
        $result = $db->query($query);
        $items = [];
        while($row = $result->fetch_assoc()) {
            $items[] = $row;
        }
        
        $template['items'] = $items;
        
        echo json_encode([
            'success' => true,
            'data' => $template
        ]);
        break;

    case 'update_template':
        try {
            $db->query("START TRANSACTION");

            $id = intval($_POST['template_id']);
            $name = $db->escape($_POST['template_name']);
            $customerType = $db->escape($_POST['customer_type']);
            $description = $db->escape($_POST['description']);
            $paymentTerms = intval($_POST['payment_terms']);
            $taxRate = floatval($_POST['tax_rate']);
            $notes = $db->escape($_POST['notes']);

            // Update template header
            $query = "UPDATE invoice_templates SET
                        template_name = '$name',
                        customer_type = '$customerType',
                        description = '$description',
                        payment_terms = $paymentTerms,
                        tax_rate = $taxRate,
                        notes = '$notes'
                     WHERE id = $id";
            
            $db->query($query);

            // Delete existing items
            $query = "DELETE FROM template_items WHERE template_id = $id";
            $db->query($query);

            // Insert new items
            foreach($_POST['items'] as $item) {
                $productId = intval($item['product_id']);
                $quantity = intval($item['quantity']);
                $price = floatval($item['price']);

                $query = "INSERT INTO template_items (
                            template_id, product_id, quantity, price
                         ) VALUES (
                            $id, $productId, $quantity, $price
                         )";
                
                $db->query($query);
            }

            $db->query("COMMIT");

            echo json_encode([
                'success' => true,
                'message' => 'Template updated successfully'
            ]);
        } catch (Exception $e) {
            $db->query("ROLLBACK");
            echo json_encode([
                'success' => false,
                'message' => 'Failed to update template: ' . $e->getMessage()
            ]);
        }
        break;

    case 'delete_template':
        try {
            $id = intval($_POST['id']);

            // Check if template is in use
            $query = "SELECT COUNT(*) as count 
                     FROM recurring_invoices 
                     WHERE template_id = $id AND status = 'active'";
            
            $result = $db->query($query)->fetch_assoc();
            if($result['count'] > 0) {
                throw new Exception('Cannot delete template: It is being used by active recurring invoices');
            }

            $db->query("START TRANSACTION");

            // Delete template items
            $query = "DELETE FROM template_items WHERE template_id = $id";
            $db->query($query);

            // Delete template
            $query = "DELETE FROM invoice_templates WHERE id = $id";
            $db->query($query);

            $db->query("COMMIT");

            echo json_encode([
                'success' => true,
                'message' => 'Template deleted successfully'
            ]);
        } catch (Exception $e) {
            $db->query("ROLLBACK");
            echo json_encode([
                'success' => false,
                'message' => $e->getMessage()
            ]);
        }
        break;

    case 'preview_template':
        $id = intval($_GET['id']);
        
        // Get template with items
        $query = "SELECT t.*, GROUP_CONCAT(
                    CONCAT(p.product_name, ':', ti.quantity, ':', ti.price)
                    SEPARATOR '|'
                 ) as items_data
                 FROM invoice_templates t
                 LEFT JOIN template_items ti ON t.id = ti.template_id
                 LEFT JOIN products p ON ti.product_id = p.id
                 WHERE t.id = $id
                 GROUP BY t.id";
        
        $template = $db->query($query)->fetch_assoc();
        
        if(!$template) {
            echo json_encode([
                'success' => false,
                'message' => 'Template not found'
            ]);
            exit;
        }
        
        // Generate preview HTML
        $html = generateTemplatePreview($template);
        
        echo json_encode([
            'success' => true,
            'html' => $html
        ]);
        break;

    case 'get_monitoring':
        $stats = [
            'active_templates' => getActiveTemplatesCount($db),
            'active_recurrences' => getActiveRecurrencesCount($db),
            'due_today' => getDueTodayCount($db),
            'failed' => getFailedGenerationsCount($db),
            'upcoming' => getUpcomingGenerations($db)
        ];
        
        echo json_encode([
            'success' => true,
            'data' => $stats
        ]);
        break;
}

function generateTemplatePreview($template) {
    $items = [];
    if($template['items_data']) {
        foreach(explode('|', $template['items_data']) as $item) {
            list($name, $qty, $price) = explode(':', $item);
            $items[] = [
                'name' => $name,
                'quantity' => $qty,
                'price' => $price,
                'total' => $qty * $price
            ];
        }
    }

    $html = '
        <div class="template-preview">
            <h4>' . htmlspecialchars($template['template_name']) . '</h4>
            <p class="text-muted">' . htmlspecialchars($template['description']) . '</p>
            
            <table class="table table-bordered mt-4">
                <thead>
                    <tr>
                        <th>Item</th>
                        <th>Quantity</th>
                        <th>Price</th>
                        <th>Total</th>
                    </tr>
                </thead>
                <tbody>';
    
    $subtotal = 0;
    foreach($items as $item) {
        $html .= '
            <tr>
                <td>' . htmlspecialchars($item['name']) . '</td>
                <td>' . $item['quantity'] . '</td>
                <td>' . formatCurrency($item['price']) . '</td>
                <td>' . formatCurrency($item['total']) . '</td>
            </tr>';
        $subtotal += $item['total'];
    }
    
    $taxAmount = $subtotal * ($template['tax_rate'] / 100);
    $total = $subtotal + $taxAmount;
    
    $html .= '
                </tbody>
                <tfoot>
                    <tr>
                        <th colspan="3" class="text-end">Subtotal:</th>
                        <td>' . formatCurrency($subtotal) . '</td>
                    </tr>
                    <tr>
                        <th colspan="3" class="text-end">Tax (' . $template['tax_rate'] . '%):</th>
                        <td>' . formatCurrency($taxAmount) . '</td>
                    </tr>
                    <tr>
                        <th colspan="3" class="text-end">Total:</th>
                        <td>' . formatCurrency($total) . '</td>
                    </tr>
                </tfoot>
            </table>
            
            <div class="mt-4">
                <strong>Payment Terms:</strong> ' . $template['payment_terms'] . ' days<br>
                <strong>Notes:</strong><br>
                ' . nl2br(htmlspecialchars($template['notes'])) . '
            </div>
        </div>';
    
    return $html;
}

// Helper functions for monitoring
function getActiveTemplatesCount($db) {
    $query = "SELECT COUNT(*) as count FROM invoice_templates";
    return $db->query($query)->fetch_assoc()['count'];
}

function getActiveRecurrencesCount($db) {
    $query = "SELECT COUNT(*) as count 
              FROM recurring_invoices 
              WHERE status = 'active'";
    return $db->query($query)->fetch_assoc()['count'];
}

function getDueTodayCount($db) {
    $query = "SELECT COUNT(*) as count 
              FROM recurring_invoices 
              WHERE status = 'active' 
              AND next_date = CURRENT_DATE";
    return $db->query($query)->fetch_assoc()['count'];
}

function getFailedGenerationsCount($db) {
    $query = "SELECT COUNT(*) as count 
              FROM recurring_invoices 
              WHERE status = 'active' 
              AND next_date < CURRENT_DATE";
    return $db->query($query)->fetch_assoc()['count'];
}

function getUpcomingGenerations($db) {
    $query = "SELECT r.*, c.customer_name
              FROM recurring_invoices r
              LEFT JOIN customers c ON r.customer_id = c.id
              WHERE r.status = 'active'
              AND r.next_date BETWEEN CURRENT_DATE 
              AND DATE_ADD(CURRENT_DATE, INTERVAL 7 DAY)
              ORDER BY r.next_date
              LIMIT 5";
    
    $result = $db->query($query);
    $upcoming = [];
    while($row = $result->fetch_assoc()) {
        $upcoming[] = $row;
    }
    return $upcoming;
}
?><?php
require_once '../../config/config.php';
require_once '../../config/database.php';
require_once '../../includes/header.php';
?>

<div class="row">
    <!-- Template List -->
    <div class="col-md-8">
        <div class="card">
            <div class="card-header d-flex justify-content-between align-items-center">
                <h4>Invoice Templates</h4>
                <button type="button" class="btn btn-primary" data-bs-toggle="modal" data-bs-target="#addTemplateModal">
                    <i class="fas fa-plus"></i> Create Template
                </button>
            </div>
            <div class="card-body">
                <table id="templatesTable" class="table table-striped">
                    <thead>
                        <tr>
                            <th>Name</th>
                            <th>Customer Type</th>
                            <th>Items</th>
                            <th>Total</th>
                            <th>Active Recurrences</th>
                            <th>Actions</th>
                        </tr>
                    </thead>
                </table>
            </div>
        </div>
    </div>

    <!-- Monitoring Dashboard -->
    <div class="col-md-4">
        <div class="card mb-4">
            <div class="card-header">
                <h4>Recurring Overview</h4>
            </div>
            <div class="card-body">
                <div class="d-flex justify-content-between mb-3">
                    <div>
                        <h6>Active Templates</h6>
                        <h3 id="activeTemplates">0</h3>
                    </div>
                    <div>
                        <h6>Active Recurrences</h6>
                        <h3 id="activeRecurrences">0</h3>
                    </div>
                </div>
                <div class="d-flex justify-content-between">
                    <div>
                        <h6>Due Today</h6>
                        <h3 id="dueToday">0</h3>
                    </div>
                    <div>
                        <h6>Failed</h6>
                        <h3 id="failedGeneration">0</h3>
                    </div>
                </div>
            </div>
        </div>

        <!-- Upcoming Generations -->
        <div class="card">
            <div class="card-header">
                <h4>Upcoming Generations</h4>
            </div>
            <div class="card-body">
                <div id="upcomingList">
                    <!-- Will be populated via AJAX -->
                </div>
            </div>
        </div>
    </div>
</div>

<!-- Add Template Modal -->
<div class="modal fade" id="addTemplateModal" tabindex="-1">
    <div class="modal-dialog modal-lg">
        <div class="modal-content">
            <div class="modal-header">
                <h5 class="modal-title">Create Invoice Template</h5>
                <button type="button" class="btn-close" data-bs-dismiss="modal"></button>
            </div>
            <form id="templateForm">
                <div class="modal-body">
                    <div class="row">
                        <div class="col-md-6">
                            <div class="mb-3">
                                <label class="form-label">Template Name</label>
                                <input type="text" class="form-control" name="template_name" required>
                            </div>
                            <div class="mb-3">
                                <label class="form-label">Customer Type</label>
                                <select class="form-select" name="customer_type" required>
                                    <option value="all">All Customers</option>
                                    <option value="corporate">Corporate</option>
                                    <option value="individual">Individual</option>
                                </select>
                            </div>
                            <div class="mb-3">
                                <label class="form-label">Description</label>
                                <textarea class="form-control" name="description" rows="3"></textarea>
                            </div>
                        </div>
                        <div class="col-md-6">
                            <div class="mb-3">
                                <label class="form-label">Payment Terms (Days)</label>
                                <input type="number" class="form-control" name="payment_terms" value="30">
                            </div>
                            <div class="mb-3">
                                <label class="form-label">Tax Rate (%)</label>
                                <input type="number" class="form-control" name="tax_rate" value="0" step="0.01">
                            </div>
                            <div class="mb-3">
                                <label class="form-label">Notes</label>
                                <textarea class="form-control" name="notes" rows="3"></textarea>
                            </div>
                        </div>
                    </div>

                    <!-- Template Items -->
                    <h5 class="mt-3">Template Items</h5>
                    <div id="templateItems">
                        <div class="item-row mb-2">
                            <div class="row">
                                <div class="col-md-5">
                                    <select class="form-select product-select" name="items[0][product_id]" required>
                                        <option value="">Select Product</option>
                                    </select>
                                </div>
                                <div class="col-md-2">
                                    <input type="number" class="form-control" name="items[0][quantity]" 
                                           placeholder="Qty" min="1" value="1" required>
                                </div>
                                <div class="col-md-2">
                                    <input type="number" class="form-control" name="items[0][price]" 
                                           placeholder="Price" step="0.01" required>
                                </div>
                                <div class="col-md-2">
                                    <input type="text" class="form-control item-total" readonly>
                                </div>
                                <div class="col-md-1">
                                    <button type="button" class="btn btn-danger btn-sm remove-item">
                                        <i class="fas fa-times"></i>
                                    </button>
                                </div>
                            </div>
                        </div>
                    </div>
                    <button type="button" class="btn btn-secondary btn-sm mt-2" onclick="addTemplateItem()">
                        Add Item
                    </button>

                    <!-- Totals -->
                    <div class="row mt-4">
                        <div class="col-md-6 offset-md-6">
                            <table class="table table-sm">
                                <tr>
                                    <th>Subtotal:</th>
                                    <td class="text-end">
                                        <span id="templateSubtotal">$0.00</span>
                                    </td>
                                </tr>
                                <tr>
                                    <th>Tax:</th>
                                    <td class="text-end">
                                        <span id="templateTax">$0.00</span>
                                    </td>
                                </tr>
                                <tr>
                                    <th>Total:</th>
                                    <td class="text-end">
                                        <span id="templateTotal">$0.00</span>
                                    </td>
                                </tr>
                            </table>
                        </div>
                    </div>
                </div>
                <div class="modal-footer">
                    <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Close</button>
                    <button type="submit" class="btn btn-primary">Save Template</button>
                </div>
            </form>
        </div>
    </div>
</div>

<!-- Template Preview Modal -->
<div class="modal fade" id="previewModal" tabindex="-1">
    <div class="modal-dialog modal-lg">
        <div class="modal-content">
            <div class="modal-header">
                <h5 class="modal-title">Template Preview</h5>
                <button type="button" class="btn-close" data-bs-dismiss="modal"></button>
            </div>
            <div class="modal-body">
                <div id="previewContent">
                    <!-- Will be populated via AJAX -->
                </div>
            </div>
        </div>
    </div>
</div>

<script>
$(document).ready(function() {
    // Initialize DataTable
    const templatesTable = $('#templatesTable').DataTable({
        ajax: 'api.php?action=list_templates',
        columns: [
            { data: 'template_name' },
            { data: 'customer_type' },
            { 
                data: 'items_count',
                render: function(data) {
                    return `${data} items`;
                }
            },
            {
                data: 'total_amount',
                render: formatCurrency
            },
            { data: 'active_recurrences' },
            {
                data: 'id',
                render: function(data) {
                    return `
                        <button class="btn btn-sm btn-info" onclick="previewTemplate(${data})">
                            <i class="fas fa-eye"></i>
                        </button>
                        <button class="btn btn-sm btn-primary" onclick="editTemplate(${data})">
                            <i class="fas fa-edit"></i>
                        </button>
                        <button class="btn btn-sm btn-danger" onclick="deleteTemplate(${data})">
                            <i class="fas fa-trash"></i>
                        </button>
                    `;
                }
            }
        ]
    });

    // Load Products
    loadProducts();
    
    // Initialize Monitoring
    updateMonitoring();
    setInterval(updateMonitoring, 60000); // Update every minute

    // Handle Template Form
    $('#templateForm').submit(function(e) {
        e.preventDefault();
        
        $.post('api.php', $(this).serialize() + '&action=save_template', function(response) {
            if(response.success) {
                $('#addTemplateModal').modal('hide');
                templatesTable.ajax.reload();
                toastr.success('Template saved successfully');
            } else {
                toastr.error(response.message);
            }
        });
    });

    // Calculate totals on item changes
    $(document).on('change', '.item-row input', calculateTotals);
});

function loadProducts() {
    $.get('../products/api.php?action=list', function(response) {
        const products = response.data;
        $('.product-select').each(function() {
            $(this).empty().append('<option value="">Select Product</option>');
            products.forEach(product => {
                $(this).append(`
                    <option value="${product.id}" data-price="${product.unit_price}">
                        ${product.product_name}
                    </option>
                `);
            });
        });
    });
}

function addTemplateItem() {
    const count = $('#templateItems .item-row').length;
    const newRow = `
        <div class="item-row mb-2">
            <div class="row">
                <div class="col-md-5">
                    <select class="form-select product-select" name="items[${count}][product_id]" required>
                        <option value="">Select Product</option>
                    </select>
                </div>
                <div class="col-md-2">
                    <input type="number" class="form-control" name="items[${count}][quantity]" 
                           placeholder="Qty" min="1" value="1" required>
                </div>
                <div class="col-md-2">
                    <input type="number" class="form-control" name="items[${count}][price]" 
                           placeholder="Price" step="0.01" required>
                </div>
                <div class="col-md-2">
                    <input type="text" class="form-control item-total" readonly>
                </div>
                <div class="col-md-1">
                    <button type="button" class="btn btn-danger btn-sm remove-item">
                        <i class="fas fa-times"></i>
                    </button>
                </div>
            </div>
        </div>
    `;
    
    $('#templateItems').append(newRow);
    loadProducts();
}

function calculateTotals() {
    let subtotal = 0;
    
    $('.item-row').each(function() {
        const quantity = parseFloat($(this).find('input[name*="quantity"]').val()) || 0;
        const price = parseFloat($(this).find('input[name*="price"]').val()) || 0;
        const total = quantity * price;
        
        $(this).find('.item-total').val(formatCurrency(total));
        subtotal += total;
    });
    
    const taxRate = parseFloat($('input[name="tax_rate"]').val()) || 0;
    const taxAmount = subtotal * (taxRate / 100);
    const total = subtotal + taxAmount;
    
    $('#templateSubtotal').text(formatCurrency(subtotal));
    $('#templateTax').text(formatCurrency(taxAmount));
    $('#templateTotal').text(formatCurrency(total));
}

function updateMonitoring() {
    $.get('api.php?action=get_monitoring', function(response) {
        if(response.success) {
            $('#activeTemplates').text(response.data.active_templates);
            $('#activeRecurrences').text(response.data.active_recurrences);
            $('#dueToday').text(response.data.due_today);
            $('#failedGeneration').text(response.data.failed);
            
            // Update upcoming list
            let upcomingHtml = '';
            response.data.upcoming.forEach(item => {
                upcomingHtml += `
                    <div class="upcoming-item mb-2">
                        <div class="d-flex justify-content-between">
                            <div>
                                <strong>${item.customer_name}</strong><br>
                                <small>${formatCurrency(item.total_amount)}</small>
                            </div>
                            <div class="text-end">
                                <span class="badge bg-info">${item.next_date}</span><br>
                                <small>${item.frequency}</small>
                            </div>
                        </div>
                    </div>
                `;
            });
            $('#upcomingList').html(upcomingHtml);
        }
    });
}

function previewTemplate(id) {
    $.get('api.php', { action: 'preview_template', id: id }, function(response) {
        if(response.success) {
            $('#previewContent').html(response.html);
            $('#previewModal').modal('show');
        }
    });
}

function editTemplate(id) {
    window.location.href = `edit_template.php?id=${id}`;
}

function deleteTemplate(id) {
    if(confirm('Are you sure you want to delete this template?')) {
        $.post('api.php', {
            action: 'delete_template',
            id: id
        }, function(response) {
            if(response.success) {
                $('#templatesTable').DataTable().ajax.reload();
                toastr.success('Template deleted successfully');
            } else {
                toastr.error(response.message);
            }
        });
    }
}

function formatCurrency(amount) {
    return new Intl.NumberFormat('en-US', {
        style: 'currency',
        currency: 'USD'
    }).format(amount);
}
</script>

<?php require_once '../../includes/footer.php'; ?><?php
require_once '../../config/config.php';
require_once '../../config/database.php';
require_once '../../includes/header.php';
?>

<div class="row">
    <!-- Financial Overview Card -->
    <div class="col-md-12 mb-4">
        <div class="card">
            <div class="card-header">
                <h4>Financial Overview</h4>
            </div>
            <div class="card-body">
                <div class="row">
                    <div class="col-md-3">
                        <div class="card bg-primary text-white">
                            <div class="card-body">
                                <h6>Total Revenue (YTD)</h6>
                                <h3 id="totalRevenue">Loading...</h3>
                            </div>
                        </div>
                    </div>
                    <div class="col-md-3">
                        <div class="card bg-success text-white">
                            <div class="card-body">
                                <h6>Outstanding Amount</h6>
                                <h3 id="outstandingAmount">Loading...</h3>
                            </div>
                        </div>
                    </div>
                    <div class="col-md-3">
                        <div class="card bg-info text-white">
                            <div class="card-body">
                                <h6>Average Invoice Value</h6>
                                <h3 id="avgInvoiceValue">Loading...</h3>
                            </div>
                        </div>
                    </div>
                    <div class="col-md-3">
                        <div class="card bg-warning text-white">
                            <div class="card-body">
                                <h6>Collection Rate</h6>
                                <h3 id="collectionRate">Loading...</h3>
                            </div>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </div>

    <!-- Revenue Trends Chart -->
    <div class="col-md-8 mb-4">
        <div class="card">
            <div class="card-header">
                <h4>Revenue Trends</h4>
            </div>
            <div class="card-body">
                <canvas id="revenueTrendsChart"></canvas>
            </div>
        </div>
    </div>

    <!-- Payment Distribution -->
    <div class="col-md-4 mb-4">
        <div class="card">
            <div class="card-header">
                <h4>Payment Methods Distribution</h4>
            </div>
            <div class="card-body">
                <canvas id="paymentMethodsChart"></canvas>
            </div>
        </div>
    </div>

    <!-- Top Customers -->
    <div class="col-md-6 mb-4">
        <div class="card">
            <div class="card-header">
                <h4>Top Customers</h4>
            </div>
            <div class="card-body">
                <div class="table-responsive">
                    <table class="table table-striped" id="topCustomersTable">
                        <thead>
                            <tr>
                                <th>Customer</th>
                                <th>Total Revenue</th>
                                <th>Invoices</th>
                                <th>Average Value</th>
                            </tr>
                        </thead>
                    </table>
                </div>
            </div>
        </div>
    </div>

    <!-- Product Performance -->
    <div class="col-md-6 mb-4">
        <div class="card">
            <div class="card-header">
                <h4>Product Performance</h4>
            </div>
            <div class="card-body">
                <div class="table-responsive">
                    <table class="table table-striped" id="productPerformanceTable">
                        <thead>
                            <tr>
                                <th>Product</th>
                                <th>Units Sold</th>
                                <th>Revenue</th>
                                <th>Trend</th>
                            </tr>
                        </thead>
                    </table>
                </div>
            </div>
        </div>
    </div>
</div>

<!-- Advanced Filters Modal -->
<div class="modal fade" id="filtersModal">
    <div class="modal-dialog">
        <div class="modal-content">
            <div class="modal-header">
                <h5 class="modal-title">Advanced Filters</h5>
                <button type="button" class="btn-close" data-bs-dismiss="modal"></button>
            </div>
            <form id="filtersForm">
                <div class="modal-body">
                    <div class="mb-3">
                        <label class="form-label">Date Range</label>
                        <select class="form-select" name="date_range">
                            <option value="7">Last 7 Days</option>
                            <option value="30">Last 30 Days</option>
                            <option value="90">Last 90 Days</option>
                            <option value="365">Last Year</option>
                            <option value="custom">Custom Range</option>
                        </select>
                    </div>
                    <div class="mb-3 custom-date-range" style="display:none;">
                        <div class="row">
                            <div class="col-md-6">
                                <label class="form-label">Start Date</label>
                                <input type="date" class="form-control" name="start_date">
                            </div>
                            <div class="col-md-6">
                                <label class="form-label">End Date</label>
                                <input type="date" class="form-control" name="end_date">
                            </div>
                        </div>
                    </div>
                    <div class="mb-3">
                        <label class="form-label">Group By</label>
                        <select class="form-select" name="group_by">
                            <option value="day">Day</option>
                            <option value="week">Week</option>
                            <option value="month">Month</option>
                            <option value="quarter">Quarter</option>
                        </select>
                    </div>
                </div>
                <div class="modal-footer">
                    <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Close</button>
                    <button type="submit" class="btn btn-primary">Apply Filters</button>
                </div>
            </form>
        </div>
    </div>
</div>

<?php
// Add SQL Views and Procedures for Analytics

$sql = "
CREATE VIEW vw_revenue_analysis AS
SELECT 
    DATE_FORMAT(i.invoice_date, '%Y-%m') as month,
    COUNT(i.id) as invoice_count,
    SUM(i.total_amount) as total_revenue,
    AVG(i.total_amount) as avg_invoice_value,
    SUM(CASE WHEN i.status = 'paid' THEN i.total_amount ELSE 0 END) as collected_amount,
    SUM(CASE WHEN i.status != 'paid' THEN i.total_amount ELSE 0 END) as outstanding_amount,
    (SUM(CASE WHEN i.status = 'paid' THEN i.total_amount ELSE 0 END) / 
     SUM(i.total_amount) * 100) as collection_rate
FROM invoices i
GROUP BY DATE_FORMAT(i.invoice_date, '%Y-%m');

CREATE VIEW vw_customer_performance AS
SELECT 
    c.id,
    c.customer_name,
    COUNT(i.id) as invoice_count,
    SUM(i.total_amount) as total_revenue,
    AVG(i.total_amount) as avg_invoice_value,
    MAX(i.invoice_date) as last_invoice_date,
    DATEDIFF(NOW(), MAX(i.invoice_date)) as days_since_last_invoice
FROM customers c
LEFT JOIN invoices i ON c.id = i.customer_id
GROUP BY c.id, c.customer_name;

CREATE VIEW vw_product_performance AS
SELECT 
    p.id,
    p.product_name,
    SUM(ii.quantity) as units_sold,
    SUM(ii.total_price) as total_revenue,
    AVG(ii.unit_price) as avg_unit_price,
    COUNT(DISTINCT i.id) as invoice_count
FROM products p
LEFT JOIN invoice_items ii ON p.id = ii.product_id
LEFT JOIN invoices i ON ii.invoice_id = i.id
GROUP BY p.id, p.product_name;

DELIMITER //

CREATE PROCEDURE sp_get_revenue_trends(
    IN p_start_date DATE,
    IN p_end_date DATE,
    IN p_group_by VARCHAR(10)
)
BEGIN
    CASE p_group_by
        WHEN 'day' THEN
            SELECT 
                DATE(invoice_date) as date,
                SUM(total_amount) as revenue
            FROM invoices
            WHERE invoice_date BETWEEN p_start_date AND p_end_date
            GROUP BY DATE(invoice_date);
            
        WHEN 'week' THEN
            SELECT 
                DATE_FORMAT(invoice_date, '%Y-%u') as week,
                SUM(total_amount) as revenue
            FROM invoices
            WHERE invoice_date BETWEEN p_start_date AND p_end_date
            GROUP BY DATE_FORMAT(invoice_date, '%Y-%u');
            
        WHEN 'month' THEN
            SELECT 
                DATE_FORMAT(invoice_date, '%Y-%m') as month,
                SUM(total_amount) as revenue
            FROM invoices
            WHERE invoice_date BETWEEN p_start_date AND p_end_date
            GROUP BY DATE_FORMAT(invoice_date, '%Y-%m');
            
        WHEN 'quarter' THEN
            SELECT 
                CONCAT(YEAR(invoice_date), '-Q', QUARTER(invoice_date)) as quarter,
                SUM(total_amount) as revenue
            FROM invoices
            WHERE invoice_date BETWEEN p_start_date AND p_end_date
            GROUP BY YEAR(invoice_date), QUARTER(invoice_date);
    END CASE;
END //

DELIMITER ;
";

// Add JavaScript for charts and analytics
?>

<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>

<script>
$(document).ready(function() {
    // Load initial data
    loadDashboardData();
    
    // Initialize DataTables
    initializeTables();
    
    // Setup event handlers
    setupEventHandlers();
});

function loadDashboardData() {
    $.get('api.php?action=get_dashboard_stats', function(response) {
        if(response.success) {
            updateDashboardStats(response.data);
            initializeCharts(response.data);
        }
    });
}

function updateDashboardStats(data) {
    $('#totalRevenue').text(formatCurrency(data.total_revenue));
    $('#outstandingAmount').text(formatCurrency(data.outstanding_amount));
    $('#avgInvoiceValue').text(formatCurrency(data.avg_invoice_value));
    $('#collectionRate').text(data.collection_rate + '%');
}

function initializeCharts(data) {
    // Revenue Trends Chart
    new Chart(document.getElementById('revenueTrendsChart'), {
        type: 'line',
        data: {
            labels: data.revenue_trends.labels,
            datasets: [{
                label: 'Revenue',
                data: data.revenue_trends.data,
                borderColor: 'rgb(75, 192, 192)',
                tension: 0.1
            }]
        },
        options: {
            responsive: true,
            maintainAspectRatio: false
        }
    });

    // Payment Methods Chart
    new Chart(document.getElementById('paymentMethodsChart'), {
        type: 'doughnut',
        data: {
            labels: data.payment_methods.labels,
            datasets: [{
                data: data.payment_methods.data,
                backgroundColor: [
                    'rgb(255, 99, 132)',
                    'rgb(54, 162, 235)',
                    'rgb(255, 206, 86)',
                    'rgb(75, 192, 192)'
                ]
            }]
        },
        options: {
            responsive: true,
            maintainAspectRatio: false
        }
    });
}

function initializeTables() {
    // Top Customers Table
    $('#topCustomersTable').DataTable({
        ajax: 'api.php?action=get_top_customers',
        columns: [
            { data: 'customer_name' },
            { 
                data: 'total_revenue',
                render: function(data) {
                    return formatCurrency(data);
                }
            },
            { data: 'invoice_count' },
            {
                data: 'avg_invoice_value',
                render: function(data) {
                    return formatCurrency(data);
                }
            }
        ]
    });

    // Product Performance Table
    $('#productPerformanceTable').DataTable({
        ajax: 'api.php?action=get_product_performance',
        columns: [
            { data: 'product_name' },
            { data: 'units_sold' },
            {
                data: 'total_revenue',
                render: function(data) {
                    return formatCurrency(data);
                }
            },
            {
                data: 'trend',
                render: function(data) {
                    return `<span class="badge bg-${data > 0 ? 'success' : 'danger'}">
                        ${data}%
                    </span>`;
                }
            }
        ]
    });
}

function setupEventHandlers() {
    // Filters form submission
    $('#filtersForm').submit(function(e) {
        e.preventDefault();
        loadDashboardData($(this).serialize());
    });

    // Date range selector
    $('select[name="date_range"]').change(function() {
        if($(this).val() === 'custom') {
            $('.custom-date-range').show();
        } else {
            $('.custom-date-range').hide();
        }
    });
}

function formatCurrency(amount) {
    return new Intl.NumberFormat('en-US', {
        style: 'currency',
        currency: 'USD'
    }).format(amount);
}
</script>

<?php require_once '../../includes/footer.php'; ?><?php
require_once '../../config/config.php';
require_once '../../config/database.php';

header('Content-Type: application/json');

$action = $_POST['action'] ?? $_GET['action'] ?? '';

switch ($action) {
    case 'sales_report':
        $startDate = $db->escape($_POST['start_date']);
        $endDate = $db->escape($_POST['end_date']);
        $status = $db->escape($_POST['status']);

        // Call the stored procedure for sales report
        $query = "CALL sp_sales_report('$startDate', '$endDate', '$status')";
        $result = $db->query($query);

        if (!$result) {
            echo json_encode(['success' => false, 'message' => 'Error fetching sales report: ' . $db->error]);
            break;
        }

        $data = [];
        while ($row = $result->fetch_assoc()) {
            $data[] = $row;
        }

        echo json_encode(['success' => true, 'data' => $data]);
        break;

    case 'customer_report':
        $customerId = intval($_POST['customer_id'] ?? 0);
        $sortBy = $db->escape($_POST['sort_by']);

        // Call the stored procedure for customer report
        $query = "CALL sp_customer_report($customerId, '$sortBy')";
        $result = $db->query($query);

        if (!$result) {
            echo json_encode(['success' => false, 'message' => 'Error fetching customer report: ' . $db->error]);
            break;
        }

        $data = [];
        while ($row = $result->fetch_assoc()) {
            $data[] = $row;
        }

        echo json_encode(['success' => true, 'data' => $data]);
        break;

    case 'product_report':
        $productId = intval($_POST['product_id'] ?? 0);
        $sortBy = $db->escape($_POST['sort_by']);

        // Call the stored procedure for product report
        $query = "CALL sp_product_report($productId, '$sortBy')";
        $result = $db->query($query);

        if (!$result) {
            echo json_encode(['success' => false, 'message' => 'Error fetching product report: ' . $db->error]);
            break;
        }

        $data = [];
        while ($row = $result->fetch_assoc()) {
            $data[] = $row;
        }

        echo json_encode(['success' => true, 'data' => $data]);
        break;

    default:
        echo json_encode(['success' => false, 'message' => 'Invalid action']);
        break;
}
?><?php
require_once '../../config/config.php';
require_once '../../config/database.php';
require_once '../../includes/header.php';
?>

<div class="row">
    <div class="col-md-12">
        <div class="card">
            <div class="card-header">
                <h4>Reports</h4>
            </div>
            <div class="card-body">
                <!-- Report Types -->
                <ul class="nav nav-tabs" id="reportTabs">
                    <li class="nav-item">
                        <a class="nav-link active" data-bs-toggle="tab" href="#salesReport">Sales Report</a>
                    </li>
                    <li class="nav-item">
                        <a class="nav-link" data-bs-toggle="tab" href="#customerReport">Customer Report</a>
                    </li>
                    <li class="nav-item">
                        <a class="nav-link" data-bs-toggle="tab" href="#productReport">Product Report</a>
                    </li>
                </ul>

                <!-- Report Content -->
                <div class="tab-content mt-3">
                    <!-- Sales Report -->
                    <div class="tab-pane fade show active" id="salesReport">
                        <form id="salesReportForm" class="mb-3">
                            <div class="row">
                                <div class="col-md-3">
                                    <div class="mb-3">
                                        <label class="form-label">Start Date</label>
                                        <input type="date" class="form-control" name="start_date" required>
                                    </div>
                                </div>
                                <div class="col-md-3">
                                    <div class="mb-3">
                                        <label class="form-label">End Date</label>
                                        <input type="date" class="form-control" name="end_date" required>
                                    </div>
                                </div>
                                <div class="col-md-3">
                                    <div class="mb-3">
                                        <label class="form-label">Status</label>
                                        <select class="form-select" name="status">
                                            <option value="">All</option>
                                            <option value="paid">Paid</option>
                                            <option value="pending">Pending</option>
                                            <option value="cancelled">Cancelled</option>
                                        </select>
                                    </div>
                                </div>
                                <div class="col-md-3">
                                    <div class="mb-3">
                                        <label class="form-label">&nbsp;</label>
                                        <button type="submit" class="btn btn-primary d-block">
                                            Generate Report
                                        </button>
                                    </div>
                                </div>
                            </div>
                        </form>
                        <div id="salesReportData"></div>
                    </div>

                    <!-- Customer Report -->
                    <div class="tab-pane fade" id="customerReport">
                        <form id="customerReportForm" class="mb-3">
                            <div class="row">
                                <div class="col-md-4">
                                    <div class="mb-3">
                                        <label class="form-label">Customer</label>
                                        <select class="form-select" name="customer_id">
                                            <option value="">All Customers</option>
                                        </select>
                                    </div>
                                </div>
                                <div class="col-md-4">
                                    <div class="mb-3">
                                        <label class="form-label">Sort By</label>
                                        <select class="form-select" name="sort_by">
                                            <option value="total_sales">Total Sales</option>
                                            <option value="invoice_count">Invoice Count</option>
                                            <option value="last_invoice">Last Invoice</option>
                                        </select>
                                    </div>
                                </div>
                                <div class="col-md-4">
                                    <div class="mb-3">
                                        <label class="form-label">&nbsp;</label>
                                        <button type="submit" class="btn btn-primary d-block">
                                            Generate Report
                                        </button>
                                    </div>
                                </div>
                            </div>
                        </form>
                        <div id="customerReportData"></div>
                    </div>

                    <!-- Product Report -->
                    <div class="tab-pane fade" id="productReport">
                        <form id="productReportForm" class="mb-3">
                            <div class="row">
                                <div class="col-md-4">
                                    <div class="mb-3">
                                        <label class="form-label">Product</label>
                                        <select class="form-select" name="product_id">
                                            <option value="">All Products</option>
                                        </select>
                                    </div>
                                </div>
                                <div class="col-md-4">
                                    <div class="mb-3">
                                        <label class="form-label">Sort By</label>
                                        <select class="form-select" name="sort_by">
                                            <option value="quantity">Quantity Sold</option>
                                            <option value="revenue">Revenue</option>
                                        </select>
                                    </div>
                                </div>
                                <div class="col-md-4">
                                    <div class="mb-3">
                                        <label class="form-label">&nbsp;</label>
                                        <button type="submit" class="btn btn-primary d-block">
                                            Generate Report
                                        </button>
                                    </div>
                                </div>
                            </div>
                        </form>
                        <div id="productReportData"></div>
                    </div>
                </div>
            </div>
        </div>
    </div>
</div>

<!-- Report Generation API -->
<?php
// Create stored procedures for reports

$sql = "
DELIMITER //

CREATE PROCEDURE sp_sales_report(
    IN p_start_date DATE,
    IN p_end_date DATE,
    IN p_status VARCHAR(20)
)
BEGIN
    SELECT 
        i.invoice_number,
        i.invoice_date,
        c.customer_name,
        i.total_amount,
        i.status,
        COUNT(ii.id) as items_count
    FROM invoices i
    LEFT JOIN customers c ON i.customer_id = c.id
    LEFT JOIN invoice_items ii ON i.id = ii.invoice_id
    WHERE i.invoice_date BETWEEN p_start_date AND p_end_date
    AND (p_status = '' OR i.status = p_status)
    GROUP BY i.id
    ORDER BY i.invoice_date DESC;
END //

CREATE PROCEDURE sp_customer_report(
    IN p_customer_id INT,
    IN p_sort_by VARCHAR(20)
)
BEGIN
    SELECT 
        c.customer_name,
        COUNT(i.id) as invoice_count,
        SUM(i.total_amount) as total_sales,
        MAX(i.invoice_date) as last_invoice_date,
        AVG(i.total_amount) as average_sale
    FROM customers c
    LEFT JOIN invoices i ON c.id = i.customer_id
    WHERE (p_customer_id = 0 OR c.id = p_customer_id)
    GROUP BY c.id
    ORDER BY
        CASE 
            WHEN p_sort_by = 'total_sales' THEN total_sales
            WHEN p_sort_by = 'invoice_count' THEN invoice_count
            WHEN p_sort_by = 'last_invoice' THEN last_invoice_date
        END DESC;
END //

CREATE PROCEDURE sp_product_report(
    IN p_product_id INT,
    IN p_sort_by VARCHAR(20)
)
BEGIN
    SELECT 
        p.product_name,
        SUM(ii.quantity) as quantity_sold,
        SUM(ii.total_price) as total_revenue,
        AVG(ii.unit_price) as average_price,
        COUNT(DISTINCT i.id) as invoices_count
    FROM products p
    LEFT JOIN invoice_items ii ON p.id = ii.product_id
    LEFT JOIN invoices i ON ii.invoice_id = i.id
    WHERE (p_product_id = 0 OR p.id = p_product_id)
    GROUP BY p.id
    ORDER BY
        CASE 
            WHEN p_sort_by = 'quantity' THEN quantity_sold
            WHEN p_sort_by = 'revenue' THEN total_revenue
        END DESC;
END //

DELIMITER ;
";

// Create the API endpoint for reports
?>

```php
// modules/reports/api.php
<?php
require_once '../../config/config.php';
require_once '../../config/database.php';

header('Content-Type: application/json');

$action = $_POST['action'] ?? $_GET['action'] ?? '';

switch($action) {
    case 'sales_report':
        $startDate = $db->escape($_POST['start_date']);
        $endDate = $db->escape($_POST['end_date']);
        $status = $db->escape($_POST['status']);
        
        $query = "CALL sp_sales_report('$startDate', '$endDate', '$status')";
        $result = $db->query($query);
        
        $data = [];
        while($row = $result->fetch_assoc()) {
            $data[] = $row;
        }
        
        echo json_encode(['success' => true, 'data' => $data]);
        break;
        
    case 'customer_report':
        $customerId = intval($_POST['customer_id']);
        $sortBy = $db->escape($_POST['sort_by']);
        
        $query = "CALL sp_customer_report($customerId, '$sortBy')";
        $result = $db->query($query);
        
        $data = [];
        while($row = $result->fetch_assoc()) {
            $data[] = $row;
        }
        
        echo json_encode(['success' => true, 'data' => $data]);
        break;
        
    case 'product_report':
        $productId = intval($_POST['product_id']);
        $sortBy = $db->escape($_POST['sort_by']);
        
        $query = "CALL sp_product_report($productId, '$sortBy')";
        $result = $db->query($query);
        
        $data = [];
        while($row = $result->fetch_assoc()) {
            $data[] = $row;
        }
        
        echo json_encode(['success' => true, 'data' => $data]);
        break;
}
?><?php
require_once '../../config/config.php';
require_once '../../config/database.php';

header('Content-Type: application/json');

$action = $_POST['action'] ?? $_GET['action'] ?? '';

switch($action) {
    case 'update_general':
        $settings = [
            'COMPANY_NAME' => $_POST['company_name'],
            'COMPANY_ADDRESS' => $_POST['company_address'],
            'COMPANY_PHONE' => $_POST['company_phone'],
            'COMPANY_EMAIL' => $_POST['company_email'],
            'CURRENCY_SYMBOL' => $_POST['currency_symbol'],
            'DATE_FORMAT' => $_POST['date_format'],
            'DEFAULT_TAX_RATE' => $_POST['default_tax_rate']
        ];
        
        if(updateSettings($settings)) {
            echo json_encode(['success' => true, 'message' => 'General settings updated']);
        } else {
            echo json_encode(['success' => false, 'message' => 'Failed to update settings']);
        }
        break;
        
    case 'backup_db':
        $backup = new DatabaseBackup();
        $filename = $backup->create();
        
        if($filename) {
            echo json_encode(['success' => true, 'filename' => $filename]);
        } else {
            echo json_encode(['success' => false, 'message' => 'Backup failed']);
        }
        break;
        
    case 'restore_db':
        if(!isset($_FILES['backup_file'])) {
            echo json_encode(['success' => false, 'message' => 'No file uploaded']);
            exit;
        }
        
        $restore = new DatabaseBackup();
        if($restore->restore($_FILES['backup_file']['tmp_name'])) {
            echo json_encode(['success' => true, 'message' => 'Database restored successfully']);
        } else {
            echo json_encode(['success' => false, 'message' => 'Restore failed']);
        }
        break;
}

function updateSettings($settings) {
    $configFile = '../../config/settings.php';
    $content = "<?php\n";
    
    foreach($settings as $key => $value) {
        $content .= "define('$key', '" . addslashes($value) . "');\n";
    }
    
    return file_put_contents($configFile, $content);
}<?php
require_once '../../config/config.php';
require_once '../../config/database.php';
require_once '../../includes/header.php';
?>

<div class="row">
    <div class="col-md-12">
        <div class="card">
            <div class="card-header">
                <h4>System Settings</h4>
            </div>
            <div class="card-body">
                <ul class="nav nav-tabs" id="settingsTabs">
                    <li class="nav-item">
                        <a class="nav-link active" data-bs-toggle="tab" href="#generalSettings">
                            General Settings
                        </a>
                    </li>
                    <li class="nav-item">
                        <a class="nav-link" data-bs-toggle="tab" href="#invoiceSettings">
                            Invoice Settings
                        </a>
                    </li>
                    <li class="nav-item">
                        <a class="nav-link" data-bs-toggle="tab" href="#emailSettings">
                            Email Settings
                        </a>
                    </li>
                    <li class="nav-item">
                        <a class="nav-link" data-bs-toggle="tab" href="#backupSettings">
                            Backup & Restore
                        </a>
                    </li>
                </ul>

                <div class="tab-content mt-3">
                    <!-- General Settings -->
                    <div class="tab-pane fade show active" id="generalSettings">
                        <form id="generalSettingsForm">
                            <input type="hidden" name="action" value="update_general">
                            <div class="row">
                                <div class="col-md-6">
                                    <div class="mb-3">
                                        <label class="form-label">Company Name</label>
                                        <input type="text" class="form-control" name="company_name" 
                                               value="<?php echo COMPANY_NAME; ?>">
                                    </div>
                                    <div class="mb-3">
                                        <label class="form-label">Company Address</label>
                                        <textarea class="form-control" name="company_address" rows="3"
                                        ><?php echo COMPANY_ADDRESS; ?></textarea>
                                    </div>
                                    <div class="mb-3">
                                        <label class="form-label">Phone Number</label>
                                        <input type="text" class="form-control" name="company_phone"
                                               value="<?php echo COMPANY_PHONE; ?>">
                                    </div>
                                    <div class="mb-3">
                                        <label class="form-label">Email Address</label>
                                        <input type="email" class="form-control" name="company_email"
                                               value="<?php echo COMPANY_EMAIL; ?>">
                                    </div>
                                </div>
                                <div class="col-md-6">
                                    <div class="mb-3">
                                        <label class="form-label">Currency Symbol</label>
                                        <input type="text" class="form-control" name="currency_symbol"
                                               value="<?php echo CURRENCY_SYMBOL; ?>">
                                    </div>
                                    <div class="mb-3">
                                        <label class="form-label">Date Format</label>
                                        <select class="form-select" name="date_format">
                                            <option value="d/m/Y" <?php echo (DATE_FORMAT == 'd/m/Y') ? 'selected' : ''; ?>>
                                                DD/MM/YYYY
                                            </option>
                                            <option value="m/d/Y" <?php echo (DATE_FORMAT == 'm/d/Y') ? 'selected' : ''; ?>>
                                                MM/DD/YYYY
                                            </option>
                                            <option value="Y-m-d" <?php echo (DATE_FORMAT == 'Y-m-d') ? 'selected' : ''; ?>>
                                                YYYY-MM-DD
                                            </option>
                                        </select>
                                    </div>
                                    <div class="mb-3">
                                        <label class="form-label">Tax Rate (%)</label>
                                        <input type="number" class="form-control" name="default_tax_rate"
                                               value="<?php echo DEFAULT_TAX_RATE; ?>" step="0.01">
                                    </div>
                                </div>
                            </div>
                            <button type="submit" class="btn btn-primary">Save General Settings</button>
                        </form>
                    </div>

                    <!-- Invoice Settings -->
                    <div class="tab-pane fade" id="invoiceSettings">
                        <form id="invoiceSettingsForm">
                            <input type="hidden" name="action" value="update_invoice">
                            <div class="row">
                                <div class="col-md-6">
                                    <div class="mb-3">
                                        <label class="form-label">Invoice Prefix</label>
                                        <input type="text" class="form-control" name="invoice_prefix"
                                               value="<?php echo INVOICE_PREFIX; ?>">
                                    </div>
                                    <div class="mb-3">
                                        <label class="form-label">Invoice Terms</label>
                                        <textarea class="form-control" name="invoice_terms" rows="4"
                                        ><?php echo INVOICE_TERMS; ?></textarea>
                                    </div>
                                    <div class="mb-3">
                                        <label class="form-label">Invoice Footer</label>
                                        <textarea class="form-control" name="invoice_footer" rows="4"
                                        ><?php echo INVOICE_FOOTER; ?></textarea>
                                    </div>
                                </div>
                                <div class="col-md-6">
                                    <div class="mb-3">
                                        <label class="form-label">Default Due Days</label>
                                        <input type="number" class="form-control" name="default_due_days"
                                               value="<?php echo DEFAULT_DUE_DAYS; ?>">
                                    </div>
                                    <div class="mb-3">
                                        <label class="form-label">Invoice Logo</label>
                                        <input type="file" class="form-control" name="invoice_logo"
                                               accept="image/*">
                                    </div>
                                    <div class="mb-3">
                                        <label class="form-label">Current Logo</label>
                                        <?php if(file_exists(INVOICE_LOGO_PATH)): ?>
                                            <img src="<?php echo INVOICE_LOGO_PATH; ?>" class="img-fluid">
                                        <?php else: ?>
                                            <p class="text-muted">No logo uploaded</p>
                                        <?php endif; ?>
                                    </div>
                                </div>
                            </div>
                            <button type="submit" class="btn btn-primary">Save Invoice Settings</button>
                        </form>
                    </div>

                    <!-- Email Settings -->
                    <div class="tab-pane fade" id="emailSettings">
                        <form id="emailSettingsForm">
                            <input type="hidden" name="action" value="update_email">
                            <div class="row">
                                <div class="col-md-6">
                                    <div class="mb-3">
                                        <label class="form-label">SMTP Host</label>
                                        <input type="text" class="form-control" name="smtp_host"
                                               value="<?php echo SMTP_HOST; ?>">
                                    </div>
                                    <div class="mb-3">
                                        <label class="form-label">SMTP Port</label>
                                        <input type="number" class="form-control" name="smtp_port"
                                               value="<?php echo SMTP_PORT; ?>">
                                    </div>
                                    <div class="mb-3">
                                        <label class="form-label">SMTP Username</label>
                                        <input type="text" class="form-control" name="smtp_username"
                                               value="<?php echo SMTP_USERNAME; ?>">
                                    </div>
                                    <div class="mb-3">
                                        <label class="form-label">SMTP Password</label>
                                        <input type="password" class="form-control" name="smtp_password"
                                               value="<?php echo SMTP_PASSWORD; ?>">
                                    </div>
                                </div>
                                <div class="col-md-6">
                                    <div class="mb-3">
                                        <label class="form-label">Email Template (Invoice)</label>
                                        <textarea class="form-control" name="email_template_invoice" rows="10"
                                        ><?php echo EMAIL_TEMPLATE_INVOICE; ?></textarea>
                                    </div>
                                </div>
                            </div>
                            <button type="submit" class="btn btn-primary">Save Email Settings</button>
                            <button type="button" class="btn btn-info" onclick="testEmail()">
                                Test Email Settings
                            </button>
                        </form>
                    </div>

                    <!-- Backup & Restore -->
                    <div class="tab-pane fade" id="backupSettings">
                        <div class="row">
                            <div class="col-md-6">
                                <div class="card">
                                    <div class="card-header">
                                        <h5>Database Backup</h5>
                                    </div>
                                    <div class="card-body">
                                        <p>Create a backup of your database.</p>
                                        <button type="button" class="btn btn-primary" onclick="createBackup()">
                                            Create Backup
                                        </button>
                                    </div>
                                </div>
                            </div>
                            <div class="col-md-6">
                                <div class="card">
                                    <div class="card-header">
                                        <h5>Restore Database</h5>
                                    </div>
                                    <div class="card-body">
                                        <form id="restoreForm">
                                            <div class="mb-3">
                                                <label class="form-label">Select Backup File</label>
                                                <input type="file" class="form-control" name="backup_file"
                                                       accept=".sql">
                                            </div>
                                            <button type="submit" class="btn btn-warning">
                                                Restore Database
                                            </button>
                                        </form>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </div>
</div>

<?php require_once '../../includes/footer.php'; ?>             i need proper database for above inculding tables, procedure, views