<?php
namespace Modules\Clients\Models;

use Core\Model;

class ClientModel extends Model {
    protected $table = 'clients';

    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.contact_person_name AS contact_name,
                    c.contact_person_phone AS contact_phone,
                    c.contact_person_email AS contact_email,
                    IF(c.is_active = 1, 'Active', 'Inactive') AS status,
                    o.name AS org_name
             FROM {$this->table} 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 {$this->table} 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.client_code LIKE ? OR c.contact_person_email LIKE ?)';
            $s = '%' . $filters['search'] . '%';
            $params[] = $s;
            $params[] = $s;
            $params[] = $s;
        }
        if (!empty($filters['industry'])) {
            $where[] = 'c.industry = ?';
            $params[] = $filters['industry'];
        }

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

    public function getWithProjects(int $id): ?array {
        $client = $this->find($id);
        if (!$client) {
            return null;
        }
        $client['contact_name'] = $client['contact_person_name'];
        $client['contact_phone'] = $client['contact_person_phone'];
        $client['contact_email'] = $client['contact_person_email'];
        $client['projects'] = $this->query(
            "SELECT * FROM projects WHERE client_id = ? ORDER BY created_at DESC",
            [$id]
        );
        return $client;
    }

    public function getStats(?int $orgId): array {
        $where = $orgId !== null ? 'WHERE org_id = ?' : '';
        $activeWhere = $orgId !== null ? 'WHERE org_id = ? AND is_active = 1' : 'WHERE is_active = 1';
        $params = $orgId !== null ? [$orgId] : [];
        $total = $this->queryOne("SELECT COUNT(*) as cnt FROM {$this->table} {$where}", $params);
        $active = $this->queryOne("SELECT COUNT(*) as cnt FROM {$this->table} {$activeWhere}", $params);
        return [
            'total' => (int)($total['cnt'] ?? 0),
            'active' => (int)($active['cnt'] ?? 0),
        ];
    }

    public function generateClientCode(): string {
        do {
            $code = generateCode('CLT');
            $exists = $this->findBy('client_code', $code);
        } while ($exists);
        return $code;
    }

    public function hasProjects(int $id): bool {
        $result = $this->queryOne("SELECT COUNT(*) as cnt FROM projects WHERE client_id = ?", [$id]);
        return (int)($result['cnt'] ?? 0) > 0;
    }
}
