from pathlib import Path
import csv
import sqlite3
import re
import string

# =========================
# SETTINGS
# =========================
ROOT = Path(__file__).resolve().parent
IMPORT_DIR = ROOT / "IMPORT"
EXPORT_CSV = ROOT / "CVS-METADATA-AIO.csv"
EXPORT_DB = ROOT / "CVS-METADATA-AIO.db"
OVERFLOW_LOG_CSV = ROOT / "00-OVERFLOW-LOG.csv"
DB_TABLE_NAME = "import_files"

SAMPLE_COUNT = 10
SAMPLE_PREFIX = "SAMPLE"
CSV_EXT = ".csv"
SAMPLE_HEADER_COUNT = 50
SAMPLE_PREFILL_ROWS = 300

# If True:
# Sample CSV files are created in folders that have NO CSV files yet.
# This fixes the old problem where samples only appeared if the folder was 100% empty.
CREATE_SAMPLES_IN_FOLDERS_WITH_NO_CSV = True

SOURCE_CSV_CELL_DELIMITER = " "
T3_EQUAL_CELL_DELIMITER = " | "
SKIP_EXACT_T_HEADERS_ROW = True

DENSE_CODE_COLUMN = "DENSE_CODE"
DENSE_CODE_NUMBER_LENGTH = 4
DENSE_CODE_MAX_NUMBER = 9999

MAX_T_COLUMNS = 50
OVERFLOW_DELIMITER = " | "
SKIP_EMPTY_LINES = True
MAX_CONSECUTIVE_BLANK_LINES_TO_KEEP = 2
NORMALIZE_ESCAPED_NEWLINES = True

# Consecutive rows with /// are concatenated into ONE exported row.
SECTION_MARKER = "///"

# All = or =* tags go into ONE T3 cell.
COLUMN_MARKER = "=*"
PLAIN_EQUAL_MARKER_ALSO_TAG = True


# =========================
# CREATE IMPORT IF MISSING
# =========================
IMPORT_DIR.mkdir(parents=True, exist_ok=True)


# =========================
# OVERFLOW LOG STORAGE
# =========================
OVERFLOW_LOG_ROWS = []


# =========================
# SAMPLE CSV CREATION
# =========================

def sample_clean_cell(value) -> str:
    """
    Local cleaner used before the main helper functions are loaded.
    This avoids skipping folders because sample creation runs near the top.
    """
    return " ".join(
        str(value)
        .replace("\r", " ")
        .replace("\n", " ")
        .replace("\t", " ")
        .split()
    )


def is_sample_csv_file(path: Path) -> bool:
    """
    True only for sample CSV files like:
    001-SAMPLE.csv
    002-SAMPLE.csv
    010-SAMPLE.csv
    999-SAMPLE.csv

    Case-insensitive for extension and sample prefix.
    """
    if not path.is_file():
        return False

    if path.suffix.lower() != CSV_EXT.lower():
        return False

    pattern = rf"^\d+-{re.escape(SAMPLE_PREFIX)}{re.escape(CSV_EXT)}$"
    return re.match(pattern, path.name, flags=re.IGNORECASE) is not None


def sample_csv_is_unused_blank_sample(path: Path) -> bool:
    """
    A sample counts as available only when:
    - It is named like 001-SAMPLE.csv
    - It has the expected T1..T50 header
    - Every data cell is blank

    If you typed real text into it, it is USED.
    Used sample files are never overwritten.
    Used sample files do not count toward SAMPLE_COUNT.
    """
    if not is_sample_csv_file(path):
        return False

    expected_headers = [f"T{i}" for i in range(1, SAMPLE_HEADER_COUNT + 1)]
    expected_headers = [header.upper() for header in expected_headers]

    try:
        with path.open("r", newline="", encoding="utf-8-sig") as f:
            reader = csv.reader(f, delimiter=",")
            rows = list(reader)
    except UnicodeDecodeError:
        try:
            with path.open("r", newline="", encoding="latin-1") as f:
                reader = csv.reader(f, delimiter=",")
                rows = list(reader)
        except Exception:
            return False
    except Exception:
        return False

    if not rows:
        return False

    header = [sample_clean_cell(cell).upper() for cell in rows[0][:SAMPLE_HEADER_COUNT]]

    if header != expected_headers:
        return False

    for row in rows[1:]:
        for cell in row[:SAMPLE_HEADER_COUNT]:
            if sample_clean_cell(cell) != "":
                return False

    return True


