-- Function to make a pre-existing column a primary key
-- Handles the case where a table may already have a primary key
CREATE OR REPLACE FUNCTION force_column_as_primary_key(
    p_table_schema TEXT,
    p_table_name TEXT,
    p_column_name TEXT
)
RETURNS void AS $$
DECLARE
    v_constraint_name TEXT;
BEGIN
    -- Drop existing primary key if one exists
    SELECT constraint_name INTO v_constraint_name
    FROM information_schema.table_constraints
    WHERE table_schema = p_table_schema
        AND table_name = p_table_name
        AND constraint_type = 'PRIMARY KEY';
    
    IF v_constraint_name IS NOT NULL THEN
        EXECUTE format('ALTER TABLE %I.%I DROP CONSTRAINT %I',
            p_table_schema, p_table_name, v_constraint_name);
    END IF;
    
    -- Add the new primary key constraint
    EXECUTE format('ALTER TABLE %I.%I ADD PRIMARY KEY (%I)',
        p_table_schema, p_table_name, p_column_name);
    
    RAISE NOTICE 'Column % in table %.% is now the primary key',
        p_column_name, p_table_schema, p_table_name;
END;
$$ LANGUAGE plpgsql;


-- Example usage:
SELECT force_column_as_primary_key('public', 'athena_clients', 'id');
SELECT force_column_as_primary_key('public', 'client_statistics', 'id');
SELECT force_column_as_primary_key('public', 'client_table_statistics', 'id');
SELECT force_column_as_primary_key('public', 'client_alert_queries', 'id');