-- Add created_by field to bots table
-- This migration adds a created_by field to track which user created each bot

-- Add the created_by column to the bots table
ALTER TABLE "bots" ADD COLUMN "created_by" TEXT NOT NULL DEFAULT '';

-- Add foreign key constraint to reference the users table
ALTER TABLE "bots" ADD CONSTRAINT "bots_created_by_fkey" FOREIGN KEY ("created_by") REFERENCES "users"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- For existing bots, we need to set a default created_by value
-- We'll use the first admin user or create a system user if none exists
DO $$
DECLARE
    admin_user_id TEXT;
BEGIN
    -- Try to find an existing admin user
    SELECT "id" INTO admin_user_id FROM "users" WHERE "role" = 'ADMIN' LIMIT 1;
    
    -- If no admin user exists, create a system user
    IF admin_user_id IS NULL THEN
        INSERT INTO "users" ("id", "name", "email", "password", "role", "email_verified", "created_at", "updated_at")
        VALUES (
            gen_random_uuid(),
            'System User',
            'system@example.com',
            '$2b$12$dummy.hash.for.system.user',
            'ADMIN',
            true,
            NOW(),
            NOW()
        )
        RETURNING "id" INTO admin_user_id;
    END IF;
    
    -- Update all bots to use this admin/system user
    UPDATE "bots" SET "created_by" = admin_user_id WHERE "created_by" = '';
END $$;

-- Remove the default value since we've populated all records
ALTER TABLE "bots" ALTER COLUMN "created_by" DROP DEFAULT;