def count_unused_blank_samples_in_folder(folder: Path) -> int:
    """
    Count only unused blank sample files in this exact folder.
    Nested folders are checked separately by the walker.
    """
    count = 0

    for item in folder.iterdir():
        if sample_csv_is_unused_blank_sample(item):
            count += 1

    return count


def next_available_sample_file(folder: Path) -> Path:
    """
    Return the next sample filename that does not exist.

    If 001-SAMPLE.csv through 010-SAMPLE.csv already exist and are used,
    the next created file will be 011-SAMPLE.csv.
    """
    number = 1

    while True:
        sample_file = folder / f"{number:03d}-{SAMPLE_PREFIX}{CSV_EXT}"

        if not sample_file.exists():
            return sample_file

        number += 1


def create_one_blank_sample_csv(sample_file: Path):
    """
    Create one blank sample CSV with:
    - T1..T50 headers
    - 300 blank rows
    """
    sample_headers = [f"T{i}" for i in range(1, SAMPLE_HEADER_COUNT + 1)]
    blank_row = [""] * SAMPLE_HEADER_COUNT

    with sample_file.open("w", newline="", encoding="utf-8-sig") as f:
        writer = csv.writer(f, delimiter=",")
        writer.writerow(sample_headers)

        for _ in range(SAMPLE_PREFILL_ROWS):
            writer.writerow(blank_row)


def ensure_folder_has_sample_count(folder: Path):
    """
    Main rule:
    BEFORE moving to the next folder, this folder must have SAMPLE_COUNT
    unused blank sample CSV files.

    This function does not skip folders.
    This function does not overwrite used sample files.
    """
    created_now = 0

    while count_unused_blank_samples_in_folder(folder) < SAMPLE_COUNT:
        sample_file = next_available_sample_file(folder)
        create_one_blank_sample_csv(sample_file)
        created_now += 1

    final_count = count_unused_blank_samples_in_folder(folder)

    print(
        f"SAMPLE CHECK: {folder} | "
        f"UNUSED BLANK SAMPLES: {final_count}/{SAMPLE_COUNT} | "
        f"CREATED NOW: {created_now}"
    )


def create_samples_where_needed():
    """
    Walk IMPORT and all nested folders.

    Do not skip any folder.
    Each folder is handled independently.
    Each folder must have SAMPLE_COUNT unused blank samples before moving on.
    """
    all_folders = [IMPORT_DIR]

    for path in sorted(IMPORT_DIR.rglob("*"), key=lambda x: str(x).lower()):
        if path.is_dir():
            all_folders.append(path)

    for folder in all_folders:
        ensure_folder_has_sample_count(folder)


create_samples_where_needed()


# =========================
# HELPERS
# =========================
def is_empty_text(value: str) -> bool:
    return str(value).strip() == ""


def normalize_escaped_newline_text(text: str) -> str:
    if not NORMALIZE_ESCAPED_NEWLINES:
        return text

    previous = None

    while previous != text:
        previous = text
        text = text.replace("\\r\\n", "\n")
        text = text.replace("\\n", "\n")
        text = text.replace("\\r", "\n")

    return text


def cap_blank_lines_before_import(value: str) -> str:
    text = str(value)

    text = normalize_escaped_newline_text(text)
    text = text.replace("\r\n", "\n").replace("\r", "\n")

    max_blank_lines = int(MAX_CONSECUTIVE_BLANK_LINES_TO_KEEP)

    if max_blank_lines < 0:
        return text

    output_lines = []
    blank_count = 0

    for line in text.split("\n"):
        if line.strip() == "":
            blank_count += 1

            if blank_count <= max_blank_lines:
                output_lines.append("")

            continue

        blank_count = 0
        output_lines.append(line)

    return "\n".join(output_lines)


def clean_all_t_cells_before_export(rows_list):
    for row in rows_list:
        for key in list(row.keys()):
            if key.startswith("T") and key[1:].isdigit():
                row[key] = cap_blank_lines_before_import(row.get(key, ""))


