<?php
declare(strict_types=1);

namespace App\Models;

final class Task extends BaseModel
{
    protected static string $table = 'tasks';
    protected static bool $softDelete = true;

    public static function forHome(?int $ownerId, int $limit = 6): array
    {
        return self::forHomeDue($ownerId, $limit);
    }

    /** Today and overdue open tasks only. */
    public static function forHomeDue(?int $ownerId, int $limit = 8): array
    {
        $todayN = (int) ceil($limit / 2);
        $overdueN = $limit - $todayN;
        $rows = array_merge(
            self::homeBucket($ownerId, 'today', $todayN),
            self::homeBucket($ownerId, 'overdue', $overdueN)
        );
        if (count($rows) >= $limit) {
            return array_slice($rows, 0, $limit);
        }
        $ids = array_map(static fn (array $r): int => (int) $r['id'], $rows);
        $need = $limit - count($rows);
        $fill = self::homeBucket($ownerId, 'today', $need, $ids);
        if (count($fill) < $need) {
            $ids = array_merge($ids, array_map(static fn (array $r): int => (int) $r['id'], $fill));
            $fill = array_merge($fill, self::homeBucket($ownerId, 'overdue', $need - count($fill), $ids));
        }
        return array_merge($rows, $fill);
    }

    /** @param list<int> $excludeIds */
    private static function homeBucket(?int $ownerId, string $bucket, int $limit, array $excludeIds = []): array
    {
        if ($limit < 1) {
            return [];
        }
        $sql = "SELECT t.*,
                       CONCAT(u.first_name, ' ', u.last_name) AS owner_name,
                       COALESCE(
                         a.name,
                         CONCAT(c.first_name, ' ', c.last_name),
                         CONCAT(l.first_name, ' ', l.last_name),
                         o.name
                       ) AS related_name
                FROM tasks t
                LEFT JOIN users u ON u.id = t.owner_id
                LEFT JOIN accounts a ON t.related_type = 'account' AND a.id = t.related_id
                LEFT JOIN contacts c ON t.related_type = 'contact' AND c.id = t.related_id
                LEFT JOIN leads l ON t.related_type = 'lead' AND l.id = t.related_id
                LEFT JOIN opportunities o ON t.related_type = 'opportunity' AND o.id = t.related_id
                WHERE t.tenant_id = :tid AND t.deleted_at IS NULL AND t.status = 'open'";
        $params = ['tid' => static::tenantId()];
        if ($ownerId) {
            $sql .= ' AND t.owner_id = :oid';
            $params['oid'] = $ownerId;
        }
        if ($excludeIds) {
            $sql .= ' AND t.id NOT IN (' . implode(',', array_map('intval', $excludeIds)) . ')';
        }
        if ($bucket === 'overdue') {
            $sql .= ' AND t.due_at IS NOT NULL AND DATE(t.due_at) < CURDATE() ORDER BY t.due_at ASC';
        } elseif ($bucket === 'today') {
            $sql .= ' AND t.due_at IS NOT NULL AND DATE(t.due_at) = CURDATE() ORDER BY t.due_at ASC';
        } elseif ($bucket === 'later') {
            $sql .= ' AND (t.due_at IS NULL OR DATE(t.due_at) > CURDATE()) ORDER BY t.due_at IS NULL, t.due_at ASC';
        } else {
            $sql .= ' ORDER BY t.due_at IS NULL, t.due_at ASC';
        }
        $sql .= ' LIMIT ' . (int) $limit;
        return static::db()->fetchAll($sql, $params);
    }

    public static function overdueCount(?int $ownerId = null): int
    {
        $sql = "SELECT COUNT(*) FROM tasks WHERE tenant_id = :tid AND deleted_at IS NULL AND status = 'open' AND due_at IS NOT NULL AND due_at < CURDATE()";
        $params = ['tid' => static::tenantId()];
        if ($ownerId) {
            $sql .= ' AND owner_id = :oid';
            $params['oid'] = $ownerId;
        }
        return (int) static::db()->fetchColumn($sql, $params);
    }

