<?php
require_once __DIR__ . '/../../config/database.php';

class InvoiceModel {

    private $db;

    public function __construct() {
        $this->db = Database::connect();
    }

public function createFromOrder($order_id, $customer_id, $created_by) {

    try {

        $this->db->begin_transaction();

        // Generate invoice number
        $invoice_no = "INV-" . date('YmdHis');

        // 1️⃣ Insert Invoice
        $stmt = $this->db->prepare("
            INSERT INTO invoices 
            (order_id, customer_id, invoice_no, status, created_by)
            VALUES (?, ?, ?, 'Unpaid', ?)
        ");

        if(!$stmt){
            throw new Exception($this->db->error);
        }

        $stmt->bind_param("iisi", $order_id, $customer_id, $invoice_no, $created_by);
        $stmt->execute();

        $invoice_id = $stmt->insert_id;
        $stmt->close();

        // 2️⃣ Get Sales Order Items
        $itemsStmt = $this->db->prepare("
            SELECT product_id, quantity, price
            FROM sales_order_items
            WHERE order_id = ?
        ");

        $itemsStmt->bind_param("i", $order_id);
        $itemsStmt->execute();
        $result = $itemsStmt->get_result();

        $total_invoice = 0;

        while($item = $result->fetch_assoc()) {

            $qty   = $item['quantity'];
            $price = $item['price'];
            $line_total = $qty * $price;

            // 3️⃣ Insert Invoice Items
            $insertItem = $this->db->prepare("
                INSERT INTO invoice_items
                (invoice_id, product_id, qty, price, total)
                VALUES (?, ?, ?, ?, ?)
            ");

            $insertItem->bind_param(
                "iiidd",
                $invoice_id,
                $item['product_id'],
                $qty,
                $price,
                $line_total
            );

            $insertItem->execute();
            $insertItem->close();

            // 4️⃣ Stock OUT Movement (NOW INSIDE LOOP ✅)
            $stockStmt = $this->db->prepare("
                INSERT INTO stock_movements
                (product_id, warehouse_id, type, quantity, reference_no, note)
                VALUES (?, 1, 'OUT', ?, ?, ?)
            ");

            $note = "Invoice Generated";

            $stockStmt->bind_param(
                "idss",
                $item['product_id'],
                $qty,
                $invoice_no,
                $note
            );

            $stockStmt->execute();
            $stockStmt->close();

            $total_invoice += $line_total;
        }

        $itemsStmt->close();

        // 5️⃣ Update Invoice Total
        $updateTotal = $this->db->prepare("
            UPDATE invoices SET total = ? WHERE id = ?
        ");

        $updateTotal->bind_param("di", $total_invoice, $invoice_id);
        $updateTotal->execute();
        $updateTotal->close();

        // 6️⃣ Update Sales Order Status
        $updateOrder = $this->db->prepare("
            UPDATE sales_orders SET status = 'Invoiced' WHERE id = ?
        ");

        $updateOrder->bind_param("i", $order_id);
        $updateOrder->execute();
        $updateOrder->close();

        // 7️⃣ Commit
        $this->db->commit();

        return $invoice_id;

    } catch(Exception $e) {

        $this->db->rollback();
        die("Invoice Creation Failed: " . $e->getMessage());
    }
}
	
	
	
	// Get invoice with customer info
public function getInvoiceById($id){
    $stmt = $this->db->prepare("
        SELECT i.*, c.name as customer_name, c.phone, c.address
        FROM invoices i
        LEFT JOIN customers c ON i.customer_id = c.id
        WHERE i.id = ?
    ");
    $stmt->bind_param("i", $id);
    $stmt->execute();
    return $stmt->get_result()->fetch_assoc();
}

// Get invoice items
public function getInvoiceItems($invoice_id){
    $stmt = $this->db->prepare("
        SELECT ii.*, p.name as product_name, p.sku
        FROM invoice_items ii
        LEFT JOIN products p ON ii.product_id = p.id
        WHERE ii.invoice_id = ?
    ");
    $stmt->bind_param("i", $invoice_id);
    $stmt->execute();
    return $stmt->get_result();
}
	
	
}