-- ======================================================
-- SQL QUERIES TO CHECK REGISTERED USERS
-- ======================================================

-- 1. BASIC QUERY: All Registered Users (excluding deleted)
SELECT 
    id,
    phone_number,
    name,
    country_code,
    user_type,
    active_role,
    is_active,
    created_at,
    last_login_at
FROM users
WHERE deleted = FALSE
ORDER BY created_at DESC;

-- 2. DETAILED QUERY: All User Information
SELECT 
    id,
    phone_number,
    name,
    country_code,
    user_type,
    active_role,
    roles,
    is_active,
    rating_average,
    rating_count,
    subscription_plan_id,
    subscription_expires_at,
    created_at,
    updated_at,
    last_login_at
FROM users
WHERE deleted = FALSE
ORDER BY created_at DESC;

-- 3. ACTIVE USERS ONLY
SELECT 
    id,
    phone_number,
    name,
    country_code,
    user_type,
    active_role,
    created_at,
    last_login_at
FROM users
WHERE deleted = FALSE 
  AND is_active = TRUE
ORDER BY last_login_at DESC NULLS LAST;

-- 4. USER COUNT SUMMARY
SELECT 
    COUNT(*) as total_users,
    COUNT(*) FILTER (WHERE is_active = TRUE) as active_users,
    COUNT(*) FILTER (WHERE is_active = FALSE) as inactive_users,
    COUNT(*) FILTER (WHERE deleted = TRUE) as deleted_users,
    COUNT(*) FILTER (WHERE subscription_plan_id IS NOT NULL) as subscribed_users
FROM users;

-- 5. RECENT REGISTRATIONS (Last 30 days)
SELECT 
    id,
    phone_number,
    name,
    country_code,
    user_type,
    active_role,
    created_at
FROM users
WHERE deleted = FALSE
  AND created_at >= NOW() - INTERVAL '30 days'
ORDER BY created_at DESC;

-- 6. USERS WITH SUBSCRIPTION INFO
SELECT 
    u.id,
    u.phone_number,
    u.name,
    u.country_code,
    sp.name as subscription_plan_name,
    u.subscription_expires_at,
    CASE 
        WHEN u.subscription_expires_at > NOW() THEN 'Active'
        ELSE 'Expired'
    END as subscription_status
FROM users u
LEFT JOIN subscription_plans sp ON u.subscription_plan_id = sp.id
WHERE u.deleted = FALSE
ORDER BY u.created_at DESC;

-- 7. SEARCH USER BY PHONE NUMBER
-- Replace 'YOUR_PHONE_NUMBER' with the actual phone number
SELECT 
    id,
    phone_number,
    name,
    country_code,
    user_type,
    active_role,
    is_active,
    created_at,
    last_login_at
FROM users
WHERE phone_number = 'YOUR_PHONE_NUMBER'
  AND deleted = FALSE;