supabase_setup.sql

-- SQL Setup Script for DiginixIT Supabase Integration (Idempotent and Non-Destructive)
-- Paste these commands into your Supabase SQL Editor (Dashboard > SQL Editor > New query)

-- 1. DROP ONLY FUNCTIONS & OBSOLETE TABLES (Safe, does not affect main table data)
DROP TRIGGER IF EXISTS trigger_hash_password ON public.users CASCADE;
DROP TRIGGER IF EXISTS trigger_enforce_plan ON public.users CASCADE;

DROP TABLE IF EXISTS public.stats CASCADE;

DROP FUNCTION IF EXISTS public.login_user_secure(TEXT, TEXT);
DROP FUNCTION IF EXISTS public.register_user_secure(TEXT, TEXT, TEXT);
DROP FUNCTION IF EXISTS public.update_profile_secure(TEXT);
DROP FUNCTION IF EXISTS public.increment_user_visit_secure();
DROP FUNCTION IF EXISTS public.increment_user_visit_secure(TEXT);
DROP FUNCTION IF EXISTS public.increment_user_visit_secure(TEXT, TEXT);
DROP FUNCTION IF EXISTS public.increment_user_visit_secure(TEXT, UUID);
DROP FUNCTION IF EXISTS public.increment_user_visit_secure(TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT);
DROP FUNCTION IF EXISTS public.increment_user_visit_secure(TEXT, UUID, TEXT, TEXT, TEXT, TEXT, TEXT);
DROP FUNCTION IF EXISTS public.get_visit_logs_admin();
DROP FUNCTION IF EXISTS public.hash_user_password();
DROP FUNCTION IF EXISTS public.verify_session_from_jwt(UUID);
DROP FUNCTION IF EXISTS public.get_filtered_users_admin(TEXT, TIMESTAMPTZ, TIMESTAMPTZ);
DROP FUNCTION IF EXISTS public.associate_session_visits(TEXT, TEXT);
DROP FUNCTION IF EXISTS public.associate_session_visits(UUID, TEXT);
DROP FUNCTION IF EXISTS public.handle_new_auth_user() CASCADE;
DROP FUNCTION IF EXISTS public.create_user_profile_from_auth(UUID, TEXT, TEXT);
DROP FUNCTION IF EXISTS public.verify_admin_access(TEXT);
DROP FUNCTION IF EXISTS public.generate_csrf_token();
DROP FUNCTION IF EXISTS public.verify_csrf_token(TEXT);
DROP FUNCTION IF EXISTS public.verify_admin_only();
DROP FUNCTION IF EXISTS public.verify_admin_and_csrf(TEXT);
DROP FUNCTION IF EXISTS public.get_public_blogs();
DROP FUNCTION IF EXISTS public.get_public_settings();
DROP FUNCTION IF EXISTS public.get_public_stats();
DROP FUNCTION IF EXISTS public.get_all_users_admin();
DROP FUNCTION IF EXISTS public.get_all_settings_admin();
DROP FUNCTION IF EXISTS public.get_all_blogs_admin();
DROP FUNCTION IF EXISTS public.save_stats_secure(TEXT, TEXT, TEXT, TEXT, TEXT);
DROP FUNCTION IF EXISTS public.save_blog_secure(BIGINT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT);
DROP FUNCTION IF EXISTS public.save_blog_secure(BIGINT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT);
DROP FUNCTION IF EXISTS public.delete_blog_secure(BIGINT, TEXT);
DROP FUNCTION IF EXISTS public.update_user_status_secure(TEXT, TEXT);
DROP FUNCTION IF EXISTS public.delete_user_secure(TEXT, TEXT);
DROP FUNCTION IF EXISTS public.save_settings_secure(TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT);
DROP FUNCTION IF EXISTS public.migrate_legacy_users();
DROP FUNCTION IF EXISTS public.get_admin_analytics(TIMESTAMPTZ, TIMESTAMPTZ);

-- Enable pgcrypto extension for secure password hashing and random bytes
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- 2. CREATE TABLES (Only if they do not exist)

-- Settings Table (Global Config using a Key-Value design)
CREATE TABLE IF NOT EXISTS public.settings (
    key_name TEXT PRIMARY KEY,
    value_text TEXT NOT NULL
);

-- Blogs Table (Insights Articles)
CREATE TABLE IF NOT EXISTS public.blogs (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    title TEXT NOT NULL,
    summary TEXT NOT NULL,
    content TEXT NOT NULL,
    author TEXT NOT NULL,
    date DATE DEFAULT NULL,
    category TEXT NOT NULL DEFAULT 'Tech',
    status TEXT NOT NULL DEFAULT 'published'
);

-- Users Table (Custom Registered Users - Client profiles linked to Supabase Auth)
CREATE TABLE IF NOT EXISTS public.users (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    auth_user_id UUID UNIQUE DEFAULT NULL,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    password TEXT DEFAULT NULL, -- Nullable for users managed by Supabase Auth
    date TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    status TEXT NOT NULL DEFAULT 'Active',
    visits INT DEFAULT 0,
    session_token UUID DEFAULT NULL,
    session_expires_at TIMESTAMP WITH TIME ZONE DEFAULT NULL,
    role TEXT NOT NULL DEFAULT 'user'
);



-- Visit Logs Table
CREATE TABLE IF NOT EXISTS public.visit_logs (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    email TEXT,
    session_id UUID DEFAULT NULL,
    visited_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    ip_address TEXT,
    country TEXT,
    device TEXT,
    page_url TEXT,
    user_agent TEXT
);

-- Admin Users Table
CREATE TABLE IF NOT EXISTS public.admin_users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email TEXT UNIQUE NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- CSRF Tokens Table
CREATE TABLE IF NOT EXISTS public.csrf_tokens (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    token TEXT NOT NULL UNIQUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    expires_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() + INTERVAL '1 hour',
    used BOOLEAN DEFAULT FALSE
);

