#!/usr/bin/env bash

set -euo pipefail

SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
INFODATA_ROOT="${INFODATA_ROOT:-/home/ubuntu/springtime-infodata}"
INFODATA_GIT_URL="${INFODATA_GIT_URL:-}"
INFODATA_DEPLOY_KEY="${INFODATA_DEPLOY_KEY:-/home/ubuntu/.config/springtime-generator/infodata_deploy_key}"
OUTPUT_DIR="${OUTPUT_DIR:-$SCRIPT_DIR/infodata}"
DATABASE_NAME="${DATABASE_NAME:-springtime}"
MYSQL_BIN="${MYSQL_BIN:-mysql}"
MYSQL_DEFAULTS_FILE="${MYSQL_DEFAULTS_FILE:-}"
OUTPUT_PATH="$OUTPUT_DIR/$(date +%Y%m%d%H%M%S).sql"
APPLY_DB=false
UPDATE=false
NO_TRANSLATE=false

if [[ -n "$INFODATA_GIT_URL" ]]; then
    export GIT_SSH_COMMAND="ssh -i $INFODATA_DEPLOY_KEY -o IdentitiesOnly=yes -o StrictHostKeyChecking=yes -o UserKnownHostsFile=/home/ubuntu/.config/springtime-generator/known_hosts"
fi

for arg in "$@"; do
    case "$arg" in
        --applydb)
            APPLY_DB=true
            ;;
        --update)
            UPDATE=true
            ;;
        --no-translate)
            NO_TRANSLATE=true
            ;;
        --help|-h)
            echo "Usage: $0 [--update] [--applydb] [--no-translate]"
            echo "  --update   Update the infodata Git repository before generating SQL"
            echo "  --applydb  Apply the generated SQL with: mysql -u root -p < file.sql"
            echo "  --no-translate  Use the current Korean info text for every language without DeepL"
            exit 0
            ;;
        *)
            echo "Unknown option: $arg" >&2
            echo "Usage: $0 [--update] [--applydb] [--no-translate]" >&2
            exit 2
            ;;
    esac
done

if [[ ! -d "$INFODATA_ROOT" ]]; then
  echo "Infodata root not found: $INFODATA_ROOT" >&2
  exit 1
fi

if [[ "$UPDATE" == true ]]; then
    if ! command -v git >/dev/null 2>&1; then
        echo "Git not found" >&2
        exit 1
    fi

    GIT=(git -c "safe.directory=$INFODATA_ROOT")
    if ! "${GIT[@]}" -C "$INFODATA_ROOT" rev-parse --is-inside-work-tree >/dev/null 2>&1; then
        echo "Infodata root is not a Git repository: $INFODATA_ROOT" >&2
        exit 1
    fi

    echo "Updating infodata repository: $INFODATA_ROOT"
    if [[ -n "$INFODATA_GIT_URL" ]]; then
        "${GIT[@]}" -C "$INFODATA_ROOT" pull --ff-only "$INFODATA_GIT_URL"
    else
        "${GIT[@]}" -C "$INFODATA_ROOT" pull --ff-only
    fi
fi

if [[ "$APPLY_DB" == true ]] && ! command -v "$MYSQL_BIN" >/dev/null 2>&1; then
    echo "MySQL client not found: $MYSQL_BIN" >&2
    exit 1
fi

mkdir -p "$OUTPUT_DIR"

INFODATA_ROOT="$INFODATA_ROOT" OUTPUT_PATH="$OUTPUT_PATH" DATABASE_NAME="$DATABASE_NAME" python3 <<'PY'
import os
import re
import sys
from pathlib import Path
from zipfile import BadZipFile, ZipFile
import xml.etree.ElementTree as ET

infodata_root = Path(os.environ['INFODATA_ROOT'])
output_path = Path(os.environ['OUTPUT_PATH'])
database_name = os.environ['DATABASE_NAME']

if not re.fullmatch(r'[A-Za-z0-9_]+', database_name):
    print(f'Invalid database name: {database_name}', file=sys.stderr)
    sys.exit(1)

main_ns = 'http://schemas.openxmlformats.org/spreadsheetml/2006/main'
document_rel_ns = 'http://schemas.openxmlformats.org/officeDocument/2006/relationships'
package_rel_ns = 'http://schemas.openxmlformats.org/package/2006/relationships'
ns = {'x': main_ns, 'r': document_rel_ns}
identifier_pattern = re.compile(r'^[A-Za-z_][A-Za-z0-9_]*$')
cell_reference_pattern = re.compile(r'^([A-Z]+)')
range_reference_pattern = re.compile(
    r"^'(?P<quoted_sheet>(?:[^']|'')+)'!\$(?P<column>[A-Z]+)\$(?P<start_row>\d+):\$(?P=column)\$(?P<end_row>\d+)$"
    r'|^(?P<sheet>[^!\[\]]+)!\$(?P<plain_column>[A-Z]+)\$(?P<plain_start_row>\d+):\$(?P=plain_column)\$(?P<plain_end_row>\d+)$'
)