def make_single_line(value) -> str:
    return " ".join(
        str(value)
        .replace("\r", " ")
        .replace("\n", " ")
        .replace("\t", " ")
        .split()
    )


def split_overflow_parts(value) -> list:
    text = str(value)

    text = text.replace("\r\n", "\n")
    text = text.replace("\r", "\n")
    text = text.replace(OVERFLOW_DELIMITER, "\n")

    parts = []

    for part in text.split("\n"):
        clean_part = make_single_line(part)
        if clean_part:
            parts.append(clean_part)

    return parts


def join_overflow_values(*values) -> str:
    parts = []

    for value in values:
        parts.extend(split_overflow_parts(value))

    return OVERFLOW_DELIMITER.join(parts)


# =========================
# DENSE CODE
# =========================
def number_to_letters(number_index: int) -> str:
    """
    Converts:
    0  -> a
    1  -> b
    ...
    25 -> z
    26 -> aa
    27 -> ab
    """
    letters = string.ascii_lowercase
    base = len(letters)

    result = ""
    number_index += 1

    while number_index > 0:
        number_index -= 1
        result = letters[number_index % base] + result
        number_index //= base

    return result


def make_dense_code(index_number: int) -> str:
    """
    Continuous increment:

    a001
    a002
    a003
    ...
    a999
    b001
    b002
    ...
    z999
    aa001
    aa002
    """
    letter_index = index_number // DENSE_CODE_MAX_NUMBER
    number_part = (index_number % DENSE_CODE_MAX_NUMBER) + 1

    letter_text = number_to_letters(letter_index)
    number_text = str(number_part).zfill(DENSE_CODE_NUMBER_LENGTH)

    return f"{letter_text}{number_text}"


def assign_dense_codes(rows_list):
    for index, row in enumerate(rows_list):
        row[DENSE_CODE_COLUMN] = make_dense_code(index)


# =========================
# FINAL CLEANUP / HEADERS / SAVE
# =========================
def force_last_t_column_single_line(row):
    last_column = f"T{MAX_T_COLUMNS}"

    if last_column in row:
        row[last_column] = join_overflow_values(row[last_column])


def force_all_last_t_columns_single_line(rows_list):
    for row in rows_list:
        force_last_t_column_single_line(row)


def build_headers(rows_list):
    max_t_number = 2

    for row in rows_list:
        for key in row.keys():
            if key.startswith("T") and key[1:].isdigit():
                max_t_number = max(max_t_number, int(key[1:]))

    max_t_number = min(max_t_number, MAX_T_COLUMNS)

    t_headers = [f"T{i}" for i in range(1, max_t_number + 1)]

    return [DENSE_CODE_COLUMN] + t_headers


def save_overflow_log():
    log_headers = [
        "ROW_NUMBER",
        "T1",
        "MAX_T_COLUMNS",
        "TARGET_COLUMN",
        "OVERFLOW_SOURCE_COLUMN",
        "OVERFLOW_VALUE",
        "T_LAST_AFTER_CONCAT",
    ]

    with OVERFLOW_LOG_CSV.open("w", newline="", encoding="utf-8-sig") as f:
        writer = csv.DictWriter(f, fieldnames=log_headers, extrasaction="ignore")
        writer.writeheader()
        writer.writerows(OVERFLOW_LOG_ROWS)


def remove_internal_keys(rows_list):
    for row in rows_list:
        keys_to_remove = [
            key for key in row.keys()
            if key.startswith("__")
        ]

        for key in keys_to_remove:
            row.pop(key, None)


def sql_quote_identifier(name: str) -> str:
    return '"' + name.replace('"', '""') + '"'


