import os
import openpyxl
import csv

# Function to parse range input (e.g., "3-6,8,10-12")
def parse_column_ranges(range_input):
    selected_columns = []
    ranges = range_input.split(',')
    for r in ranges:
        if '-' in r:
            start, end = map(int, r.split('-'))
            selected_columns.extend(range(start, end + 1))
        else:
            selected_columns.append(int(r))
    return sorted(set(selected_columns))

# Step 1: List all .xlsx files in the current directory
xlsx_files = [f for f in os.listdir('.') if f.endswith('.xlsx')]

if not xlsx_files:
    print("Δεν βρέθηκαν αρχεία .xlsx σε αυτό τον φάκελο.")
else:
    # Display available files to the user
    print("Διαθέσιμα αρχεία:")
    for idx, file_name in enumerate(xlsx_files):
        print(f"{idx + 1}: {file_name}")
    
    # Prompt the user to select a file
    file_choice = int(input("Βάλε τον αριθμό του αρχείου προς επεξεργασία: ")) - 1
    selected_file = xlsx_files[file_choice]

    # Load the selected workbook
    workbook = openpyxl.load_workbook(selected_file)
    print(f"\nΑρχείο '{selected_file}' ανοίχτηκε επιτυχώς.\n")

    # Step 2: Display available sheets in the workbook
    sheet_names = workbook.sheetnames
    print("Διαθέσιμα φύλλα εργασίας:")
    for idx, sheet_name in enumerate(sheet_names):
        print(f"{idx + 1}: {sheet_name}")
    
    # Prompt the user to select a sheet
    sheet_choice = int(input("Διάλεξε φύλο εργασίας προς επεξεργασία: ")) - 1
    selected_sheet = workbook[sheet_names[sheet_choice]]

    print(f"\nΕπιλέχτηκε το φύλλο εργασίας {sheet_names[sheet_choice]} επιτυχώς")

    # Step 3: Display first row values to allow column selection
    first_row = list(selected_sheet.iter_rows(values_only=True, max_row=1))[0]
    print("\nΤίτλοι στήλων:")
    for idx, header in enumerate(first_row):
        print(f"{idx + 1}: {header}")

    # Prompt the user to select column ranges
    range_input = input("Βάλε εύρος στήλων για μετατροπή (e.g., 3-6,8,10-12): ")
    selected_columns = parse_column_ranges(range_input)

    # Step 4: Prompt user to rename selected column headers for ASCII compatibility
    new_headers = {}
    print("\nΜετονόμασε τις μεταβλητές ώστε να είναι συμβατές με το SPSS:")
    for col_idx in selected_columns:
        original_header = first_row[col_idx - 1]
        new_header = input(f"Μετονόμασε την μεταβλητή '{original_header}' (μόνο λατινικοί χαρακτήρες, όχι κενά): ")
        new_headers[col_idx] = new_header

    # Prepare the output file for mappings
    mapping_filename = "valueLabels.txt"
    with open(mapping_filename, "w", encoding="utf-8") as map_file:
        map_file.write(f"Value Labels για SPSS\n\n")

        # Step 5: Create a new workbook for the remapped data
        new_workbook = openpyxl.Workbook()
        new_sheet = new_workbook.active
        new_sheet.title = "data"

        # Add 'id' column header
        new_sheet["A1"] = "id"

        # Add the renamed headers for the selected columns
        for idx, col_idx in enumerate(selected_columns, start=2):
            new_sheet.cell(row=1, column=idx).value = new_headers[col_idx]

        # Add id values starting from 1
        for row_idx in range (2,selected_sheet.max_row + 1):
            new_sheet.cell(row=row_idx, column=1).value = row_idx -1 # Incremental id

        # CSV data container
        csv_data = [["id"] + [new_headers[col] for col in selected_columns]]

        # Process each selected column independently
        column_mappings = {col: {} for col in selected_columns}  # Store mappings per column
        next_numbers = {col: 1 for col in selected_columns}  # Track next number per column

        for row_idx in range(2, selected_sheet.max_row + 1):
            # Start the new row with the ID
            new_row = [row_idx - 1]  # ID starts from 1

            for idx, col in enumerate(selected_columns, start=2):
                cell_value = selected_sheet.cell(row=row_idx, column=col).value

                # Check if the value is already mapped
                if cell_value not in column_mappings[col]:
                    column_mappings[col][cell_value] = next_numbers[col]
                    next_numbers[col] += 1

                # Retrieve the mapped value
                mapped_value = column_mappings[col][cell_value]

                # Write the mapped value to the new sheet
                new_sheet.cell(row=row_idx, column=idx).value = mapped_value
                new_row.append(mapped_value)

            # Append the row to the CSV data
            csv_data.append(new_row)

        # Log mappings
        for col in selected_columns:
            header = new_headers[col]
            map_file.write(f"Μεταβλητή '{header}':\n")
            print(f"\nΜεταβλητή '{header}':")
            for original_value, mapped_value in column_mappings[col].items():
                map_file.write(f"\t{mapped_value}\t-> '{original_value}'\n")
                print(f"\t{mapped_value}\t-> '{original_value}'")
            map_file.write("\n")

    # Save the new workbook with the 'id' column and remapped values
    new_filename_xlsx = "dataForSPSS.xlsx"
    new_workbook.save(new_filename_xlsx)
    print(f"\nΝέο Excel αποθηκεύτηκε ως '{new_filename_xlsx}'")

    # Save the remapped data as a CSV file
    new_filename_csv = "dataForSPSS.csv"
    with open(new_filename_csv, mode="w", newline="", encoding="utf-8") as csv_file:
        csv_writer = csv.writer(csv_file)
        csv_writer.writerows(csv_data)

    print(f"Δεδομένα αποθηκεύτηκαν και στο '{new_filename_csv}'")
    print(f"Value Labels αποθηκεύτηκαν στο '{mapping_filename}'")