import pandas as pd
import json
import argparse
import re
import os

# =========================
# 1) CLEAN COLUMN NAMES
# =========================
def clean_column_name(col):
    col = str(col)
    col = col.replace("\ufeff", "")  # UTF-8 BOM (if any)
    col = col.replace("", "")     # BOM mis-decoded as latin1
    col = col.lower()
    col = re.sub(r"[^\w\s]", "", col)
    col = col.strip().replace(" ", "_")
    col = re.sub(r"_+", "_", col)
    return col


# =========================
# 2) DETECT DATA TYPE
# =========================
def detect_dtype(series, col_name):

    if "id" in col_name:
        return "varchar"

    numeric = pd.to_numeric(series, errors='coerce')
    if numeric.notna().sum() > 0.9 * len(series):
        if (numeric.dropna() % 1 == 0).all():
            return "int"
        return "float"

    try:
        parsed = pd.to_datetime(series, errors='coerce')
        if parsed.notna().sum() > 0.8 * len(series):
            return "date"
    except:
        pass

    return "varchar"


# =========================
# 3) MULTI DETECTION
# =========================
def detect_multiple_options(series):

    multi_count = 0
    total = 0

    for val in series.dropna():

        if isinstance(val, str):

            # ❌ Skip file/image values
            if any(ext in val.lower() for ext in [".jpg", ".jpeg", ".png", ".pdf"]):
                continue

            total += 1

            if any(d in val for d in [",", "|", "/"]):
                parts = re.split(r"[,|/]", val)
                parts = [p.strip() for p in parts if p.strip()]

                if len(parts) > 1:
                    multi_count += 1

    if total == 0:
        return False

    return (multi_count / total) > 0.3   # 🔥 threshold


# =========================
# 4) MAIN PROCESS
# =========================
def generate_schema(df, lookup_threshold):

    df = df.loc[:, ~df.columns.str.contains("^Unnamed")]

    for col in df.select_dtypes(include=["object"]).columns:
        df[col] = df[col].str.strip()

    # Clean column names
    original_cols = df.columns.tolist()
    cleaned_cols = [clean_column_name(c) for c in original_cols]
    df.columns = cleaned_cols

    submissions = []
    lookup_tables = []

    col_id_counter = 1

    for col in df.columns:

        series = df[col]

        dtype = detect_dtype(series, col)
        nullable = series.isna().any()

        is_coord = is_coordinate_column(series)

        if is_coord:
            multiple_options_flag = False
        else:
            multiple_options_flag = detect_multiple_options(series)


        if dtype in ["date", "datetime"]:
            multiple_options_flag = False


        sample_val = " ".join(series.dropna().astype(str).head(3).tolist()).lower()
        if any(ext in sample_val for ext in [".jpg", ".jpeg", ".png", ".pdf"]):
            multiple_options_flag = False

        # =========================
        # VALUE COLLECTION
        # =========================
        all_values = set()

        for val in series.dropna():

            if isinstance(val, str):

                parts = re.split(r"[,|/]", val) if multiple_options_flag else [val]

                for p in parts:
                    clean_val = p.strip()
                    if clean_val:
                        all_values.add(clean_val)

        unique_count = len(all_values)

        # =========================
        # LOOKUP LOGIC (your logic)
        # =========================
        if multiple_options_flag:
            lookup_flag = True

        if unique_count <= lookup_threshold:
            lookup_flag = True
        else:
            lookup_flag = False

        length_value = 255 if dtype == "varchar" else ""

        col_id = f"COL_{col_id_counter}"

        column_obj = {
            "id": col_id,
            "name": col,
            "multiple_options": multiple_options_flag,
            "datatype": "varchar",
            "nullable": bool(nullable),
            "predefined": "",
            "length": 255,
            "is_lookup": lookup_flag,
            "lookup_reference": ""
        }

        # =========================
        # LOOKUP TABLE
        # =========================
        mapping = {}

        if lookup_flag and len(all_values) > 1:
            mapping = {str(i + 1): v for i, v in enumerate(sorted(all_values))}

            table_name = f"{col_id}_lookup"
            column_obj["lookup_reference"] = table_name

            lookup_tables.append({
                "table_name": table_name,
                "Name": {
                    "options": mapping
                },
                "dtype": "varchar"
            })

        submissions.append(column_obj)

        col_id_counter += 1

    final_json = {
        "Schema": {
            "tables": {
                "submissions": submissions,
                "lookup_tables": lookup_tables,
            }
        }
    }

    return final_json

def is_coordinate_column(series):
    sample = series.dropna().astype(str).head(20)

    coord_pattern = re.compile(r"^\s*-?\d+(\.\d+)?\s*,\s*-?\d+(\.\d+)?\s*$")

    match_count = 0

    for val in sample:
        if coord_pattern.match(val):
            match_count += 1

    if len(sample) == 0:
        return False

    return (match_count / len(sample)) > 0.7



# =========================
# 5) CLI
# =========================
def main():

    parser = argparse.ArgumentParser()
    parser.add_argument("--input", required=True)
    parser.add_argument("--output", default="schema.json")
    parser.add_argument("--lookup_threshold", type=int, default=20)

    args = parser.parse_args()

    try:
        df = pd.read_csv(args.input, encoding="utf-8-sig", low_memory=False)
    except Exception:
        try:
            df = pd.read_csv(args.input, encoding="latin1", low_memory=False)
        except Exception:
            df = pd.read_csv(args.input, low_memory=False)

    schema = generate_schema(df, args.lookup_threshold)

    # Save main schema
    with open(args.output, "w") as f:
        json.dump(schema, f, indent=2)

    # Counters
    total_submissions = len(schema["Schema"]["tables"]["submissions"])
    total_lookups = len(schema["Schema"]["tables"]["lookup_tables"])

    print(f"Total Columns: {total_submissions}")
    print(f"Lookup Tables: {total_lookups}")
    print("Child Tables: 0")

    print("Done. Schema generated.")


if __name__ == "__main__":
    main()