<?php

namespace App\Modules\Finance\Models;

use Core\Model;

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

    public function getAllForOrg(?int $orgId, array $filters, int $page, int $perPage): array
    {
        [$whereSql, $params] = $this->buildFilters($orgId, $filters);
        $offset = ($page - 1) * $perPage;

        $data = $this->query(
            "SELECT i.*, i.invoice_date AS date, i.total_amount AS total,
                    p.name as project_name,
                    COALESCE(c.company_name, s.company_name) as contractor_name,
                    o.name as org_name
             FROM invoices i
             LEFT JOIN projects p ON i.project_id = p.id
             LEFT JOIN contractors c ON i.contractor_id = c.id
             LEFT JOIN suppliers s ON i.supplier_id = s.id
             LEFT JOIN organizations o ON i.org_id = o.id
             WHERE {$whereSql}
             ORDER BY i.created_at DESC
             LIMIT {$perPage} OFFSET {$offset}",
            $params
        );

        $total = $this->queryOne("SELECT COUNT(*) as cnt FROM invoices i WHERE {$whereSql}", $params);

        return ['data' => $data, 'total' => (int)($total['cnt'] ?? 0)];
    }

    private function buildFilters(?int $orgId, array $filters): array
    {
        $where = [];
        $params = [];
        if ($orgId !== null) {
            $where[] = 'i.org_id = ?';
            $params[] = $orgId;
        }

        if (!empty($filters['project_id'])) {
            $where[] = 'i.project_id = ?';
            $params[] = $filters['project_id'];
        }
        if (!empty($filters['contractor_id'])) {
            $where[] = 'i.contractor_id = ?';
            $params[] = $filters['contractor_id'];
        }
        if (!empty($filters['status'])) {
            $where[] = 'i.status = ?';
            $params[] = $filters['status'];
        }
        if (!empty($filters['date_from'])) {
            $where[] = 'i.invoice_date >= ?';
            $params[] = $filters['date_from'];
        }
        if (!empty($filters['date_to'])) {
            $where[] = 'i.invoice_date <= ?';
            $params[] = $filters['date_to'];
        }

        return [$where ? implode(' AND ', $where) : '1=1', $params];
    }

    public function getWithDetails(int $id): ?array
    {
        $invoice = $this->queryOne(
            "SELECT i.*, i.invoice_date AS date, i.total_amount AS total,
                    p.name as project_name,
                    COALESCE(c.company_name, s.company_name) as contractor_name
             FROM invoices i
             LEFT JOIN projects p ON i.project_id = p.id
             LEFT JOIN contractors c ON i.contractor_id = c.id
             LEFT JOIN suppliers s ON i.supplier_id = s.id
             WHERE i.id = ?",
            [$id]
        );

        if ($invoice) {
            $invoice['items'] = $this->query(
                "SELECT *, quantity AS qty FROM invoice_items WHERE invoice_id = ?",
                [$id]
            );
            $invoice['attachments'] = $this->query(
                "SELECT * FROM invoice_attachments WHERE invoice_id = ?",
                [$id]
            );
        }

        return $invoice ?: null;
    }

    public function getInvoicesByContractor(int $contractorId): array
    {
        return $this->query("SELECT * FROM invoices WHERE contractor_id = ?", [$contractorId]);
    }

    public function getStats(?int $orgId): array
    {
        $where = $orgId !== null ? 'WHERE org_id = ?' : '';
        $params = $orgId !== null ? [$orgId] : [];
        $row = $this->queryOne(
            "SELECT COUNT(*) as total_invoices,
                    SUM(IF(status IN ('submitted','under_review'), 1, 0)) as pending,
                    SUM(IF(status = 'approved', 1, 0)) as approved,
                    COALESCE(SUM(IF(status = 'paid', total_amount, 0)), 0) as total_paid_amount
             FROM invoices {$where}",
            $params
        );
        return [
            'total_invoices' => (int)($row['total_invoices'] ?? 0),
            'pending' => (int)($row['pending'] ?? 0),
            'approved' => (int)($row['approved'] ?? 0),
            'total_paid_amount' => (float)($row['total_paid_amount'] ?? 0),
        ];
    }

    public function generateInvoiceNumber(int $orgId): string
    {
        do {
            $number = 'INV-' . date('Ymd') . '-' . strtoupper(substr(bin2hex(random_bytes(3)), 0, 5));
            $exists = $this->findBy('invoice_number', $number);
        } while ($exists);
        return $number;
    }

    public function addComment(int $invoiceId, string $comment, int $userId): bool
    {
        return $this->execute(
            "INSERT INTO invoice_comments (invoice_id, comment, created_by, created_at) VALUES (?, ?, ?, NOW())",
            [$invoiceId, $comment, $userId]
        );
    }

    public function getComments(int $invoiceId): array
    {
        return $this->query(
            "SELECT c.*, CONCAT(u.first_name, ' ', u.last_name) as user_name
             FROM invoice_comments c LEFT JOIN users u ON c.created_by = u.id
             WHERE c.invoice_id = ? ORDER BY c.created_at ASC",
            [$invoiceId]
        );
    }

    public function saveInvoice(array $data, array $items): int
    {
        $this->db->beginTransaction();
        try {
            $invoiceId = $this->create($data);

            foreach ($items as $item) {
                if (empty($item['description'])) {
                    continue;
                }
                $qty = (float)($item['qty'] ?? 0);
                $price = (float)($item['unit_price'] ?? 0);
                $this->execute(
                    "INSERT INTO invoice_items (invoice_id, description, quantity, unit_price, total) VALUES (?, ?, ?, ?, ?)",
                    [$invoiceId, $item['description'], $qty, $price, $qty * $price]
                );
            }

            $this->db->commit();
            return $invoiceId;
        } catch (\Throwable $e) {
            $this->db->rollBack();
            throw $e;
        }
    }

    public function updateInvoice(int $id, array $data, array $items): bool
    {
        $this->db->beginTransaction();
        try {
            $this->update($id, $data);

            $this->execute("DELETE FROM invoice_items WHERE invoice_id = ?", [$id]);
            foreach ($items as $item) {
                if (empty($item['description'])) {
                    continue;
                }
                $qty = (float)($item['qty'] ?? 0);
                $price = (float)($item['unit_price'] ?? 0);
                $this->execute(
                    "INSERT INTO invoice_items (invoice_id, description, quantity, unit_price, total) VALUES (?, ?, ?, ?, ?)",
                    [$id, $item['description'], $qty, $price, $qty * $price]
                );
            }

            $this->db->commit();
            return true;
        } catch (\Throwable $e) {
            $this->db->rollBack();
            throw $e;
        }
    }

    public function updateStatus(int $id, string $status, array $extra = []): bool
    {
        $data = array_merge(['status' => $status], $extra);
        $set = implode(', ', array_map(fn($k) => "$k = ?", array_keys($data)));
        return $this->execute("UPDATE invoices SET {$set} WHERE id = ?", [...array_values($data), $id]);
    }

    public function addAttachment(int $invoiceId, array $upload, string $mimeType, int $userId): void
    {
        $this->execute(
            "INSERT INTO invoice_attachments (invoice_id, file_name, file_path, file_size, mime_type, uploaded_by) VALUES (?, ?, ?, ?, ?, ?)",
            [$invoiceId, $upload['name'], $upload['path'], $upload['size'], $mimeType, $userId]
        );
    }
}
