<?php
class NotificationModel extends Model {
    protected static string $table = 'notifications';

    public static function unread(?int $userId = null): array {
        $tid  = static::tenantId();
        $uid  = $userId ?? Auth::userId();
        $stmt = db()->prepare(
            "SELECT * FROM notifications WHERE tenant_id=? AND is_read=0 AND (user_id=? OR user_id IS NULL)
             ORDER BY created_at DESC LIMIT 20"
        );
        $stmt->execute([$tid,$uid]);
        return $stmt->fetchAll();
    }

    public static function unreadCount(): int {
        $tid = static::tenantId();
        $uid = Auth::userId();
        $stmt= db()->prepare(
            "SELECT COUNT(*) FROM notifications WHERE tenant_id=? AND is_read=0 AND (user_id=? OR user_id IS NULL)"
        );
        $stmt->execute([$tid,$uid]);
        return (int)$stmt->fetchColumn();
    }

    public static function markRead(int $id): void {
        db()->prepare("UPDATE notifications SET is_read=1 WHERE id=? AND tenant_id=?")
             ->execute([$id,static::tenantId()]);
    }

    public static function markAllRead(): void {
        db()->prepare("UPDATE notifications SET is_read=1 WHERE tenant_id=?")
             ->execute([static::tenantId()]);
    }

    public static function add(int $tenantId, string $type, string $title, string $message, int $refId=0, string $refType=''): void {
        db()->prepare(
            "INSERT INTO notifications (tenant_id,type,title,message,reference_id,reference_type) VALUES (?,?,?,?,?,?)"
        )->execute([$tenantId,$type,$title,$message,$refId,$refType]);
    }

    public static function checkAndCreate(int $tenantId): void {
        // Low stock check
        $low = db()->prepare(
            "SELECT p.id,p.name,COALESCE(SUM(s.quantity),0) as total,p.alert_qty
             FROM products p LEFT JOIN stock s ON s.product_id=p.id AND s.tenant_id=p.tenant_id
             WHERE p.tenant_id=? AND p.is_active=1 GROUP BY p.id HAVING total <= p.alert_qty"
        );
        $low->execute([$tenantId]);
        foreach ($low->fetchAll() as $p) {
            // Check if notification already exists today
            $exists = db()->prepare(
                "SELECT id FROM notifications WHERE tenant_id=? AND type='low_stock' AND reference_id=? AND DATE(created_at)=CURDATE()"
            );
            $exists->execute([$tenantId,$p['id']]);
            if (!$exists->fetch()) {
                self::add($tenantId,'low_stock','Low Stock Alert',"Product \"{$p['name']}\" has low stock ({$p['total']} left)",$p['id'],'product');
            }
        }
    }
}
