import openpyxl
import csv
from datetime import datetime, timedelta
import os
import re

source_file = r'c:\laragon\www\sgmi\docs\estudiantes activos 2023-2026.xlsx'
output_file = r'c:\laragon\www\sgmi\storage\app\imports\seeders\estudiantes_final.csv'

# Target Headers for the application
# name,email,cedula,tipo_documento,telefono,fecha_nacimiento,genero,profesion,empresa,cargo,nexo,direccion,role,fecha_emision,fecha_vencimiento,estado_carnet,observaciones
TARGET_HEADERS = [
    'name', 'email', 'cedula', 'tipo_documento', 'telefono', 'fecha_nacimiento', 
    'genero', 'profesion', 'empresa', 'cargo', 'nexo', 'direccion', 'role', 
    'fecha_emision', 'fecha_vencimiento', 'estado_carnet', 'observaciones'
]

def format_date(val):
    if not val:
        return ""
    if isinstance(val, datetime):
        return val.strftime('%Y-%m-%d')
    try:
        # Try DD-MM-YYYY
        dt = datetime.strptime(str(val).strip(), '%d-%m-%Y')
        return dt.strftime('%Y-%m-%d')
    except:
        try:
            # Try YYYY-MM-DD
            dt = datetime.strptime(str(val).strip(), '%Y-%m-%d')
            return dt.strftime('%Y-%m-%d')
        except:
            return str(val)

def clean_cedula(val):
    if not val:
        return ""
    # Strip everything that is NOT a digit (to pass app validation)
    return re.sub(r'\D', '', str(val))

def convert():
    print(f"Opening {source_file}...")
    wb = openpyxl.load_workbook(source_file, data_only=True, read_only=True)
    sheet = wb.active
    
    # Track duplicates within the file
    processed_emails = set()
    processed_cedulas = set()
    
    # Dates for mandatory fields
    current_date = datetime.now()
    fecha_emision = current_date.strftime('%Y-%m-%d')
    # Default expiry: 2 years from now
    fecha_vencimiento = (current_date + timedelta(days=730)).strftime('%Y-%m-%d')
    
    with open(output_file, mode='w', newline='', encoding='utf-8') as f:
        writer = csv.DictWriter(f, fieldnames=TARGET_HEADERS)
        writer.writeheader()
        
        count = 0
        duplicates_skipped = 0
        invalid_ids = 0
        
        # Start from row 10 (data according to previous inspection)
        for row in sheet.iter_rows(min_row=10):
            # Column mapping based on Excel:
            # 2: Tip, 3: Identificacion, 4: Apellido, 5: Nombre, 6: Sex, 9: Fe Nac, 23: Telefono, 26: Email Inst, 27: Email Pers
            
            cedula_raw = str(row[3].value).strip() if row[3].value else ""
            if not cedula_raw or cedula_raw == "Identificación" or cedula_raw == "None":
                continue
            
            # Application requires numeric cedula
            cedula = clean_cedula(cedula_raw)
            if not cedula:
                invalid_ids += 1
                continue
            
            # Email priority: Inst then Pers
            email_inst = str(row[26].value).strip() if row[26].value else ""
            email_pers = str(row[27].value).strip() if row[27].value else ""
            email = email_inst if email_inst else email_pers
            email = email.lower()

            if not email:
                continue

            # Check for duplicates within the XLSX file itself
            if email in processed_emails or cedula in processed_cedulas:
                duplicates_skipped += 1
                continue
                
            processed_emails.add(email)
            processed_cedulas.add(cedula)
            
            apellido = str(row[4].value).strip() if row[4].value else ""
            nombre = str(row[5].value).strip() if row[5].value else ""
            
            data = {
                'name': f"{nombre} {apellido}".strip(),
                'email': email,
                'cedula': cedula,
                'tipo_documento': str(row[2].value).strip() if row[2].value else "V",
                'telefono': str(row[23].value).strip() if row[23].value else "",
                'fecha_nacimiento': format_date(row[9].value),
                'genero': str(row[6].value).strip().upper()[0] if row[6].value else "",
                'profesion': 'Estudiante',
                'empresa': 'IESA',
                'cargo': 'Estudiante',
                'nexo': 'Estudiante',
                'direccion': '',
                'role': 'user',
                'fecha_emision': fecha_emision,
                'fecha_vencimiento': fecha_vencimiento,
                'estado_carnet': 'disponible',
                'observaciones': 'Importación masiva (Corregido: Dúplicas y Formato)'
            }
            
            writer.writerow(data)
            count += 1
            
    print(f"Finished!")
    print(f"- Processed: {count}")
    print(f"- Duplicates within file skipped: {duplicates_skipped}")
    print(f"- Rows with invalid IDs skipped: {invalid_ids}")
    print(f"Output available at: {output_file}")

if __name__ == "__main__":
    convert()