<?php
// Initialize and load dependencies
include_once '../includes/constants.php';
include_once '../includes/functions.php';

// Validate and sanitize input
$invoiceid = isset($_GET['id']) ? intval($_GET['id']) : 0;
if ($invoiceid <= 0) {
    die("Invalid invoice ID.");
}

// Connect to database
$mysqli = new mysqli($host, $user, $pass, $db);
if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}

// Open file for writing
$filename = "invoice.xls";
$fp = fopen($filename, "w");
if (!$fp) {
    die("Unable to create export file.");
}

// Write header row
fwrite($fp, "Program ID;From Date;To Date;Fee Type;Quantity;Unit Price;TotalPrice\n\r");

// Get invoice details
$stmt = $mysqli->prepare("SELECT * FROM partners_invoice WHERE invoice_id = ?");
$stmt->bind_param("i", $invoiceid);
$stmt->execute();
$result = $stmt->get_result();
if (!$row1 = $result->fetch_object()) {
    fclose($fp);
    die("Invoice not found.");
}
$merchantid = $row1->invoice_merchantid;
$monthyear = $row1->invoice_monthyear;
$year_month = substr($monthyear, 3, 4) . "-" . substr($monthyear, 0, 2);
$stmt->close();

// Get invoice status periods
$invoice_date_arr = $status_arr = [];
$stmt = $mysqli->prepare("
    SELECT invoice_date, invoice_status 
    FROM partners_invoiceStat 
    WHERE invoice_merchantid = ? 
    AND SUBSTRING(invoice_date, 1, 7) = ?
    ORDER BY invoice_id ASC
");
$stmt->bind_param("is", $merchantid, $year_month);
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_object()) {
    $invoice_date_arr[] = $row->invoice_date;
    $status_arr[] = $row->invoice_status;
}
$stmt->close();

// Get all program IDs for the merchant
$program_id_arr = [];
$stmt = $mysqli->prepare("SELECT joinpgm_programid FROM partners_joinpgm WHERE joinpgm_merchantid = ?");
$stmt->bind_param("i", $merchantid);
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_object()) {
    $program_id_arr[] = $row->joinpgm_programid;
}
$stmt->close();

$totalprice = 0;
$count = count($invoice_date_arr);

foreach ($program_id_arr as $programid) {
    $joinpgm_ids = [];
    $stmt = $mysqli->prepare("
        SELECT joinpgm_id 
        FROM partners_joinpgm 
        WHERE joinpgm_programid = ? AND joinpgm_merchantid = ?
    ");
    $stmt->bind_param("ii", $programid, $merchantid);
    $stmt->execute();
    $result = $stmt->get_result();
    while ($row = $result->fetch_object()) {
        $joinpgm_ids[] = $row->joinpgm_id;
    }
    $stmt->close();

    foreach ($joinpgm_ids as $joinpgmid) {
        for ($p = 0; $p < $count; $p += 2) {
            $fromdate = $invoice_date_arr[$p];
            $todate = $invoice_date_arr[$p + 1] ?? '';

            if (empty($todate)) {
                if ($status_arr[$count - 1] === 'inactive') break;
                $todate = date('Y-m-d');
            }

            $clicks = $sales = $leads = $registers = $program_fees = 0;

            // Helper function
            function countQuery($mysqli, $sql, $params) {
                $stmt = $mysqli->prepare($sql);
                if ($stmt && $params) {
                    $stmt->bind_param(...$params);
                    $stmt->execute();
                    $result = $stmt->get_result();
                    if ($row = $result->fetch_object()) {
                        return intval($row->c);
                    }
                }
                return 0;
            }

            $clicks = countQuery($mysqli,
                "SELECT COUNT(*) AS c FROM partners_transaction WHERE transaction_type = 'click' AND transaction_dateoftransaction BETWEEN ? AND ? AND transaction_joinpgmid = ?",
                ['ssi', $fromdate, $todate, $joinpgmid]);

            $sales = countQuery($mysqli,
                "SELECT COUNT(*) AS c FROM partners_transaction WHERE transaction_type = 'sale' AND transaction_dateoftransaction BETWEEN ? AND ? AND transaction_joinpgmid = ?",
                ['ssi', $fromdate, $todate, $joinpgmid]);

            $leads = countQuery($mysqli,
                "SELECT COUNT(*) AS c FROM partners_transaction WHERE transaction_type = 'lead' AND transaction_dateoftransaction BETWEEN ? AND ? AND transaction_joinpgmid = ?",
                ['ssi', $fromdate, $todate, $joinpgmid]);

            $registers = countQuery($mysqli,
                "SELECT COUNT(*) AS c FROM partners_fee WHERE adjust_memberid = ? AND adjust_action LIKE 'register' AND adjust_date BETWEEN ? AND ?",
                ['iss', $merchantid, $fromdate, $todate]);

            $program_fees = countQuery($mysqli,
                "SELECT COUNT(*) AS c FROM partners_fee WHERE adjust_memberid = ? AND adjust_action LIKE 'programFee' AND adjust_date BETWEEN ? AND ?",
                ['iss', $merchantid, $fromdate, $todate]);

            // Calculate totals
            $click_total = $clicks * $admin_clickrate;
            $sale_total = $sales * $admin_salerate;
            $lead_total = $leads * $admin_leadrate;
            $totalprice += ($click_total + $sale_total + $lead_total);

            // Write lines
            if ($clicks > 0) fwrite($fp, "$programid;$fromdate;$todate;Click;$clicks;$currSymbol$admin_clickrate;$currSymbol" . round($click_total, 2) . "\n\r");
            if ($sales > 0) fwrite($fp, "$programid;$fromdate;$todate;Sale;$sales;$currSymbol$admin_salerate;$currSymbol" . round($sale_total, 2) . "\n\r");
            if ($leads > 0) fwrite($fp, "$programid;$fromdate;$todate;Lead;$leads;$currSymbol$admin_leadrate;$currSymbol" . round($lead_total, 2) . "\n\r");
            if ($registers > 0) fwrite($fp, "$programid;$fromdate;$todate;Member Fee;$registers;$currSymbol$unitprice;\n\r");
            if ($program_fees > 0) fwrite($fp, "$programid;$fromdate;$todate;Program Fee;$program_fees;$currSymbol$unitprice;\n\r");
        }
    }
}

// Final summary row
$summaryFrom = $invoice_date_arr[0] ?? '';
$summaryTo = $invoice_date_arr[$count - 1] ?? '';
fwrite($fp, "Total;$summaryFrom;$summaryTo;;;;$currSymbol" . round($totalprice, 2));

// Close file
fclose($fp);

// Output download link
echo '<br><br><a href="' . htmlspecialchars($filename) . '">Click here to download the file</a>';
?>
