<?php
namespace Modules\Contractors\Models;

use Core\Model;

class ContractorModel extends Model {
    protected $table = 'contractors';

    private const STATUS_EXPR = "IF(c.is_active = 0, 'suspended', IF(c.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 c.*,
                    c.contractor_code AS code,
                    " . self::STATUS_EXPR . " AS status,
                    c.contact_person_name AS contact_name,
                    c.contact_person_phone AS contact_phone,
                    c.contact_person_email AS contact_email,
                    o.name AS org_name,
                    (SELECT COUNT(*) FROM project_contractors pc WHERE pc.contractor_id = c.id AND pc.status = 'accepted') AS active_projects
             FROM contractors c
             LEFT JOIN organizations o ON c.org_id = o.id
             WHERE {$whereSql}
             ORDER BY c.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 contractors c WHERE {$whereSql}", $params);
        return (int)($result['cnt'] ?? 0);
    }

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

        if (!empty($filters['search'])) {
            $where[] = '(c.company_name LIKE ? OR c.contractor_code LIKE ? OR c.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 {
        $contractor = $this->queryOne(
            "SELECT c.*,
                    c.contractor_code AS code,
                    " . self::STATUS_EXPR . " AS status,
                    c.contact_person_name AS contact_name,
                    c.contact_person_phone AS contact_phone,
                    c.contact_person_email AS contact_email
             FROM contractors c WHERE c.id = ?",
            [$id]
        );
        return $contractor ?: null;
    }

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

    public function getUsers(int $contractorId): 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, cu.is_admin
             FROM contractor_users cu
             INNER JOIN users u ON cu.user_id = u.id
             WHERE cu.contractor_id = ?
             ORDER BY cu.is_admin DESC, u.first_name",
            [$contractorId]
        );
    }

    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 contractors {$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 getContractorStats(int $contractorId): array {
        $projects = $this->queryOne(
            "SELECT COUNT(*) AS total, SUM(IF(status = 'accepted', 1, 0)) AS accepted
             FROM project_contractors WHERE contractor_id = ?",
            [$contractorId]
        );
        $invoiced = $this->queryOne(
            "SELECT COALESCE(SUM(total_amount), 0) AS total FROM invoices WHERE contractor_id = ? AND status = 'paid'",
            [$contractorId]
        );
        $activeUsers = $this->queryOne(
            "SELECT COUNT(*) AS cnt FROM contractor_users cu INNER JOIN users u ON cu.user_id = u.id
             WHERE cu.contractor_id = ? AND u.is_active = 1",
            [$contractorId]
        );
        return [
            'total_projects' => (int)($projects['total'] ?? 0),
            'accepted_projects' => (int)($projects['accepted'] ?? 0),
            'total_invoiced' => formatCurrency((float)($invoiced['total'] ?? 0)),
            'active_users' => (int)($activeUsers['cnt'] ?? 0),
        ];
    }

    public function getProjects(int $contractorId): array {
        return $this->query(
            "SELECT p.id, p.project_code AS code, p.name, p.start_date, p.project_value AS value, pc.status AS assignment_status
             FROM project_contractors pc
             INNER JOIN projects p ON pc.project_id = p.id
             WHERE pc.contractor_id = ?
             ORDER BY p.created_at DESC",
            [$contractorId]
        );
    }

    public function getInvoices(int $contractorId): 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.contractor_id = ?
             ORDER BY i.invoice_date DESC",
            [$contractorId]
        );
    }

    public function generateContractorCode(): string {
        do {
            $code = generateCode('CTR');
            $exists = $this->findBy('contractor_code', $code);
        } while ($exists);
        return $code;
    }

    public function getContractorByUserId(int $userId): ?array {
        $result = $this->queryOne(
            "SELECT c.* FROM contractors c INNER JOIN contractor_users cu ON cu.contractor_id = c.id WHERE cu.user_id = ?",
            [$userId]
        );
        return $result ?: null;
    }
}
