-- ============================================================================
-- Test Data for Port Smith Database
-- ============================================================================
-- This file contains test data for inserting into Supabase tables
-- Run these in the Supabase SQL Editor to insert test records
-- ============================================================================

-- ============================================================================
-- Create/Update notification_types enum (if it doesn't exist or needs updating)
-- ============================================================================
-- Run this first to ensure the enum has all required values

-- Drop and recreate enum if it exists (be careful - this will affect existing data)
-- DO $$ 
-- BEGIN
--     IF EXISTS (SELECT 1 FROM pg_type WHERE typname = 'notification_types') THEN
--         DROP TYPE public.notification_types CASCADE;
--     END IF;
-- END $$;

-- Create the enum with all notification types
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'notification_types') THEN
        CREATE TYPE public.notification_types AS ENUM (
            'app_update_info',
            'deprecation',
            'force',
            'new_feature',
            'new_apps',
            'app_promotion'
        );
    END IF;
END $$;

-- If enum exists but is missing values, add them one by one
-- Note: PostgreSQL doesn't support IF NOT EXISTS for ADD VALUE, so check first
DO $$ 
BEGIN
    -- Add 'force' if it doesn't exist
    IF NOT EXISTS (
        SELECT 1 FROM pg_enum 
        WHERE enumlabel = 'force' 
        AND enumtypid = (SELECT oid FROM pg_type WHERE typname = 'notification_types')
    ) THEN
        ALTER TYPE public.notification_types ADD VALUE 'force';
    END IF;
    
    -- Add 'deprecation' if it doesn't exist
    IF NOT EXISTS (
        SELECT 1 FROM pg_enum 
        WHERE enumlabel = 'deprecation' 
        AND enumtypid = (SELECT oid FROM pg_type WHERE typname = 'notification_types')
    ) THEN
        ALTER TYPE public.notification_types ADD VALUE 'deprecation';
    END IF;
    
    -- Add 'new_feature' if it doesn't exist
    IF NOT EXISTS (
        SELECT 1 FROM pg_enum 
        WHERE enumlabel = 'new_feature' 
        AND enumtypid = (SELECT oid FROM pg_type WHERE typname = 'notification_types')
    ) THEN
        ALTER TYPE public.notification_types ADD VALUE 'new_feature';
    END IF;
    
    -- Add 'new_apps' if it doesn't exist
    IF NOT EXISTS (
        SELECT 1 FROM pg_enum 
        WHERE enumlabel = 'new_apps' 
        AND enumtypid = (SELECT oid FROM pg_type WHERE typname = 'notification_types')
    ) THEN
        ALTER TYPE public.notification_types ADD VALUE 'new_apps';
    END IF;
    
    -- Add 'app_promotion' if it doesn't exist
    IF NOT EXISTS (
        SELECT 1 FROM pg_enum 
        WHERE enumlabel = 'app_promotion' 
        AND enumtypid = (SELECT oid FROM pg_type WHERE typname = 'notification_types')
    ) THEN
        ALTER TYPE public.notification_types ADD VALUE 'app_promotion';
    END IF;
END $$;

