-- Simple script to fix the immediate issue with company_id column
-- This addresses the specific error: "column "company_id" of relation "companies" already exists"

-- Check the current state and fix the companies table
DO $$ 
BEGIN
    -- If both company_id and company_uuid exist, handle carefully
    IF EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'companies' AND column_name = 'company_id') 
       AND EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'companies' AND column_name = 'company_uuid') THEN
        -- Check if company_id is already UUID type
        IF EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'companies' AND column_name = 'company_id' AND data_type = 'uuid') THEN
            -- company_id is already UUID, so we can drop company_uuid
            ALTER TABLE companies DROP COLUMN company_uuid;
        ELSE
            -- company_id is still bigint, so we need to rename company_uuid to company_id
            -- But first we need to drop the existing company_id column
            ALTER TABLE companies DROP COLUMN company_id;
            -- Now rename company_uuid to company_id
            ALTER TABLE companies RENAME COLUMN company_uuid TO company_id;
            -- Add primary key constraint
            ALTER TABLE companies ADD CONSTRAINT companies_pkey PRIMARY KEY (company_id);
        END IF;
    -- If only company_uuid exists, rename it to company_id
    ELSIF EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'companies' AND column_name = 'company_uuid') THEN
        ALTER TABLE companies RENAME COLUMN company_uuid TO company_id;
        -- Add primary key constraint
        ALTER TABLE companies ADD CONSTRAINT companies_pkey PRIMARY KEY (company_id);
    -- If only company_id exists and it's already UUID, do nothing
    ELSIF EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'companies' AND column_name = 'company_id' AND data_type = 'uuid') THEN
        -- Ensure primary key constraint exists
        IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'companies_pkey') THEN
            ALTER TABLE companies ADD CONSTRAINT companies_pkey PRIMARY KEY (company_id);
        END IF;
    END IF;
END $$;