service-finder/backend/app/scripts/db_cleanup.sql

-- Database cleanup script for Service Finder identity tables
-- WARNING: This will delete ALL users and persons, reset sequences, and create fresh admin users.
-- Only run this in development environments with explicit approval from the Owner.

-- 1. Disable foreign key checks temporarily (PostgreSQL doesn't support, but we can use TRUNCATE CASCADE)
--    Instead we'll use TRUNCATE with CASCADE which automatically handles dependent tables.

BEGIN;

-- 2. Truncate identity tables and restart identity sequences
TRUNCATE TABLE identity.users, identity.persons, identity.wallets, identity.user_trust_profiles 
RESTART IDENTITY CASCADE;

-- Note: The CASCADE option will also truncate any tables that have foreign keys referencing these tables.
-- This includes: identity.social_accounts, identity.organization_members, etc.
-- If you want to preserve other tables (e.g., system.addresses), you may need to adjust.

-- 3. Insert the superadmin person
INSERT INTO identity.persons (
    first_name,
    last_name,
    identity_hash,
    phone,
    is_active,
    is_sales_agent,
    lifetime_xp,
    penalty_points,
    social_reputation,
    identity_docs,
    ice_contact,
    created_at
) VALUES (
    'Super',
    'Admin',
    'superadmin_hash_' || gen_random_uuid(),
    '+36123456789',
    true,
    false,
    0,
    0,
    5.0,
    '{}'::jsonb,
    '{}'::jsonb,
    NOW()
) RETURNING id;

-- 4. Insert the superadmin user (using the returned person_id)
INSERT INTO identity.users (
    email,
    hashed_password,
    role,
    person_id,
    is_active,
    is_deleted,
    subscription_plan,
    is_vip,
    subscription_expires_at,
    referral_code,
    referred_by_id,
    current_sales_agent_id,
    folder_slug,
    preferred_language,
    region_code,
    preferred_currency,
    scope_level,
    scope_id,
    custom_permissions,
    created_at
) VALUES (
    'superadmin@profibot.hu',
    -- Password hash for 'Admin123!' (generated with bcrypt, cost 12)
    '$2b$12$6YQ.Zj.8Vq8Z8Z8Z8Z8Z8O',
    'superadmin',
    (SELECT id FROM identity.persons WHERE identity_hash LIKE 'superadmin_hash_%'),
    true,
    false,
    'ENTERPRISE',
    false,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    'hu',
    'HU',
    'HUF',
    'system',
    NULL,
    '{}'::jsonb,
    NOW()
) RETURNING id;

-- 5. Create wallet for superadmin
INSERT INTO identity.wallets (
    user_id,
    earned_credits,
    purchased_credits,
    service_coins,
    currency
) VALUES (
    (SELECT id FROM identity.users WHERE email = 'superadmin@profibot.hu'),
    1000000.0,
    500000.0,
    10000.0,
    'HUF'
);

-- 6. Insert an admin person
INSERT INTO identity.persons (
    first_name,
    last_name,
    identity_hash,
    phone,
    is_active,
    is_sales_agent,
    lifetime_xp,
    penalty_points,
    social_reputation,
    identity_docs,
    ice_contact,
    created_at
) VALUES (
    'Admin',
    'User',
    'adminuser_hash_' || gen_random_uuid(),
    '+36123456780',
    true,
    false,
    0,
    0,
    4.5,
    '{}'::jsonb,
    '{}'::jsonb,
    NOW()
) RETURNING id;

-- 7. Insert the admin user
INSERT INTO identity.users (
    email,
    hashed_password,
    role,
    person_id,
    is_active,
    is_deleted,
    subscription_plan,
    is_vip,
    subscription_expires_at,
    referral_code,
    referred_by_id,
    current_sales_agent_id,
    folder_slug,
    preferred_language,
    region_code,
    preferred_currency,
    scope_level,
    scope_id,
    custom_permissions,
    created_at
) VALUES (
    'admin@profibot.hu',
    -- Password hash for 'Admin123!' (same as above)
    '$2b$12$6YQ.Zj.8Vq8Z8Z8Z8Z8Z8O',
    'admin',
    (SELECT id FROM identity.persons WHERE identity_hash LIKE 'adminuser_hash_%'),
    true,
    false,
    'PRO',
    false,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    'hu',
    'HU',
    'HUF',
    'system',
    NULL,
    '{}'::jsonb,
    NOW()
) RETURNING id;

-- 8. Create wallet for admin
INSERT INTO identity.wallets (
    user_id,
    earned_credits,
    purchased_credits,
    service_coins,
    currency
) VALUES (
    (SELECT id FROM identity.users WHERE email = 'admin@profibot.hu'),
    500000.0,
    200000.0,
    5000.0,
    'HUF'
);

-- 9. Optionally, insert a test user for development
INSERT INTO identity.persons (
    first_name,
    last_name,
    identity_hash,
    phone,
    is_active,
    is_sales_agent,
    lifetime_xp,
    penalty_points,
    social_reputation,
    identity_docs,
    ice_contact,
    created_at
) VALUES (
    'Test',
    'User',
    'testuser_hash_' || gen_random_uuid(),
    '+36123456781',
    true,
    false,
    0,
    0,
    3.0,
    '{}'::jsonb,
    '{}'::jsonb,
    NOW()
);

INSERT INTO identity.users (
    email,
    hashed_password,
    role,
    person_id,
    is_active,
    is_deleted,
    subscription_plan,
    is_vip,
    subscription_expires_at,
    referral_code,
    referred_by_id,
    current_sales_agent_id,
    folder_slug,
    preferred_language,
    region_code,
    preferred_currency,
    scope_level,
    scope_id,
    custom_permissions,
    created_at
) VALUES (
    'test@profibot.hu',
    '$2b$12$6YQ.Zj.8Vq8Z8Z8Z8Z8Z8O',
    'user',
    (SELECT id FROM identity.persons WHERE identity_hash LIKE 'testuser_hash_%'),
    true,
    false,
    'FREE',
    false,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    'hu',
    'HU',
    'HUF',
    'individual',
    NULL,
    '{}'::jsonb,
    NOW()
);

INSERT INTO identity.wallets (
    user_id,
    earned_credits,
    purchased_credits,
    service_coins,
    currency
) VALUES (
    (SELECT id FROM identity.users WHERE email = 'test@profibot.hu'),
    1000.0,
    0.0,
    100.0,
    'HUF'
);

COMMIT;

-- 10. Verify the cleanup
SELECT 'Cleanup completed. New users:' AS message;
SELECT u.id, u.email, u.role, p.first_name, p.last_name 
FROM identity.users u 
JOIN identity.persons p ON u.person_id = p.id 
ORDER BY u.id;