import os

import requests
import ast
import [Link]
import pandas as pd
import json
import re
import time
from datetime import datetime
from graphviz import Source, Digraph
import certifi
import openpyxl
from openpyxl import load_workbook
from [Link] import Alignment, PatternFill
from [Link] import Image
import tiktoken
# === LLaMA 3.1 Setup ===
import [Link]
from [Link] import default
from [Link] import Request
from langchain_core.prompts import PromptTemplate
from langchain_core.runnables import RunnablePassthrough, Runnable
from langchain_core.output_parsers import StrOutputParser
from google.oauth2 import service_account
from langchain_core.runnables import Runnable

print('*'*100)
print('Process Started')

SERVICE_URL = "[Link]
SERVICE_ACCOUNT_FILE = "[Link]"

def call_cloud_run_model(prompt: str, temperature: float = 0.7) -> dict:

try:
credentials = service_account.IDTokenCredentials.from_service_account_file(
SERVICE_ACCOUNT_FILE,
target_audience="[Link]
)
auth_request = [Link]()
[Link](auth_request)

headers = {
"Authorization": f"Bearer {[Link]}",
"Content-Type": "application/json"
}

payload = {
"prompt": prompt,
"temperature": temperature
}

response = [Link](SERVICE_URL, headers=headers, json=payload)

response.raise_for_status()
return [Link]()

except Exception as e:
print(f"❌ Error in call_cloud_run_model: {str(e)}")
return {"error": str(e)}
class CloudRunLLM(Runnable):
def invoke(self, input, config=None):
try:
prompt_text = [Link]('formatted_prompt') if isinstance(input, dict)
else str(input)
print("🟢 Prompt sent to Cloud Run:")
print(prompt_text)

response = call_cloud_run_model(prompt_text)
print("🟢 Response from Cloud Run:")
print(response)

if 'error' in response:
return f"Error: {response['error']}"

if isinstance(response, dict):
# Vertex/Custom style
if 'predictions' in response:
preds = response['predictions']
# Some servers nest choices inside predictions, others return a
list of strings
if isinstance(preds, dict) and 'choices' in preds and
preds['choices']:
ch0 = preds['choices'][0]
msg = [Link]('message') or {}
content = ([Link]('content') if isinstance(msg, dict) else
None) or [Link]('text')
if content:
return content
if isinstance(preds, list):
# Try common shapes inside list
first = preds[0] if preds else {}
if isinstance(first, dict):
msg = [Link]('message') or {}
content = ([Link]('content') if isinstance(msg, dict)
else None) or [Link]('text')
if content:
return content

# OpenAI style
if 'choices' in response and response['choices']:
ch0 = response['choices'][0]
msg = [Link]('message') or {}
content = ([Link]('content') if isinstance(msg, dict) else
None) or [Link]('text')
if content:
return content

# Fallback: stringify (last resort)

return [Link](response, ensure_ascii=False)

return str(response)
except Exception as e:
print(f"Error in [Link]: {str(e)}")
return f"Error processing request: {str(e)}"

llm = CloudRunLLM()
def chunk_sql_by_tokens(file_path, token_limit=500):

if not [Link](file_path):
raise FileNotFoundError(f"File not found: {file_path}")

if not file_path.endswith(('.sql', '.txt')):

raise ValueError("File must be a .sql or .txt file")

# Load the tokenizer

tokenizer = tiktoken.get_encoding("cl100k_base")

# Read the file content

with open(file_path, 'r', encoding='utf-8') as f:
raw_sql = [Link]()

# Step 1: Clean SQL (remove comments and blank lines)

sql_final = ""
for line in raw_sql.replace('\r\n', '\n').split('\n'):
if [Link]() and not [Link]().startswith("--"):
sql_final += line + '\n'

# Step 2: Token-aware chunking

chunks = []
chunk_sql = ""

for trans in sql_final.split(";"):

if len([Link](chunk_sql + trans)) <= token_limit:
chunk_sql += trans + ';'
else:
[Link](chunk_sql.replace(";;", ";").strip())
chunk_sql = trans + ';'