-- 2.5 ALTER EXISTING TABLES (For non-destructive schema updates)
-- Run this BEFORE indexes or triggers are compiled so the columns exist even if tables were already present.
ALTER TABLE public.users ADD COLUMN IF NOT EXISTS auth_user_id UUID UNIQUE DEFAULT NULL;
ALTER TABLE public.users DROP COLUMN IF EXISTS plan;
ALTER TABLE public.users ALTER COLUMN password DROP NOT NULL;
ALTER TABLE public.users ALTER COLUMN date TYPE TIMESTAMPTZ USING date::TIMESTAMPTZ;
ALTER TABLE public.users ALTER COLUMN date SET DEFAULT NOW();
ALTER TABLE public.users ADD COLUMN IF NOT EXISTS role TEXT NOT NULL DEFAULT 'user';
ALTER TABLE public.visit_logs ADD COLUMN IF NOT EXISTS session_id UUID DEFAULT NULL;
ALTER TABLE public.visit_logs ADD COLUMN IF NOT EXISTS ip_address TEXT DEFAULT NULL;
ALTER TABLE public.visit_logs ADD COLUMN IF NOT EXISTS country TEXT DEFAULT NULL;
ALTER TABLE public.visit_logs ADD COLUMN IF NOT EXISTS device TEXT DEFAULT NULL;
ALTER TABLE public.visit_logs ADD COLUMN IF NOT EXISTS page_url TEXT DEFAULT NULL;
ALTER TABLE public.visit_logs ADD COLUMN IF NOT EXISTS user_agent TEXT DEFAULT NULL;
ALTER TABLE public.blogs ADD COLUMN IF NOT EXISTS status TEXT NOT NULL DEFAULT 'published';
ALTER TABLE public.blogs DROP COLUMN IF EXISTS image;
ALTER TABLE public.blogs ALTER COLUMN date DROP NOT NULL;
ALTER TABLE public.blogs ALTER COLUMN date SET DEFAULT NULL;

-- 2.6 CREATE INDEXES
CREATE INDEX IF NOT EXISTS idx_visit_logs_visited_at ON public.visit_logs(visited_at);
CREATE INDEX IF NOT EXISTS idx_visit_logs_email ON public.visit_logs(email);
CREATE INDEX IF NOT EXISTS idx_visit_logs_lower_email ON public.visit_logs(LOWER(email));
CREATE INDEX IF NOT EXISTS idx_users_lower_email ON public.users(LOWER(email));
CREATE INDEX IF NOT EXISTS idx_visit_logs_session_id ON public.visit_logs(session_id);
CREATE INDEX IF NOT EXISTS idx_csrf_tokens_user_id ON public.csrf_tokens(user_id);
CREATE INDEX IF NOT EXISTS idx_csrf_tokens_expires ON public.csrf_tokens(expires_at);

-- NOTE: The FK constraint users_auth_user_id_fkey on public.users(auth_user_id)
-- referencing auth.users(id) has been intentionally REMOVED. Supabase's GoTrue
-- auth service fires the on_auth_user_created trigger before the auth.users row
-- is fully committed, causing a foreign key violation during signup. The
-- relationship is still maintained correctly by the handle_new_auth_user trigger
-- and the create_user_profile_from_auth RPC function.
-- Safely drop the foreign key constraint if it exists from a previous setup.
DO $$
BEGIN
    IF EXISTS (
        SELECT 1 FROM pg_constraint WHERE conname = 'users_auth_user_id_fkey'
    ) THEN
        ALTER TABLE public.users 
        DROP CONSTRAINT users_auth_user_id_fkey;
    END IF;
END;
$$;

-- 3. SEED INITIAL VALUES (Preserving existing edits)
INSERT INTO public.settings (key_name, value_text) VALUES 
('siteName', 'DIGINIXIT.'),
('contactEmail', 'contact@diginix.com'),
('contactPhone', '+92 300 7960300'),
('maintenanceMode', 'false'),
('linkedin', 'https://linkedin.com/'),
('instagram', 'https://instagram.com/'),
('twitter', 'https://twitter.com/'),
('facebook', 'https://facebook.com/'),
('youtube', 'https://youtube.com/')
ON CONFLICT (key_name) DO NOTHING;



INSERT INTO public.admin_users (email) VALUES 
('admin@diginixit.com')
ON CONFLICT (email) DO NOTHING;

-- Seed some mock anonymous visit logs if table is empty
INSERT INTO public.visit_logs (email, visited_at, ip_address, country, device, page_url, user_agent)
SELECT 
    CASE WHEN val % 5 = 0 THEN 'john@doe.com' WHEN val % 7 = 0 THEN 'sarah@connor.com' ELSE NULL END,
    NOW() - (val || ' hours')::INTERVAL,
    '203.135.' || (val % 255) || '.' || (val % 100 + 1),
    CASE WHEN val % 3 = 0 THEN 'Pakistan' WHEN val % 3 = 1 THEN 'United States' ELSE 'United Kingdom' END,
    CASE WHEN val % 4 = 0 THEN 'Mobile' WHEN val % 4 = 1 THEN 'Tablet' ELSE 'Desktop' END,
    CASE WHEN val % 4 = 0 THEN '/home' WHEN val % 4 = 1 THEN '/blog' WHEN val % 4 = 2 THEN '/article.html' ELSE '/services' END,
    'Mozilla/5.0 (Windows NT 10.0; Win64; x64)'
FROM generate_series(1, 200) AS val
WHERE NOT EXISTS (SELECT 1 FROM public.visit_logs LIMIT 1);

-- 4. DATABASE TRIGGERS FOR SECURITY AND SANITIZATION

-- Trigger function to hash user password using bcrypt (bf) for legacy fallback
CREATE OR REPLACE FUNCTION public.hash_user_password()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.password IS NOT NULL 
       AND NEW.password <> '' 
       AND NEW.password NOT LIKE '$2a$%' 
       AND NEW.password NOT LIKE '$2b$%' 
       AND (TG_OP = 'INSERT' OR (TG_OP = 'UPDATE' AND NEW.password <> OLD.password)) THEN
        NEW.password := crypt(NEW.password, gen_salt('bf', 8));
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

DROP TRIGGER IF EXISTS trigger_hash_password ON public.users;
CREATE TRIGGER trigger_hash_password
BEFORE INSERT OR UPDATE ON public.users
FOR EACH ROW
EXECUTE FUNCTION public.hash_user_password();

-- Trigger function removed (no default plan constraint needed)

-- 5. SECURE RPC FUNCTIONS (SECURITY DEFINER to bypass table RLS selectively)

-- Migration function to import existing users from public.users to auth.users (bcrypt migration)
CREATE OR REPLACE FUNCTION public.migrate_legacy_users()
RETURNS VOID AS $$
DECLARE
    r RECORD;
    v_new_uuid UUID;
BEGIN
    FOR r IN SELECT * FROM public.users WHERE auth_user_id IS NULL AND password IS NOT NULL AND password <> '' LOOP
        v_new_uuid := gen_random_uuid();
        
        IF NOT EXISTS (SELECT 1 FROM auth.users WHERE email = r.email) THEN
            INSERT INTO auth.users (
                id,
                instance_id,
                email,
                encrypted_password,
                email_confirmed_at,
                raw_app_meta_data,
                raw_user_meta_data,
                aud,
                role,
                created_at,
                updated_at,
                confirmation_token,
                recovery_token,
                email_change_token_new,
                email_change
            )
            VALUES (
                v_new_uuid,
                '00000000-0000-0000-0000-000000000000',
                r.email,
                r.password, -- existing bcrypt hash
                NOW(),
                '{"provider": "email", "providers": ["email"]}'::jsonb,
                json_build_object('full_name', r.name),
                'authenticated',
                'authenticated',
                r.date::timestamp,
                NOW(),
                '',
                '',
                '',
                ''
            );
            
            UPDATE public.users
            SET auth_user_id = v_new_uuid
            WHERE id = r.id;
        ELSE
            UPDATE public.users
            SET auth_user_id = (SELECT id FROM auth.users WHERE email = r.email)
            WHERE id = r.id;
        END IF;
    END LOOP;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Backend Session Verification using Supabase Auth JWT
