<?php

class Invoice extends Model {

    public function addInvoice($data){
    $stmt = $this->db->prepare("
        INSERT INTO invoices (customer_id, invoice_date, due_date, discount, shipping, tax, status, notes, invoice_type)
        VALUES (:customer_id, :invoice_date, :due_date, :discount, :shipping, :tax, :status, :notes, :invoice_type)
    ");
    $success = $stmt->execute([
        ':customer_id' => $data['customer_id'],
        ':invoice_date' => $data['invoice_date'],
        ':due_date' => $data['due_date'],
        ':discount' => $data['discount'],
        ':shipping' => $data['shipping'],
        ':tax' => $data['tax'],
        ':status' => $data['status'],
        ':notes' => $data['notes'],
        ':invoice_type' => $data['invoice_type']
    ]);
    return $success ? $this->db->lastInsertId() : false;
}

    public function addInvoiceItem($data){
        $stmt = $this->db->prepare("
            INSERT INTO invoice_items (invoice_id, product_id, product_name, quantity, price, discount, total)
            VALUES (:invoice_id, :product_id, :product_name, :quantity, :price, :discount, :total)
        ");
        return $stmt->execute([
            ':invoice_id' => $data['invoice_id'],
            ':product_id' => $data['product_id'],
            ':product_name' => $data['product_name'],
            ':quantity' => $data['quantity'],
            ':price' => $data['price'],
            ':discount' => $data['discount'],
            ':total' => $data['total']
           
        ]);
    }

    public function getAllInvoices($userId, $role) {
        if ($role === 'admin') {
            $stmt = $this->db->query("SELECT * FROM invoices");
            return $stmt->fetchAll(PDO::FETCH_ASSOC);
        } else {
            $stmt = $this->db->prepare("SELECT * FROM invoices WHERE user_id = ?");
            $stmt->execute([$userId]);
            return $stmt->fetchAll(PDO::FETCH_ASSOC);
        }
    }

//     public function getInvoicesPaginated($userId, $role, $limit, $offset) {
//     if (strtolower($role) === 'admin') {
//         $stmt = $this->db->prepare("
//             SELECT invoices.*, 
//                   customers.name AS customer_name, 
//                   SUM(invoice_items.total) AS amount
//             FROM invoices
//             LEFT JOIN customers ON invoices.customer_id = customers.id
//             LEFT JOIN invoice_items ON invoices.id = invoice_items.invoice_id
//             GROUP BY invoices.id 
//             LIMIT :limit OFFSET :offset
//         ");
//     } else {
//         $stmt = $this->db->prepare("
//             SELECT invoices.*, 
//                   customers.name AS customer_name, 
//                   SUM(invoice_items.total) AS amount
//             FROM invoices
//             LEFT JOIN customers ON invoices.customer_id = customers.id
//             LEFT JOIN invoice_items ON invoices.id = invoice_items.invoice_id
//             WHERE invoices.user_id = :user_id
//             GROUP BY invoices.id
//             LIMIT :limit OFFSET :offset
//         ");
//         $stmt->bindValue(':user_id', $userId, PDO::PARAM_INT);
//     }

//     $stmt->bindValue(':limit', (int)$limit, PDO::PARAM_INT);
//     $stmt->bindValue(':offset', (int)$offset, PDO::PARAM_INT);
//     $stmt->execute();

//     return $stmt->fetchAll(PDO::FETCH_ASSOC);
// }

//     public function countInvoices($userId, $role) {
//         if (strtolower($role) === 'admin') {
//             $stmt = $this->db->query("SELECT COUNT(*) FROM invoices");
//         } else {
//             $stmt = $this->db->prepare("SELECT COUNT(*) FROM invoices WHERE user_id = ?");
//             $stmt->execute([$userId]);
//         }
//         return $stmt->fetchColumn();
//     }


public function getInvoicesPaginatedFiltered($userId, $role, $limit, $offset, $filters = []) {
    $params = [];
    $where = [];

    // Role-based access
    if (strtolower($role) !== 'admin') {
        $where[] = 'invoices.user_id = :user_id';
        $params[':user_id'] = $userId;
    }

    // Filters
    if (!empty($filters['id'])) {
        $where[] = 'invoices.id = :id';
        $params[':id'] = $filters['id'];
    }
    if (!empty($filters['customer'])) {
        $where[] = 'customers.name LIKE :customer';
        $params[':customer'] = '%' . $filters['customer'] . '%';
    }
    if (!empty($filters['status'])) {
        $where[] = 'invoices.status = :status';
        $params[':status'] = $filters['status'];
    }
    if (!empty($filters['invoice_type'])) {
        $where[] = 'invoices.invoice_type = :invoice_type';
        $params[':invoice_type'] = $filters['invoice_type'];
    }
    if (!empty($filters['due_date'])) {
        $where[] = 'invoices.due_date = :due_date';
        $params[':due_date'] = $filters['due_date'];
    }

    $whereSql = '';
    if (!empty($where)) {
        $whereSql = 'WHERE ' . implode(' AND ', $where);
    }

    $sql = "
        SELECT invoices.*, 
               customers.name AS customer_name, 
               SUM(invoice_items.total) AS amount
        FROM invoices
        LEFT JOIN customers ON invoices.customer_id = customers.id
        LEFT JOIN invoice_items ON invoices.id = invoice_items.invoice_id
        $whereSql
        GROUP BY invoices.id
        ORDER BY invoices.id DESC
        LIMIT :limit OFFSET :offset
    ";

    $stmt = $this->db->prepare($sql);
    foreach ($params as $key => $val) {
        $stmt->bindValue($key, $val);
    }
    $stmt->bindValue(':limit', (int)$limit, PDO::PARAM_INT);
    $stmt->bindValue(':offset', (int)$offset, PDO::PARAM_INT);

    $stmt->execute();
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

public function countInvoicesFiltered($userId, $role, $filters = []) {
    $params = [];
    $where = [];

    if (strtolower($role) !== 'admin') {
        $where[] = 'user_id = :user_id';
        $params[':user_id'] = $userId;
    }

    if (!empty($filters['id'])) {
        $where[] = 'id = :id';
        $params[':id'] = $filters['id'];
    }
    if (!empty($filters['customer'])) {
        $where[] = 'customer_id IN (SELECT id FROM customers WHERE name LIKE :customer)';
        $params[':customer'] = '%' . $filters['customer'] . '%';
    }
    if (!empty($filters['status'])) {
        $where[] = 'status = :status';
        $params[':status'] = $filters['status'];
    }
    if (!empty($filters['invoice_type'])) {
        $where[] = 'invoice_type = :invoice_type';
        $params[':invoice_type'] = $filters['invoice_type'];
    }
    if (!empty($filters['due_date'])) {
        $where[] = 'due_date = :due_date';
        $params[':due_date'] = $filters['due_date'];
    }

    $whereSql = '';
    if (!empty($where)) $whereSql = 'WHERE ' . implode(' AND ', $where);

    $stmt = $this->db->prepare("SELECT COUNT(*) FROM invoices $whereSql");
    foreach ($params as $key => $val) {
        $stmt->bindValue($key, $val);
    }
    $stmt->execute();
    return $stmt->fetchColumn();
}


    // public function updateInvoice($id, $data) {
    //     $stmt = $this->db->prepare("
    //         UPDATE invoices
    //         SET customer_name = ?,customer_email = ?, amount = ?, due_date = ?,invoice_date = ?, status = ?,notes = ?
    //         WHERE id = ?
    //     ");
    //     return $stmt->execute([
    //         $data['customer_name'],
    //         $data['customer_email'],
    //         $data['amount'],
    //         $data['due_date'],
    //         $data['invoice_date'],
    //         $data['status'],
    //         $data['notes'],
    //         $id
    //     ]);
    // }

    public function getInvoiceById($id) {
        $stmt = $this->db->prepare("SELECT * FROM invoices WHERE id = ?");
        $stmt->execute([$id]);
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }
    
    public function getInvoiceItemsByInvoiceId($invoiceId) {
        $stmt = $this->db->prepare("SELECT * FROM invoice_items WHERE invoice_id = ?");
        $stmt->execute([$invoiceId]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function deleteInvoice($id) {
        $stmt = $this->db->prepare("DELETE FROM invoices WHERE id = ?");
        return $stmt->execute([$id]);
    }
    
    public function updateInvoice($id, $data){
        $stmt = $this->db->prepare("
            UPDATE invoices
            SET customer_id = :customer_id,
                invoice_date = :invoice_date,
                due_date = :due_date,
                discount = :discount,
                shipping = :shipping,
                tax = :tax,
                status = :status,
                notes = :notes,
                invoice_type = :invoice_type
            WHERE id = :id
        ");
        return $stmt->execute([
            ':customer_id' => $data['customer_id'],
            ':invoice_date' => $data['invoice_date'],
            ':due_date' => $data['due_date'],
            ':discount' => $data['discount'],
            ':shipping' => $data['shipping'],
            ':tax' => $data['tax'],
            ':status' => $data['status'],
            ':notes' => $data['notes'],
            ':invoice_type' => $data['invoice_type'],
            ':id' => $id
        ]);
    }

    public function updateInvoiceItem($id, $data){
        $stmt = $this->db->prepare("
            UPDATE invoice_items
            SET product_id = :product_id,
                product_name = :product_name,
                quantity = :quantity,
                price = :price,
                discount = :discount,
                total = :total
            WHERE id = :id
        ");
        return $stmt->execute([
            ':product_id' => $data['product_id'],
            ':product_name' => $data['product_name'],
            ':quantity' => $data['quantity'],
            ':price' => $data['price'],
            ':discount' => $data['discount'],
            ':total' => $data['total'],
            ':id' => $id
        ]);
    }
    
    // In app/models/Invoice.php (inside class Invoice extends Model)
    public function deleteInvoiceItemsByInvoiceId($invoice_id){
        $stmt = $this->db->prepare("
            DELETE FROM invoice_items
            WHERE invoice_id = :invoice_id
        ");
        return $stmt->execute([':invoice_id' => (int)$invoice_id]);
    }


}
