<?php
declare(strict_types=1);

namespace App\Models;

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

class CreditNoteModel extends Model
{
    protected string $table = 'credit_notes';

    public function getAll(int $tenantId, array $filters = [], int $page = 1, int $perPage = 50): array
    {
        $offset = ($page - 1) * $perPage;
        $sql = "SELECT cn.*, c.name as customer_name, inv.invoice_number as linked_invoice_number
                FROM credit_notes cn
                JOIN customers c ON cn.customer_id = c.id
                LEFT JOIN invoices inv ON cn.invoice_id = inv.id
                WHERE cn.tenant_id = :tenant_id";

        $params = [':tenant_id' => $tenantId];

        if (!empty($filters['status']) && $filters['status'] !== 'all') {
            $sql .= " AND cn.status = :status";
            $params[':status'] = $filters['status'];
        }

        if (!empty($filters['search'])) {
            $sql .= " AND (cn.cn_number LIKE :search OR c.name LIKE :search)";
            $params[':search'] = '%' . $filters['search'] . '%';
        }

        $sql .= " ORDER BY cn.cn_date DESC, cn.id DESC LIMIT " . (string)$perPage . " OFFSET " . (string)$offset;

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

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

        if (!empty($filters['status']) && $filters['status'] !== 'all') {
            $sql .= " AND cn.status = :status";
            $params[':status'] = $filters['status'];
        }
        if (!empty($filters['search'])) {
            $sql .= " AND (cn.cn_number LIKE :search OR c.name LIKE :search)";
            $params[':search'] = '%' . $filters['search'] . '%';
        }

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

    public function getById(int $id, int $tenantId): ?array
    {
        $sql = "SELECT cn.*, c.name as customer_name, c.email as customer_email,
                       c.gstin as customer_gstin, c.billing_address, i.invoice_number as linked_invoice_number
                FROM credit_notes cn
                JOIN customers c ON cn.customer_id = c.id
                LEFT JOIN invoices i ON cn.invoice_id = i.id
                WHERE cn.id = ? AND cn.tenant_id = ?";

        $stmt = $this->db->prepare($sql);
        $stmt->execute([$id, $tenantId]);
        $cn = $stmt->fetch();
        if (!$cn) return null;

        $stmt = $this->db->prepare("SELECT * FROM credit_note_items WHERE credit_note_id = ?");
        $stmt->execute([$id]);
        $cn['items'] = $stmt->fetchAll();

        return $cn;
    }

    public function createCN(int $tenantId, array $data, int $userId): array
    {
        $this->db->beginTransaction();
        try {
            $customerId = (int)($data['customer_id'] ?? 0);
            if (!$customerId && !empty($data['invoice_id'])) {
                // Fetch customer from invoice if not provided
                $stmt = $this->db->prepare("SELECT customer_id FROM invoices WHERE id = ? AND tenant_id = ?");
                $stmt->execute([(int)$data['invoice_id'], $tenantId]);
                $customerId = (int)$stmt->fetchColumn();
            }

            if (!$customerId) throw new Exception("Customer is required");

            $stmt = $this->db->prepare("SELECT * FROM customers WHERE id = ? AND tenant_id = ?");
            $stmt->execute([$customerId, $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';
            $customerStateCode = $customer['billing_state_code'] ?? $businessStateCode;

            $isInterstate = GSTHelper::isInterstate($businessStateCode, $customerStateCode);

            // Enrich items from invoice if needed
            $items = $this->enrichItemsFromInvoice($data['items'], $tenantId);
            $calculated = GSTHelper::calculateInvoiceTotals($items, 'amount', 0, $isInterstate);
            $cnNumber = InvoiceHelper::generateAutoNumber('credit_notes', 'CN', $tenantId);

            $template = $data['template'] ?? $tenantSettings['default_template'] ?? 'classic';
            $sql = "INSERT INTO credit_notes (
                tenant_id, cn_number, invoice_id, customer_id, cn_date,
                reason, reason_notes, subtotal, taxable_amount,
                cgst_amount, sgst_amount, igst_amount, total_tax, grand_total,
                is_interstate, template, status, created_by
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'draft', ?)";

            $stmt = $this->db->prepare($sql);
            $stmt->execute([
                $tenantId, $cnNumber, empty($data['invoice_id']) ? null : $data['invoice_id'], $customerId,
                empty($data['cn_date']) ? date('Y-m-d') : $data['cn_date'], $data['reason'] ?? 'other',
                empty($data['reason_notes']) ? null : $data['reason_notes'],
                $calculated['subtotal'], $calculated['taxable_amount'],
                $calculated['cgst_amount'], $calculated['sgst_amount'], $calculated['igst_amount'],
                $calculated['total_tax'], $calculated['grand_total'],
                $isInterstate ? 1 : 0, $template, $userId
            ]);

            $cnId = (int)$this->db->lastInsertId();
            $this->insertItems($cnId, $items, $isInterstate);

            $this->db->commit();
            return ['id' => $cnId, 'cn_number' => $cnNumber];
        } catch (Exception $e) {
            $this->db->rollBack();
            throw new Exception("Error creating Credit Note: " . $e->getMessage());
        }
    }

    public function updateCN(int $cnId, int $tenantId, array $data): bool
    {
        $this->db->beginTransaction();
        try {
            $stmt = $this->db->prepare("SELECT * FROM customers WHERE id = ? AND tenant_id = ?");
            $stmt->execute([(int)$data['customer_id'], $tenantId]);
            $customer = $stmt->fetch();
            if (!$customer) throw new Exception("Customer not found");

            $stmt = $this->db->prepare("SELECT * FROM tenant_settings WHERE tenant_id = ?");
            $stmt->execute([$tenantId]);
            $tenantSettings = $stmt->fetch();
            $businessStateCode = $tenantSettings['business_state_code'] ?? '27';
            $customerStateCode = $customer['billing_state_code'] ?? $businessStateCode;

            $isInterstate = GSTHelper::isInterstate($businessStateCode, $customerStateCode);
            $items = $this->enrichItemsFromInvoice($data['items'], $tenantId);
            $calculated = GSTHelper::calculateInvoiceTotals($items, 'amount', 0, $isInterstate);

            $sql = "UPDATE credit_notes SET
                customer_id = ?, invoice_id = ?, cn_date = ?, reason = ?, reason_notes = ?,
                subtotal = ?, taxable_amount = ?, cgst_amount = ?, sgst_amount = ?,
                igst_amount = ?, total_tax = ?, grand_total = ?, is_interstate = ?
                WHERE id = ? AND tenant_id = ? AND status = 'draft'";

            $stmt = $this->db->prepare($sql);
            $stmt->execute([
                $data['customer_id'], empty($data['invoice_id']) ? null : $data['invoice_id'], empty($data['cn_date']) ? date('Y-m-d') : $data['cn_date'],
                $data['reason'] ?? 'other', empty($data['reason_notes']) ? null : $data['reason_notes'],
                $calculated['subtotal'], $calculated['taxable_amount'],
                $calculated['cgst_amount'], $calculated['sgst_amount'], $calculated['igst_amount'],
                $calculated['total_tax'], $calculated['grand_total'],
                $isInterstate ? 1 : 0, $cnId, $tenantId
            ]);

            // Delete old items and re-insert
            $this->db->prepare("DELETE FROM credit_note_items WHERE credit_note_id = ?")->execute([$cnId]);
            $this->insertItems($cnId, $items, $isInterstate);

            $this->db->commit();
            return true;
        } catch (Exception $e) {
            $this->db->rollBack();
            throw new Exception("Error updating Credit Note: " . $e->getMessage());
        }
    }

    public function deleteCN(int $cnId, int $tenantId): bool
    {
        $this->db->beginTransaction();
        try {
            $this->db->prepare("DELETE FROM credit_note_items WHERE credit_note_id = ?")->execute([$cnId]);
            $stmt = $this->db->prepare("DELETE FROM credit_notes WHERE id = ? AND tenant_id = ? AND status = 'draft'");
            $stmt->execute([$cnId, $tenantId]);
            $this->db->commit();
            return true;
        } catch (Exception $e) {
            $this->db->rollBack();
            throw new Exception("Error deleting Credit Note: " . $e->getMessage());
        }
    }

    public function finalizeCN(int $cnId, int $tenantId, int $userId): bool
    {
        $this->db->beginTransaction();
        try {
            $cn = $this->getById($cnId, $tenantId);
            if (!$cn) throw new Exception("Credit Note not found");
            if ($cn['status'] !== 'draft') throw new Exception("Only draft Credit Notes can be issued");

            // Add back stock for returned products
            $productModel = new ProductModel();
            $warehouseId = $productModel->getDefaultWarehouse($tenantId);
            foreach ($cn['items'] as $item) {
                if (!empty($item['product_id'])) {
                    $productModel->updateStock(
                        (int)$item['product_id'], $warehouseId, (float)$item['quantity'],
                        'return_in', 'credit_note', $cnId, (float)$item['unit_price'], $userId
                    );
                }
            }

            $this->db->prepare("UPDATE credit_notes SET status = 'issued' WHERE id = ?")->execute([$cnId]);

            // Apply to Invoice if linked
            if (!empty($cn['invoice_id'])) {
                $stmtInv = $this->db->prepare("SELECT * FROM invoices WHERE id = ? AND tenant_id = ?");
                $stmtInv->execute([$cn['invoice_id'], $tenantId]);
                $inv = $stmtInv->fetch();
                if ($inv && $inv['balance_due'] > 0) {
                    $applyAmount = min((float)$cn['grand_total'], (float)$inv['balance_due']);
                    
                    // Update Invoice Balance
                    $newBalance = (float)$inv['balance_due'] - $applyAmount;
                    $status = $newBalance <= 0.01 ? 'paid' : ($inv['status'] === 'overdue' ? 'overdue' : 'partial');
                    
                    $this->db->prepare("UPDATE invoices SET balance_due = ?, status = ? WHERE id = ?")->execute([$newBalance, $status, $inv['id']]);
                    
                    // Update Credit Note Applied Amount
                    $this->db->prepare("UPDATE credit_notes SET applied_amount = ?, status = ? WHERE id = ?")->execute([$applyAmount, $applyAmount >= (float)$cn['grand_total'] - 0.01 ? 'applied' : 'issued', $cnId]);
                }
            }

            $this->db->commit();
            return true;
        } catch (Exception $e) {
            $this->db->rollBack();
            throw new Exception("Error issuing Credit Note: " . $e->getMessage());
        }
    }

    private function enrichItemsFromInvoice(array $items, int $tenantId): array
    {
        $enriched = [];
        foreach ($items as $item) {
            if (!empty($item['invoice_item_id']) && (empty($item['unit_price']) || empty($item['description']))) {
                $stmt = $this->db->prepare("SELECT * FROM invoice_items WHERE id = ? AND tenant_id = ?");
                $stmt->execute([(int)$item['invoice_item_id'], $tenantId]);
                $invItem = $stmt->fetch();
                if ($invItem) {
                    $item['product_id'] = $item['product_id'] ?? $invItem['product_id'];
                    $item['description'] = $item['description'] ?? $invItem['description'];
                    $item['hsn_code'] = $item['hsn_code'] ?? $invItem['hsn_code'];
                    $item['unit_price'] = $item['unit_price'] ?? $invItem['unit_price'];
                    $item['tax_rate'] = $item['tax_rate'] ?? $invItem['tax_rate'];
                }
            }
            $enriched[] = $item;
        }
        return $enriched;
    }

    private function insertItems(int $cnId, array $items, bool $isInterstate): void
    {
        $sqlItem = "INSERT INTO credit_note_items (
            credit_note_id, product_id, description, hsn_code,
            quantity, unit_price, tax_rate, cgst_amount, sgst_amount, igst_amount, total_amount
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        $stmtItem = $this->db->prepare($sqlItem);

        foreach ($items as $item) {
            $itemCalc = GSTHelper::calculateLineItem(
                (float)($item['unit_price'] ?? 0), (float)($item['quantity'] ?? 0),
                0, (float)($item['tax_rate'] ?? 0), $isInterstate
            );
            $stmtItem->execute([
                $cnId, $item['product_id'] ?? null,
                $item['description'] ?? '', $item['hsn_code'] ?? null,
                $item['quantity'] ?? 0, $item['unit_price'] ?? 0, $item['tax_rate'] ?? 0,
                $itemCalc['cgst_amount'], $itemCalc['sgst_amount'], $itemCalc['igst_amount'],
                $itemCalc['total_amount']
            ]);
        }
    }
}
