<?php

declare(strict_types=1);

namespace App\Models;

class Task extends BaseModel
{
    protected string $table = 'tasks';
    protected string $primaryKey = 'id';

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

    public function getByLeadId(int $leadId): array
    {
        $items = $this->query(
            "SELECT t.*, u.name as assigned_to_name
             FROM {$this->table} t
             LEFT JOIN users u ON t.assigned_to = u.id
             WHERE t.lead_id = :lead_id
             ORDER BY t.scheduled_date DESC, t.scheduled_time DESC
             LIMIT 100",
            ['lead_id' => $leadId]
        );

        if (!empty($items)) {
            $taskIds = array_column($items, 'id');
            $placeholders = implode(',', array_fill(0, count($taskIds), '?'));
            $attStmt = $this->db->prepare(
                "SELECT id, task_id, file_path, original_name, created_at
                 FROM task_attachments WHERE task_id IN ($placeholders)
                 ORDER BY created_at ASC"
            );
            $attStmt->execute($taskIds);
            $allAtt = $attStmt->fetchAll();

            $attByTask = [];
            foreach ($allAtt as $att) {
                $attByTask[$att['task_id']][] = $att;
            }

            foreach ($items as &$item) {
                $item['attachments'] = $attByTask[$item['id']] ?? [];
            }
            unset($item);
        }

        return $items;
    }

    public function getByUserId(int $userId, array $filters = [], int $page = 1, int $perPage = 50): array
    {
        $where = ['t.assigned_to = :user_id'];
        $params = ['user_id' => $userId];

        return $this->getTasksQuery($where, $params, $filters, $page, $perPage);
    }

    public function getAll(array $filters = [], int $page = 1, int $perPage = 50): array
    {
        $where = [];
        $params = [];

        return $this->getTasksQuery($where, $params, $filters, $page, $perPage);
    }

    private function getTasksQuery(array $where, array $params, array $filters, int $page, int $perPage): array
    {

        if (!empty($filters['status'])) {
            $where[] = 't.status = :status';
            $params['status'] = $filters['status'];
        }

        if (!empty($filters['type'])) {
            $where[] = 't.type = :type';
            $params['type'] = $filters['type'];
        }

        if (!empty($filters['assigned_to'])) {
            $where[] = 't.assigned_to = :assigned_to';
            $params['assigned_to'] = (int) $filters['assigned_to'];
        }

        if (!empty($filters['date_from'])) {
            $where[] = 't.scheduled_date >= :date_from';
            $params['date_from'] = $filters['date_from'];
        }

        if (!empty($filters['date_to'])) {
            $where[] = 't.scheduled_date <= :date_to';
            $params['date_to'] = $filters['date_to'];
        }

        $perPage = max(1, $perPage);
        $whereClause = !empty($where) ? implode(' AND ', $where) : '1';
        $offset = ($page - 1) * $perPage;

        $total = (int) $this->queryOne(
            "SELECT COUNT(*) as cnt FROM {$this->table} t WHERE {$whereClause}",
            $params
        )['cnt'];

        $items = $this->query(
            "SELECT t.*, u.name as assigned_to_name,
                    l.first_name as lead_first_name, l.last_name as lead_last_name, l.company as lead_company
             FROM {$this->table} t
             LEFT JOIN users u ON t.assigned_to = u.id
             LEFT JOIN leads l ON t.lead_id = l.id
             WHERE {$whereClause}
             ORDER BY t.scheduled_date ASC
             LIMIT {$perPage} OFFSET {$offset}",
            $params
        );

        if (!empty($items)) {
            $taskIds = array_column($items, 'id');
            $placeholders = implode(',', array_fill(0, count($taskIds), '?'));
            $attStmt = $this->db->prepare(
                "SELECT id, task_id, file_path, original_name, created_at
                 FROM task_attachments WHERE task_id IN ($placeholders)
                 ORDER BY created_at ASC"
            );
            $attStmt->execute($taskIds);
            $allAtt = $attStmt->fetchAll();

            $attByTask = [];
            foreach ($allAtt as $att) {
                $attByTask[$att['task_id']][] = $att;
            }

            foreach ($items as &$item) {
                $item['attachments'] = $attByTask[$item['id']] ?? [];
            }
            unset($item);
        }

        return [
            'items' => $items,
            'total' => $total,
            'page' => $page,
            'per_page' => $perPage,
            'total_pages' => (int) ceil($total / max(1, $perPage)),
        ];
    }
}