# Append any remaining chunk

if chunk_sql.strip():
[Link](chunk_sql.replace(";;", ";").strip())

return [chunk for chunk in chunks if chunk]

def analyse_chunks(plsql_chunks, context=None, dialect="T-SQL",

filename="default_file"):
"""
Accepts pre-chunked SQL strings and returns glossary DataFrames.
"""
db_schema_prompt = PromptTemplate.from_template("""
You are a data engineer with expertise in understanding optimized,
enterprise-grade PL/SQL procedures with a focus on the Banking sector. Analyze the
complete provided PL/SQL script
which is being used to transform data from source systems build reports in
the Banking domain. Create a thorough and comprehensive table that captures the
necessary details for both
business analysts and data engineers. Optionally, you may be provided with
a business glossary of the source system. Use the business glossary of the data
source to augment your analysis.
The JSON should include information about multiple layers of
transformations, tables and columns involved in each Section, Data Types, key
constraints, description of each and every column,
join tables and columns, join conditions, filter conditions, transformation
statements, and the purpose of applying the joins, filters and transformations.
Follow these steps to sequentially
perform the analysis and populate the JSON:

1. Analyse each and every section of the PL/SQL procedure. The PL/SQL
procedure consists of multiple layers of data transformation, designed to optimize
query performance.
Determine the purpose of each section in the data engineering and report
building process. You will utilize this analysis in naming and describing the
sections as well as
in documenting the tables involved in the section. Ensure that each section
has only one concise and high-level description documented under "Description".

2. Identify the variables, tables CTEs and columns in the PL/SQL procedure.
Analyse the insert, update, delete select, from and join statements, aggregations,
select case when and other filter conditions. You will use this analysis to
document the target table name, the columns belonging to the table in a hierarchial
manner of data flow. You will also use this analysis to determine the purpose of
the table and column in the report building process.

3. Since this is a multi-stage transformation process, multiple tables and

columns are being transformed and derived using join statements, complex
mathematical formulae, case when conditions,
aggregations, filters, etc in each step. The tables and columns derived in
one step may further be transformed to derive new tables and columns in subsequent
steps. You will analyse every statement in which some table and its columns are
being derived. You will classify them into the parts responsible for performing
join operations and parts responsible for applying transformation logic such as
filters, case when statements, aggregations, etc. If a statement does not contain
any joins, you will not classify any part of it as a join statement.
You will map aliases to their actual table names and identify all the
columns and tables being used in both sides of the various join operation,
subsequent boolean operators and subqueries.
You will identify all the tables and columns being used in where clauses,
aggregations, select when and other filter conditions for deriving some table and
its columns. You will analyse these
statements and utilize the analyses from steps 1 and 2 to comprehend the
derivation logic and the purpose of the derivation logic in the overall ETL
pipeline. You will use these analyses to
accurately document every table and column being invoked in the select
statements and join part of the statements under "Source Table" and "Source Column"
seperated by commas. You will only
document the truncated join statements under "Join Statement". You will
only document the tables and columns upon which case when, aggregation and other
filter conditions are being applied,
under "Transformation Table" and "Transformation Column", separated by
commas. Truncated versions of the derivation statements should be documented under
"Transformation Statement". Finally, each
transformation step should be explained in business-centric terms under
"Derivation Purpose"

For example, in the statement "insert @lins select [Link], case when
[Link] is null then 'O' else 'R' end, [Link], [Link] from @PTRN1 a left
join SOURCE_TABLE b on [Link] = [Link] and
[Link] = [Link] and [Link] !='2' and [Link] = 'B522'":
- "Source Table": "@PTRN1, SOURCE_TABLE" (they have been mapped to aliases
'a' and 'b' respectively)
- "Source Column": "@[Link], SOURCE_TABLE.chdrnum, @[Link],
SOURCE_TABLE.PTRNEFF" ('SOURCE_TABLE.chdrnum', 'SOURCE_TABLE.validflag' and
'SOURCE_TABLE.batctrcde' will not be included in the join columns)
- "Join Statement": "from @PTRN1 a left join SOURCE_TABLE b on [Link] =
[Link] and [Link] = [Link]"
- "Transformation Table": "@PTRN1, SOURCE_TABLE"
- "Transformation Column": "SOURCE_TABLE.chdrnum, SOURCE_TABLE.validflag,
SOURCE_TABLE.batctrcde"
- "Transformation Statement": "insert @lins select [Link], case when
[Link] is null then 'O' else 'R' end, [Link] !='2' and [Link] =
'B522'"

4. You will utilise the analyses from Step 2 and Step 3 to rationalise the
purpose of performing the transformation at each step, in business terms. You will
document this rationalisation
under "Derivation Purpose" for each transformation step.

5. Cursor and Loop Handling:

Identify cursor declarations (e.g., CURSOR cursor_name IS SELECT ...). If a
cursor is used to populate a table or derive a column, document the cursor's name
as well as the loop variable
under "Source Table" along with an explanation of its purpose in
"Derivation Purpose". Analyze the set of operations within the loop that process
the rows fetched from the cursor. Document
these operations within the "Transformation Statement", focusing on how
they contribute to the derivation of the target table or column.

For example, "CURSOR policy_cursor IS SELECT policy_id, premium_amount FROM

policy_table WHERE status = 'Active'; FOR policy_record IN policy_cursor LOOP
total_premium := total_premium + policy_record.premium_amount; END LOOP;"
is a cursor which fetches policy data and a loop calculates the total
premium for each policy, and this total is used in a column, the documentation
should be:
- "Target Column": "total__premium"
- "Source Table": "policy_cursor, policy_table, policy_record"
- "Source Column": "policy_id, premium_amount"
- "Join Statement": "N/A"
- "Transformation Table": "policy_table"
- "Transformation Column": "status"
- "Transformation Statement": "... CURSOR policy_cursor IS SELECT
policy_id, premium_amount FROM policy_table WHERE status = 'Active'; FOR
policy_record IN policy_cursor LOOP total_premium := total_premium +
policy_record.premium_amount; END LOOP; ..."
- "Derivation Purpose": "... Calculate the total premium for each active
policy using a cursor and loop..."

6. Since this may a PL/SQL procedure to provide data for a reporting

dashboard, you will also analyse the section of the procedure which applies SELECT,
UNION, JOIN and other such operations on the tables and columns derived
from the various stages of the transformation to procure the data for the
report. These elements may not have table and column names, so you will assign a
table and column name to them and an accurate description when documenting this
analysis.
Suitable table names may be report, dbms_output, etc.

7. Identify function declarations (e.g., function

getfxdcrncyunits(fxdcrncycode varchar2, varcrncycode varchar2, rate_code varchar2,
asondate date)). Document the functions under "Source Table" alongwith
any resulting variables from the function's logic under "Source Column".
You will also document the join queries, transformation logic and purpose for their
derivation under the relevant columns.
 8. Finally, you will populate the data thoroughly with the technical
specifics and clear, contextual descriptions you will obtain from the above steps.
The data should be in JSON format given below:

For each DML or data-modification operation (INSERT, UPDATE, DELETE, MERGE,

TRUNCATE, etc) extract source_tables, target_tables, transformation for each
transformation and while analysing the SQL stored procedure, consider each DML
statement as a individual transaction:

a. For operations affecting multiple tables (e.g., joins, subqueries),

list all involved source tables.
b. Cursors: If a cursor is created and used, treat the entire cursor
(including its definition and body) as a single transformation and summarize its
source/target tables and the main transformation logic. Do not break down the
cursor loop unless it references non-sql procedural logic.
Your response **must be one valid JSON array** which contails all the
transformations in the below format:
{json}
c. Your output must be only the JSON array as above, nothing else and
it should not throw error while converting into JSON from string.
d. Do not hallucinate tables, columns, or steps that do not exist in
the procedure.
e. Use only **UPPERCASE** for all table names, as they appear in the
code. Do not hallucinate table names or steps not present while retrurning JSON.
f. If a section of code does not involve a DML statement, skip it.
g. All transformations from SQL SP should be documented in your JSON
output.
h. Skip any non-DML procedural blocks like EXCEPTION, COMMIT, DECLARE,
etc.
i. If DML statement is a simple update, then source_tables and
target_tables should have same table name in JSON.
i. In your output, Section should be type of transformation like
insert, delete, update, truncate, etc.
k. You should return output in same order how transformation defined in
stored procedure.
You will write N/A where values are not applicable.
For each transformation in stored procedure you should return values for
Section, Description, Target Table, Table Description, Target Column, Data Type,
Column Description, Source Table, Source Column, Join Statement, Transformation
Table, Transformation Column, Transformation Statement, Derivation Purpose columns
in your JSON output. You should not return value for extra columns which are not
given.
Truncate Join Statement and Transformation Statement to no more than 20
words then add it to the documentation with "..." to denote continuation.
If a Section and consequently Description pertains to multiple tables and
their columns, write S/B for repeating instances.
If a Target Table and consequently, Table Description pertains to multiple
columns, write S/B for repeating instances.
If a Source Table, Source Column, Join Statement, Transformation Table,
Transformation Column, Transformation Statement and consequently the Derivation
Purpose pertains to multiple Column Names, fill the row
for the first instance with the Join Statement and Transformation Statement
as instructed above, and write "S/B" for subsequent instances.
Following is the PL/SQL script:
{plsql_chunks}

{context}
Note that, you should not miss capturing any transformation or logic even
if that is simple. All tables used in stored procedure should be captured in result
you will be giving.

The resulting JSON will provide a detailed overview of the PL/SQL script's
operations, data flow, and business logic. It should serve as a reference guide for
data engineers to understand the exact queries and operations applied, and for
business analysts to grasp the purpose and functionality of each section, element
and statements within the script.
Perform the documentation thoroughly and ensure no part of your analysis is
excluded from the JSON output as this documentation is highly critical. Perform as
much of the analysis as you can. Do not include introductions, labels, backticks
and conclusions. Only respond with the table. Each row of the table should end with
a newline character.
you should give all the your analyse in one JSON array, There should not be
multple JSON arrays.
""")

