-- Seed data for client_users table
-- Note: Passwords are bcrypt hashed. Default password for all users: "password123"
-- Hash generated using: go run scripts/generate_password_hash.go password123
-- Hash: $2a$10$LVTOIQGpCF9mqX5s6/h15.OEfMrgOVG1hjqgvIXdzrNcvCqa6Eloq

-- Clear existing data (optional - comment out if you want to keep existing data)
-- TRUNCATE TABLE client_users CASCADE;

-- Get client IDs dynamically from the clients table
-- This ensures the seeder works with your actual client data
DO $$
DECLARE
    client1_id VARCHAR(255);
    client2_id VARCHAR(255);
    client3_id VARCHAR(255);
    client_count INTEGER;
BEGIN
    -- Count available clients
    SELECT COUNT(*) INTO client_count FROM clients WHERE deleted_at IS NULL;
    
    IF client_count = 0 THEN
        RAISE EXCEPTION 'No clients found in database. Please ensure clients table has data.';
    END IF;
    
    -- Get first 3 clients (or fewer if less than 3 exist)
    SELECT id INTO client1_id FROM clients WHERE deleted_at IS NULL ORDER BY created_at LIMIT 1 OFFSET 0;
    
    IF client_count >= 2 THEN
        SELECT id INTO client2_id FROM clients WHERE deleted_at IS NULL ORDER BY created_at LIMIT 1 OFFSET 1;
    ELSE
        client2_id := client1_id; -- Use same client if only one exists
    END IF;
    
    IF client_count >= 3 THEN
        SELECT id INTO client3_id FROM clients WHERE deleted_at IS NULL ORDER BY created_at LIMIT 1 OFFSET 2;
    ELSE
        client3_id := client1_id; -- Use same client if less than 3 exist
    END IF;
    
    -- TechCorp Kenya Users (using client1_id)
    INSERT INTO client_users (
        id, client_id, first_name, last_name, email, phone, username, password,
        role, status, is_active, is_verified, preferred_language, timezone,
        created_at, updated_at
    ) VALUES
    (
        gen_random_uuid(),
        client1_id,
        'John',
        'Mwangi',
        'john.mwangi@techcorp.ke',
        '+254712345678',
        'john.mwangi',
        '$2a$10$LVTOIQGpCF9mqX5s6/h15.OEfMrgOVG1hjqgvIXdzrNcvCqa6Eloq',
        'admin',
        'active',
        true,
        true,
        'en',
        'Africa/Nairobi',
        CURRENT_TIMESTAMP,
        CURRENT_TIMESTAMP
    ),
    (
        gen_random_uuid(),
        client1_id,
        'Mary',
        'Wanjiku',
        'mary.wanjiku@techcorp.ke',
        '+254712345679',
        'mary.wanjiku',
        '$2a$10$LVTOIQGpCF9mqX5s6/h15.OEfMrgOVG1hjqgvIXdzrNcvCqa6Eloq',
        'manager',
        'active',
        true,
        true,
        'en',
        'Africa/Nairobi',
        CURRENT_TIMESTAMP,
        CURRENT_TIMESTAMP
    ),
    (
        gen_random_uuid(),
        client1_id,
        'Peter',
        'Ochieng',
        'peter.ochieng@techcorp.ke',
        NULL,
        'peter.ochieng',
        '$2a$10$LVTOIQGpCF9mqX5s6/h15.OEfMrgOVG1hjqgvIXdzrNcvCqa6Eloq',
        'cashier',
        'active',
        true,
        false,
        'en',
        'Africa/Nairobi',
        CURRENT_TIMESTAMP,
        CURRENT_TIMESTAMP
    ),
    (
        gen_random_uuid(),
        client1_id,
        'Sarah',
        'Kamau',
        NULL,
        '+254712345680',
        'sarah.kamau',
        '$2a$10$LVTOIQGpCF9mqX5s6/h15.OEfMrgOVG1hjqgvIXdzrNcvCqa6Eloq',
        'staff',
        'active',
        true,
        false,
        'en',
        NULL,
        CURRENT_TIMESTAMP,
        CURRENT_TIMESTAMP
    );

    -- Digital Solutions Ltd Users (using client2_id)
    INSERT INTO client_users (
        id, client_id, first_name, last_name, email, phone, username, password,
        role, status, is_active, is_verified, address, city, country,
        preferred_language, timezone, created_at, updated_at
    ) VALUES
    (
        gen_random_uuid(),
        client2_id,
        'Ahmed',
        'Hassan',
        'ahmed.hassan@digsol.ke',
        '+254798765432',
        'ahmed.hassan',
        '$2a$10$LVTOIQGpCF9mqX5s6/h15.OEfMrgOVG1hjqgvIXdzrNcvCqa6Eloq',
        'admin',
        'active',
        true,
        true,
        'Mombasa Road, Building 5',
        'Mombasa',
        'Kenya',
        'en',
        'Africa/Nairobi',
        CURRENT_TIMESTAMP,
        CURRENT_TIMESTAMP
    ),
    (
        gen_random_uuid(),
        client2_id,
        'Fatuma',
        'Ali',
        'fatuma.ali@digsol.ke',
        '+254798765433',
        'fatuma.ali',
        '$2a$10$LVTOIQGpCF9mqX5s6/h15.OEfMrgOVG1hjqgvIXdzrNcvCqa6Eloq',
        'manager',
        'active',
        true,
        true,
        NULL,
        'Mombasa',
        'Kenya',
        'sw',
        'Africa/Nairobi',
        CURRENT_TIMESTAMP,
        CURRENT_TIMESTAMP
    ),
    (
        gen_random_uuid(),
        client2_id,
        'James',
        'Omondi',
        'james.omondi@digsol.ke',
        NULL,
        'james.omondi',
        '$2a$10$LVTOIQGpCF9mqX5s6/h15.OEfMrgOVG1hjqgvIXdzrNcvCqa6Eloq',
        'cashier',
        'active',
        true,
        false,
        'Nyali Beach Road',
        'Mombasa',
        'Kenya',
        'en',
        'Africa/Nairobi',
        CURRENT_TIMESTAMP,
        CURRENT_TIMESTAMP
    );

    -- Innovation Hub Users (using client3_id)
    INSERT INTO client_users (
        id, client_id, first_name, last_name, email, phone, username, password,
        role, status, is_active, is_verified, date_of_birth, gender, address,
        city, state, country, postal_code, preferred_language, timezone,
        avatar_url, bio, created_at, updated_at
    ) VALUES
    (
        gen_random_uuid(),
        client3_id,
        'Grace',
        'Akinyi',
        'grace.akinyi@innovationhub.ke',
        '+254712111111',
        'grace.akinyi',
        '$2a$10$LVTOIQGpCF9mqX5s6/h15.OEfMrgOVG1hjqgvIXdzrNcvCqa6Eloq',
        'admin',
        'active',
        true,
        true,
        '1990-05-15',
        'Female',
        'Kisumu Central, Office Complex',
        'Kisumu',
        'Kisumu County',
        'Kenya',
        '40100',
        'en',
        'Africa/Nairobi',
        'https://example.com/avatars/grace.jpg',
        'Experienced administrator with 5+ years in tech',
        CURRENT_TIMESTAMP,
        CURRENT_TIMESTAMP
    ),
    (
        gen_random_uuid(),
        client3_id,
        'David',
        'Onyango',
        'david.onyango@innovationhub.ke',
        '+254712111112',
        'david.onyango',
        '$2a$10$LVTOIQGpCF9mqX5s6/h15.OEfMrgOVG1hjqgvIXdzrNcvCqa6Eloq',
        'manager',
        'active',
        true,
        true,
        '1988-08-20',
        'Male',
        NULL,
        'Kisumu',
        'Kisumu County',
        'Kenya',
        NULL,
        'en',
        'Africa/Nairobi',
        NULL,
        NULL,
        CURRENT_TIMESTAMP,
        CURRENT_TIMESTAMP
    ),
    (
        gen_random_uuid(),
        client3_id,
        'Lucy',
        'Achieng',
        'lucy.achieng@innovationhub.ke',
        NULL,
        'lucy.achieng',
        '$2a$10$LVTOIQGpCF9mqX5s6/h15.OEfMrgOVG1hjqgvIXdzrNcvCqa6Eloq',
        'staff',
        'inactive',
        false,
        false,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        'en',
        NULL,
        NULL,
        NULL,
        CURRENT_TIMESTAMP,
        CURRENT_TIMESTAMP
    ),
    (
        gen_random_uuid(),
        client3_id,
        'Michael',
        'Oduor',
        'michael.oduor@innovationhub.ke',
        '+254712111113',
        'michael.oduor',
        '$2a$10$LVTOIQGpCF9mqX5s6/h15.OEfMrgOVG1hjqgvIXdzrNcvCqa6Eloq',
        'cashier',
        'suspended',
        true,
        true,
        '1995-03-10',
        'Male',
        'Milimani Estate',
        'Kisumu',
        'Kisumu County',
        'Kenya',
        '40100',
        'en',
        'Africa/Nairobi',
        NULL,
        NULL,
        CURRENT_TIMESTAMP,
        CURRENT_TIMESTAMP
    );

    -- Add some users with permissions JSON
    INSERT INTO client_users (
        id, client_id, first_name, last_name, email, username, password,
        role, permissions, status, is_active, is_verified,
        preferred_language, timezone, created_at, updated_at
    ) VALUES
    (
        gen_random_uuid(),
        client1_id,
        'Admin',
        'User',
        'admin@techcorp.ke',
        'admin',
        '$2a$10$LVTOIQGpCF9mqX5s6/h15.OEfMrgOVG1hjqgvIXdzrNcvCqa6Eloq',
        'admin',
        '{"can_manage_users": true, "can_manage_packages": true, "can_manage_vouchers": true, "can_view_reports": true, "can_manage_payments": true, "can_manage_settings": true, "can_view_logs": true}'::jsonb,
        'active',
        true,
        true,
        'en',
        'Africa/Nairobi',
        CURRENT_TIMESTAMP,
        CURRENT_TIMESTAMP
    ),
    (
        gen_random_uuid(),
        client2_id,
        'Manager',
        'User',
        'manager@digsol.ke',
        'manager',
        '$2a$10$LVTOIQGpCF9mqX5s6/h15.OEfMrgOVG1hjqgvIXdzrNcvCqa6Eloq',
        'manager',
        '{"can_manage_users": false, "can_manage_packages": true, "can_manage_vouchers": true, "can_view_reports": true, "can_manage_payments": true, "can_manage_settings": false, "can_view_logs": true}'::jsonb,
        'active',
        true,
        true,
        'en',
        'Africa/Nairobi',
        CURRENT_TIMESTAMP,
        CURRENT_TIMESTAMP
    );

    -- Update login tracking for some users (simulate activity)
    UPDATE client_users
    SET last_login = CURRENT_TIMESTAMP - INTERVAL '2 days',
        last_login_ip = '192.168.1.100',
        last_activity = CURRENT_TIMESTAMP - INTERVAL '1 hour',
        login_count = 45,
        email_verified_at = CURRENT_TIMESTAMP - INTERVAL '30 days'
    WHERE username IN ('john.mwangi', 'ahmed.hassan', 'grace.akinyi');

    UPDATE client_users
    SET last_login = CURRENT_TIMESTAMP - INTERVAL '5 days',
        last_login_ip = '192.168.1.101',
        last_activity = CURRENT_TIMESTAMP - INTERVAL '2 days',
        login_count = 12,
        email_verified_at = CURRENT_TIMESTAMP - INTERVAL '60 days'
    WHERE username IN ('mary.wanjiku', 'fatuma.ali', 'david.onyango');

    -- Set some failed login attempts
    UPDATE client_users
    SET failed_login_attempts = 2
    WHERE username = 'peter.ochieng';

    UPDATE client_users
    SET failed_login_attempts = 5,
        locked_until = CURRENT_TIMESTAMP + INTERVAL '30 minutes'
    WHERE username = 'michael.oduor';

    RAISE NOTICE 'Successfully seeded client users. Client 1: %, Client 2: %, Client 3: %', client1_id, client2_id, client3_id;
END $$;
