#! /usr/bin/python3

# SPDX-FileCopyrightText: 2023 Andrew Engelbrecht
#
# SPDX-License-Identifier: GPL-3.0-or-later

import mysql.connector
from datetime import date
import sys


static_group_id = "1983"


condition_email = "e.email = x.email"
condition_names = "c.first_name = x.first_name and c.last_name = x.last_name"

def command_help():
    sys.stderr.write("correct usage: find-mailing-duplicates.py [-n]")

if len(sys.argv) > 2:
    command_help
    exit(1)
elif len(sys.argv) == 2 and sys.argv[1] == '-n':
    condition = condition_names
elif len(sys.argv) == 2:
    command_help
    exit(1)
else:
    condition = condition_email


sys.stderr.write('\nstarting new run... %s\n\n' % date.today())

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password=sys.argv[1],
  database="civicrm"
)

mycursor0 = mydb.cursor(buffered=True)
mycursor1 = mydb.cursor(buffered=True)
mycursor2 = mydb.cursor(buffered=True)

"search for pairs of contacts with matching criteria (name or email), of which each has at least one address."

mycursor0.execute(
"""
select c.id, x.id
from civicrm_contact c
inner join civicrm_email e on c.id = e.contact_id
inner join civicrm_address a on c.id = a.contact_id
inner join (
    select i.id, k.street_address, i.is_deleted, j.email, i.first_name, i.last_name
    from civicrm_contact i
    inner join civicrm_email j on i.id = j.contact_id
    inner join civicrm_address k on i.id = k.contact_id
    group by i.id
    having count(i.id) > 0
) x on {0}
where c.id != x.id and c.id > x.id and a.street_address is not NULL and x.street_address is not NULL and c.is_deleted != 1 and x.is_deleted != 1
group by c.id
having count(c.id) > 0;
""".format(condition)
)

for row in mycursor0:
    id1 = row[0] # c.id
    id2 = row[1] # x.id

    mycursor1.execute("select count(*) from civicrm_dedupe_exception where (contact_id1 = %s and contact_id2 = %s) or (contact_id1 = %s and contact_id2 = %s);", (id1, id2, id2, id1))

    count1 = mycursor1.fetchone()[0]
    if count1 == 0:
        mycursor2.execute(f"select count(*) from civicrm_group_contact where (contact_id = {id1} or contact_id = {id2}) and group_id = {static_group_id};")

        count2 = mycursor2.fetchone()[0]
        if count2 > 0:
            print(f'https://crm.fsf.org/civicrm/contact/merge?reset=1&cid={id1}&oid={id2}')

mydb.commit()