-- ============================================================================
-- Schema for portsmith_versions (if table doesn't exist)
-- ============================================================================
-- Uncomment and run this if the table doesn't exist yet

-- CREATE TABLE IF NOT EXISTS public.portsmith_versions (
--     id BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
--     created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
--     platform VARCHAR(50) NOT NULL CHECK (platform IN ('mac', 'windows')),
--     current_version VARCHAR(50) NOT NULL,
--     min_supported_version VARCHAR(50) NOT NULL,
--     update_url TEXT NOT NULL,
--     website_url TEXT NULL,
--     CONSTRAINT portsmith_versions_pkey PRIMARY KEY (id)
-- ) TABLESPACE pg_default;
--
-- CREATE INDEX IF NOT EXISTS idx_portsmith_versions_platform ON public.portsmith_versions(platform);
-- CREATE INDEX IF NOT EXISTS idx_portsmith_versions_created_at ON public.portsmith_versions(created_at DESC);
--
-- COMMENT ON TABLE public.portsmith_versions IS 'Stores app version information for different platforms';
-- COMMENT ON COLUMN public.portsmith_versions.current_version IS 'Latest available version';
-- COMMENT ON COLUMN public.portsmith_versions.min_supported_version IS 'Minimum supported version (below this is deprecated)';

-- ============================================================================
-- Test Data for portsmith_notifications
-- ============================================================================

-- Test 1: Force Update (Priority 1 - Highest)
INSERT INTO public.portsmith_notifications (
    active,
    dismissable,
    title,
    description,
    type,
    action_text
) VALUES (
    true,
    false,
    'Critical Update Required',
    'Your current version is no longer supported. Please update immediately to continue using Port Smith. Version 2.0.0 or higher is required.',
    'force',
    'https://example.com/download/latest'
);

-- Test 2: Deprecation Warning (Priority 2)
INSERT INTO public.portsmith_notifications (
    active,
    dismissable,
    title,
    description,
    type,
    action_text
) VALUES (
    true,
    true,
    'Version Deprecation Notice',
    'Your current version will be deprecated soon. Please update to version 2.0.0 or higher before the end of the month.',
    'deprecation',
    'https://example.com/download'
);

-- Test 3: Update Available (Priority 3)
INSERT INTO public.portsmith_notifications (
    active,
    dismissable,
    title,
    description,
    type,
    action_text
) VALUES (
    true,
    true,
    'New Version Available',
    'Version 2.1.0 is now available with bug fixes and performance improvements. Update now to get the latest features.',
    'app_update_info',
    'https://example.com/download/2.1.0'
);

-- Test 4: New Feature Announcement (Priority 4)
INSERT INTO public.portsmith_notifications (
    active,
    dismissable,
    title,
    description,
    type,
    action_text
) VALUES (
    true,
    true,
    'New Feature: Advanced Port Filtering',
    'We''ve added advanced port filtering options! You can now filter by port ranges, process names, and more. Check it out in Settings.',
    'new_feature',
    'https://example.com/features'
);

-- Test 5: New App Promotion (Priority 5)
INSERT INTO public.portsmith_notifications (
    active,
    dismissable,
    title,
    description,
    type,
    action_text
) VALUES (
    true,
    true,
    'Check Out Our New App',
    'We''ve launched Network Monitor Pro! Monitor your entire network with advanced analytics. Get 20% off for Port Smith users.',
    'new_apps',
    'https://example.com/network-monitor-pro'
);

-- Test 6: App Promotion (Priority 5)
INSERT INTO public.portsmith_notifications (
    active,
    dismissable,
    title,
    description,
    type,
    action_text
) VALUES (
    true,
    true,
    'Upgrade to Pro',
    'Unlock advanced features with Port Smith Pro. Get priority support, custom themes, and more.',
    'app_promotion',
    'https://example.com/pro'
);

-- ============================================================================
-- Test Data for portsmith_versions (if you still need this table)
-- ============================================================================
-- Note: This table may be deprecated in favor of notifications,
-- but keeping for backward compatibility or reference
--
-- Table structure based on AppVersion struct:
-- - platform (mac/windows)
-- - current_version
-- - min_supported_version
-- - update_url
-- - website_url
-- - created_at

-- Test version record for macOS
INSERT INTO public.portsmith_versions (
    platform,
    current_version,
    min_supported_version,
    update_url,
    website_url,
    created_at
) VALUES (
    'mac',
    '2.1.0',
    '2.0.0',
    'https://example.com/download/2.1.0',
    'https://portsmith.app',
    NOW()
) ON CONFLICT DO NOTHING;

-- Test version record for Windows (if applicable)
INSERT INTO public.portsmith_versions (
    platform,
    current_version,
    min_supported_version,
    update_url,
    website_url,
    created_at
) VALUES (
    'windows',
    '1.5.0',
    '1.0.0',
    'https://example.com/download/windows/1.5.0',
    'https://portsmith.app',
    NOW()
) ON CONFLICT DO NOTHING;

-- ============================================================================
-- Test Scenarios
-- ============================================================================
-- To test priority ordering, you can:
-- 1. Insert multiple active notifications with different types
-- 2. The NotificationManager will automatically sort by priority
-- 3. The highest priority (lowest number) notification will be shown first
--
-- Example test scenario:
-- - Insert Force (Priority 1) - should show first
-- - Insert Deprecation (Priority 2) - should show if no Force
-- - Insert Info (Priority 3) - should show if no Force/Deprecation
-- - Insert New Feature (Priority 4) - should show if no higher priority
-- - Insert New App (Priority 5) - should show if no higher priority
--
-- When multiple notifications of same priority exist, most recent wins