CREATE OR REPLACE FUNCTION public.verify_session_from_jwt(p_user_id UUID)
RETURNS TABLE (
    id BIGINT,
    auth_user_id UUID,
    name TEXT,
    email TEXT,
    date TIMESTAMPTZ,
    status TEXT,
    visits INT
) AS $$
BEGIN
    RETURN QUERY
    SELECT u.id, u.auth_user_id, u.name, u.email, u.date, u.status, u.visits
    FROM public.users u
    WHERE u.auth_user_id = p_user_id
      AND u.status = 'Active';
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

GRANT EXECUTE ON FUNCTION public.verify_session_from_jwt(UUID) TO authenticated, anon;

-- Create User Profile from Supabase Auth
CREATE OR REPLACE FUNCTION public.create_user_profile_from_auth(
    p_auth_user_id UUID,
    p_name TEXT,
    p_email TEXT
)
RETURNS public.users AS $$
DECLARE
    v_user public.users;
BEGIN
    -- Input Validation
    IF p_email IS NULL OR p_email !~* '^[a-z0-9._%+-]+@[a-z0-9.-]+\.[a-z]{2,}$' THEN
        RAISE EXCEPTION 'Invalid email address format.';
    END IF;

    IF p_name IS NULL OR length(trim(p_name)) < 2 THEN
        RAISE EXCEPTION 'Name must be at least 2 characters long.';
    END IF;

    IF EXISTS (SELECT 1 FROM public.users WHERE LOWER(email) = LOWER(p_email)) THEN
        -- If profile already exists, link it and return it
        UPDATE public.users
        SET auth_user_id = p_auth_user_id
        WHERE LOWER(email) = LOWER(p_email)
        RETURNING * INTO v_user;
    ELSE
        -- Insert user profile
        INSERT INTO public.users (auth_user_id, name, email, password, status, date)
        VALUES (p_auth_user_id, p_name, p_email, NULL, 'Active', CURRENT_DATE)
        RETURNING * INTO v_user;
    END IF;
    
    RETURN v_user;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

GRANT EXECUTE ON FUNCTION public.create_user_profile_from_auth(UUID, TEXT, TEXT) TO authenticated, anon;

