<?php
declare(strict_types=1);

namespace App\Models;

use App\Core\Model;
use App\Helpers\GSTHelper;
use App\Helpers\InvoiceHelper;
use Exception;
use PDO;

class InvoiceModel extends Model
{
    protected string $table = 'invoices';

    // Get all invoices with pagination and filtering
    public function getAll(int $tenantId, array $filters = [], int $page = 1, int $perPage = 20): array
    {
        $offset = ($page - 1) * $perPage;
        
        $sql = "SELECT i.id, i.invoice_number, i.invoice_date, i.due_date, i.grand_total, i.balance_due, i.status, c.name as customer_name
                FROM invoices i
                JOIN customers c ON i.customer_id = c.id
                WHERE i.tenant_id = :tenant_id";
        
        $params = [':tenant_id' => $tenantId];

        if (!empty($filters['status'])) {
            $statusList = explode(',', $filters['status']);
            $placeholders = [];
            foreach ($statusList as $idx => $s) {
                $pName = ":status_" . $idx;
                $placeholders[] = $pName;
                $params[$pName] = $s;
            }
            $sql .= " AND i.status IN (" . implode(',', $placeholders) . ")";
        }
        
        if (!empty($filters['customer_id'])) {
            $sql .= " AND i.customer_id = :customer_id";
            $params[':customer_id'] = $filters['customer_id'];
        }

        if (!empty($filters['from_date']) && !empty($filters['to_date'])) {
            $sql .= " AND i.invoice_date BETWEEN :from_date AND :to_date";
            $params[':from_date'] = $filters['from_date'];
            $params[':to_date'] = $filters['to_date'];
        }

        if (!empty($filters['search'])) {
            $sql .= " AND (i.invoice_number LIKE :search1 OR c.name LIKE :search2 OR c.company_name LIKE :search3)";
            $params[':search1'] = '%' . $filters['search'] . '%';
            $params[':search2'] = '%' . $filters['search'] . '%';
            $params[':search3'] = '%' . $filters['search'] . '%';
        }

        $sort = $filters['sort'] ?? 'i.invoice_date';
        $allowedSort = ['i.invoice_date', 'i.invoice_number', 'i.grand_total', 'i.status', 'c.name'];
        if (!in_array($sort, $allowedSort)) $sort = 'i.invoice_date';
        
        $order = isset($filters['order']) && strtoupper($filters['order']) === 'ASC' ? 'ASC' : 'DESC';

        $sql .= " ORDER BY $sort $order LIMIT $perPage OFFSET $offset";
        
        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        $items = $stmt->fetchAll();

        return [
            'items' => $items,
            'total' => $this->countAll($tenantId, $filters),
            'page' => $page,
            'per_page' => $perPage
        ];
    }

    public function countAll(int $tenantId, array $filters = []): int
    {
        $sql = "SELECT COUNT(*) FROM invoices i
                JOIN customers c ON i.customer_id = c.id
                WHERE i.tenant_id = :tenant_id";
        
        $params = [':tenant_id' => $tenantId];

        if (!empty($filters['status'])) {
            $statusList = explode(',', $filters['status']);
            $placeholders = [];
            foreach ($statusList as $idx => $s) {
                $pName = ":status_" . $idx;
                $placeholders[] = $pName;
                $params[$pName] = $s;
            }
            $sql .= " AND i.status IN (" . implode(',', $placeholders) . ")";
        }
        
        if (!empty($filters['search'])) {
            $sql .= " AND (i.invoice_number LIKE :search1 OR c.name LIKE :search2 OR c.company_name LIKE :search3)";
            $params[':search1'] = '%' . $filters['search'] . '%';
            $params[':search2'] = '%' . $filters['search'] . '%';
            $params[':search3'] = '%' . $filters['search'] . '%';
        }

        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        return (int) $stmt->fetchColumn();
    }

