#!/usr/bin/env python3
"""
transform_clob_inserts.py

Transforms Toad-exported INSERT statements containing CLOB fields
into valid PL/SQL blocks using DBMS_LOB.

Usage:
    python3 transform_clob_inserts.py input.sql output.sql
"""

import re
import sys

CHUNK_SIZE = 3900
CLOB_COLUMNS = {'QUESTION', 'PROMPT', 'RESPONSE', 'ANSWER'}  # adjust to your table


def escape_oracle_string(text):
    return text.replace("'", "''")


def extract_values_section(stmt):
    table_match = re.match(
        r'insert\s+into\s+([\w.]+)\s*\(', stmt, re.IGNORECASE | re.DOTALL
    )
    if not table_match:
        return None, None, None
    table = table_match.group(1)

    cols_start = stmt.index('(') + 1
    depth = 1
    pos = cols_start
    while pos < len(stmt) and depth > 0:
        if stmt[pos] == '(':
            depth += 1
        elif stmt[pos] == ')':
            depth -= 1
        pos += 1
    cols_str = stmt[cols_start:pos - 1]
    columns = [c.strip() for c in cols_str.split(',')]

    values_keyword = re.search(r'\bvalues\b', stmt[pos:], re.IGNORECASE)
    if not values_keyword:
        return table, columns, None

    values_start_pos = pos + values_keyword.end()
    while values_start_pos < len(stmt) and stmt[values_start_pos] != '(':
        values_start_pos += 1
    values_start_pos += 1

    values = []
    current_val = []
    depth = 1
    in_str = False
    i = values_start_pos

    while i < len(stmt) and depth > 0:
        ch = stmt[i]
        if in_str:
            current_val.append(ch)
            if ch == "'":
                if i + 1 < len(stmt) and stmt[i + 1] == "'":
                    current_val.append(stmt[i + 1])
                    i += 2
                    continue
                else:
                    in_str = False
        else:
            if ch == "'":
                in_str = True
                current_val.append(ch)
            elif ch == '(':
                depth += 1
                current_val.append(ch)
            elif ch == ')':
                depth -= 1
                if depth == 0:
                    values.append(''.join(current_val).strip())
                    break
                else:
                    current_val.append(ch)
            elif ch == ',' and depth == 1:
                values.append(''.join(current_val).strip())
                current_val = []
            else:
                current_val.append(ch)
        i += 1

    return table, columns, values


def extract_string_content(val):
    v = val.strip()
    if v.startswith("'") and v.endswith("'"):
        return v[1:-1].replace("''", "'")
    return None


def transform_insert(stmt, clob_columns):
    table, columns, values = extract_values_section(stmt)
    if not columns or not values or len(columns) != len(values):
        return stmt

    clob_indices = [
        idx for idx, col in enumerate(columns)
        if col.upper() in clob_columns
        and idx < len(values)
        and values[idx].strip().startswith("'")
    ]

    if not clob_indices:
        return stmt

    lines = ['DECLARE']
    for idx in clob_indices:
        lines.append(f"  v_{columns[idx].lower()} CLOB;")

    lines.append('BEGIN')

    for idx in clob_indices:
        col = columns[idx].lower()
        raw = extract_string_content(values[idx])
        lines.append(f"  DBMS_LOB.CREATETEMPORARY(v_{col}, TRUE);")

        text_parts = raw.split('\n')
        chunk = ''
        for line_idx, part in enumerate(text_parts):
            escaped = escape_oracle_string(part)
            is_last = (line_idx == len(text_parts) - 1)

            if len(chunk) + len(escaped) > CHUNK_SIZE:
                if chunk:
                    lines.append(f"  DBMS_LOB.APPEND(v_{col}, TO_CLOB('{chunk}'));")
                chunk = escaped
            else:
                chunk += escaped

            if not is_last:
                if len(chunk) > CHUNK_SIZE - 10:
                    lines.append(f"  DBMS_LOB.APPEND(v_{col}, TO_CLOB('{chunk}'));")
                    lines.append(f"  DBMS_LOB.APPEND(v_{col}, CHR(10));")
                    chunk = ''
                else:
                    if chunk:
                        lines.append(f"  DBMS_LOB.APPEND(v_{col}, TO_CLOB('{chunk}'));")
                    lines.append(f"  DBMS_LOB.APPEND(v_{col}, CHR(10));")
                    chunk = ''

        if chunk:
            lines.append(f"  DBMS_LOB.APPEND(v_{col}, TO_CLOB('{chunk}'));")

    new_values = list(values)
    for idx in clob_indices:
        new_values[idx] = f"v_{columns[idx].lower()}"

    lines.append(f"  INSERT INTO {table}")
    lines.append(f"  ({','.join(columns)})")
    lines.append(f"  VALUES")
    lines.append(f"  ({','.join(new_values)});")

    for idx in clob_indices:
        lines.append(f"  DBMS_LOB.FREETEMPORARY(v_{columns[idx].lower()});")

    lines.append('END;')
    lines.append('/')
    return '\n'.join(lines)


def parse_sql_file(content):
    statements = []
    current = []
    paren_depth = 0
    in_string = False
    i = 0
    while i < len(content):
        ch = content[i]
        if in_string:
            current.append(ch)
            if ch == "'":
                if i + 1 < len(content) and content[i + 1] == "'":
                    current.append(content[i + 1])
                    i += 2
                    continue
                else:
                    in_string = False
        else:
            if ch == "'":
                in_string = True
                current.append(ch)
            elif ch == '(':
                paren_depth += 1
                current.append(ch)
            elif ch == ')':
                paren_depth -= 1
                current.append(ch)
            elif ch == ';' and paren_depth == 0:
                current.append(';')
                stmt = ''.join(current).strip()
                if stmt.upper().startswith('INSERT'):
                    statements.append(stmt)
                current = []
                i += 1
                continue
            else:
                current.append(ch)
        i += 1
    return statements


def main():
    if len(sys.argv) != 3:
        print("Usage: python3 transform_clob_inserts.py input.sql output.sql")
        sys.exit(1)
    with open(sys.argv[1], 'r', encoding='utf-8') as f:
        content = f.read()
    statements = parse_sql_file(content)
    print(f"Found {len(statements)} INSERT statements")
    transformed = [transform_insert(s, CLOB_COLUMNS) for s in statements]
    with open(sys.argv[2], 'w', encoding='utf-8') as f:
        f.write('\n\n'.join(transformed) + '\n')
    print(f"Done. Output written to {sys.argv[2]}")


if __name__ == '__main__':
    main()