-- Verify Admin Access based on Role/Email
CREATE OR REPLACE FUNCTION public.verify_admin_access(p_user_email TEXT)
RETURNS TABLE (
    is_admin BOOLEAN,
    user_email TEXT
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        (EXISTS (SELECT 1 FROM public.users WHERE LOWER(email) = LOWER(p_user_email) AND role = 'admin'))::BOOLEAN,
        p_user_email;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

GRANT EXECUTE ON FUNCTION public.verify_admin_access(TEXT) TO authenticated, anon;

-- Handle New Auth User Profile and Admin Sync Trigger
CREATE OR REPLACE FUNCTION public.handle_new_auth_user()
RETURNS TRIGGER AS $$
DECLARE
    v_role TEXT := 'user';
BEGIN
    IF EXISTS (SELECT 1 FROM public.admin_users WHERE LOWER(email) = LOWER(NEW.email)) THEN
        v_role := 'admin';
    END IF;

    INSERT INTO public.users (auth_user_id, name, email, status, role, date)
    VALUES (
        NEW.id,
        COALESCE(NEW.raw_user_meta_data->>'full_name', 'User'),
        NEW.email,
        'Active',
        v_role,
        NOW()
    )
    ON CONFLICT (email) DO UPDATE
    SET auth_user_id = EXCLUDED.auth_user_id,
        role = v_role;
        
    RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

DROP TRIGGER IF EXISTS on_auth_user_created ON auth.users;
CREATE TRIGGER on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW EXECUTE FUNCTION public.handle_new_auth_user();

-- CSRF Token Generator
CREATE OR REPLACE FUNCTION public.generate_csrf_token()
RETURNS TABLE (
    csrf_token TEXT,
    expires_at TIMESTAMP WITH TIME ZONE
) AS $$
DECLARE
    v_token TEXT;
    v_user_id UUID;
    v_expires TIMESTAMP WITH TIME ZONE;
BEGIN
    v_user_id := auth.uid();
    IF v_user_id IS NULL THEN
        RAISE EXCEPTION 'Not authenticated';
    END IF;

    v_token := encode(gen_random_bytes(32), 'hex');
    v_expires := NOW() + INTERVAL '1 hour';

    INSERT INTO public.csrf_tokens (user_id, token, expires_at)
    VALUES (v_user_id, v_token, v_expires);

    RETURN QUERY SELECT v_token, v_expires;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

GRANT EXECUTE ON FUNCTION public.generate_csrf_token() TO authenticated;

-- CSRF Token Verification Helper
CREATE OR REPLACE FUNCTION public.verify_csrf_token(p_token TEXT)
RETURNS BOOLEAN AS $$
DECLARE
    v_user_id UUID;
    v_exists BOOLEAN;
BEGIN
    v_user_id := auth.uid();
    IF v_user_id IS NULL THEN
        RETURN FALSE;
    END IF;

    SELECT EXISTS (
        SELECT 1 FROM public.csrf_tokens
        WHERE user_id = v_user_id
          AND token = p_token
          AND expires_at > NOW()
          AND used = FALSE
    ) INTO v_exists;

    IF v_exists THEN
        UPDATE public.csrf_tokens
        SET used = TRUE
        WHERE user_id = v_user_id AND token = p_token;
    END IF;

    RETURN v_exists;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Guard: Verify Admin Role only (Read Actions)
CREATE OR REPLACE FUNCTION public.verify_admin_only()
RETURNS BOOLEAN AS $$
DECLARE
    v_role TEXT;
BEGIN
    SELECT u.role INTO v_role 
    FROM public.users u 
    WHERE u.auth_user_id = auth.uid();
    
    IF v_role IS NULL OR v_role <> 'admin' THEN
        RAISE EXCEPTION 'Admin access required';
    END IF;
    RETURN TRUE;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Guard: Verify Admin Role and Validate CSRF (Write Actions)
CREATE OR REPLACE FUNCTION public.verify_admin_and_csrf(p_csrf_token TEXT)
RETURNS BOOLEAN AS $$
BEGIN
    PERFORM public.verify_admin_only();

    IF NOT public.verify_csrf_token(p_csrf_token) THEN
        RAISE EXCEPTION 'Invalid or expired security token. Please reload the page and try again.';
    END IF;

    RETURN TRUE;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Public read-only functions (escapes direct table SELECT)
CREATE OR REPLACE FUNCTION public.get_public_blogs()
RETURNS SETOF public.blogs AS $$
BEGIN
    RETURN QUERY
    SELECT * FROM public.blogs 
    WHERE status = 'published'
    ORDER BY id DESC;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

GRANT EXECUTE ON FUNCTION public.get_public_blogs() TO anon, authenticated;

CREATE OR REPLACE FUNCTION public.get_public_settings()
RETURNS TABLE (key_name TEXT, value_text TEXT) AS $$
BEGIN
    RETURN QUERY
    SELECT s.key_name, s.value_text
    FROM public.settings s
    WHERE s.key_name NOT IN ('adminSecret', 'apiToken', 'internalKey');
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

GRANT EXECUTE ON FUNCTION public.get_public_settings() TO anon, authenticated;

CREATE OR REPLACE FUNCTION public.get_public_stats()
RETURNS TABLE (
    activeusers TEXT,
    visits INT8,
    userhistory TEXT,
    visithistory TEXT
) AS $$
DECLARE
    v_user_count INT8;
    v_total_visits INT8;
    v_user_history TEXT;
    v_visit_history TEXT;
BEGIN
    -- Count of total users
    SELECT COUNT(*) INTO v_user_count FROM public.users;
    
    -- Count of total visits
    SELECT COALESCE(SUM(u.visits), 0) + (SELECT COUNT(*) FROM public.visit_logs WHERE email IS NULL OR email = '')
    INTO v_total_visits FROM public.users u;

    -- Calculate User History (Last 6 months user signups, e.g. '5,10,12,18,20,25')
    SELECT string_agg(count_val::text, ',') INTO v_user_history
    FROM (
        SELECT 
            COALESCE(COUNT(u.id), 0) AS count_val
        FROM generate_series(
            date_trunc('month', NOW() - INTERVAL '5 months'),
            date_trunc('month', NOW()),
            '1 month'::interval
        ) AS gs(dt)
        LEFT JOIN public.users u ON u.date >= gs.dt AND u.date < gs.dt + INTERVAL '1 month'
        GROUP BY gs.dt
        ORDER BY gs.dt
    ) t;

    -- Calculate Visit History (Last 6 months visits, e.g. '10,15,30,45,60,75')
    SELECT string_agg(count_val::text, ',') INTO v_visit_history
    FROM (
        SELECT 
            COALESCE(COUNT(v.id), 0) AS count_val
        FROM generate_series(
            date_trunc('month', NOW() - INTERVAL '5 months'),
            date_trunc('month', NOW()),
            '1 month'::interval
        ) AS gs(dt)
        LEFT JOIN public.visit_logs v ON v.visited_at >= gs.dt AND v.visited_at < gs.dt + INTERVAL '1 month'
        GROUP BY gs.dt
        ORDER BY gs.dt
    ) t;

    RETURN QUERY
    SELECT 
        v_user_count::TEXT,
        v_total_visits::INT8,
        COALESCE(v_user_history, '0,0,0,0,0,0')::TEXT,
        COALESCE(v_visit_history, '0,0,0,0,0,0')::TEXT;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

GRANT EXECUTE ON FUNCTION public.get_public_stats() TO anon, authenticated;

-- Admin read-only functions (checks admin role)
CREATE OR REPLACE FUNCTION public.get_all_users_admin()
RETURNS TABLE (
    name TEXT,
    email TEXT,
    date TIMESTAMPTZ,
    status TEXT,
    visits INT
) AS $$
BEGIN
    PERFORM public.verify_admin_only();
    RETURN QUERY
    SELECT u.name, u.email, u.date, u.status, u.visits
    FROM public.users u
    ORDER BY u.id DESC;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

GRANT EXECUTE ON FUNCTION public.get_all_users_admin() TO authenticated;

-- Admin read-only function to retrieve visit logs
CREATE OR REPLACE FUNCTION public.get_visit_logs_admin()
RETURNS TABLE (
    email TEXT,
    visited_at TIMESTAMPTZ,
    ip_address TEXT,
    country TEXT,
    device TEXT,
    page_url TEXT,
    user_agent TEXT
) AS $$
BEGIN
    PERFORM public.verify_admin_only();
    RETURN QUERY
    SELECT v.email, v.visited_at, v.ip_address, v.country, v.device, v.page_url, v.user_agent
    FROM public.visit_logs v
    ORDER BY v.visited_at DESC;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

GRANT EXECUTE ON FUNCTION public.get_visit_logs_admin() TO authenticated;

CREATE OR REPLACE FUNCTION public.get_all_settings_admin()
RETURNS TABLE (key_name TEXT, value_text TEXT) AS $$
BEGIN
    PERFORM public.verify_admin_only();
    RETURN QUERY
    SELECT s.key_name, s.value_text
    FROM public.settings s;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

GRANT EXECUTE ON FUNCTION public.get_all_settings_admin() TO authenticated;

CREATE OR REPLACE FUNCTION public.get_all_blogs_admin()
RETURNS SETOF public.blogs AS $$
BEGIN
    PERFORM public.verify_admin_only();
    RETURN QUERY
    SELECT * FROM public.blogs ORDER BY id DESC;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

GRANT EXECUTE ON FUNCTION public.get_all_blogs_admin() TO authenticated;

-- Admin mutation functions (verifies admin and CSRF)

CREATE OR REPLACE FUNCTION public.save_blog_secure(
    p_id BIGINT,
    p_title TEXT,
    p_category TEXT,
    p_author TEXT,
    p_summary TEXT,
    p_content TEXT,
    p_status TEXT,
    p_csrf_token TEXT
)
RETURNS BOOLEAN AS $$
BEGIN
    PERFORM public.verify_admin_and_csrf(p_csrf_token);

    -- Strict validations
    IF p_title IS NULL OR length(trim(p_title)) < 2 THEN
        RAISE EXCEPTION 'Title must be at least 2 characters long.';
    END IF;
    IF p_author IS NULL OR length(trim(p_author)) < 2 THEN
        RAISE EXCEPTION 'Author must be at least 2 characters long.';
    END IF;

    IF p_id IS NOT NULL AND p_id > 0 THEN
        UPDATE public.blogs
        SET title = p_title,
            category = p_category,
            author = p_author,
            summary = p_summary,
            content = p_content,
            status = COALESCE(p_status, 'published'),
            date = CASE 
                WHEN COALESCE(p_status, 'published') = 'published' AND (status = 'draft' OR date IS NULL) THEN CURRENT_DATE
                WHEN COALESCE(p_status, 'published') = 'draft' THEN NULL
                ELSE date
            END
        WHERE id = p_id;
    ELSE
        INSERT INTO public.blogs (title, category, author, summary, content, date, status)
        VALUES (
            p_title, 
            p_category, 
            p_author, 
            p_summary, 
            p_content, 
            CASE WHEN COALESCE(p_status, 'published') = 'published' THEN CURRENT_DATE ELSE NULL END, 
            COALESCE(p_status, 'published')
        );
    END IF;

    RETURN TRUE;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

GRANT EXECUTE ON FUNCTION public.save_blog_secure(BIGINT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT) TO authenticated;

CREATE OR REPLACE FUNCTION public.delete_blog_secure(
    p_id BIGINT,
    p_csrf_token TEXT
)
RETURNS BOOLEAN AS $$
BEGIN
    PERFORM public.verify_admin_and_csrf(p_csrf_token);
    DELETE FROM public.blogs WHERE id = p_id;
    RETURN TRUE;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

GRANT EXECUTE ON FUNCTION public.delete_blog_secure(BIGINT, TEXT) TO authenticated;

CREATE OR REPLACE FUNCTION public.update_user_status_secure(
    p_email TEXT,
    p_csrf_token TEXT
)
RETURNS TABLE (success BOOLEAN, status TEXT) AS $$
DECLARE
    v_status TEXT;
    v_new_status TEXT;
BEGIN
    PERFORM public.verify_admin_and_csrf(p_csrf_token);

    SELECT u.status INTO v_status FROM public.users u WHERE LOWER(u.email) = LOWER(p_email);
    IF v_status IS NULL THEN
        RAISE EXCEPTION 'User not found';
    END IF;

    v_new_status := CASE WHEN v_status = 'Blocked' THEN 'Active' ELSE 'Blocked' END;

    UPDATE public.users
    SET status = v_new_status
    WHERE LOWER(email) = LOWER(p_email);

    RETURN QUERY SELECT TRUE, v_new_status;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

GRANT EXECUTE ON FUNCTION public.update_user_status_secure(TEXT, TEXT) TO authenticated;

CREATE OR REPLACE FUNCTION public.delete_user_secure(
    p_email TEXT,
    p_csrf_token TEXT
)
RETURNS BOOLEAN AS $$
BEGIN
    PERFORM public.verify_admin_and_csrf(p_csrf_token);
    DELETE FROM public.users WHERE LOWER(email) = LOWER(p_email);
    DELETE FROM public.visit_logs WHERE LOWER(email) = LOWER(p_email);
    RETURN TRUE;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

GRANT EXECUTE ON FUNCTION public.delete_user_secure(TEXT, TEXT) TO authenticated;

CREATE OR REPLACE FUNCTION public.save_settings_secure(
    p_site_name TEXT,
    p_contact_email TEXT,
    p_contact_phone TEXT,
    p_maintenance_mode TEXT,
    p_linkedin TEXT,
    p_instagram TEXT,
    p_twitter TEXT,
    p_facebook TEXT,
    p_youtube TEXT,
    p_csrf_token TEXT
)
RETURNS BOOLEAN AS $$
BEGIN
    PERFORM public.verify_admin_and_csrf(p_csrf_token);

    INSERT INTO public.settings (key_name, value_text) VALUES 
    ('siteName', p_site_name),
    ('contactEmail', p_contact_email),
    ('contactPhone', p_contact_phone),
    ('maintenanceMode', p_maintenance_mode),
    ('linkedin', p_linkedin),
    ('instagram', p_instagram),
    ('twitter', p_twitter),
    ('facebook', p_facebook),
    ('youtube', p_youtube)
    ON CONFLICT (key_name) DO UPDATE SET value_text = EXCLUDED.value_text;

    RETURN TRUE;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

GRANT EXECUTE ON FUNCTION public.save_settings_secure(TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT) TO authenticated;

-- User Profile security definer mutations (requires valid authenticated JWT session)
CREATE OR REPLACE FUNCTION public.update_profile_secure(p_name TEXT)
RETURNS TABLE (
    id BIGINT,
    name TEXT,
    email TEXT,
    date TIMESTAMPTZ,
    status TEXT,
    visits INT
) AS $$
DECLARE
    v_id BIGINT;
BEGIN
    SELECT u.id INTO v_id FROM public.users u WHERE u.auth_user_id = auth.uid();
    
    IF v_id IS NULL THEN
        RAISE EXCEPTION 'Not authenticated or profile not found';
    END IF;

    -- Input Validation
    IF p_name IS NULL OR length(trim(p_name)) < 2 THEN
        RAISE EXCEPTION 'Name must be at least 2 characters long.';
    END IF;

    UPDATE public.users
    SET name = p_name
    WHERE public.users.id = v_id;
    
    RETURN QUERY
    SELECT u.id, u.name, u.email, u.date, u.status, u.visits
    FROM public.users u
    WHERE u.id = v_id;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

GRANT EXECUTE ON FUNCTION public.update_profile_secure(TEXT) TO authenticated;

-- update_subscription function removed

-- Secure increment visit
CREATE OR REPLACE FUNCTION public.increment_user_visit_secure(
    p_email TEXT DEFAULT NULL,
    p_session_id UUID DEFAULT NULL,
    p_ip_address TEXT DEFAULT NULL,
    p_country TEXT DEFAULT NULL,
    p_device TEXT DEFAULT NULL,
    p_page_url TEXT DEFAULT NULL,
    p_user_agent TEXT DEFAULT NULL
)
RETURNS INT AS $$
DECLARE
    v_id BIGINT;
    v_visits INT := 0;
    v_status TEXT;
    v_already_tracked BOOLEAN := FALSE;
BEGIN
    IF p_session_id IS NULL THEN
        -- Avoid tracking visits with missing session IDs (prevents bot/crawlers/storage-disabled spam)
        IF p_email IS NOT NULL AND p_email <> '' THEN
            SELECT COALESCE(visits, 0) INTO v_visits
            FROM public.users
            WHERE LOWER(email) = LOWER(p_email);
        END IF;
        RETURN COALESCE(v_visits, 0);
    END IF;

    SELECT EXISTS (
        SELECT 1 FROM public.visit_logs WHERE session_id = p_session_id
    ) INTO v_already_tracked;

    -- Always log the visit in visit_logs to record multiple page views per session
    INSERT INTO public.visit_logs (email, session_id, visited_at, ip_address, country, device, page_url, user_agent)
    VALUES (p_email, p_session_id, NOW(), p_ip_address, p_country, p_device, p_page_url, p_user_agent);

    -- Only increment the user's visits count if this is the first visit of the session
    IF NOT v_already_tracked THEN
        IF p_email IS NOT NULL AND p_email <> '' THEN
            SELECT u.id, u.status INTO v_id, v_status
            FROM public.users u
            WHERE LOWER(u.email) = LOWER(p_email);
              
            IF v_id IS NOT NULL AND (v_status IS NULL OR v_status <> 'Blocked') THEN
                UPDATE public.users
                SET visits = COALESCE(visits, 0) + 1
                WHERE public.users.id = v_id
                RETURNING public.users.visits INTO v_visits;
            END IF;
        END IF;
    ELSE
        IF p_email IS NOT NULL AND p_email <> '' THEN
            SELECT COALESCE(visits, 0) INTO v_visits
            FROM public.users
            WHERE LOWER(email) = LOWER(p_email);
        END IF;
    END IF;
    
    RETURN COALESCE(v_visits, 0);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

GRANT EXECUTE ON FUNCTION public.increment_user_visit_secure(TEXT, UUID, TEXT, TEXT, TEXT, TEXT, TEXT) TO anon, authenticated;

-- Associate session visits to a logged-in user
CREATE OR REPLACE FUNCTION public.associate_session_visits(
    p_session_id UUID,
    p_email TEXT
)
RETURNS BOOLEAN AS $$
DECLARE
    v_id BIGINT;
    v_status TEXT;
    v_associated_count INT := 0;
BEGIN
    IF p_session_id IS NULL OR p_email IS NULL OR p_email = '' THEN
        RETURN FALSE;
    END IF;

    SELECT u.id, u.status INTO v_id, v_status
    FROM public.users u
    WHERE LOWER(u.email) = LOWER(p_email);

    IF v_id IS NULL OR v_status = 'Blocked' THEN
        RETURN FALSE;
    END IF;

    -- Update unassociated visit logs for this session
    WITH updated AS (
        UPDATE public.visit_logs
        SET email = p_email
        WHERE session_id = p_session_id 
          AND (email IS NULL OR email = '')
        RETURNING 1
    )
    SELECT COUNT(*) INTO v_associated_count FROM updated;

    IF v_associated_count > 0 THEN
        UPDATE public.users
        SET visits = COALESCE(visits, 0) + v_associated_count
        WHERE id = v_id;
    END IF;

    RETURN TRUE;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

GRANT EXECUTE ON FUNCTION public.associate_session_visits(UUID, TEXT) TO anon, authenticated;

-- Server-side filtering and aggregation for the admin portal user list
CREATE OR REPLACE FUNCTION public.get_filtered_users_admin(
    p_search TEXT DEFAULT NULL,
    p_start_date TIMESTAMPTZ DEFAULT NULL,
    p_end_date TIMESTAMPTZ DEFAULT NULL
)
RETURNS TABLE (
    name TEXT,
    email TEXT,
    date TIMESTAMPTZ,
    status TEXT,
    range_visits INT,
    total_visits INT
) AS $$
BEGIN
    PERFORM public.verify_admin_only();

    RETURN QUERY
    SELECT 
        u.name,
        u.email,
        u.date,
        u.status,
        COALESCE((
            SELECT COUNT(DISTINCT COALESCE(v.session_id::text, v.id::text))::INT
            FROM public.visit_logs v
            WHERE LOWER(v.email) = LOWER(u.email)
              AND (p_start_date IS NULL OR v.visited_at >= p_start_date)
              AND (p_end_date IS NULL OR v.visited_at <= p_end_date)
        ), 0) AS range_visits,
        COALESCE(u.visits, 0) AS total_visits
    FROM public.users u
    WHERE
        (p_search IS NULL OR p_search = '' OR
            LOWER(u.name) LIKE '%' || LOWER(p_search) || '%' OR
            LOWER(u.email) LIKE '%' || LOWER(p_search) || '%' OR
            LOWER(u.status) LIKE '%' || LOWER(p_search) || '%'
        )
        AND
        (
            (p_start_date IS NULL AND p_end_date IS NULL) OR
            (u.date >= p_start_date AND u.date <= p_end_date) OR
            EXISTS (
                SELECT 1 
                FROM public.visit_logs v 
                WHERE LOWER(v.email) = LOWER(u.email) 
                  AND v.visited_at >= p_start_date 
                  AND v.visited_at <= p_end_date
            )
        )
    ORDER BY u.id DESC;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

GRANT EXECUTE ON FUNCTION public.get_filtered_users_admin(TEXT, TIMESTAMPTZ, TIMESTAMPTZ) TO authenticated;

-- Enable Realtime Replication
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_publication_tables 
        WHERE pubname = 'supabase_realtime' AND schemaname = 'public' AND tablename = 'visit_logs'
    ) THEN
        ALTER PUBLICATION supabase_realtime ADD TABLE public.visit_logs;
    END IF;
    
    IF NOT EXISTS (
        SELECT 1 FROM pg_publication_tables 
        WHERE pubname = 'supabase_realtime' AND schemaname = 'public' AND tablename = 'users'
    ) THEN
        ALTER PUBLICATION supabase_realtime ADD TABLE public.users;
    END IF;
    

    
    IF NOT EXISTS (
        SELECT 1 FROM pg_publication_tables 
        WHERE pubname = 'supabase_realtime' AND schemaname = 'public' AND tablename = 'blogs'
    ) THEN
        ALTER PUBLICATION supabase_realtime ADD TABLE public.blogs;
    END IF;
