<?php
namespace Modules\Projects\Models;

use Core\Model;

class ProjectModel extends Model
{
    protected $table = 'projects';

    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 p.*, c.company_name as client_name, cat.name as category_name, o.name as org_name,
                    (SELECT COUNT(*) FROM project_contractors pc WHERE pc.project_id = p.id AND pc.status = 'accepted') as contractor_count
             FROM {$this->table} p
             LEFT JOIN clients c ON p.client_id = c.id
             LEFT JOIN project_categories cat ON p.category_id = cat.id
             LEFT JOIN organizations o ON p.org_id = o.id
             WHERE {$whereSql}
             ORDER BY p.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} p WHERE {$whereSql}", $params);
        return (int)($result['cnt'] ?? 0);
    }

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

        if (!empty($filters['status'])) {
            $where[] = 'p.status = ?';
            $params[] = $filters['status'];
        }
        if (!empty($filters['category'])) {
            $where[] = 'p.category_id = ?';
            $params[] = $filters['category'];
        }
        if (!empty($filters['client_id'])) {
            $where[] = 'p.client_id = ?';
            $params[] = $filters['client_id'];
        }
        if (!empty($filters['search'])) {
            $where[] = '(p.name LIKE ? OR p.project_code LIKE ?)';
            $s = '%' . $filters['search'] . '%';
            $params[] = $s;
            $params[] = $s;
        }

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

    public function getWithDetails(int $id): ?array
    {
        $project = $this->queryOne(
            "SELECT p.*, c.company_name as client_name, cat.name as category_name
             FROM {$this->table} p
             LEFT JOIN clients c ON p.client_id = c.id
             LEFT JOIN project_categories cat ON p.category_id = cat.id
             WHERE p.id = ?",
            [$id]
        );
        if (!$project) {
            return null;
        }

        $project['contractors'] = $this->getAssignedContractors($id);
        $project['milestones'] = $this->getMilestones($id);
        $project['documents'] = $this->query("SELECT * FROM project_documents WHERE project_id = ? ORDER BY created_at DESC", [$id]);
        $project['managers'] = $this->query(
            "SELECT u.id, u.first_name, u.last_name, u.email FROM project_managers pm INNER JOIN users u ON pm.user_id = u.id WHERE pm.project_id = ?",
            [$id]
        );

        return $project;
    }

    public function getProjectsByContractor(int $contractorId): array
    {
        return $this->query(
            "SELECT p.*, pc.status as assignment_status FROM {$this->table} p
             INNER JOIN project_contractors pc ON p.id = pc.project_id
             WHERE pc.contractor_id = ?",
            [$contractorId]
        );
    }

    public function getProjectsByClient(int $clientId): array
    {
        return $this->findWhere(['client_id' => $clientId]);
    }

    public function getProjectStats(?int $orgId): array
    {
        $where = $orgId !== null ? 'WHERE org_id = ?' : '';
        $params = $orgId !== null ? [$orgId] : [];
        $row = $this->queryOne(
            "SELECT COUNT(*) as total,
                    SUM(IF(status = 'active', 1, 0)) as active,
                    SUM(IF(status = 'completed', 1, 0)) as completed,
                    COALESCE(SUM(project_value), 0) as total_value
             FROM {$this->table} {$where}",
            $params
        );
        return [
            'total' => (int)($row['total'] ?? 0),
            'active' => (int)($row['active'] ?? 0),
            'completed' => (int)($row['completed'] ?? 0),
            'total_value' => (float)($row['total_value'] ?? 0),
        ];
    }

    public function getContractorProjects(int $contractorId, string $status = ''): array
    {
        return $this->getProjectsByContractor($contractorId);
    }

    public function assignContractor(int $projectId, int $contractorId, int $assignedBy): bool
    {
        $exists = $this->queryOne("SELECT id FROM project_contractors WHERE project_id = ? AND contractor_id = ?", [$projectId, $contractorId]);
        if ($exists) {
            return true;
        }
        return $this->execute(
            "INSERT INTO project_contractors (project_id, contractor_id, assigned_by, status, notification_sent) VALUES (?, ?, ?, 'pending', 0)",
            [$projectId, $contractorId, $assignedBy]
        );
    }

    public function markContractorNotified(int $projectId, int $contractorId): void
    {
        $this->execute(
            "UPDATE project_contractors SET notification_sent = 1 WHERE project_id = ? AND contractor_id = ?",
            [$projectId, $contractorId]
        );
    }

    public function updateContractorStatus(int $projectId, int $contractorId, string $status, string $comment = ''): bool
    {
        return $this->execute(
            "UPDATE project_contractors SET status = ?, response_comment = ?, response_at = NOW() WHERE project_id = ? AND contractor_id = ?",
            [$status, $comment, $projectId, $contractorId]
        );
    }

    public function getAssignedContractors(int $projectId): array
    {
        return $this->query(
            "SELECT pc.*, c.company_name, c.contractor_code, c.contact_person_email
             FROM project_contractors pc INNER JOIN contractors c ON pc.contractor_id = c.id
             WHERE pc.project_id = ? ORDER BY pc.assigned_at DESC",
            [$projectId]
        );
    }

    public function assignProjectManager(int $projectId, int $userId, int $assignedBy): bool
    {
        $exists = $this->queryOne("SELECT id FROM project_managers WHERE project_id = ? AND user_id = ?", [$projectId, $userId]);
        if ($exists) {
            return true;
        }
        return $this->execute(
            "INSERT INTO project_managers (project_id, user_id, assigned_by) VALUES (?, ?, ?)",
            [$projectId, $userId, $assignedBy]
        );
    }

    public function addMilestone(array $data): int
    {
        return $this->create($data);
    }

    public function getMilestones(int $projectId): array
    {
        return $this->query("SELECT * FROM project_milestones WHERE project_id = ? ORDER BY due_date ASC", [$projectId]);
    }
}
