<?php
/**
 * Migration Runner - Add Historical Ledger Entries
 * This script retroactively creates ledger entries for existing purchases and sales
 * Access this file in your browser: http://localhost/TellerPro/run_ledger_data_migration.php
 */

require_once __DIR__ . '/config/config.php';
require_once __DIR__ . '/config/database.php';

echo '<!DOCTYPE html>
<html>
<head>
    <title>Running Ledger Data Migration</title>
    <style>
        body { font-family: Arial, sans-serif; padding: 20px; max-width: 800px; margin: 0 auto; }
        .success { color: green; }
        .error { color: red; }
        .info { color: blue; }
        .warning { color: orange; }
    </style>
</head>
<body>';

echo '<h1>Historical Ledger Data Migration</h1>';

try {
    echo '<p class="info">Starting migration...</p>';
    
    db()->beginTransaction();
    
    // Get all tenants
    $tenants = db()->query("SELECT id FROM tenants")->fetchAll();
    
    $totalPurchaseEntries = 0;
    $totalSaleEntries = 0;
    
    foreach ($tenants as $tenant) {
        $tid = $tenant['id'];
        echo "<p class='info'>Processing tenant ID: {$tid}</p>";
        
        // === Supplier Ledger Entries ===
        echo "<p class='info'>Creating supplier ledger entries...</p>";
        
        // First, check all purchases with suppliers
        $checkSql = "SELECT p.id, p.purchase_no, p.supplier_id, p.total, p.amount_paid, p.payment_status, s.name as supplier_name
                     FROM purchases p
                     LEFT JOIN suppliers s ON s.id = p.supplier_id
                     WHERE p.tenant_id = ? AND p.supplier_id IS NOT NULL";
        $checkStmt = db()->prepare($checkSql);
        $checkStmt->execute([$tid]);
        $allPurchases = $checkStmt->fetchAll();
        
        echo "<p class='info'>Found " . count($allPurchases) . " purchases with suppliers</p>";
        
        // Get purchases with due amounts that don't have ledger entries
        $sql = "SELECT p.id, p.purchase_no, p.supplier_id, p.total, p.amount_paid, p.created_at, p.payment_status, s.name as supplier_name
                FROM purchases p
                LEFT JOIN suppliers s ON s.id = p.supplier_id
                WHERE p.tenant_id = ? AND p.supplier_id IS NOT NULL
                AND p.total > p.amount_paid
                AND p.id NOT IN (
                    SELECT DISTINCT reference_id
                    FROM supplier_ledger
                    WHERE tenant_id = ? AND transaction_type = 'purchase'
                )";
        $stmt = db()->prepare($sql);
        $stmt->execute([$tid, $tid]);
        $purchases = $stmt->fetchAll();
        
        foreach ($purchases as $purchase) {
            $due = $purchase['total'] - $purchase['amount_paid'];
            echo "<p class='info'>Checking purchase {$purchase['purchase_no']} (Supplier: {$purchase['supplier_name']}, Due: {$due}, Payment Status: {$purchase['payment_status']})</p>";
            if ($due > 0) {
                // Get current balance before this entry
                $balanceSql = "SELECT COALESCE(SUM(debit - credit), 0) as balance
                              FROM supplier_ledger
                              WHERE tenant_id = ? AND supplier_id = ? AND transaction_date < ?";
                $balanceStmt = db()->prepare($balanceSql);
                $balanceStmt->execute([$tid, $purchase['supplier_id'], date('Y-m-d', strtotime($purchase['created_at']))]);
                $currentBalance = (float)$balanceStmt->fetchColumn();
                $newBalance = $currentBalance + $due;

                $insertSql = "INSERT INTO supplier_ledger
                             (tenant_id, supplier_id, transaction_type, reference_id, reference_no,
                              debit, credit, balance, description, transaction_date, user_id)
                             VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
                $insertStmt = db()->prepare($insertSql);
                $insertStmt->execute([
                    $tid,
                    $purchase['supplier_id'],
                    'purchase',
                    $purchase['id'],
                    $purchase['purchase_no'],
                    $due,
                    0,
                    $newBalance,
                    'Purchase ' . $purchase['purchase_no'],
                    date('Y-m-d', strtotime($purchase['created_at'])),
                    null
                ]);
                $totalPurchaseEntries++;
                echo "<p class='success'>Created ledger entry for purchase {$purchase['purchase_no']}</p>";
            }
        }
        
        // === Customer Ledger Entries ===
        echo "<p class='info'>Creating customer ledger entries...</p>";

        // First, check all sales with customers
        $checkSql = "SELECT s.id, s.sale_no, s.customer_id, s.total, s.amount_paid, s.payment_status, c.name as customer_name
                     FROM sales s
                     LEFT JOIN customers c ON c.id = s.customer_id
                     WHERE s.tenant_id = ? AND s.customer_id IS NOT NULL";
        $checkStmt = db()->prepare($checkSql);
        $checkStmt->execute([$tid]);
        $allSales = $checkStmt->fetchAll();

        echo "<p class='info'>Found " . count($allSales) . " sales with customers</p>";

        // Get sales with due amounts that don't have ledger entries
        $sql = "SELECT s.id, s.sale_no, s.customer_id, s.total, s.amount_paid, s.created_at, s.payment_status, c.name as customer_name
                FROM sales s
                LEFT JOIN customers c ON c.id = s.customer_id
                WHERE s.tenant_id = ? AND s.customer_id IS NOT NULL
                AND s.total > s.amount_paid
                AND s.id NOT IN (
                    SELECT DISTINCT reference_id
                    FROM customer_ledger
                    WHERE tenant_id = ? AND transaction_type = 'sale'
                )";
        $stmt = db()->prepare($sql);
        $stmt->execute([$tid, $tid]);
        $sales = $stmt->fetchAll();

        foreach ($sales as $sale) {
            $due = $sale['total'] - $sale['amount_paid'];
            echo "<p class='info'>Checking sale {$sale['sale_no']} (Customer: {$sale['customer_name']}, Due: {$due}, Payment Status: {$sale['payment_status']})</p>";
            if ($due > 0) {
                // Get current balance before this entry
                $balanceSql = "SELECT COALESCE(SUM(debit - credit), 0) as balance
                              FROM customer_ledger
                              WHERE tenant_id = ? AND customer_id = ? AND transaction_date < ?";
                $balanceStmt = db()->prepare($balanceSql);
                $balanceStmt->execute([$tid, $sale['customer_id'], date('Y-m-d', strtotime($sale['created_at']))]);
                $currentBalance = (float)$balanceStmt->fetchColumn();
                $newBalance = $currentBalance + $due;

                $insertSql = "INSERT INTO customer_ledger
                             (tenant_id, customer_id, transaction_type, reference_id, reference_no,
                              debit, credit, balance, description, transaction_date, user_id)
                             VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
                $insertStmt = db()->prepare($insertSql);
                $insertStmt->execute([
                    $tid,
                    $sale['customer_id'],
                    'sale',
                    $sale['id'],
                    $sale['sale_no'],
                    $due,
                    0,
                    $newBalance,
                    'Sale ' . $sale['sale_no'],
                    date('Y-m-d', strtotime($sale['created_at'])),
                    null
                ]);
                $totalSaleEntries++;
                echo "<p class='success'>Created ledger entry for sale {$sale['sale_no']}</p>";
            }
        }
    }
    
    db()->commit();
    
    echo '<h2 class="success">Migration completed successfully!</h2>';
    echo "<p>Supplier ledger entries created: {$totalPurchaseEntries}</p>";
    echo "<p>Customer ledger entries created: {$totalSaleEntries}</p>";
    echo '<p><a href="' . APP_URL . '/supplier-ledger">Go to Supplier Ledger</a></p>';
    echo '<p><a href="' . APP_URL . '/customer-ledger">Go to Customer Ledger</a></p>';

} catch (Exception $e) {
    db()->rollBack();
    echo '<h2 class="error">Migration failed</h2>';
    echo '<pre class="error">' . htmlspecialchars($e->getMessage()) . '</pre>';
    echo '<p>Please check your database configuration and try again.</p>';
}

echo '</body>
</html>';