END;
$$;

-- Secure Admin Analytics aggregator (Bypasses 1000 row api limit and executes in milliseconds)
CREATE OR REPLACE FUNCTION public.get_admin_analytics(
    p_start_date TIMESTAMPTZ,
    p_end_date TIMESTAMPTZ
)
RETURNS JSON AS $$
DECLARE
    v_range_interval INTERVAL;
    v_prior_start TIMESTAMPTZ;
    v_prior_end TIMESTAMPTZ;
    
    v_total_visits INT8;
    v_active_users INT8;
    v_avg_duration INT8;
    v_articles_read INT8;
    
    v_prior_total_visits INT8;
    v_prior_active_users INT8;
    v_prior_avg_duration INT8;
    v_prior_articles_read INT8;
    
    v_diff_days NUMERIC;
    v_trunc_unit TEXT;
    
    v_devices JSON;
    v_countries JSON;
    v_pages JSON;
    v_visits_trend JSON;
    v_users_trend JSON;
    v_retention_trend JSON;
    v_recent_visitors JSON;
    
    v_result JSON;
BEGIN
    v_range_interval := p_end_date - p_start_date;
    v_prior_start := p_start_date - v_range_interval;
    v_prior_end := p_start_date - INTERVAL '1 millisecond';

    -- Current stats
    WITH current_sessions AS (
        SELECT 
            COALESCE(session_id::text, id::text) as s_id,
            MIN(visited_at) as min_time,
            MAX(visited_at) as max_time,
            COUNT(*) as page_views
        FROM public.visit_logs
        WHERE visited_at >= p_start_date AND visited_at <= p_end_date
        GROUP BY COALESCE(session_id::text, id::text)
    ),
    current_stats AS (
        SELECT
            COUNT(*) as total_sessions,
            SUM(CASE WHEN page_views = 1 THEN 1 ELSE 0 END) as bounces
        FROM current_sessions
    )
    SELECT 
        COALESCE((SELECT total_sessions FROM current_stats), 0),
        (SELECT COUNT(*) FROM public.users),
        COALESCE((SELECT CASE WHEN total_sessions > 0 THEN ROUND((bounces::NUMERIC / total_sessions::NUMERIC) * 100) ELSE 0 END FROM current_stats), 0),
        (SELECT COUNT(*)::INT8 FROM public.visit_logs WHERE visited_at >= p_start_date AND visited_at <= p_end_date AND (page_url LIKE '%article.html%' OR page_url LIKE '%article%'))
    INTO v_total_visits, v_active_users, v_avg_duration, v_articles_read;

    -- Prior stats
    WITH prior_sessions AS (
        SELECT 
            COALESCE(session_id::text, id::text) as s_id,
            MIN(visited_at) as min_time,
            MAX(visited_at) as max_time,
            COUNT(*) as page_views
        FROM public.visit_logs
        WHERE visited_at >= v_prior_start AND visited_at <= v_prior_end
        GROUP BY COALESCE(session_id::text, id::text)
    ),
    prior_stats AS (
        SELECT
            COUNT(*) as total_sessions,
            SUM(CASE WHEN page_views = 1 THEN 1 ELSE 0 END) as bounces
        FROM prior_sessions
    )
    SELECT 
        COALESCE((SELECT total_sessions FROM prior_stats), 0),
        (SELECT COUNT(*) FROM public.users WHERE date < p_start_date),
        COALESCE((SELECT CASE WHEN total_sessions > 0 THEN ROUND((bounces::NUMERIC / total_sessions::NUMERIC) * 100) ELSE 0 END FROM prior_stats), 0),
        (SELECT COUNT(*)::INT8 FROM public.visit_logs WHERE visited_at >= v_prior_start AND visited_at <= v_prior_end AND (page_url LIKE '%article.html%' OR page_url LIKE '%article%'))
    INTO v_prior_total_visits, v_prior_active_users, v_prior_avg_duration, v_prior_articles_read;

    -- Devices breakdown
    SELECT COALESCE(json_agg(t), '[]'::json) INTO v_devices FROM (
        SELECT COALESCE(device, 'Desktop') as label, COUNT(*)::INT8 as count
        FROM public.visit_logs
        WHERE visited_at >= p_start_date AND visited_at <= p_end_date
        GROUP BY COALESCE(device, 'Desktop')
        ORDER BY count DESC
        LIMIT 5
    ) t;

    -- Countries breakdown
    SELECT COALESCE(json_agg(t), '[]'::json) INTO v_countries FROM (
        SELECT COALESCE(country, 'Unknown') as label, COUNT(*)::INT8 as count
        FROM public.visit_logs
        WHERE visited_at >= p_start_date AND visited_at <= p_end_date
        GROUP BY COALESCE(country, 'Unknown')
        ORDER BY count DESC
        LIMIT 5
    ) t;

    -- Pages breakdown
    SELECT COALESCE(json_agg(t), '[]'::json) INTO v_pages FROM (
        SELECT COALESCE(page_url, '/home') as label, COUNT(*)::INT8 as count
        FROM public.visit_logs
        WHERE visited_at >= p_start_date AND visited_at <= p_end_date
        GROUP BY COALESCE(page_url, '/home')
        ORDER BY count DESC
        LIMIT 5
    ) t;

    -- Determine time truncation unit
    v_diff_days := EXTRACT(EPOCH FROM (p_end_date - p_start_date)) / 86400;
    IF v_diff_days <= 1.1 THEN
        v_trunc_unit := 'hour';
    ELSIF v_diff_days <= 31 THEN
        v_trunc_unit := 'day';
    ELSIF v_diff_days <= 180 THEN
        v_trunc_unit := 'week';
    ELSE
        v_trunc_unit := 'month';
    END IF;

    -- Visits Trend
    SELECT COALESCE(json_agg(t), '[]'::json) INTO v_visits_trend FROM (
        SELECT 
            date_trunc(v_trunc_unit, visited_at) as bucket_time,
            COUNT(DISTINCT COALESCE(session_id::text, id::text))::INT8 as count
        FROM public.visit_logs
        WHERE visited_at >= p_start_date AND visited_at <= p_end_date
        GROUP BY bucket_time
        ORDER BY bucket_time
    ) t;

    -- Acquisitions Trend
    SELECT COALESCE(json_agg(t), '[]'::json) INTO v_users_trend FROM (
        SELECT 
            date_trunc(v_trunc_unit, date) as bucket_time,
            COUNT(*)::INT8 as count
        FROM public.users
        WHERE date >= p_start_date AND date <= p_end_date
        GROUP BY bucket_time
        ORDER BY bucket_time
    ) t;

    -- Session retention & articles read trends by time bucket
    SELECT COALESCE(json_agg(t), '[]'::json) INTO v_retention_trend FROM (
        WITH bucket_sessions AS (
            SELECT 
                date_trunc(v_trunc_unit, visited_at) as bucket_time,
                COALESCE(session_id::text, id::text) as s_id,
                COUNT(*) as page_views
            FROM public.visit_logs
            WHERE visited_at >= p_start_date AND visited_at <= p_end_date
            GROUP BY bucket_time, COALESCE(session_id::text, id::text)
        ),
        bucket_reads AS (
            SELECT 
                date_trunc(v_trunc_unit, visited_at) as bucket_time,
                COUNT(*)::INT8 as reads_count
            FROM public.visit_logs
            WHERE visited_at >= p_start_date AND visited_at <= p_end_date
              AND (page_url LIKE '%article.html%' OR page_url LIKE '%article%')
            GROUP BY bucket_time
        )
        SELECT 
            bs.bucket_time,
            ROUND((SUM(CASE WHEN bs.page_views = 1 THEN 1 ELSE 0 END)::NUMERIC / COUNT(*)::NUMERIC) * 100)::INT8 as avg_duration,
            COALESCE(br.reads_count, 0)::INT8 as articles_read
        FROM bucket_sessions bs
        LEFT JOIN bucket_reads br ON br.bucket_time = bs.bucket_time
        GROUP BY bs.bucket_time, br.reads_count
        ORDER BY bs.bucket_time
    ) t;

    -- Recent Visitors
    SELECT COALESCE(json_agg(t), '[]'::json) INTO v_recent_visitors FROM (
        SELECT 
            COALESCE(ip_address, '127.0.0.1') as ip_address,
            COALESCE(country, 'Unknown') as country,
            COALESCE(page_url, '/home') as page_url,
            visited_at
        FROM public.visit_logs
        WHERE visited_at >= p_start_date AND visited_at <= p_end_date
        ORDER BY visited_at DESC
        LIMIT 7
    ) t;

    v_result := json_build_object(
        'total_visits', v_total_visits,
        'active_users', v_active_users,
        'avg_session_duration', v_avg_duration,
        'articles_read', v_articles_read,
        'prior_total_visits', v_prior_total_visits,
        'prior_active_users', v_prior_active_users,
        'prior_avg_session_duration', v_prior_avg_duration,
        'prior_articles_read', v_prior_articles_read,
        'devices', v_devices,
        'countries', v_countries,
        'pages', v_pages,
        'visits_trend', v_visits_trend,
        'users_trend', v_users_trend,
        'retention_trend', v_retention_trend,
        'recent_visitors', v_recent_visitors
    );

    RETURN v_result;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