def save_sqlite_db(db_path: Path, table_name: str, headers_list, rows_list):
    if db_path.exists():
        db_path.unlink()

    quoted_table = sql_quote_identifier(table_name)
    quoted_columns = [sql_quote_identifier(header) for header in headers_list]

    column_definitions = ", ".join(
        f"{quoted_column} TEXT"
        for quoted_column in quoted_columns
    )

    insert_columns = ", ".join(quoted_columns)
    placeholders = ", ".join(["?"] * len(headers_list))
    last_column = f"T{MAX_T_COLUMNS}"

    with sqlite3.connect(db_path) as conn:
        cur = conn.cursor()

        cur.execute(f"CREATE TABLE {quoted_table} ({column_definitions})")

        if rows_list:
            values_to_insert = []

            for row in rows_list:
                db_row = []

                for header in headers_list:
                    value = row.get(header, "")

                    if header.startswith("T") and header[1:].isdigit():
                        value = cap_blank_lines_before_import(value)

                    if header == last_column:
                        value = join_overflow_values(value)

                    db_row.append(value)

                values_to_insert.append(db_row)

            cur.executemany(
                f"INSERT INTO {quoted_table} ({insert_columns}) VALUES ({placeholders})",
                values_to_insert
            )

        conn.commit()


# =========================
# CSV PROCESSING HELPERS
# =========================
def is_exact_t_headers_row(cells) -> bool:
    cleaned = [make_single_line(cell).upper() for cell in cells]
    cleaned = [cell for cell in cleaned if cell]

    if not cleaned:
        return False

    expected = [f"T{i}" for i in range(1, len(cleaned) + 1)]
    return cleaned == expected


def row_has_section_marker(cells) -> bool:
    """
    TRUE only if the CSV row contains /// as its own cell.
    """
    for cell in cells[:SAMPLE_HEADER_COUNT]:
        if make_single_line(cell) == SECTION_MARKER:
            return True

    return False


def remove_section_marker_from_cells(cells) -> list:
    cleaned_cells = []

    for cell in cells[:SAMPLE_HEADER_COUNT]:
        clean_cell = make_single_line(cell)

        if clean_cell == SECTION_MARKER:
            continue

        cleaned_cells.append(cell)

    return cleaned_cells


def extract_embedded_marker_values(value) -> tuple[str, list[str]]:
    """
    Pulls metadata tags out of normal text.

    Supported:
    =*OSHA
    =OSHA

    Example:
    OSH =BUIDSD =CPOMC

    Output:
    normal_text = OSH
    marker_values = ["BUIDSD", "CPOMC"]
    """
    clean_value = cap_blank_lines_before_import(value)
    clean_value = make_single_line(clean_value)

    if clean_value == "":
        return "", []

    if PLAIN_EQUAL_MARKER_ALSO_TAG:
        marker_pattern = r"(?<!\S)(?:=\*|=)\s*([^=]+)"
    else:
        marker_pattern = r"(?<!\S)=\*\s*([^=]+)"

    marker_values = []

    for match in re.finditer(marker_pattern, clean_value):
        marker_value = make_single_line(match.group(1))
        if marker_value:
            marker_values.append(marker_value)

    normal_text = re.sub(marker_pattern, " ", clean_value)
    normal_text = make_single_line(normal_text)

    return normal_text, marker_values


def add_marker_value_to_row(row, marker_value):
    clean_value = make_single_line(marker_value)

    if clean_value.startswith(COLUMN_MARKER):
        clean_value = clean_value[len(COLUMN_MARKER):].strip()
    elif PLAIN_EQUAL_MARKER_ALSO_TAG and clean_value.startswith("="):
        clean_value = clean_value[1:].strip()

    if clean_value:
        row["T3"] = join_overflow_values(row.get("T3", ""), clean_value)


def build_row_from_cells(relative_path: str, cells, row_number: int):
    row = {
        "__ROW_NUMBER": row_number,
        "T1": relative_path,
        "T2": ""
    }

    normal_parts = []

    for cell in cells[:SAMPLE_HEADER_COUNT]:
        normal_text, marker_values = extract_embedded_marker_values(cell)

        for marker_value in marker_values:
            add_marker_value_to_row(row, marker_value)

        if normal_text:
            normal_parts.append(normal_text)

    row["T2"] = SOURCE_CSV_CELL_DELIMITER.join(normal_parts)

    has_t2_text = row["T2"].strip() != ""
    has_t3_text = str(row.get("T3", "")).strip() != ""

    if SKIP_EMPTY_LINES and not has_t2_text and not has_t3_text:
        return None

    return row