def load_shared_strings(archive):
    if 'xl/sharedStrings.xml' not in archive.namelist():
        return []

    root = ET.fromstring(archive.read('xl/sharedStrings.xml'))
    return [
        ''.join(node.text or '' for node in item.iterfind(f'.//{{{main_ns}}}t'))
        for item in root.findall(f'{{{main_ns}}}si')
    ]


def worksheet_paths(archive):
    workbook = ET.fromstring(archive.read('xl/workbook.xml'))
    relationships = ET.fromstring(archive.read('xl/_rels/workbook.xml.rels'))
    targets = {
        relation.get('Id'): relation.get('Target')
        for relation in relationships.findall(f'{{{package_rel_ns}}}Relationship')
    }

    paths = []
    for sheet in workbook.findall('x:sheets/x:sheet', ns):
        relation_id = sheet.get(f'{{{document_rel_ns}}}id')
        target = targets.get(relation_id)
        if target is None:
            continue
        target = target.lstrip('/')
        if not target.startswith('xl/'):
            target = 'xl/' + target
        paths.append((sheet.get('name', target), target))
    return paths


def cell_value(cell, shared_strings):
    cell_type = cell.get('t')
    if cell_type == 'inlineStr':
        value = ''.join(node.text or '' for node in cell.iterfind(f'.//{{{main_ns}}}t'))
        return value, True

    value_node = cell.find(f'{{{main_ns}}}v')
    if value_node is None or value_node.text is None:
        return None, False

    value = value_node.text
    if cell_type == 's':
        try:
            return shared_strings[int(value)], True
        except (IndexError, ValueError):
            raise ValueError(f'Invalid shared string index: {value}')
    if cell_type in ('str', 'e'):
        return value, True
    if cell_type == 'b':
        return '1' if value == '1' else '0', False
    return value, False


def column_index(cell_reference):
    match = cell_reference_pattern.match(cell_reference or '')
    if match is None:
        raise ValueError(f'Invalid cell reference: {cell_reference}')

    index = 0
    for character in match.group(1):
        index = index * 26 + ord(character) - ord('A') + 1
    return index - 1


def row_values(row, shared_strings):
    values = {}
    for cell in row.findall('x:c', ns):
        values[column_index(cell.get('r'))] = cell_value(cell, shared_strings)
    return values


def has_first_column_checkboxes(rows, shared_strings):
    checkbox_cells = []
    for row in rows[1:]:
        for cell in row.findall('x:c', ns):
            if column_index(cell.get('r')) == 0:
                value, _ = cell_value(cell, shared_strings)
                if value is not None and value != '':
                    checkbox_cells.append(cell)
                break
    return bool(checkbox_cells) and all(cell.get('t') == 'b' for cell in checkbox_cells)


def cell_coordinates(cell_reference):
    match = re.fullmatch(r'([A-Z]+)(\d+)', cell_reference or '')
    if match is None:
        raise ValueError(f'Invalid cell reference: {cell_reference}')
    return column_index(match.group(1)), int(match.group(2))


def cell_range(reference):
    start, separator, end = reference.partition(':')
    start_column, start_row = cell_coordinates(start)
    if not separator:
        return start_column, start_row, start_column, start_row
    end_column, end_row = cell_coordinates(end)
    return start_column, start_row, end_column, end_row


def worksheet_validations(sheet):
    validations = []
    for validation in sheet.iter():
        if validation.tag.rsplit('}', 1)[-1] != 'dataValidation' or validation.get('type') != 'list':
            continue

        formula = None
        sqref = validation.get('sqref')
        for node in validation.iter():
            local_name = node.tag.rsplit('}', 1)[-1]
            if local_name in ('formula1', 'f') and node.text and node.text.strip():
                formula = node.text.strip()
            elif local_name == 'sqref' and node.text and node.text.strip():
                sqref = node.text.strip()

        if not formula or not sqref:
            raise ValueError('list validation is missing its formula or target range')
        for reference in sqref.split():
            validations.append((*cell_range(reference.replace('$', '')), formula))
    return validations


def parse_list_formula(formula):
    formula = formula.removeprefix('=').strip()
    match = range_reference_pattern.fullmatch(formula)
    if match is None:
        raise ValueError(f'Unsupported list validation formula: {formula!r}')

    if match.group('quoted_sheet') is not None:
        sheet_name = match.group('quoted_sheet').replace("''", "'")
        column = match.group('column')
        start_row = match.group('start_row')
        end_row = match.group('end_row')
    else:
        sheet_name = match.group('sheet')
        column = match.group('plain_column')
        start_row = match.group('plain_start_row')
        end_row = match.group('plain_end_row')
    return sheet_name, column_index(column), int(start_row), int(end_row)