GRANT EXECUTE ON FUNCTION public.get_admin_analytics(TIMESTAMPTZ, TIMESTAMPTZ) TO authenticated;

-- 6. ROW LEVEL SECURITY (RLS) POLICIES
-- Enable RLS on all tables to protect them from direct unauthorized PostgREST access.
-- By defining NO policy permissions, direct API table queries default to Deny All.
-- Client interactions go strictly through defined SQL functions (SECURITY DEFINER).
ALTER TABLE public.settings ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.blogs ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.users ENABLE ROW LEVEL SECURITY;

ALTER TABLE public.admin_users ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.csrf_tokens ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.visit_logs ENABLE ROW LEVEL SECURITY;

-- Helper to check if current authenticated user is an admin
CREATE OR REPLACE FUNCTION public.is_admin()
RETURNS BOOLEAN AS $$
BEGIN
    RETURN EXISTS (
        SELECT 1 FROM public.users 
        WHERE auth_user_id = auth.uid() 
          AND role = 'admin'
    );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 7. RLS SELECT POLICIES FOR REALTIME REPLICATION (ADMIN ONLY)
-- Realtime listeners run under the client's role. To receive replication events,
-- the listener's user must have SELECT permission on the table.
DROP POLICY IF EXISTS "Allow admin read access on settings" ON public.settings;
CREATE POLICY "Allow admin read access on settings" ON public.settings
    FOR SELECT TO authenticated USING (public.is_admin());

