<?php
declare(strict_types=1);

namespace App\Models;

final class Activity extends BaseModel
{
    protected static string $table = 'activities';
    protected static bool $softDelete = false;

    public static function timeline(string $type, int $id, int $limit = 50): array
    {
        return static::db()->fetchAll(
            "SELECT a.*, CONCAT(u.first_name,' ',u.last_name) AS owner_name
             FROM activities a
             LEFT JOIN users u ON u.id = a.owner_id
             WHERE a.tenant_id = :tid AND a.related_type = :rt AND a.related_id = :rid
             ORDER BY a.activity_at DESC, a.id DESC
             LIMIT $limit",
            ['tid' => static::tenantId(), 'rt' => $type, 'rid' => $id]
        );
    }

    public static function recent(?int $ownerId = null, int $limit = 8): array
    {
        return self::listForHome($ownerId, $limit, [], false);
    }

    public static function forHome(?int $ownerId, int $limit = 5): array
    {
        $plan = [
            ['email', 'today'],
            ['call', 'today'],
            ['email', 'yesterday'],
            ['meeting', 'yesterday'],
            ['note', 'older'],
            ['call', 'older'],
            ['visit', 'older'],
            ['meeting', 'older'],
            ['email', 'older'],
        ];
        $picked = [];
        $ids = [];
        foreach ($plan as [$type, $when]) {
            if (count($picked) >= $limit) {
                break;
            }
            $row = self::oneForHome($ownerId, $type, $when, $ids);
            if ($row) {
                $picked[] = $row;
                $ids[] = (int) $row['id'];
            }
        }
        if (count($picked) >= $limit) {
            $picked = array_slice($picked, 0, $limit);
        } else {
            $picked = array_merge($picked, self::listForHome($ownerId, $limit - count($picked), $ids, true));
        }
        usort($picked, static function (array $a, array $b): int {
            return strcmp((string) $b['activity_at'], (string) $a['activity_at']);
        });
        return $picked;
    }

    /** @param list<int> $excludeIds */
    private static function oneForHome(?int $ownerId, string $type, string $when, array $excludeIds): ?array
    {
        $rows = self::queryHome($ownerId, $type, $when, $excludeIds, true, 1);
        return $rows[0] ?? null;
    }

    /** @param list<int> $excludeIds */
    private static function listForHome(?int $ownerId, int $limit, array $excludeIds, bool $skipFiller = true): array
    {
        if ($limit < 1) {
            return [];
        }
        return self::queryHome($ownerId, null, 'any', $excludeIds, $skipFiller, $limit);
    }

    /** @param list<int> $excludeIds */
    private static function queryHome(?int $ownerId, ?string $type, string $when, array $excludeIds, bool $skipFiller, int $limit): array
    {
        $sql = "SELECT a.*, CONCAT(u.first_name,' ',u.last_name) AS owner_name,
                       COALESCE(
                         acc.name,
                         CONCAT(ct.first_name,' ',ct.last_name),
                         CONCAT(ld.first_name,' ',ld.last_name),
                         opp.name
                       ) AS related_name
                FROM activities a
                LEFT JOIN users u ON u.id = a.owner_id
                LEFT JOIN accounts acc ON a.related_type = 'account' AND acc.id = a.related_id
                LEFT JOIN contacts ct ON a.related_type = 'contact' AND ct.id = a.related_id
                LEFT JOIN leads ld ON a.related_type = 'lead' AND ld.id = a.related_id
                LEFT JOIN opportunities opp ON a.related_type = 'opportunity' AND opp.id = a.related_id
                WHERE a.tenant_id = :tid";
        $params = ['tid' => static::tenantId()];
        if ($ownerId) {
            $sql .= ' AND a.owner_id = :oid';
            $params['oid'] = $ownerId;
        }
        if ($type) {
            $sql .= ' AND a.type = :typ';
            $params['typ'] = $type;
        }
        if ($skipFiller) {
            $sql .= " AND (a.body IS NULL OR a.body NOT LIKE 'Logged from demo seed%')";
        }
        if ($when === 'today') {
            $sql .= ' AND DATE(a.activity_at) = CURDATE()';
        } elseif ($when === 'yesterday') {
            $sql .= ' AND DATE(a.activity_at) = DATE_SUB(CURDATE(), INTERVAL 1 DAY)';
        } elseif ($when === 'older') {
            $sql .= ' AND DATE(a.activity_at) < DATE_SUB(CURDATE(), INTERVAL 1 DAY)';
        }
        if ($excludeIds) {
            $sql .= ' AND a.id NOT IN (' . implode(',', array_map('intval', $excludeIds)) . ')';
        }
        $sql .= ' ORDER BY a.activity_at DESC, a.id DESC LIMIT ' . (int) $limit;
        return static::db()->fetchAll($sql, $params);
    }

    public static function monthCounts(?int $ownerId = null): array
    {
        $sql = "SELECT DATE(activity_at) AS d, COUNT(*) AS c
                FROM activities
                WHERE tenant_id = :tid AND activity_at >= DATE_FORMAT(CURDATE(), '%Y-%m-01')";
        $params = ['tid' => static::tenantId()];
        if ($ownerId) {
            $sql .= ' AND owner_id = :oid';
            $params['oid'] = $ownerId;
        }
        $sql .= ' GROUP BY DATE(activity_at) ORDER BY d';
        return static::db()->fetchAll($sql, $params);
    }

    public static function typeCountsThisMonth(?int $ownerId = null): array
    {
        $sql = "SELECT type, COUNT(*) AS c FROM activities
                WHERE tenant_id = :tid AND activity_at >= DATE_FORMAT(CURDATE(), '%Y-%m-01')";
        $params = ['tid' => static::tenantId()];
        if ($ownerId) {
            $sql .= ' AND owner_id = :oid';
            $params['oid'] = $ownerId;
        }
        $sql .= ' GROUP BY type';
        $rows = static::db()->fetchAll($sql, $params);
        $out = [];
        foreach ($rows as $r) {
            $out[$r['type']] = (int) $r['c'];
        }
        return $out;
    }

    public static function typeCountsThisMonthBeforeToday(?int $ownerId = null): array
    {
        $sql = "SELECT type, COUNT(*) AS c FROM activities
                WHERE tenant_id = :tid AND activity_at >= DATE_FORMAT(CURDATE(), '%Y-%m-01') AND activity_at < CURDATE()";
        $params = ['tid' => static::tenantId()];
        if ($ownerId) {
            $sql .= ' AND owner_id = :oid';
            $params['oid'] = $ownerId;
        }
        $sql .= ' GROUP BY type';
        $rows = static::db()->fetchAll($sql, $params);
        $out = [];
        foreach ($rows as $r) {
            $out[$r['type']] = (int) $r['c'];
        }
        return $out;
    }
}