    public static function dueTodayCount(?int $ownerId = null): int
    {
        $sql = "SELECT COUNT(*) FROM tasks WHERE tenant_id = :tid AND deleted_at IS NULL AND status = 'open' AND due_at IS NOT NULL AND DATE(due_at) = CURDATE()";
        $params = ['tid' => static::tenantId()];
        if ($ownerId) {
            $sql .= ' AND owner_id = :oid';
            $params['oid'] = $ownerId;
        }
        return (int) static::db()->fetchColumn($sql, $params);
    }

    public static function dueOpenCount(?int $ownerId = null, bool $includeToday = true): int
    {
        $sql = "SELECT COUNT(*) FROM tasks WHERE tenant_id = :tid AND deleted_at IS NULL AND status = 'open' AND due_at IS NOT NULL";
        $params = ['tid' => static::tenantId()];
        $sql .= $includeToday ? ' AND DATE(due_at) <= CURDATE()' : ' AND DATE(due_at) < CURDATE()';
        if ($ownerId) {
            $sql .= ' AND owner_id = :oid';
            $params['oid'] = $ownerId;
        }
        return (int) static::db()->fetchColumn($sql, $params);
    }

    /** @return list<string> */
    public static function relatedTabs(): array
    {
        return ['all', 'leads', 'opportunities', 'others'];
    }

    public static function relatedClause(string $related): string
    {
        return match ($related) {
            'leads' => "t.related_type = 'lead'",
            'opportunities' => "t.related_type = 'opportunity'",
            'others' => "(t.related_type IS NULL OR t.related_type = '' OR t.related_type NOT IN ('lead','opportunity'))",
            default => '1=1',
        };
    }

    public static function whenTabs(): array
    {
        return ['all', 'today', 'upcoming', 'overdue', 'this_week', 'this_month', 'last_week', 'last_month'];
    }