runnable = RunnablePassthrough()
output_parser = StrOutputParser()
chain = runnable | db_schema_prompt | llm | output_parser

df_chunks = []

for i, query in enumerate(plsql_chunks, 1):

print(f"\n--- Chunk {i} ---")

print(query)
print("-" * 50)

for attempt in range(10):

try:
glossary = [Link]({
"json":'''[{"Section": "Type of
transformation","Description": "Insert new records into Table if they do not
exist","Target Table": "Table Name","Table Description": "Summary of table","Target
Column": "Target columns used in transformation separated by comma", "Data Type":
"VARCHAR", "Column Description": "description for each column used in
transformation","Source Table": "Source table names used in transformation","Source
Column": "Source columns used in transformation","Join Statement": "FROM table1 o
JOIN table2 c ON [Link] = [Link]","Transformation Table": "Transformation table
names","Transformation Column": "Transformation column names used in
transformation","Transformation Statement": "Key trasformation
statement","Derivation Purpose": "Purpose of transformation"}, {"Section": "Section
2",....continue]''',
"plsql_chunks": query,
"context": "" + context if context else ""
})

# Clean the response

glossary_clean = [Link]('```json', '').replace('```',
'').strip()

# Try to find JSON array with improved regex patterns

patterns = [
r'\[\s*\{.*?\}\s*\]', # Standard JSON array
r'\[\s*\{[^}]*\}(?:\s*,\s*\{[^}]*\})*\s*\]', # Multiple
objects
r'\[[\s\S]*?\]', # Any content between square brackets
]
 match = None
