<?php
declare(strict_types=1);

namespace App\Models;

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

    public static function upcoming(?int $ownerId = null, int $limit = 6): array
    {
        $sql = "SELECT m.*,
                       COALESCE(a.name, CONCAT(c.first_name,' ',c.last_name)) AS related_name
                FROM meetings m
                LEFT JOIN accounts a ON m.related_type = 'account' AND a.id = m.related_id
                LEFT JOIN contacts c ON m.related_type = 'contact' AND c.id = m.related_id
                WHERE m.tenant_id = :tid AND m.deleted_at IS NULL AND m.starts_at >= CURDATE()";
        $params = ['tid' => static::tenantId()];
        if ($ownerId) {
            $sql .= ' AND m.owner_id = :oid';
            $params['oid'] = $ownerId;
        }
        $sql .= " ORDER BY m.starts_at ASC LIMIT $limit";
        return static::db()->fetchAll($sql, $params);
    }

    public static function todayCount(?int $ownerId = null): int
    {
        $sql = "SELECT COUNT(*) FROM meetings
                WHERE tenant_id = :tid AND deleted_at IS NULL AND DATE(starts_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 inRange(string $from, string $to, ?int $ownerId = null): array
    {
        $sql = "SELECT m.*, CONCAT(u.first_name,' ',u.last_name) AS owner_name,
                       COALESCE(a.name, CONCAT(c.first_name,' ',c.last_name)) AS related_name
                FROM meetings m
                LEFT JOIN users u ON u.id = m.owner_id
                LEFT JOIN accounts a ON m.related_type = 'account' AND a.id = m.related_id
                LEFT JOIN contacts c ON m.related_type = 'contact' AND c.id = m.related_id
                WHERE m.tenant_id = :tid AND m.deleted_at IS NULL
                  AND m.starts_at < :to AND (m.ends_at IS NULL OR m.ends_at > :from)";
        $params = ['tid' => static::tenantId(), 'from' => $from, 'to' => $to];
        if ($ownerId) {
            $sql .= ' AND m.owner_id = :oid';
            $params['oid'] = $ownerId;
        }
        $sql .= ' ORDER BY m.starts_at';
        return static::db()->fetchAll($sql, $params);
    }

    public static function monthCounts(int $year, int $month, ?int $ownerId = null): array
    {
        $from = sprintf('%04d-%02d-01 00:00:00', $year, $month);
        $to = date('Y-m-d H:i:s', strtotime($from . ' +1 month'));
        $sql = "SELECT
                    COUNT(*) AS all_n,
                    SUM(DATE(starts_at) = CURDATE()) AS today_n,
                    SUM(starts_at >= NOW()) AS upcoming_n,
                    SUM(meeting_type = 'call') AS call_n,
                    SUM(meeting_type = 'visit') AS visit_n,
                    SUM(meeting_type = 'meeting') AS meeting_n
                FROM meetings
                WHERE tenant_id = :tid AND deleted_at IS NULL
                  AND starts_at >= :from AND starts_at < :to";
        $params = ['tid' => static::tenantId(), 'from' => $from, 'to' => $to];
        if ($ownerId) {
            $sql .= ' AND owner_id = :oid';
            $params['oid'] = $ownerId;
        }
        $row = static::db()->fetch($sql, $params) ?: [];
        return [
            'all' => (int) ($row['all_n'] ?? 0),
            'today' => (int) ($row['today_n'] ?? 0),
            'upcoming' => (int) ($row['upcoming_n'] ?? 0),
            'call' => (int) ($row['call_n'] ?? 0),
            'visit' => (int) ($row['visit_n'] ?? 0),
            'meeting' => (int) ($row['meeting_n'] ?? 0),
        ];
    }

    public static function detail(int $id): ?array
    {
        return static::db()->fetch(
            "SELECT m.*, CONCAT(u.first_name,' ',u.last_name) AS owner_name
             FROM meetings m LEFT JOIN users u ON u.id = m.owner_id
             WHERE m.id = :id AND m.tenant_id = :tid AND m.deleted_at IS NULL",
            ['id' => $id, 'tid' => static::tenantId()]
        );
    }
}
