-- Un-duplicate partners (same name, same address)
-- This meta-script records the rows that need to be updated accross the database in order to un-duplicates partners, and prints an SQL script which executes the un-duplication

-- To remove a duplicate partner:
-- * make sure it's a child of the one you want to keep
-- * empty the fields partner-number, phone, fax, mobile, email
-- * check the box "use company address"

-- To only record and display the needed changes but not execute them:
--   psql -d <database> < duplicate_partners.sql

-- To record the changes and execute them:
--  psql -d <database> < duplicate_partners.sql | psql -d <database> --echo-all

-- This script is idempotent: running it multiple times only process the new duplicates
-- Make sure no new foreign key pointing to res_partner is created between the time you record the changes and the tim you apply them. 

-- This fixes a mistake we made in premigration:
-- What happened for is that we wrongly pre-processed our database to make sure all contacts had a name, to keep the migration script from calling them "PARTNER WITH NO NAME" as per your comment from the migration case 604296 :
--    ''' 2) Another case is when an address has null name but also a partner_id, but that partner has more than one related (child) addresses. Then this address too will have a name as "PARTNER WITH NO NAME". '''
-- As it turns out, this was a bad decision, not knowing that it would make the migration script duplicate them.
-- So now we have to un-duplicate them.

\set QUIET
\t ON
\a
\f ' '

-- Table to store the changes to do to unduplicate the partners
CREATE TABLE IF NOT EXISTS dedup_res_partner_changes (
  fk_table varchar,
  fk_column varchar,
  old_partner_id integer,
  new_partner_id integer,
  execute_date timestamp without time zone
);

-- View to easily find duplicates
CREATE OR REPLACE VIEW dedup_res_partner_duplicates AS (
  -- partners with a single contact with the same name
  SELECT 
    parent.id as parent_id,
    child.id as child_id
  FROM res_partner parent
    -- same name, same address
    INNER JOIN res_partner child ON (
      child.parent_id=parent.id
      AND (child.name=parent.name or child.name='PARTNER WITH NO NAME')
      AND (child.city IS NULL OR child.city=coalesce(parent.city,''))
      AND (child.street IS NULL OR child.street=coalesce(parent.street,''))
      AND (child.street2 IS NULL OR child.street2=coalesce(parent.street2,''))
      AND (child.zip IS NULL OR child.zip=coalesce(parent.zip,''))
      AND (child.email IS NULL OR child.email=coalesce(parent.email,''))
      AND (child.mobile IS NULL OR child.mobile=coalesce(parent.mobile,''))
      AND (child.phone IS NULL OR child.phone=coalesce(parent.phone,''))
    )
  WHERE child.active
    AND coalesce(child.ref, '')=''
);

CREATE OR REPLACE VIEW dedup_res_partner_foreign_key AS (
  -- all the foreign keys that have to be updated
  -- inspired from http://stackoverflow.com/questions/1152260/postgres-sql-to-list-table-foreign-keys
  SELECT
      tc.table_name, kcu.column_name
  FROM
      information_schema.table_constraints AS tc 
      JOIN information_schema.key_column_usage AS kcu
        ON tc.constraint_name = kcu.constraint_name
      JOIN information_schema.constraint_column_usage AS ccu
        ON ccu.constraint_name = tc.constraint_name
  WHERE constraint_type = 'FOREIGN KEY' AND ccu.table_name='res_partner');


-- delete the changes already recorded but never applied
DELETE FROM dedup_res_partner_changes
WHERE execute_date IS NULL;

-- Record changes to do to redirect all the foreign keys pointing to the children partners, to now point to the parents
INSERT INTO dedup_res_partner_changes (
  fk_table,
  fk_column,
  old_partner_id,
  new_partner_id)
SELECT
  table_name,
  column_name,
  child_id,
  parent_id
FROM
  -- yes we want a carthesian product, not a join
  dedup_res_partner_foreign_key, dedup_res_partner_duplicates;



-- Now, generate the SQL script to apply the pending changes
\echo ----------------------------------------------------------------------
\echo -- THE NEEDED CHANGES HAVE BEEN RECORDED IN dedup_res_partner_changes.
\echo -- TO APPLY THE CHANGES, EXECUTE THE FOLLOWING SQL SCRIPT.
\echo ----------------------------------------------------------------------
\echo BEGIN;

\echo -- Apply the changes recorded in dedup_res_partner_changes
SELECT
  'UPDATE', fk_table,
  'SET', fk_column, '=', new_partner_id,
  'WHERE', fk_column, '=', old_partner_id, ';'
FROM dedup_res_partner_changes
WHERE execute_date IS NULL
ORDER BY new_partner_id, old_partner_id, fk_table, fk_column;

\echo -- Notify the deduplication in the partner's    chat
VALUES
  ('INSERT INTO mail_message (type, model, res_id, author_id, subject, body, date)'),
  ('SELECT DISTINCT ''notification'', ''res.partner'', new_partner_id, 1, ''Dé-doublonnage'', ''Ce partenaire a été dédoublonné avec le partenaire identifié sous le n°''||old_partner_id||'' : même nom ; adresse/tél/mobile/fax/email identiques ou non-renseignés ; contact sans n° de client.'', now()'),
  ('FROM dedup_res_partner_changes'),
  ('WHERE execute_date IS NULL;');

\echo -- Deactivate the children
\echo UPDATE res_partner
\echo SET active=FALSE
\echo WHERE id in (
\echo   SELECT old_partner_id
\echo   FROM dedup_res_partner_changes
\echo   WHERE execute_date IS NULL
\echo );

\echo -- Mark the changes executed
\echo UPDATE dedup_res_partner_changes
\echo SET execute_date=now()
\echo WHERE execute_date IS NULL;

\echo COMMIT;

