-- Drop the foreign key constraint if it exists
DO $$
BEGIN
    IF EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'fk_userlogs_OrganisationID') THEN
        ALTER TABLE userlogs DROP CONSTRAINT fk_userlogs_OrganisationID;
    END IF;
END $$;

-- Drop columns if they exist
DO $$
BEGIN
    IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'userlogs' AND column_name = 'organisation') THEN
        ALTER TABLE userlogs DROP COLUMN Organisation;
    END IF;
    IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'userlogs' AND column_name = 'organisationid') THEN
        ALTER TABLE userlogs DROP COLUMN OrganisationID;
    END IF;
    IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'userlogs' AND column_name = 'usertype') THEN
        ALTER TABLE userlogs DROP COLUMN UserType;
    END IF;
    IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'userlogs' AND column_name = 'status') THEN
        ALTER TABLE userlogs DROP COLUMN Status;
    END IF;
END $$;

-- Add columns and constraint
ALTER TABLE userlogs
ADD COLUMN Organisation VARCHAR(100),
ADD COLUMN OrganisationID INT,
ADD COLUMN UserType VARCHAR(100),
ADD COLUMN Status VARCHAR(100) DEFAULT 'active';

-- Add foreign key constraint
ALTER TABLE userlogs
ADD CONSTRAINT fk_userlogs_OrganisationID FOREIGN KEY (OrganisationID) REFERENCES exportcompanies(id);