DROP POLICY IF EXISTS "Allow admin read access on blogs" ON public.blogs;
CREATE POLICY "Allow admin read access on blogs" ON public.blogs
    FOR SELECT TO authenticated USING (public.is_admin());

DROP POLICY IF EXISTS "Allow admin read access on users" ON public.users;
CREATE POLICY "Allow admin read access on users" ON public.users
    FOR SELECT TO authenticated USING (public.is_admin());

DROP POLICY IF EXISTS "Allow admin read access on admin_users" ON public.admin_users;
CREATE POLICY "Allow admin read access on admin_users" ON public.admin_users
    FOR SELECT TO authenticated USING (public.is_admin());

DROP POLICY IF EXISTS "Allow admin read access on csrf_tokens" ON public.csrf_tokens;
CREATE POLICY "Allow admin read access on csrf_tokens" ON public.csrf_tokens
    FOR SELECT TO authenticated USING (public.is_admin());

DROP POLICY IF EXISTS "Allow admin read access on visit_logs" ON public.visit_logs;
CREATE POLICY "Allow admin read access on visit_logs" ON public.visit_logs
    FOR SELECT TO authenticated USING (public.is_admin());

-- 8. FAKE/DISPOSABLE EMAIL REGISTRATION PROTECTION
-- Trigger on auth.users (Supabase Auth schema) to block disposable/fake email domains.
CREATE OR REPLACE FUNCTION public.block_disposable_emails()
RETURNS TRIGGER AS $$
DECLARE
    v_domain TEXT;
