<?php
namespace Modules\Suppliers\Models;

use Core\Model;

class SupplierModel extends Model {
    protected $table = 'suppliers';

    private const STATUS_EXPR = "IF(s.is_active = 0, 'suspended', IF(s.is_approved = 1, 'approved', 'pending'))";

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

        return $this->query(
            "SELECT s.*,
                    s.supplier_code AS code,
                    " . self::STATUS_EXPR . " AS status,
                    s.contact_person_name AS contact_name,
                    s.contact_person_phone AS contact_phone,
                    s.contact_person_email AS contact_email,
                    o.name AS org_name
             FROM suppliers s
             LEFT JOIN organizations o ON s.org_id = o.id
             WHERE {$whereSql}
             ORDER BY s.created_at DESC
             LIMIT {$perPage} OFFSET {$offset}",
            $params
        );
    }

    public function countForOrg(?int $orgId, array $filters): int {
        [$whereSql, $params] = $this->buildFilters($orgId, $filters);
        $result = $this->queryOne("SELECT COUNT(*) as cnt FROM suppliers s WHERE {$whereSql}", $params);
        return (int)($result['cnt'] ?? 0);
    }

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

        if (!empty($filters['search'])) {
            $where[] = '(s.company_name LIKE ? OR s.supplier_code LIKE ? OR s.contact_person_email LIKE ?)';
            $s = '%' . $filters['search'] . '%';
            $params[] = $s;
            $params[] = $s;
            $params[] = $s;
        }
        if (!empty($filters['status'])) {
            $where[] = self::STATUS_EXPR . ' = ?';
            $params[] = $filters['status'];
        }

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

    public function getWithDetails(int $id): ?array {
        $supplier = $this->queryOne(
            "SELECT s.*,
                    s.supplier_code AS code,
                    " . self::STATUS_EXPR . " AS status,
                    s.contact_person_name AS contact_name,
                    s.contact_person_phone AS contact_phone,
                    s.contact_person_email AS contact_email
             FROM suppliers s WHERE s.id = ?",
            [$id]
        );
        return $supplier ?: null;
    }

    public function getDocuments(int $supplierId): array {
        return $this->query(
            "SELECT id, document_type AS type, document_name AS name, file_path, file_size AS size, created_at
             FROM supplier_documents WHERE supplier_id = ? ORDER BY created_at DESC",
            [$supplierId]
        );
    }

    public function getUsers(int $supplierId): array {
        return $this->query(
            "SELECT u.id, CONCAT(u.first_name, ' ', u.last_name) AS name, u.email, u.is_active,
                    IF(u.is_active = 1, 'active', 'inactive') AS status, su.is_admin
             FROM supplier_users su
             INNER JOIN users u ON su.user_id = u.id
             WHERE su.supplier_id = ?
             ORDER BY su.is_admin DESC, u.first_name",
            [$supplierId]
        );
    }

    public function getStats(?int $orgId): array {
        $where = $orgId !== null ? 'WHERE org_id = ?' : '';
        $params = $orgId !== null ? [$orgId] : [];
        $row = $this->queryOne(
            "SELECT COUNT(*) AS total,
                    SUM(IF(is_approved = 1 AND is_active = 1, 1, 0)) AS approved,
                    SUM(IF(is_approved = 0 AND is_active = 1, 1, 0)) AS pending,
                    SUM(IF(is_active = 0, 1, 0)) AS suspended
             FROM suppliers {$where}",
            $params
        );
        return [
            'total' => (int)($row['total'] ?? 0),
            'approved' => (int)($row['approved'] ?? 0),
            'pending' => (int)($row['pending'] ?? 0),
            'suspended' => (int)($row['suspended'] ?? 0),
        ];
    }

    public function getSupplierStats(int $supplierId): array {
        $projects = $this->queryOne(
            "SELECT COUNT(DISTINCT project_id) AS total FROM invoices WHERE supplier_id = ?",
            [$supplierId]
        );
        $invoiced = $this->queryOne(
            "SELECT COALESCE(SUM(total_amount), 0) AS total FROM invoices WHERE supplier_id = ? AND status = 'paid'",
            [$supplierId]
        );
        $activeUsers = $this->queryOne(
            "SELECT COUNT(*) AS cnt FROM supplier_users su INNER JOIN users u ON su.user_id = u.id
             WHERE su.supplier_id = ? AND u.is_active = 1",
            [$supplierId]
        );
        return [
            'total_projects' => (int)($projects['total'] ?? 0),
            'accepted_projects' => (int)($projects['total'] ?? 0),
            'total_invoiced' => formatCurrency((float)($invoiced['total'] ?? 0)),
            'active_users' => (int)($activeUsers['cnt'] ?? 0),
        ];
    }

    public function getInvoices(int $supplierId): array {
        return $this->query(
            "SELECT i.id, i.invoice_number AS number, i.invoice_date AS date, i.total_amount AS total, i.status,
                    p.name AS project_name
             FROM invoices i
             INNER JOIN projects p ON i.project_id = p.id
             WHERE i.supplier_id = ?
             ORDER BY i.invoice_date DESC",
            [$supplierId]
        );
    }

    public function generateSupplierCode(): string {
        do {
            $code = generateCode('SUP');
            $exists = $this->findBy('supplier_code', $code);
        } while ($exists);
        return $code;
    }

    public function getSupplierByUserId(int $userId): ?array {
        $result = $this->queryOne(
            "SELECT s.* FROM suppliers s INNER JOIN supplier_users su ON su.supplier_id = s.id WHERE su.user_id = ?",
            [$userId]
        );
        return $result ?: null;
    }
}
