import psycopg2
import csv

# Database connection parameters
conn_params = {
    'dbname': 'bdr_unified',
    'user': 'mylar',
    'password': 'Myl@r1999',
    'host': '127.0.0.1',
    'port': '5432'
}

# Function to format phone numbers
def format_phone_number(phone_number):
    # Remove any non-numeric characters
    cleaned_number = ''.join(filter(str.isdigit, phone_number))

    # Ensure the number starts with 233 and is exactly 12 digits long
    if cleaned_number.startswith('0'):
        # Replace leading 0 with 233
        cleaned_number = '233' + cleaned_number[1:]
    elif not cleaned_number.startswith('233'):
        # Add 233 if it doesn't start with it
        cleaned_number = '233' + cleaned_number

    # Ensure the number is exactly 12 digits long
    if len(cleaned_number) > 12:
        # Truncate to 12 digits
        cleaned_number = cleaned_number[:12]
    elif len(cleaned_number) < 12:
        # Pad with zeros (if necessary, though this is unlikely for Ghanaian numbers)
        cleaned_number = cleaned_number.ljust(12, '0')

    return cleaned_number

# Connect to the database
conn = psycopg2.connect(**conn_params)
cur = conn.cursor()

# Execute the SQL query
cur.execute("""
    SELECT DISTINCT informant_phone_number AS phone_number
    FROM public.late_birth_registrations
    WHERE informant_phone_number IS NOT NULL
    UNION
    SELECT DISTINCT mother_phone_number AS phone_number
    FROM public.late_birth_registrations
    WHERE mother_phone_number IS NOT NULL
    UNION
    SELECT DISTINCT father_phone_number AS phone_number
    FROM public.late_birth_registrations
    WHERE father_phone_number IS NOT NULL;
""")

# Fetch all results
phone_numbers = cur.fetchall()

# Process phone numbers
formatted_phone_numbers = [format_phone_number(row[0]) for row in phone_numbers]

# Write to CSV
with open('unique_phone_numbers.csv', 'w', newline='') as csvfile:
    writer = csv.writer(csvfile)
    writer.writerow(['phone_number'])  # Write header
    for number in formatted_phone_numbers:
        writer.writerow([number])

# Close the database connection
cur.close()
conn.close()

print("Phone numbers have been written to unique_phone_numbers.csv")