seed.sql

DO $$
DECLARE
    -- Config
    TOTAL_USERS INT := 500;
    TXNS_PER_USER INT := 100;

    -- Analytics Categories
    CAT_NAMES text[] := ARRAY[
        'Groceries', 'Rent', 'Utilities', 'Entertainment',
        'Shopping', 'Travel', 'Healthcare', 'Salary'
    ];
BEGIN
    RAISE NOTICE 'Starting Fixed Data Generation...';

    -- 1. Clean Slate
    TRUNCATE TABLE users, categories, transactions RESTART IDENTITY CASCADE;

    -- 2. Ensure Extensions (pgcrypto is required for bcrypt)
    CREATE EXTENSION IF NOT EXISTS "pgcrypto";
    CREATE EXTENSION IF NOT EXISTS "citext";

    -- 3. Create Users with Valid BCrypt Hashes
    CREATE TEMP TABLE temp_users AS
    WITH inserted_users AS (
        INSERT INTO users (id, name, email, password_hash, created_at, updated_at)
        SELECT
            gen_random_uuid(),
            'User ' || i,
            'user_' || i || '@analytics.dev',
            -- FIXED: Generates a valid bcrypt hash ($2a$...) instead of SHA-256
            crypt('password', gen_salt('bf')),
            NOW() - (random() * interval '6 months'),
            NOW()
        FROM generate_series(1, TOTAL_USERS) AS i
        RETURNING id
    )
    SELECT id FROM inserted_users;

    RAISE NOTICE 'Generated % Users with BCrypt passwords', TOTAL_USERS;

    -- 4. Create Categories
    INSERT INTO categories (id, name, user_id, created_at, updated_at)
    SELECT
        gen_random_uuid(),
        cat_name,
        u.id,
        NOW(),
        NOW()
    FROM temp_users u
    CROSS JOIN unnest(CAT_NAMES) AS cat_name;

    -- 5. Create Transactions
    INSERT INTO transactions (
        id, user_id, category_id, from_user, to_user,
        upi_transaction_id, amount, created_at, updated_at
    )
    SELECT
        gen_random_uuid(),
        u.id,
        (
            SELECT c.id FROM categories c
            WHERE c.user_id = u.id
            ORDER BY random() LIMIT 1
        ),
        CASE WHEN (random() > 0.5) THEN 'Merchant' ELSE 'User ' || floor(random()*100) END,
        'Self',
        'UPI-FIX-' || u.id || '-' || seq || '-' || substr(md5(random()::text), 1, 5),
        (random() * 2000 + 50)::numeric(10, 2),
        NOW() - (random() * interval '6 months'),
        NOW()
    FROM temp_users u
    CROSS JOIN generate_series(1, TXNS_PER_USER) AS seq;

    RAISE NOTICE 'Generated % Transactions', (TOTAL_USERS * TXNS_PER_USER);
    DROP TABLE temp_users;

END $$;