def validation_formula(validations, column, row):
    matches = [
        formula for start_column, start_row, end_column, end_row, formula in validations
        if start_column <= column <= end_column and start_row <= row <= end_row
    ]
    if len(matches) > 1:
        raise ValueError(f'multiple list validations apply to cell at row {row}, column {column + 1}')
    return matches[0] if matches else None


def sql_identifier(value, label):
    value = value.strip().lower()
    if not identifier_pattern.fullmatch(value):
        raise ValueError(f'Invalid {label}: {value!r}')
    return f'`{value}`'


def sql_literal(value, is_text):
    if value is None or value == '':
        return 'NULL'
    if not is_text:
        if not re.fullmatch(r'[+-]?(?:\d+(?:\.\d*)?|\.\d+)(?:[Ee][+-]?\d+)?', value):
            raise ValueError(f'Invalid numeric value: {value!r}')
        return value
    return "'" + value.replace("'", "''") + "'"


xlsx_paths = sorted(
    path for path in infodata_root.rglob('*')
    if path.is_file() and path.suffix.lower() == '.xlsx' and not path.name.startswith('~$')
)
if not xlsx_paths:
    print(f'No XLSX files found under: {infodata_root}', file=sys.stderr)
    sys.exit(1)

output_lines = [
    '-- Generated by generated_infosql.sh',
    f'USE `{database_name}`;',
    'SET NAMES utf8mb4;',
    'SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS;',
    'SET FOREIGN_KEY_CHECKS=0;',
    'START TRANSACTION;',
    '',
]
query_count = 0
successful_files = []
failed_files = []
workbooks = []

for xlsx_path in xlsx_paths:
    relative_path = xlsx_path.relative_to(infodata_root).as_posix()
    try:
        workbook_sheets = []
        workbook_registry = {}
        with ZipFile(xlsx_path) as archive:
            shared_strings = load_shared_strings(archive)
            for sheet_name, sheet_path in worksheet_paths(archive):
                sheet_xml = ET.fromstring(archive.read(sheet_path))
                rows = sheet_xml.findall('.//x:sheetData/x:row', ns)
                if not rows:
                    raise ValueError(f'{sheet_name}: worksheet is empty')

                header_values = row_values(rows[0], shared_strings)
                headers = []
                checkbox_column = has_first_column_checkboxes(rows, shared_strings)
                first_data_column = 1 if checkbox_column else 0
                columns = []
                for index in range(first_data_column, max(header_values.keys(), default=-1) + 1):
                    value, _ = header_values.get(index, (None, False))
                    if value is None or value.strip() == '':
                        break
                    headers.append(value)
                    columns.append(index)
                if not headers:
                    raise ValueError(f'{sheet_name}: header row is empty')

                sheet_data = {
                    'name': sheet_name,
                    'headers': headers,
                    'columns': columns,
                    'checkbox_column': checkbox_column,
                    'rows': [(int(row.get('r')), row_values(row, shared_strings)) for row in rows[1:]],
                    'validations': worksheet_validations(sheet_xml),
                    'relative_path': relative_path,
                }
                registry_name = sheet_name.strip().lower()
                if registry_name in workbook_registry:
                    raise ValueError(f'{sheet_name}: duplicate worksheet name in workbook')
                workbook_registry[registry_name] = sheet_data
                workbook_sheets.append(sheet_data)
        workbooks.append((relative_path, workbook_sheets, workbook_registry))
    except (BadZipFile, ET.ParseError, KeyError, ValueError) as error:
        failed_files.append((relative_path, str(error)))