BEGIN
    v_domain := LOWER(SUBSTRING(NEW.email FROM '@(.*)$'));
    
    -- List of common disposable email domains
    IF v_domain IN (
        'mailinator.com', '10minutemail.com', 'tempmail.com', 'temp-mail.org',
        'yopmail.com', 'sharklasers.com', 'guerrillamail.com', 'dispostable.com',
        'getairmail.com', 'maildrop.cc', 'throwawaymail.com', 'tempmailaddress.com',
        'binkmail.com', 'safetymail.info', 'discardmail.com', 'mailnull.com',
        'generator.email', 'tempmail.dev', 'temp-mail.io', '10minutemail.co.uk',
        '10minutemail.net', 'tempmail.net'
    ) THEN
        RAISE EXCEPTION 'Disposable email addresses are not allowed.';
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Apply trigger to auth.users
DROP TRIGGER IF EXISTS trigger_block_disposable_emails ON auth.users;
CREATE TRIGGER trigger_block_disposable_emails
    BEFORE INSERT ON auth.users
    FOR EACH ROW
    EXECUTE FUNCTION public.block_disposable_emails();


-- Public client reporting function (No admin check, SECURE DEFINER to bypass table RLS)
CREATE OR REPLACE FUNCTION public.get_filtered_users_public(
    p_search TEXT DEFAULT NULL,
    p_start_date TIMESTAMPTZ DEFAULT NULL,
    p_end_date TIMESTAMPTZ DEFAULT NULL
)
RETURNS TABLE (
    name TEXT,
    email TEXT,
    date TIMESTAMPTZ,
    status TEXT,
    range_visits INT,
    total_visits INT
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        u.name,
        u.email,
        u.date,
        u.status,
        COALESCE((
            SELECT COUNT(DISTINCT COALESCE(v.session_id::text, v.id::text))::INT
            FROM public.visit_logs v
            WHERE LOWER(v.email) = LOWER(u.email)
              AND (p_start_date IS NULL OR v.visited_at >= p_start_date)
              AND (p_end_date IS NULL OR v.visited_at <= p_end_date)
        ), 0) AS range_visits,
        COALESCE(u.visits, 0) AS total_visits
    FROM public.users u
    WHERE
        (p_search IS NULL OR p_search = '' OR
            LOWER(u.name) LIKE '%' || LOWER(p_search) || '%' OR
            LOWER(u.email) LIKE '%' || LOWER(p_search) || '%' OR
            LOWER(u.status) LIKE '%' || LOWER(p_search) || '%'
        )
        AND
        (
            (p_start_date IS NULL AND p_end_date IS NULL) OR
            (u.date >= p_start_date AND u.date <= p_end_date) OR
            EXISTS (
                SELECT 1 
                FROM public.visit_logs v 
                WHERE LOWER(v.email) = LOWER(u.email) 
                  AND v.visited_at >= p_start_date 
                  AND v.visited_at <= p_end_date
            )
        )
    ORDER BY u.id DESC;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

GRANT EXECUTE ON FUNCTION public.get_filtered_users_public(TEXT, TIMESTAMPTZ, TIMESTAMPTZ) TO anon, authenticated;