    public static function whenClause(string $when): string
    {
        return match ($when) {
            'today' => 't.due_at IS NOT NULL AND DATE(t.due_at) = CURDATE()',
            'upcoming' => 't.due_at IS NOT NULL AND DATE(t.due_at) > CURDATE()',
            'overdue' => "t.due_at IS NOT NULL AND DATE(t.due_at) < CURDATE() AND t.status = 'open'",
            'this_week' => 't.due_at IS NOT NULL
                AND DATE(t.due_at) >= DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY)
                AND DATE(t.due_at) < DATE_ADD(DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY), INTERVAL 7 DAY)',
            'this_month' => "t.due_at IS NOT NULL
                AND DATE(t.due_at) >= DATE_FORMAT(CURDATE(), '%Y-%m-01')
                AND DATE(t.due_at) < DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01')",
            'last_week' => 't.due_at IS NOT NULL
                AND DATE(t.due_at) >= DATE_SUB(DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY), INTERVAL 7 DAY)
                AND DATE(t.due_at) < DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY)',
            'last_month' => "t.due_at IS NOT NULL
                AND DATE(t.due_at) >= DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01')
                AND DATE(t.due_at) < DATE_FORMAT(CURDATE(), '%Y-%m-01')",
            default => '1=1',
        };
    }

    public static function paginate(string $q, string $status, int $page, int $perPage, ?int $ownerId = null, string $related = 'all', string $when = 'all'): array
    {
        $related = in_array($related, self::relatedTabs(), true) ? $related : 'all';
        $when = in_array($when, self::whenTabs(), true) ? $when : 'all';
        $where = 't.tenant_id = :tid AND t.deleted_at IS NULL AND ' . self::relatedClause($related) . ' AND ' . self::whenClause($when);
        $params = ['tid' => static::tenantId()];
        if ($q !== '') {
            $where .= ' AND t.title LIKE :q';
            $params['q'] = '%' . $q . '%';
        }
        if ($status !== '' && $status !== 'all') {
            $where .= ' AND t.status = :st';
            $params['st'] = $status;
        }
        if ($ownerId) {
            $where .= ' AND t.owner_id = :oid';
            $params['oid'] = $ownerId;
        }
        $total = (int) static::db()->fetchColumn("SELECT COUNT(*) FROM tasks t WHERE $where", $params);
        $offset = ($page - 1) * $perPage;
        $rows = static::db()->fetchAll(
            "SELECT t.*, CONCAT(u.first_name,' ',u.last_name) AS owner_name,
                    COALESCE(
                      a.name,
                      CONCAT(c.first_name, ' ', c.last_name),
                      CONCAT(l.first_name, ' ', l.last_name),
                      o.name
                    ) AS related_name
             FROM tasks t
             LEFT JOIN users u ON u.id = t.owner_id
             LEFT JOIN accounts a ON t.related_type = 'account' AND a.id = t.related_id
             LEFT JOIN contacts c ON t.related_type = 'contact' AND c.id = t.related_id
             LEFT JOIN leads l ON t.related_type = 'lead' AND l.id = t.related_id
             LEFT JOIN opportunities o ON t.related_type = 'opportunity' AND o.id = t.related_id
             WHERE $where
             ORDER BY t.due_at IS NULL, t.due_at ASC, FIELD(t.status, 'open', 'completed', 'cancelled'), t.id DESC
             LIMIT $perPage OFFSET $offset",
            $params
        );
        return ['rows' => $rows, 'total' => $total];
    }

    public static function boardCounts(?int $ownerId = null, string $related = 'all'): array
    {
        $related = in_array($related, self::relatedTabs(), true) ? $related : 'all';
        $sql = "SELECT
                    SUM(t.status = 'open') AS open_n,
                    SUM(t.status = 'completed') AS completed_n,
                    SUM(t.status = 'cancelled') AS cancelled_n,
                    SUM(t.status = 'open' AND t.due_at IS NOT NULL AND DATE(t.due_at) < CURDATE()) AS overdue_n,
                    SUM(t.status = 'open' AND t.due_at IS NOT NULL AND DATE(t.due_at) = CURDATE()) AS today_n
                FROM tasks t
                WHERE t.tenant_id = :tid AND t.deleted_at IS NULL AND " . self::relatedClause($related);
        $params = ['tid' => static::tenantId()];
        if ($ownerId) {
            $sql .= ' AND t.owner_id = :oid';
            $params['oid'] = $ownerId;
        }
        $row = static::db()->fetch($sql, $params) ?: [];
        return [
            'open' => (int) ($row['open_n'] ?? 0),
            'completed' => (int) ($row['completed_n'] ?? 0),
            'cancelled' => (int) ($row['cancelled_n'] ?? 0),
            'overdue' => (int) ($row['overdue_n'] ?? 0),
            'today' => (int) ($row['today_n'] ?? 0),
            'all' => (int) ($row['open_n'] ?? 0) + (int) ($row['completed_n'] ?? 0) + (int) ($row['cancelled_n'] ?? 0),
        ];
    }

    /** @return array{leads: int, opportunities: int, others: int} */
    public static function relatedCounts(?int $ownerId = null): array
    {
        $sql = "SELECT
                    COALESCE(SUM(related_type = 'lead'), 0) AS leads,
                    COALESCE(SUM(related_type = 'opportunity'), 0) AS opportunities,
                    COALESCE(SUM(related_type IS NULL OR related_type = '' OR related_type NOT IN ('lead','opportunity')), 0) AS others
                FROM tasks
                WHERE tenant_id = :tid AND deleted_at IS NULL";
        $params = ['tid' => static::tenantId()];
        if ($ownerId) {
            $sql .= ' AND owner_id = :oid';
            $params['oid'] = $ownerId;
        }
        $row = static::db()->fetch($sql, $params) ?: [];
        return [
            'leads' => (int) ($row['leads'] ?? 0),
            'opportunities' => (int) ($row['opportunities'] ?? 0),
            'others' => (int) ($row['others'] ?? 0),
        ];
    }

    public static function setStatus(int $id, string $status): void
    {
        self::update($id, [
            'status' => $status,
            'completed_at' => $status === 'completed' ? date('Y-m-d H:i:s') : null,
        ]);
    }

    public static function forRelated(string $type, int $id): array
    {
        return static::db()->fetchAll(
            "SELECT t.*, CONCAT(u.first_name,' ',u.last_name) AS owner_name
             FROM tasks t LEFT JOIN users u ON u.id = t.owner_id
             WHERE t.tenant_id = :tid AND t.related_type = :rt AND t.related_id = :rid AND t.deleted_at IS NULL
             ORDER BY t.due_at IS NULL, t.due_at ASC",
            ['tid' => static::tenantId(), 'rt' => $type, 'rid' => $id]
        );
    }
}