for pattern in patterns:
match = [Link](pattern, glossary_clean, [Link])
if match:
break

if not match:
print(f"❌ No JSON array found in chunk {i} response:")
print(f"Response preview: {glossary_clean[:300]}...")
continue

json_string = [Link](0)

# Try to parse the JSON

try:
# First try direct JSON parsing
json_val = [Link](json_string)
except [Link]:
try:
# If that fails, try ast.literal_eval
json_val = ast.literal_eval(json_string)
except (ValueError, SyntaxError) as e:
print(f"❌ JSON/AST parsing failed in chunk {i}: {e}")
print(f"🟠 Extracted JSON string:
{json_string[:200]}...")
continue

# Process the parsed JSON

df = process_table_1(json_val)
if df: # Only add if we got valid data
df_chunks += df
print(f"✅ Processed chunk {i}")
break
else:
print(f"⚠️ No valid data extracted from chunk {i}")
continue

except Exception as e:
print(f"❌ Error in chunk {i} (attempt {attempt+1}): {e}")
if attempt == 9: # Last attempt
print(f"🟠 Final raw response for chunk {i}:")
print(f"{glossary[:500]}...")

else:
print(f"⚠️ Skipped chunk {i} after 10 failed attempts.")

if not df_chunks:
print("❌ No data was successfully processed from any chunks")
return {filename: [Link]()}, {filename: [Link]()}

df = [Link](df_chunks)
df_filled = [Link]("S/B", [Link]).ffill()
return {filename: df}, {filename: df_filled}

def process_table_1(table):
rows = []
try:
 for line in table:
row = {
"Section": [Link]('Section'),
"Description": [Link]('Description'),
"Target Table": [Link]('Target Table', "").upper() if
[Link]('Target Table') else None,
"Table Description": [Link]('Table Description'),
"Target Column": [Link]('Target Column'),
"Data Type": [Link]('Data Type'),
"Column Description": [Link]('Column Description'),
"Source Table": [Link]('Source Table', "").strip().upper() if
[Link]('Source Table') else "S/B",
"Source Column": [Link]('Source Column', "").strip() if
[Link]('Source Column') else "S/B",
"Join Statement": [Link]('Join Statement', "").strip() if
[Link]('Join Statement') else "S/B",
"Transformation Table": [Link]('Transformation Table',
"").strip().upper() if [Link]('Transformation Table') else "S/B",
"Transformation Column": [Link]('Transformation Column',
"").strip() if [Link]('Transformation Column') else "S/B",
"Transformation Statement": [Link]('Transformation Statement',
"").strip() if [Link]('Transformation Statement') else "S/B",
"Derivation Purpose": [Link]('Derivation Purpose', "").strip() if
[Link]('Derivation Purpose') else "S/B"
}
[Link](row)
return rows
except Exception as e:
print('❌ Error in parsing output in process_table_1:', str(e))
return []

def chunk_to_json(plsql_chunks, llm, context=None, filename="default_file"):

# JSON schema definition for lineage

json_schema = """
[
{
"source_tables": [ "list of source table names" ],
"target_tables": [ "list of target table names" ],
"column_mappings": {
"source_table.column": "target_table.column"
},
"transformation_logic": "Short description of what transformation is
happening"
}
]
"""

# Prompt for the model

db_schema_prompt = PromptTemplate.from_template("""
You are an expert in SQL data lineage extraction.
Given the following PL/SQL chunk, extract all source tables, target tables,
column mappings, and Short description of what transformation is happening.

Always output in EXACTLY this JSON format:

{json_schema}

Do NOT include any explanations outside the JSON.

 PL/SQL Chunk:
{plsql_chunks}

Additional Context (optional):

{context}
""")

# Chain setup
runnable = RunnablePassthrough()
output_parser = StrOutputParser()
chain = runnable | db_schema_prompt | llm | output_parser

combined_results = []

for i, query in enumerate(plsql_chunks, 1):

print(f"\n--- Processing Chunk {i} ---")
print(query)
print("-" * 50)

for attempt in range(10): # Retry a few times if JSON parsing fails

try:
response = [Link]({
"json_schema": json_schema.strip(),
"plsql_chunks": query,
"context": context or ""
})

# Clean model output

clean_resp = [Link]("```json", "").replace("```",
"").strip()

# Regex patterns to locate JSON array

patterns = [
r'\[\s*\{.*?\}\s*\]',
r'\[\s*\{[^}]*\}(?:\s*,\s*\{[^}]*\})*\s*\]',
r'\[[\s\S]*?\]',
]
match = None
for pattern in patterns:
match = [Link](pattern, clean_resp, [Link])
if match:
break

if not match:
print(f"❌ No JSON found in chunk {i} attempt {attempt+1}")
continue

json_string = [Link](0)

try:
json_val = [Link](json_string)
except [Link]:
json_val = ast.literal_eval(json_string)

# Ensure result is a list

if isinstance(json_val, dict):
json_val = [json_val]
 combined_results.extend(json_val)
break # Exit retry loop if success

except Exception as e:
print(f"⚠️ Error processing chunk {i} attempt {attempt+1}: {e}")
continue

print("\n✅ All chunks processed successfully.")

return combined_results

def preprocess_unclean_to_clean(unclean_json):
clean_list = []
step = 1
for item in unclean_json:
sources = [Link]("source_tables", [])
targets = [Link]("target_tables", [])
col_map = [Link]("column_mappings", {})
# Try to infer operation from transformation_logic (very basic heuristic)
trans_logic = [Link]("transformation_logic", "").lower()
if "update" in trans_logic:
operation = "UPDATE"
elif "insert" in trans_logic:
operation = "INSERT"
elif "create" in trans_logic:
operation = "CREATE TABLE AS"
else:
operation = "INSERT" # default fallback

for target in targets:

for source in sources:
columns = {}
for src_col, tgt_col in col_map.items():
# Make sure src_col starts with source table name
if src_col.startswith(source + ".") and
tgt_col.startswith(target + "."):
src_col_name = src_col.split(".", 1)[1]
tgt_col_name = tgt_col.split(".", 1)[1]
columns[src_col_name] = tgt_col_name
clean_list.append({
"source": source,
"target": target,
"columns": columns,
"operation": operation,
"joins": [] # no joins info in unclean JSON, can be extended
later
})
step += 1
return clean_list

# -------------------------
# 3. Your existing diagram generator (unchanged)
# -------------------------
def generate_interactive_html_diagram(clean_json):
"""Generate an interactive HTML diagram using [Link] with operation & join
details."""
nodes = {}
edges = []
table_details = {}
 for idx, item in enumerate(clean_json, 1):
source = [Link]("source")
target = [Link]("target")
columns = [Link]("columns", {})
operation = [Link]("operation", "SELECT")
joins = [Link]("joins", [])

# Add nodes
for tbl, role in [(source, "Source"), (target, "Target")]:
if tbl and tbl not in nodes:
nodes[tbl] = {
"id": tbl,
"label": tbl,
"color": "#ADD8E6" if role == "Source" else "#90EE90",
"title": f"{role} Table: {tbl}"
}
table_details[tbl] = {"type": role, "operations": []}

# Add table-level details

if source in table_details:
table_details[source]["operations"].append({
"step": idx,
"operation": operation,
"columns": columns,
"joins": joins
})
if target in table_details:
table_details[target]["operations"].append({
"step": idx,
"operation": operation,
"columns": columns,
"joins": joins
})

# Edge tooltip
col_map_str = "\n".join([f"{src} → {tgt}" for src, tgt in [Link]()])
joins_str = "\n".join([f"{j['type']}: {j['left_table']} ↔
{j['right_table']} ON {j['condition']}" for j in joins]) or "No joins"
edge_tooltip = f"Step {idx}: {operation}\n\nColumns:\n{col_map_str or 'No
column mapping'}\n\nJoins:\n{joins_str}"

# Add edge
if source and target:
[Link]({
"id": f"edge_{idx}",
"from": source,
"to": target,
"label": f"Step {idx}: {operation}",
"color": "#00CC00" if [Link]() == "SELECT" else "#FF6600",
"title": edge_tooltip
})

# JSON for HTML

nodes_json = [Link](list([Link]()), indent=4)
edges_json = [Link](edges, indent=4)
table_details_json = [Link](table_details, indent=4)

html_content = f"""
<!DOCTYPE html>
<html>
<head>
<title>Data Lineage Diagram</title>
<script src="[Link]
[Link]"></script>
<style>
body {{ font-family: Arial; margin: 20px; }}
#network {{ width: 100%; height: 800px; border: 1px solid #ccc; }}
.info-panel {{ margin: 20px 0; padding: 15px; background: #f5f5f5; }}
.legend {{ display: flex; gap: 20px; }}
.legend-item {{ display: flex; align-items: center; gap: 5px; }}
.legend-color {{ width: 20px; height: 20px; }}
</style>
</head>
<body>
<h1>Data Lineage Diagram</h1>
<div class="info-panel">
<h3>Legend</h3>
<div class="legend">
<div class="legend-item"><div class="legend-color" style="background:
#ADD8E6;"></div>Source Tables</div>
<div class="legend-item"><div class="legend-color" style="background:
#90EE90;"></div>Target Tables</div>
</div>
</div>
<div id="network"></div>
<div id="table-details" class="info-panel">
<h3>Table Details</h3>
<div id="selected-table">Click on a table to see details</div>
</div>
<script>
const nodes = new [Link]({nodes_json});
const edges = new [Link]({edges_json});
const container = [Link]('network');
const data = {{ nodes: nodes, edges: edges }};
const options = {{
nodes: {{ shape: 'box', font: {{ size: 12 }}, borderWidth: 2 }},
edges: {{ arrows: {{ to: {{ enabled: true }} }} }},
physics: {{ enabled: true, solver: 'forceAtlas2Based' }},
interaction: {{ hover: true }}
}};
const network = new [Link](container, data, options);
const tableDetails = {table_details_json};

[Link]('selectNode', function(params) {{
const nodeId = [Link][0];
const details = tableDetails[nodeId];
let html = `<h4>${{nodeId}}</h4><p>Type: ${{[Link]}}</p><ul>`;
[Link](op => {{
html += `<li><strong>Step:</strong> ${{[Link]}} |
<strong>Op:</strong> ${{[Link]}}<br>`;
if ([Link]([Link]).length) {{
html += "<strong>Columns:</strong><ul>";
for (const [src, tgt] of [Link]([Link])) {{
html += `<li>${{src}} → ${{tgt}}</li>`;
}}
html += "</ul>";
}}
 if ([Link]) {{
html += "<strong>Joins:</strong><ul>";
[Link](j => {{
html += `<li>${{[Link]}}: ${{j.left_table}} ↔ $
{{j.right_table}} ON ${{[Link]}}</li>`;
}});
html += "</ul>";
}}
html += "</li>";
}});
html += "</ul>";
[Link]('selected-table').innerHTML = html;
}});
</script>
</body>
</html>
"""
return html_content

if __name__ == "__main__":

file_path = r"/home/gcp8099/demo_app/sp_rq.sql"

# Extract filename automatically (without extension)

filename = [Link]([Link](file_path))[0]

chunks = chunk_sql_by_tokens(file_path)

results = chunk_to_json(chunks, llm)

with open("[Link]", "w") as f:
[Link](results, f, indent=2)

# Assume results is already defined from your LLM chunk_to_json call

clean_json_from_unclean = preprocess_unclean_to_clean(results)
html_output = generate_interactive_html_diagram(clean_json_from_unclean)

with open("lineage_diagram.html", "w") as f:

[Link](html_output)

df_raw, df_filled = analyse_chunks(

chunks,
context="",
filename=filename
)

pd.set_option('display.max_rows', None)
pd.set_option('display.max_columns', None)
pd.set_option('[Link]', None)

# Convert filled DataFrame to JSON

json_output = df_filled[filename].to_json(orient='records', indent=2)
print(json_output)

[Link]("./output/etl documentation", exist_ok=True)

df_filled[filename].to_csv("./output/etl documentation/[Link]",
index=False)
# Preview DataFrame
print(df_filled[filename])