def build_row_from_section_block(relative_path: str, block_rows, row_number: int):
    row = {
        "__ROW_NUMBER": row_number,
        "T1": relative_path,
        "T2": ""
    }

    t2_lines = []

    for cells in block_rows:
        cells_without_marker = remove_section_marker_from_cells(cells)
        normal_parts = []

        for cell in cells_without_marker:
            normal_text, marker_values = extract_embedded_marker_values(cell)

            for marker_value in marker_values:
                add_marker_value_to_row(row, marker_value)

            if normal_text:
                normal_parts.append(normal_text)

        logical_line = SOURCE_CSV_CELL_DELIMITER.join(normal_parts)

        if logical_line:
            t2_lines.append(logical_line)

    row["T2"] = cap_blank_lines_before_import("\n".join(t2_lines))

    has_t2_text = row["T2"].strip() != ""
    has_t3_text = str(row.get("T3", "")).strip() != ""

    if SKIP_EMPTY_LINES and not has_t2_text and not has_t3_text:
        return None

    return row


def flush_section_block(rows_list, relative_path, section_block):
    if not section_block:
        return []

    row = build_row_from_section_block(
        relative_path=relative_path,
        block_rows=section_block,
        row_number=len(rows_list) + 1
    )

    if row is not None:
        rows_list.append(row)

    return []


def process_csv_file_by_rows(csv_file: Path, relative_path: str, rows_list):
    try:
        f = csv_file.open("r", newline="", encoding="utf-8-sig")
    except UnicodeDecodeError:
        f = csv_file.open("r", newline="", encoding="latin-1")

    with f:
        reader = csv.reader(f, delimiter=",")
        csv_records = list(reader)

    section_block = []

    for cells in csv_records:
        if SKIP_EXACT_T_HEADERS_ROW and is_exact_t_headers_row(cells):
            continue

        if row_has_section_marker(cells):
            section_block.append(cells)
            continue

        section_block = flush_section_block(rows_list, relative_path, section_block)

        row = build_row_from_cells(
            relative_path=relative_path,
            cells=cells,
            row_number=len(rows_list) + 1
        )

        if row is not None:
            rows_list.append(row)

    section_block = flush_section_block(rows_list, relative_path, section_block)


# =========================
# READ CSV FILES CASE-INSENSITIVE
# =========================
rows = []

csv_files = sorted(
    [
        p for p in IMPORT_DIR.rglob("*")
        if p.is_file() and p.suffix.lower() == CSV_EXT
    ],
    key=lambda x: str(x).lower()
)

for csv_file in csv_files:
    relative_path = csv_file.relative_to(IMPORT_DIR).as_posix()
    process_csv_file_by_rows(csv_file, relative_path, rows)


# =========================
# FINAL PROCESSING
# =========================
clean_all_t_cells_before_export(rows)
force_all_last_t_columns_single_line(rows)

assign_dense_codes(rows)

headers = build_headers(rows)

save_overflow_log()
remove_internal_keys(rows)


# =========================
# SAVE CSV
# =========================
with EXPORT_CSV.open("w", newline="", encoding="utf-8-sig") as f:
    writer = csv.DictWriter(f, fieldnames=headers, extrasaction="ignore")
    writer.writeheader()

    for row in rows:
        safe_row = {}

        for header in headers:
            value = row.get(header, "")

            if header.startswith("T") and header[1:].isdigit():
                value = cap_blank_lines_before_import(value)

            safe_row[header] = value

        writer.writerow(safe_row)


# =========================
# SAVE SQLITE DB
# =========================
save_sqlite_db(EXPORT_DB, DB_TABLE_NAME, headers, rows)


print("DONE")
print(f"IMPORT FOLDER: {IMPORT_DIR}")
print(f"CSV FILES FOUND: {len(csv_files)}")
print(f"ROWS EXPORTED: {len(rows)}")
print(f"COLUMNS EXPORTED: {len(headers)}")
print(f"EXPORT CSV: {EXPORT_CSV}")
print(f"EXPORT DB: {EXPORT_DB}")
print(f"OVERFLOW LOG CSV: {OVERFLOW_LOG_CSV}")
print(f"OVERFLOW VALUES LOGGED: {len(OVERFLOW_LOG_ROWS)}")
print(f"DB TABLE: {DB_TABLE_NAME}")