if not failed_files:
    for relative_path, workbook_sheets, workbook_registry in workbooks:
        file_lines = []
        file_row_count = 0
        try:
            for sheet_data in workbook_sheets:
                sheet_name = sheet_data['name']
                headers = sheet_data['headers']
                columns = sheet_data['columns']
                table_sql = sql_identifier(sheet_name, 'worksheet/table name')
                column_sql = ','.join(sql_identifier(header, 'column name') for header in headers)
                insert_lines = []

                for row_number, values in sheet_data['rows']:
                    if sheet_data['checkbox_column'] and values.get(0, ('0', False))[0] != '1':
                        continue
                    data_values = [values.get(index, (None, False)) for index in columns]
                    if all(value is None or value == '' for value, _ in data_values):
                        continue

                    for data_index, (value, _) in enumerate(data_values):
                        column = columns[data_index]
                        formula = validation_formula(sheet_data['validations'], column, row_number)
                        if formula is None:
                            continue
                        if value is None or value == '':
                            raise ValueError(f'{sheet_name}!{headers[data_index]} row {row_number}: list value is empty')

                        field_name = headers[data_index].strip().lower()
                        expected_source_name = f'info_{field_name}'
                        source_name, display_column, start_row, end_row = parse_list_formula(formula)
                        if source_name.strip().lower() != expected_source_name:
                            raise ValueError(
                                f'{sheet_name}!{headers[data_index]} row {row_number}: '
                                f'list worksheet must be {expected_source_name}, not {source_name}'
                            )
                        source_sheet = workbook_registry.get(expected_source_name)
                        if source_sheet is None:
                            raise ValueError(
                                f'{sheet_name}!{headers[data_index]} row {row_number}: '
                                f'list worksheet not found in the same workbook: {expected_source_name}'
                            )
                        source_fields = {
                            header.strip().lower(): source_column
                            for header, source_column in zip(source_sheet['headers'], source_sheet['columns'])
                        }
                        if field_name not in source_fields:
                            raise ValueError(
                                f'{sheet_name}!{headers[data_index]} row {row_number}: '
                                f'field not found in list worksheet {expected_source_name}'
                            )
                        if 'title' not in source_fields:
                            raise ValueError(
                                f'{sheet_name}!{headers[data_index]} row {row_number}: '
                                f'title field not found in list worksheet {expected_source_name}'
                            )
                        if display_column != source_fields['title']:
                            raise ValueError(
                                f'{sheet_name}!{headers[data_index]} row {row_number}: '
                                f'list formula must reference {expected_source_name}.title'
                            )
                        replacement_column = source_fields[field_name]
                        matching_rows = [
                            source_values for source_row, source_values in source_sheet['rows']
                            if start_row <= source_row <= end_row
                            and source_values.get(display_column, (None, False))[0] == value
                        ]
                        if len(matching_rows) != 1:
                            raise ValueError(
                                f'{sheet_name}!{headers[data_index]} row {row_number}: '
                                f'list value {value!r} matched {len(matching_rows)} rows in {expected_source_name}.title'
                            )
                        replacement = matching_rows[0].get(replacement_column, (None, False))
                        if replacement[0] is None or replacement[0] == '':
                            raise ValueError(
                                f'{sheet_name}!{headers[data_index]} row {row_number}: '
                                f'replacement value is empty in {expected_source_name}.{headers[data_index]}'
                            )
                        data_values[data_index] = replacement

                    literals = ','.join(sql_literal(value, is_text) for value, is_text in data_values)
                    insert_lines.append(f'INSERT INTO {table_sql} ({column_sql}) VALUES ({literals});')

                if not insert_lines and not sheet_data['checkbox_column']:
                    raise ValueError(f'{sheet_name}: no data rows found')
                file_lines.append(f'-- {relative_path} / {sheet_name}')
                file_lines.append(f'DELETE FROM {table_sql};')
                file_lines.extend(insert_lines)
                file_lines.append('')
                file_row_count += len(insert_lines)
        except ValueError as error:
            failed_files.append((relative_path, str(error)))
            continue

        output_lines.extend(file_lines)
        query_count += file_row_count + len(workbook_sheets)
        successful_files.append((relative_path, len(workbook_sheets), file_row_count))

print('XLSX processing result')
print('SUCCESS')
if successful_files:
    for relative_path, sheet_count, row_count in successful_files:
        print(f'  [OK] {relative_path} ({sheet_count} sheets, {row_count} rows)')
else:
    print('  (none)')

print('FAILURE')
if failed_files:
    for relative_path, error in failed_files:
        print(f'  [FAIL] {relative_path}: {error}')
else:
    print('  (none)')

if failed_files:
    print('SQL file was not generated because one or more XLSX files failed.', file=sys.stderr)
    sys.exit(1)

output_lines.append('COMMIT;')
output_lines.append('SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;')
output_lines.append('')
output_path.write_text('\n'.join(output_lines), encoding='utf-8')
print(f'Generated {output_path} ({len(xlsx_paths)} files, {query_count} queries)')
PY

if [[ "$APPLY_DB" == true ]]; then
    echo "Applying generated SQL to MySQL: $OUTPUT_PATH"
    if [[ -n "$MYSQL_DEFAULTS_FILE" ]]; then
        "$MYSQL_BIN" --defaults-extra-file="$MYSQL_DEFAULTS_FILE" -u root < "$OUTPUT_PATH"
    else
        "$MYSQL_BIN" -u root -p < "$OUTPUT_PATH"
    fi
    echo "Applied generated SQL successfully: $OUTPUT_PATH"
fi

echo "Generating info files with make_info.php"
MAKE_INFO_ARGS=()
if [[ "$NO_TRANSLATE" == true ]]; then
    MAKE_INFO_ARGS+=(--no-translate)
fi
php "$SCRIPT_DIR/make_info.php" "${MAKE_INFO_ARGS[@]}"
echo "Generated info files successfully"