    public function getStats(int $tenantId, array $filters = []): array
    {
        $sql = "SELECT 
                    COUNT(*) as total_count,
                    SUM(grand_total) as total_amount,
                    SUM(balance_due) as total_unpaid,
                    SUM(CASE WHEN due_date < CURRENT_DATE AND balance_due > 0 THEN 1 ELSE 0 END) as overdue_count
                FROM invoices i
                JOIN customers c ON i.customer_id = c.id
                WHERE i.tenant_id = :tenant_id";
        
        $params = [':tenant_id' => $tenantId];

        if (!empty($filters['status'])) {
            $statusList = explode(',', $filters['status']);
            $placeholders = [];
            foreach ($statusList as $idx => $s) {
                $pName = ":status_" . $idx;
                $placeholders[] = $pName;
                $params[$pName] = $s;
            }
            $sql .= " AND i.status IN (" . implode(',', $placeholders) . ")";
        }
        
        if (!empty($filters['search'])) {
            $sql .= " AND (i.invoice_number LIKE :search1 OR c.name LIKE :search2 OR c.company_name LIKE :search3)";
            $params[':search1'] = '%' . $filters['search'] . '%';
            $params[':search2'] = '%' . $filters['search'] . '%';
            $params[':search3'] = '%' . $filters['search'] . '%';
        }

        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);

