-- back up tables 
CREATE TABLE threegcell_backup_20251115
    AS
        SELECT
            *
        FROM
            threegcell;

CREATE TABLE cell_backup_20251115
    AS
        SELECT
            *
        FROM
            cell;

CREATE TABLE threegcarrier_backup_20251115
    AS
        SELECT
            *
        FROM
            threegcarrier;

-- for THREEGCARRIER
DELETE FROM threegcarrier
WHERE
    carrierid IN (
        SELECT
            b.carrierid
        FROM
                 threegcarrier b
            INNER JOIN cell c ON b.cellid = c.cellid
            INNER JOIN (
                SELECT
                    c.objectid, c.cellname, g.nodebid, MAX(c.insertdate) AS latest_insert
                FROM
                         threegcell g
                    INNER JOIN cell c ON g.cellid = c.cellid
                GROUP BY
                    c.objectid, c.cellname,
                    g.nodebid
                HAVING
                    COUNT(*) > 1
            )    d ON c.objectid = d.objectid
                   AND c.cellname = d.cellname
                   AND c.insertdate <> d.latest_insert
    );
    
    
    -- for threeg cell 
DELETE FROM threegcell
WHERE
    cellid IN (
        SELECT
            g.cellid
        FROM
                 threegcell g
            INNER JOIN cell c ON g.cellid = c.cellid
            INNER JOIN (
                SELECT
                    c.objectid, c.cellname, g.nodebid, MAX(c.insertdate) AS latest_insert
                FROM
                         threegcell g
                    INNER JOIN cell c ON g.cellid = c.cellid
                GROUP BY
                    c.objectid, c.cellname,
                    g.nodebid
                HAVING
                    COUNT(*) > 1
            )    d ON c.objectid = d.objectid
                   AND c.cellname = d.cellname
                   AND c.insertdate <> d.latest_insert
    );


-- for  cell 
DELETE FROM cell
WHERE
    cellid IN (
        SELECT
            c.cellid
        FROM
                 threegcell_backup_20251115 g
            INNER JOIN cell c ON g.cellid = c.cellid
            INNER JOIN (
                SELECT
                    c.objectid, c.cellname, g.nodebid, MAX(c.insertdate) AS latest_insert
                FROM
                         threegcell_backup_20251115 g
                    INNER JOIN cell c ON g.cellid = c.cellid
                GROUP BY
                    c.objectid, c.cellname,
                    g.nodebid
                HAVING
                    COUNT(*) > 1
            )    d ON c.objectid = d.objectid
                   AND c.cellname = d.cellname
                   AND c.insertdate <> d.latest_insert
    );

COMMIT;