import re
import shutil
import pandas as pd
from openpyxl import load_workbook


def extract_coord(url, param):
    match = re.search(rf'[?&]{param}=([-\d.]+)', str(url))
    return match.group(1) if match else None


def process_data(source_file_path, destination_template_path, output_path):
    # Read source CSV (semicolon-delimited)
    src = pd.read_csv(source_file_path, sep=';')
    src.columns = src.columns.str.strip()

    def build_address(row):
        parts = []
        if pd.notna(row.get('civique')):
            parts.append(str(int(row['civique'])))
        if pd.notna(row.get('appartement')):
            parts.append(f"App. {row['appartement']}")
        if pd.notna(row.get('rue')):
            parts.append(str(row['rue']).strip())
        return ' '.join(parts)

    # Copy the template so we preserve its formatting/styles
    shutil.copy(destination_template_path, output_path)
    wb = load_workbook(output_path)
    ws = wb.active

    # Map column names to their Excel column index (based on header row 1)
    header = {cell.value: cell.column for cell in ws[1]}

    # Write each row starting at row 2
    for i, row in src.iterrows():
        excel_row = i + 2  # row 1 is the header

        ws.cell(excel_row, header['ID de Ligne'])          .value = (i + 1) * 100
        ws.cell(excel_row, header['Province'])              .value = 'PQ'
        ws.cell(excel_row, header['Code barre'])            .value = str(row['codeBar']).strip()
        ws.cell(excel_row, header['Latitude'])              .value = extract_coord(row['lien map targo'], 'y')
        ws.cell(excel_row, header['Longitude'])             .value = extract_coord(row['lien map targo'], 'x')
        ws.cell(excel_row, header['Numéro Électrique'])     .value = str(row['tagHydro']).strip()
        ws.cell(excel_row, header['Adresse'])               .value = build_address(row)
        ws.cell(excel_row, header['Ville'])                 .value = str(row['ville']).strip()
        ws.cell(excel_row, header['Traitement de poteau'])  .value = 'non traitée'

    wb.save(output_path)
    print(f"Done! {len(src)} row(s) written into {output_path}")


if __name__ == "__main__":
    SOURCE_FILE   = r"C:\Users\Antoine\PycharmProjects\excel_import\selectionElements.csv"
    TEMPLATE_FILE = r"C:\Users\Antoine\PycharmProjects\excel_import\CAPermitPoteauxImportation_Gabarit_fr-CA.xlsx"
    OUTPUT_FILE   = r"C:\Users\Antoine\PycharmProjects\excel_import\CAPermit_Final_Output.xlsx"

    process_data(SOURCE_FILE, TEMPLATE_FILE, OUTPUT_FILE)