-- Goal: consolidate all duplicate users into 1 user by choosing the minima, assigning all other
-- copies' permissions to that minima, and then deleting the rest
-- Create a minima table ()
-- Tested on MySQL


DROP TABLE
    IF EXISTS external_entities_min;
CREATE
    TABLE external_entities_min AS
    (
        SELECT
            MIN(id)AS id,
            name,
            'EXT' AS type
        FROM
            external_entities ee
        GROUP BY
            name
    );
ALTER TABLE
    external_entities_min ADD PRIMARY KEY (id);
--Create a join set
DROP TABLE
    IF EXISTS compareset;
CREATE
    TABLE compareset AS
    (
        SELECT
            eem.id          AS min_id,
            emt.groupid     AS source_group_id ,
            emt.extentityid AS source_ent_id
        FROM
            external_members emt
        JOIN external_entities ee
        ON
            emt.extentityid = ee.id
        JOIN external_entities_min eem
        WHERE
            eem.name = ee.name
    );


--POINT OF NO RETURN.  DO NOT DO THIS IF YOU HAVE NO BACKUP

--Clear the existing table
TRUNCATE external_members;


--Repopulate the table with just the minimum values 
INSERT
INTO
    external_members
    (
        SELECT
            min_id,
            source_group_id
        FROM
            compareset c
        GROUP BY
            min_id,
            source_group_id
    );



-- Remove all duplicate users.
DELETE
FROM
    external_entities
WHERE
    id NOT IN
    (
        SELECT
            id
        FROM
            external_entities_min
    );
--Cleanup
DROP TABLE
    IF EXISTS compareset;
DROP TABLE
    IF EXISTS external_entities_min;