<?php
declare(strict_types=1);

namespace App\Models;

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

class DebitNoteModel extends Model
{
    protected string $table = 'debit_notes';

    public function getAll(int $tenantId, array $filters = [], int $page = 1, int $perPage = 20): array
    {
        $offset = ($page - 1) * $perPage;
        
        $sql = "SELECT dn.*, v.name as vendor_name 
                FROM debit_notes dn
                JOIN vendors v ON dn.vendor_id = v.id
                WHERE dn.tenant_id = :tenant_id";
        
        $params = [':tenant_id' => $tenantId];

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

        $sql .= " ORDER BY dn.dn_date DESC LIMIT $perPage OFFSET $offset";
        
        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll();
    }

    public function getById(int $id, int $tenantId): ?array
    {
        $sql = "SELECT dn.*, v.name as vendor_name, v.email as vendor_email
                FROM debit_notes dn
                JOIN vendors v ON dn.vendor_id = v.id
                WHERE dn.id = ? AND dn.tenant_id = ?";
        
        $stmt = $this->db->prepare($sql);
        $stmt->execute([$id, $tenantId]);
        $dn = $stmt->fetch();
        
        if (!$dn) return null;
        
        $stmt = $this->db->prepare("SELECT * FROM debit_note_items WHERE debit_note_id = ?");
        $stmt->execute([$id]);
        $dn['items'] = $stmt->fetchAll();
        
        return $dn;
    }

    public function createDN(int $tenantId, array $data, int $userId): array
    {
        $this->db->beginTransaction();

        try {
            $isInterstate = isset($data['is_interstate']) ? (bool)$data['is_interstate'] : false;
            $calculated = GSTHelper::calculateInvoiceTotals($data['items'], 'amount', 0, $isInterstate);
            
            $st = $this->db->prepare("SELECT default_template FROM tenant_settings WHERE tenant_id = ?");
            $st->execute([$tenantId]);
            $template = $st->fetchColumn() ?: 'classic';
            
            $dnNumber = InvoiceHelper::generateAutoNumber('debit_notes', 'DN', $tenantId);
            
            $sql = "INSERT INTO debit_notes (
                tenant_id, dn_number, po_id, vendor_id, dn_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, $dnNumber, empty($data['po_id']) ? null : $data['po_id'], $data['vendor_id'],
                empty($data['dn_date']) ? date('Y-m-d') : $data['dn_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
            ]);
            
            $dnId = (int)$this->db->lastInsertId();

            foreach ($data['items'] as $item) {
                $itemCalc = GSTHelper::calculateLineItem(
                    (float)$item['unit_price'],
                    (float)$item['quantity'],
                    0,
                    (float)($item['tax_rate'] ?? 0),
                    $isInterstate
                );

                $sqlItem = "INSERT INTO debit_note_items (
                    debit_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);
                $stmtItem->execute([
                    $dnId, $item['product_id'] ?? null, $item['description'], $item['hsn_code'] ?? null,
                    $item['quantity'], $item['unit_price'], $item['tax_rate'],
                    $itemCalc['cgst_amount'], $itemCalc['sgst_amount'], $itemCalc['igst_amount'], $itemCalc['total_amount']
                ]);
            }
            
            $this->db->commit();
            return ['id' => $dnId, 'dn_number' => $dnNumber];
        } catch (Exception $e) {
            $this->db->rollBack();
            throw new Exception("Error creating Debit Note: " . $e->getMessage());
        }
    }

    public function updateDN(int $dnId, int $tenantId, array $data): bool
    {
        $this->db->beginTransaction();
        try {
            $isInterstate = isset($data['is_interstate']) ? (bool)$data['is_interstate'] : false;
            $calculated = GSTHelper::calculateInvoiceTotals($data['items'], 'amount', 0, $isInterstate);

            $sql = "UPDATE debit_notes SET 
                vendor_id = ?, po_id = ?, dn_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['vendor_id'], empty($data['po_id']) ? null : $data['po_id'], empty($data['dn_date']) ? date('Y-m-d') : $data['dn_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, $dnId, $tenantId
            ]);

            $this->db->prepare("DELETE FROM debit_note_items WHERE debit_note_id = ?")->execute([$dnId]);

            foreach ($data['items'] as $item) {
                $itemCalc = GSTHelper::calculateLineItem(
                    (float)$item['unit_price'], (float)$item['quantity'],
                    0, (float)($item['tax_rate'] ?? 0), $isInterstate
                );

                $sqlItem = "INSERT INTO debit_note_items (
                    debit_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);
                $stmtItem->execute([
                    $dnId, $item['product_id'] ?? null, $item['description'], $item['hsn_code'] ?? null,
                    $item['quantity'], $item['unit_price'], $item['tax_rate'],
                    $itemCalc['cgst_amount'], $itemCalc['sgst_amount'], $itemCalc['igst_amount'], $itemCalc['total_amount']
                ]);
            }

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

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

    public function finalizeDN(int $dnId, int $tenantId, int $userId): bool
    {
        $this->db->beginTransaction();
        try {
            $stmt = $this->db->prepare("SELECT * FROM debit_notes WHERE id = ? AND tenant_id = ?");
            $stmt->execute([$dnId, $tenantId]);
            $dn = $stmt->fetch();
            if (!$dn) throw new Exception("Debit Note not found");
            if ($dn['status'] !== 'draft') throw new Exception("Already issued");

            $stmtItems = $this->db->prepare("SELECT * FROM debit_note_items WHERE debit_note_id = ?");
            $stmtItems->execute([$dnId]);
            $items = $stmtItems->fetchAll();

            $productModel = new ProductModel();
            $warehouseId = $productModel->getDefaultWarehouse($tenantId);
            foreach ($items as $item) {
                if ($item['product_id']) {
                    // return_out for purchase return (subtract stock)
                    $productModel->updateStock((int)$item['product_id'], $warehouseId, -1 * (float)$item['quantity'], 'return_out', 'debit_note', $dnId, (float)$item['unit_price'], $userId);
                }
            }

            $this->db->prepare("UPDATE debit_notes SET status = 'issued' WHERE id = ?")->execute([$dnId]);
            $this->db->commit();
            return true;
        } catch (Exception $e) {
            $this->db->rollBack();
            throw new Exception($e->getMessage());
        }
    }
}