        return [
            'count' => (int) ($row['total_count'] ?? 0),
            'total_amount' => (float) ($row['total_amount'] ?? 0),
            'total_unpaid' => (float) ($row['total_unpaid'] ?? 0),
            'total_paid' => (float) (($row['total_amount'] ?? 0) - ($row['total_unpaid'] ?? 0)),
            'overdue_count' => (int) ($row['overdue_count'] ?? 0)
        ];
    }

    // Get single invoice by ID
    public function getById(int $id, int $tenantId): ?array
    {
        $sql = "SELECT i.*, c.name as customer_name, c.company_name, c.gstin as customer_gstin, c.email as customer_email, c.work_phone as customer_phone
                FROM invoices i
                JOIN customers c ON i.customer_id = c.id
                WHERE i.id = ? AND i.tenant_id = ?";
        
        $stmt = $this->db->prepare($sql);
        $stmt->execute([$id, $tenantId]);
        $invoice = $stmt->fetch();

        if (!$invoice) return null;

        // Fetch items
        $stmt = $this->db->prepare("SELECT * FROM invoice_items WHERE invoice_id = ? ORDER BY sort_order ASC");
        $stmt->execute([$id]);
        $invoice['items'] = $stmt->fetchAll();

        // Check if overdue manually
        if (InvoiceHelper::isOverdue($invoice)) {
            $invoice['status'] = 'overdue';
        }

        return $invoice;
    }

    // Create Invoice
    public function createInvoice(int $tenantId, array $data, int $userId): array
    {
        $this->db->beginTransaction();

        try {
            // Check Customer
            $stmt = $this->db->prepare("SELECT * FROM customers WHERE id = ? AND tenant_id = ?");
            $stmt->execute([$data['customer_id'], $tenantId]);
            $customer = $stmt->fetch();
            
            if (!$customer) throw new Exception("Customer not found");

            // Check Tenant Settings for state code
            $stmt = $this->db->prepare("SELECT * FROM tenant_settings WHERE tenant_id = ?");
            $stmt->execute([$tenantId]);
            $tenantSettings = $stmt->fetch();
            $businessStateCode = $tenantSettings['business_state_code'] ?? '27'; // Default to Maharashtra if not set
            $customerStateCode = $data['place_of_supply_code'] ?? $customer['billing_state_code'];

            if(empty($customerStateCode)) $customerStateCode = $businessStateCode; // Default to intra-state if missing

            $isInterstate = GSTHelper::isInterstate($businessStateCode, $customerStateCode);
            
            // Calculate GST Totals
            $discountType = $data['discount_type'] ?? 'percent';
            $discountValue = (float)($data['discount_value'] ?? 0);
            
            $calculated = GSTHelper::calculateInvoiceTotals($data['items'], $discountType, $discountValue, $isInterstate);
            
            // Generate Invoice Number
            $invoiceNumber = empty($data['invoice_number']) ? InvoiceHelper::generateInvoiceNumber($tenantId) : $data['invoice_number'];
            $invoiceDate = empty($data['invoice_date']) ? date('Y-m-d') : $data['invoice_date'];
            $dueDate = empty($data['due_date']) ? InvoiceHelper::calculateDueDate($invoiceDate, $data['terms'] ?? 'Net 30') : $data['due_date'];
            
            // Insert Invoice Header
            $sql = "INSERT INTO invoices (
                tenant_id, invoice_number, invoice_type, customer_id, invoice_date, due_date,
                po_number, place_of_supply, place_of_supply_code, billing_address, shipping_address,
                is_interstate, subtotal, discount_type, discount_value, discount_amount,
                taxable_amount, cgst_amount, sgst_amount, igst_amount, cess_amount,
                total_tax, round_off, grand_total, balance_due, notes, terms, template, status, created_by
            ) VALUES (
                ?, ?, ?, ?, ?, ?,
                ?, ?, ?, ?, ?,
                ?, ?, ?, ?, ?,
                ?, ?, ?, ?, ?,
                ?, ?, ?, ?, ?, ?, ?, ?, ?
            )";
            
            // Determine Template
            $template = $data['template'] ?? null;
            if (!$template) {
                $st = $this->db->prepare("SELECT default_template FROM tenant_settings WHERE tenant_id = ?");
                $st->execute([$tenantId]);
                $template = $st->fetchColumn() ?: 'classic';
            }

            $stmt = $this->db->prepare($sql);
            $stmt->execute([
                $tenantId, $invoiceNumber, $data['invoice_type'] ?? 'tax_invoice', $customer['id'], $invoiceDate, $dueDate,
                $data['po_number'] ?? null, $data['place_of_supply'] ?? null, $customerStateCode, $customer['billing_address'] ?? null, $customer['shipping_address'] ?? null,
                $isInterstate ? 1 : 0, $calculated['subtotal'], $discountType, $discountValue, $calculated['discount_amount'],
                $calculated['taxable_amount'], $calculated['cgst_amount'], $calculated['sgst_amount'], $calculated['igst_amount'], $calculated['cess_amount'],
                $calculated['total_tax'], $calculated['round_off'], $calculated['grand_total'], $calculated['grand_total'], $data['notes'] ?? null, $data['terms'] ?? null, $template,
                $data['status'] ?? 'draft', $userId
            ]);
            
            $invoiceId = (int)$this->db->lastInsertId();
            $this->saveItems($invoiceId, $tenantId, $data['items'], $isInterstate, $data['status'] ?? 'draft', $userId);
            
            $this->db->commit();
            return ['id' => $invoiceId, 'invoice_number' => $invoiceNumber, 'calculated' => $calculated];

        } catch (Exception $e) {
            $this->db->rollBack();
            throw new Exception("Error creating invoice: " . $e->getMessage());
        }
    }

    public function updateInvoice(int $id, int $tenantId, array $data, int $userId): array
    {
        $this->db->beginTransaction();
        try {
            $invoice = $this->getById($id, $tenantId);
            if (!$invoice) throw new Exception("Invoice not found.");
            
            if ($invoice['status'] === 'paid' || $invoice['status'] === 'cancelled') {
                throw new Exception("Cannot edit " . $invoice['status'] . " invoice.");
            }

            // Reverse current stock movements if previously confirmed
            if ($invoice['status'] !== 'draft') {
                $warehouseId = (int)$this->db->query("SELECT id FROM warehouses WHERE tenant_id = $tenantId ORDER BY is_default DESC LIMIT 1")->fetchColumn();
                $productModel = new ProductModel();
                foreach ($invoice['items'] as $item) {
                    if (!empty($item['product_id']) && $warehouseId) {
                        $productModel->updateStock((int)$item['product_id'], $warehouseId, (float)$item['quantity'], 'revert_sale', 'invoice_update', $id, 0, $userId);
                    }
                }
            }

            // Calculation Logic (same as create)
            $stmt = $this->db->prepare("SELECT * FROM tenant_settings WHERE tenant_id = ?");
            $stmt->execute([$tenantId]);
            $tenantSettings = $stmt->fetch();
            $businessStateCode = $tenantSettings['business_state_code'] ?? '27';
            $customerStateCode = $data['place_of_supply_code'] ?? $invoice['place_of_supply_code'];
            $isInterstate = GSTHelper::isInterstate($businessStateCode, $customerStateCode);

            $discountType = $data['discount_type'] ?? 'percent';
            $discountValue = (float)($data['discount_value'] ?? 0);
            $calculated = GSTHelper::calculateInvoiceTotals($data['items'], $discountType, $discountValue, $isInterstate);

            $invoiceDate = empty($data['invoice_date']) ? date('Y-m-d') : $data['invoice_date'];
            $dueDate = empty($data['due_date']) ? InvoiceHelper::calculateDueDate($invoiceDate, $data['terms'] ?? 'Net 30') : $data['due_date'];

            // Update Header
            $sql = "UPDATE invoices SET 
                customer_id = ?, invoice_date = ?, due_date = ?, po_number = ?, 
                place_of_supply_code = ?, is_interstate = ?, subtotal = ?, 
                discount_type = ?, discount_value = ?, discount_amount = ?,
                taxable_amount = ?, cgst_amount = ?, sgst_amount = ?, igst_amount = ?,
                total_tax = ?, grand_total = ?, balance_due = grand_total - amount_paid,
                notes = ?, status = ?
                WHERE id = ? AND tenant_id = ?";
            
            $stmt = $this->db->prepare($sql);
            $stmt->execute([
                $data['customer_id'], $invoiceDate, $dueDate, empty($data['po_number']) ? null : $data['po_number'],
                $customerStateCode, $isInterstate ? 1 : 0, $calculated['subtotal'],
                $discountType, $discountValue, $calculated['discount_amount'],
                $calculated['taxable_amount'], $calculated['cgst_amount'], $calculated['sgst_amount'], $calculated['igst_amount'],
                $calculated['total_tax'], $calculated['grand_total'],
                $data['notes'] ?? null, $data['status'] ?? $invoice['status'],
                $id, $tenantId
            ]);

            // Clear old items and save new ones
            $this->db->prepare("DELETE FROM invoice_items WHERE invoice_id = ?")->execute([$id]);
            $this->saveItems($id, $tenantId, $data['items'], $isInterstate, $data['status'] ?? $invoice['status'], $userId);

            $this->db->commit();
            return ['id' => $id, 'calculated' => $calculated];
        } catch (Exception $e) {
            $this->db->rollBack();
            throw new Exception("Update failed: " . $e->getMessage());
        }
    }

    private function saveItems(int $invoiceId, int $tenantId, array $items, bool $isInterstate, string $status, int $userId): void
    {
        $warehouseId = (int)$this->db->query("SELECT id FROM warehouses WHERE tenant_id = $tenantId ORDER BY is_default DESC LIMIT 1")->fetchColumn();
        $productModel = new ProductModel();
        $sortOrder = 0;

        foreach ($items as $item) {
            $itemCalc = GSTHelper::calculateLineItem(
                (float)$item['unit_price'],
                (float)$item['quantity'],
                (float)($item['discount_percent'] ?? 0),
                (float)($item['tax_rate'] ?? 0),
                $isInterstate,
                (float)($item['cess_rate'] ?? 0)
            );
            
            $sqlItem = "INSERT INTO invoice_items (
                invoice_id, tenant_id, product_id, sort_order, description, hsn_code,
                quantity, unit, unit_price, discount_percent, discount_amount,
                taxable_amount, tax_rate, cgst_rate, cgst_amount, sgst_rate, sgst_amount,
                igst_rate, igst_amount, cess_rate, cess_amount, total_amount
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
            
            $stmtItem = $this->db->prepare($sqlItem);
            $stmtItem->execute([
                $invoiceId, $tenantId, $item['product_id'] ?? null, $sortOrder++, $item['description'], $item['hsn_code'] ?? null,
                $itemCalc['quantity'], $item['unit'] ?? 'PCS', $itemCalc['unit_price'], $itemCalc['discount_percent'], $itemCalc['discount_amount'],
                $itemCalc['taxable_amount'], $itemCalc['tax_rate'], $itemCalc['cgst_rate'], $itemCalc['cgst_amount'], $itemCalc['sgst_rate'], $itemCalc['sgst_amount'],
                $itemCalc['igst_rate'], $itemCalc['igst_amount'], $itemCalc['cess_rate'], $itemCalc['cess_amount'], $itemCalc['total_amount']
            ]);

            if (!empty($item['product_id']) && $status !== 'draft' && $warehouseId) {
                $productModel->updateStock((int)$item['product_id'], $warehouseId, -((float)$itemCalc['quantity']), 'sale', 'invoice', $invoiceId, 0, $userId);
            }
        }
    }

    public function cancel(int $id, int $tenantId, int $userId): void
    {
        $this->db->beginTransaction();

        try {
            $invoice = $this->getById($id, $tenantId);
            if (!$invoice) throw new Exception("Invoice not found.");
            
            if ($invoice['status'] === 'cancelled') throw new Exception("Already cancelled.");
            
            // Revert stock movements
            $warehouseId = (int)$this->db->query("SELECT id FROM warehouses WHERE tenant_id = $tenantId ORDER BY is_default DESC LIMIT 1")->fetchColumn();
            $productModel = new ProductModel();

            foreach ($invoice['items'] as $item) {
                if (!empty($item['product_id']) && $invoice['status'] !== 'draft' && $warehouseId) {
                    $productModel->updateStock((int)$item['product_id'], $warehouseId, (float)$item['quantity'], 'sale_return', 'invoice_cancel', $id, 0, $userId);
                }
            }

            $stmt = $this->db->prepare("UPDATE invoices SET status = 'cancelled' WHERE id = ? AND tenant_id = ?");
            $stmt->execute([$id, $tenantId]);
            
            $this->db->commit();
        } catch (Exception $e) {
            $this->db->rollBack();
            throw new Exception("Cancellation failed: " . $e->getMessage());
        }
    }

    public function recordPayment(int $id, array $paymentData, int $tenantId, int $userId): array
    {
        $this->db->beginTransaction();
        try {
            $invoice = $this->getById($id, $tenantId);
            if (!$invoice) throw new Exception("Invoice not found.");

            $amount = (float)$paymentData['amount'];
            if ($amount <= 0) throw new Exception("Invalid payment amount.");

            if ($amount > $invoice['balance_due']) throw new Exception("Amount exceeds balance due!");

            // Insert Payment
            $pmtNum = 'REC/' . date('Ym') . '/' . mt_rand(1000, 9999);
            $pmtSql = "INSERT INTO payments (tenant_id, payment_number, payment_type, reference_type, reference_id, customer_id, amount, payment_date, payment_method, reference_number, notes, created_by)
                       VALUES (?, ?, 'received', 'invoice', ?, ?, ?, ?, ?, ?, ?, ?)";
            $stmtPMT = $this->db->prepare($pmtSql);
            $stmtPMT->execute([
                $tenantId, $pmtNum, $id, $invoice['customer_id'], $amount, 
                $paymentData['payment_date'] ?? date('Y-m-d'), 
                $paymentData['payment_method'] ?? 'cash',
                $paymentData['reference_number'] ?? null,
                $paymentData['notes'] ?? null,
                $userId
            ]);

            $paymentId = $this->db->lastInsertId();

            // Update Invoice
            $newAmountPaid = $invoice['amount_paid'] + $amount;
            $newBalanceDue = $invoice['grand_total'] - $newAmountPaid;
            
            $pmtStatus = $newBalanceDue <= 0.01 ? 'paid' : 'partial';
            $status = $newBalanceDue <= 0.01 ? 'paid' : ($invoice['status'] == 'sent' ? 'partial' : $invoice['status']);

            $stmtInv = $this->db->prepare("UPDATE invoices SET amount_paid = ?, balance_due = ?, payment_status = ?, status = ? WHERE id = ?");
            $stmtInv->execute([$newAmountPaid, $newBalanceDue, $pmtStatus, $status, $id]);

            $this->db->commit();
            return ['payment_id' => $paymentId, 'new_balance' => $newBalanceDue];

        } catch (Exception $e) {
            $this->db->rollBack();
            throw new Exception("Failed to record payment: " . $e->getMessage());
        }
    }

    public function getPayments(int $invoiceId, int $tenantId): array
    {
        $stmt = $this->db->prepare("SELECT * FROM payments WHERE reference_type = 'invoice' AND reference_id = ? AND tenant_id = ? ORDER BY payment_date DESC, id DESC");
        $stmt->execute([$invoiceId, $tenantId]);
        return $stmt->fetchAll();
    }
}
