const MONACO_VERSION="0.52.2";const PGLITE_VERSION="0.5.4";const PGLITE_URL=`https://cdn.jsdelivr.net/npm/@electric-sql/pglite@${PGLITE_VERSION}/dist/index.js`;const PGLITE_WORKER_URL=`https://cdn.jsdelivr.net/npm/@electric-sql/pglite@${PGLITE_VERSION}/dist/worker/index.js`;const THEME_KEY="pglite-theme";const QUERY_HISTORY_KEY="websql-query-history";const QUERY_HISTORY_MAX=50;const RESULTS_PAGE_SIZE=1000;const SHARED_DB_PARAM="shared";const useSharedDb=new URLSearchParams(window.location.search).get(SHARED_DB_PARAM)==="1";const useReadOnly=new URLSearchParams(window.location.search).get("readonly")==="1";const isMinimalStyle=new URLSearchParams(window.location.search).get("style")==="minimal";const useDeferredDb=new URLSearchParams(window.location.search).has("result");const resultLabel=new URLSearchParams(window.location.search).get("result");const DB_READY_TIMEOUT_MS=10000;const SPLASH_RETRY_REVEAL_MS=10000;function menu(){console.log("\n\n\n")
console.log("Admin Menu:")
console.log(" a. Print ERD Table Positions")}
function a(){logErdTablePositions()}
let scriptFragments=null
let postsFetched=null
let currentLabel=null
let dataCallback=null
const metadata=[]
function loadBloggerFeed(label,callback,bloggerStartIndex=1,isNextPage=false){if(callback){scriptFragments={}
postsFetched=0
dataCallback=callback
metadata.length=0}
currentLabel=label
const script=document.createElement('script');script.src=`https://websqldata.blogspot.com/feeds/posts/default/-/${label}?alt=json-in-script&start-index=${bloggerStartIndex}&callback=handleFeed`;document.head.appendChild(script);}
function handleFeed(json){const entries=json.feed.entry||[];if(entries.length>0){postsFetched+=entries.length
entries.forEach(entry=>{for(const cat of entry.category){if(isNaN(cat.term)){if(cat.term==="metadata"){const meta=JSON.parse(entry.content.$t)
meta.label=firstLabelExcept(entry.category,"metadata")
metadata.push(meta)}}else{scriptFragments[cat.term]=entry.content.$t}}});const startIndex=postsFetched+1
loadBloggerFeed(currentLabel,null,startIndex,true);}else{if(Object.keys(scriptFragments).length===0){dataCallback(metadata)}else{const order=Object.keys(scriptFragments).map(Number).sort((a,b)=>a-b)
for(let x=0;x{dataLoadCompleteResolve=resolve;});}
async function loadDataFromBlog(script){if(!script){setDataLoading(false);setBusy(false);dataLoadCompleteResolve?.();dataLoadCompleteResolve=null;return;}
try{await wipeCurrentDatabaseStore();await switchDatabase(()=>createDatabase(),DEFAULT_DB_LABEL,{showLoadingOverlay:false});await pg.exec(script);await recordLoadedDatasetLabel(currentLabel);setDbLabel(currentLabel);await refreshTables();clearResults("Run a query to see results here.");await maybeSetDatabaseReadOnly();setDataUrlParam(currentLabel);showToast(`Loaded "${currentLabel}"`,"success");}catch(err){console.error(err);setStatus("Ready");showToast(`Could not load "${currentLabel}": ${err.message}`,"error");}finally{setDataLoading(false);setBusy(false);dataLoadCompleteResolve?.();dataLoadCompleteResolve=null;}}
let resultFetchStartedAt=null;let resultDataFallback=null;function loadResultFromBlog(label){resultFetchStartedAt=performance.now();const script=document.createElement("script");script.src=`https://websqldata.blogspot.com/feeds/posts/default/-/${label}?alt=json-in-script&max-results=1&callback=handleResultFeed`;document.head.appendChild(script);}
function handleResultFeed(json){const elapsedMs=Math.round(performance.now()-resultFetchStartedAt);const entries=json.feed.entry||[];if(entries.length===0){setStatus("Ready");showToast(`No canned result found for "${resultLabel}"`,"error");return;}
try{const result=JSON.parse(entries[0].content.$t);const params=new URLSearchParams(window.location.search);if(!params.has("sql")&&result.sql&&editor){editor.setValue(result.sql);shrinkEditorToFitQuery();}
if(!params.has("data")&&result.data){resultDataFallback=result.data;}
renderResults([result],elapsedMs);setStatus("Ready");}catch(err){console.error(err);setStatus("Ready");showToast(`Could not parse canned result for "${resultLabel}": ${err.message}`,"error");}}
function showDatasets(data){if(data){const overlay=document.createElement("div");overlay.className="loading-overlay dataset-modal-overlay";const dialog=document.createElement("div");dialog.className="loading-dialog dataset-modal-dialog";const closeModal=()=>overlay.remove();const sortedData=[...data].sort((a,b)=>(a.title||a.label||"").localeCompare(b.title||b.label||"",undefined,{sensitivity:"base"}));const itemsHtml=sortedData.map(ds=>{const label=ds.label||"";const title=ds.title||label||"Untitled dataset";const description=escapeHtml(ds.description||"").replace(/\r\n|\r|\n/g,"
");return`
`;}).join("");dialog.innerHTML=`
`;dialog.querySelector(".dataset-modal-close").addEventListener("click",closeModal);dialog.querySelectorAll(".dataset-modal-title").forEach((toggle)=>{toggle.addEventListener("click",()=>{const item=toggle.closest(".dataset-modal-item");const desc=item.querySelector(".dataset-modal-description");if(desc)desc.hidden=!desc.hidden;});});dialog.querySelectorAll(".dataset-modal-load-btn").forEach((btn)=>{btn.addEventListener("click",()=>{const label=btn.getAttribute("data-label")||"";const spinner=btn.querySelector(".spinner");if(spinner)spinner.hidden=false;closeModal();getDataFromBlog(label);});});overlay.addEventListener("click",(e)=>{if(e.target===overlay)closeModal();});document.addEventListener("keydown",function onKeydown(e){if(e.key==="Escape"){closeModal();document.removeEventListener("keydown",onKeydown);}});overlay.appendChild(dialog);document.body.appendChild(overlay);}else{loadBloggerFeed('metadata',showDatasets)}}
function sharedDbIdForPathname(pathname){const path=(pathname||"/").replace(/\/+$/,"")||"/";if(path==="/")return"websql-studio";const safe=path.replace(/^\/+/,"").replace(/[^a-zA-Z0-9._-]+/g,"-").replace(/-+/g,"-").replace(/^-|-$/g,"");return safe?`websql-studio-${safe}`:"websql-studio";}
const DB_ID=sharedDbIdForPathname(window.location.pathname);const DATA_DIR=`idb://${DB_ID}`;const isAdminMode=new URLSearchParams(window.location.search).get("mode")==="admin";const DEFAULT_DB_LABEL=useSharedDb?DATA_DIR:"in-memory";const DEFAULT_SQL=`-- Welcome to WebSQL Studio, running PostgreSQL Lite (PGlite).
-- Write SQL below and press Run (or Ctrl/Cmd + Enter).
select now() as server_time, version() as postgres_version;`;let pg=null;let editor=null;let monacoRef=null;let currentFileLabel=DEFAULT_DB_LABEL;let currentResultSets=[];let currentResultSetsSql="";let savedQueriesCache=[];let hasUnsavedChanges=false;let pendingTablesRefresh=Promise.resolve();let columnsCache=new Map();let dataLoadingDepth=0;let unsubLeaderChange=null;let resultsViewMode="results";let erdMode="sql";let erdClause="select";let erdZoom=1;let erdPositions=new Map();let erdState=null;let hasShownSchemaForCurrentDb=false;let erdDrag=null;let erdPinch=null;let erdGestureStartZoom=null;let erdSelectedRelIndex=null;let lastResultsHtml=`
`);let settled=false;const finish=(result)=>{if(settled)return;settled=true;document.removeEventListener("keydown",onKeydown);overlay.remove();resolve(result);};const onKeydown=(e)=>{if(e.repeat)return;if(e.key!=="Escape"&&e.key!=="Enter")return;e.preventDefault();finish(e.key==="Enter");};dialog.querySelector(".app-dialog-cancel").addEventListener("click",()=>finish(false));dialog.querySelector(".app-dialog-confirm").addEventListener("click",()=>finish(true));overlay.addEventListener("click",(e)=>{if(e.target===overlay)finish(false);});document.addEventListener("keydown",onKeydown);dialog.querySelector(".app-dialog-confirm").focus();});}
function showPromptDialog(message,opts={}){const{placeholder="",defaultValue="",confirmLabel="OK",cancelLabel="Cancel"}=opts;return new Promise((resolve)=>{const{overlay,dialog}=createAppDialogShell(message);dialog.insertAdjacentHTML("beforeend",`
`);const input=dialog.querySelector(".app-dialog-input");let settled=false;const finish=(result)=>{if(settled)return;settled=true;document.removeEventListener("keydown",onKeydown);overlay.remove();resolve(result);};const onKeydown=(e)=>{if(e.repeat)return;if(e.key==="Escape"){e.preventDefault();finish(null);}else if(e.key==="Enter"){e.preventDefault();finish(input.value);}};dialog.querySelector(".app-dialog-cancel").addEventListener("click",()=>finish(null));dialog.querySelector(".app-dialog-confirm").addEventListener("click",()=>finish(input.value));overlay.addEventListener("click",(e)=>{if(e.target===overlay)finish(null);});document.addEventListener("keydown",onKeydown);input.focus();input.select();});}
function setBusy(isBusy){el.run.disabled=isBusy;el.run.classList.toggle("is-busy",isBusy);el.run.setAttribute("aria-busy",isBusy?"true":"false");const runIcon=el.run.querySelector(".run-icon");const runSpinner=el.run.querySelector(".spinner");if(runIcon)runIcon.hidden=isBusy;if(runSpinner)runSpinner.hidden=!isBusy;el.menuButton.disabled=isBusy;}
let splashRetryTimer=null;function hideSplash(){if(splashRetryTimer){clearTimeout(splashRetryTimer);splashRetryTimer=null;}
if(el.splash)el.splash.style.display="none";if(el.appShell)el.appShell.style.display="";}
function scheduleSplashRetryReveal(){splashRetryTimer=setTimeout(()=>{splashRetryTimer=null;if(el.splashRetry)el.splashRetry.hidden=false;},SPLASH_RETRY_REVEAL_MS);}
function setStatusBarVisible(isVisible){if(el.statusBar){el.statusBar.hidden=!isVisible;el.statusBar.style.display=isVisible?"flex":"none";}}
function showDbLoadingOverlay(title,subtitle){el.dbLoadingTitle.textContent=title||"Loading database…";el.dbLoadingSubtitle.textContent=subtitle||"This may take a moment on first load.";el.dbLoadingOverlay.hidden=false;}
function hideDbLoadingOverlay(){el.dbLoadingOverlay.hidden=true;}
function setDataLoading(isLoading,message="Loading data…"){if(isLoading){dataLoadingDepth+=1;setStatusBarVisible(true);el.loadingIndicator.hidden=false;el.loadingIndicator.style.display="inline-flex";el.loadingIndicator.querySelector(".loading-label").textContent=message;showDbLoadingOverlay(message,"This may take a moment.");return;}
dataLoadingDepth=Math.max(dataLoadingDepth-1,0);if(dataLoadingDepth===0){el.loadingIndicator.hidden=true;el.loadingIndicator.style.display="none";setStatusBarVisible(false);hideDbLoadingOverlay();}}
function initMonaco(){return new Promise((resolve)=>{require.config({paths:{vs:`https://cdn.jsdelivr.net/npm/monaco-editor@${MONACO_VERSION}/min/vs`},});require(["vs/editor/editor.main"],(monaco)=>{monacoRef=monaco;const params=new URLSearchParams(window.location.search);const initialSql=params.has("sql")?params.get("sql"):DEFAULT_SQL;editor=monaco.editor.create(el.editorPane,{value:initialSql||DEFAULT_SQL,language:"sql",theme:monacoThemeFor(document.documentElement.getAttribute("data-theme")),fontSize:13,minimap:{enabled:false},automaticLayout:true,scrollBeyondLastLine:false,wordWrap:"on",});editor.addCommand(monaco.KeyMod.CtrlCmd|monaco.KeyCode.Enter,()=>userRunQuery());resolve();});});}
const INITIAL_SQL_RETRY_MS=3000;function applySqlUrlParameter(){if(!editor)return;const params=new URLSearchParams(window.location.search);if(!params.has("sql"))return;const value=params.get("sql");if(value!==null){editor.setValue(value);if(!useDeferredDb){runInitialSqlQuery();}}}
async function runInitialSqlQuery(){if(userHasRunQuery)return;if(!pg){setTimeout(runInitialSqlQuery,INITIAL_SQL_RETRY_MS);return;}
await runQuery();if(!userHasRunQuery&&lastResultsClassName.includes("has-error")){setTimeout(runInitialSqlQuery,INITIAL_SQL_RETRY_MS);}}
async function applyDataUrlParameter(){const params=new URLSearchParams(window.location.search);const label=params.get("data")||resultDataFallback;if(!label)return;if((await getLoadedDatasetLabel())===label){currentLabel=label;setDbLabel(label);setDataUrlParam(label);return;}
await getDataFromBlog(label);}
const APP_META_SCHEMA="_websql_studio";const LOADED_DATASET_TABLE=`${APP_META_SCHEMA}.loaded_dataset`;const SAVED_QUERIES_SCHEMA="system";const SAVED_QUERIES_TABLE=`${SAVED_QUERIES_SCHEMA}.saved_query`;async function getLoadedDatasetLabel(){if(!pg)return null;try{const{rows}=await pg.query(`select label from ${LOADED_DATASET_TABLE} limit 1;`);return rows[0]?.label??null;}catch(_){return null;}}
async function recordLoadedDatasetLabel(label){if(!pg)return;try{await pg.exec(`create schema if not exists ${APP_META_SCHEMA};
create table if not exists ${LOADED_DATASET_TABLE} (label text);
delete from ${LOADED_DATASET_TABLE};`);await pg.query(`insert into ${LOADED_DATASET_TABLE} (label) values ($1);`,[label]);}catch(err){console.error("Failed to record loaded dataset label",err);}}
function withTimeout(promise,ms,message){return new Promise((resolve,reject)=>{const timer=setTimeout(()=>reject(new Error(message)),ms);promise.then((value)=>{clearTimeout(timer);resolve(value);},(err)=>{clearTimeout(timer);reject(err);});});}
async function waitForDbReady(){if(pg&&"waitReady"in pg){await withTimeout(pg.waitReady,DB_READY_TIMEOUT_MS,"Timed out waiting for the shared database connection to become ready. Try reloading this frame.");}}
function setDataUrlParam(label){const url=new URL(window.location.href);url.searchParams.set("data",label);window.history.replaceState(window.history.state,"",url);}
function buildCurrentSetupUrl(){const url=new URL(window.location.href);url.searchParams.delete("style");url.searchParams.delete(SHARED_DB_PARAM);url.searchParams.delete("readonly");if(editor){const sql=editor.getValue();if(sql)url.searchParams.set("sql",sql);else url.searchParams.delete("sql");}
return url.toString();}
function handleOpenInNewTab(){const url=buildCurrentSetupUrl();window.open(url,"_blank","noopener,noreferrer");}
async function loadPGlite(){const mod=await import(PGLITE_URL);return mod.PGlite;}
async function loadPGliteWorker(){const mod=await import(PGLITE_WORKER_URL);return mod.PGliteWorker;}
function createPGliteBlobWorker(){const source=`
import { PGlite } from ${JSON.stringify(PGLITE_URL)};
import { worker } from ${JSON.stringify(PGLITE_WORKER_URL)};
worker({
async init(options) {
return new PGlite({
dataDir: options.dataDir,
loadDataDir: options.loadDataDir,
});
},
});
`;const blob=new Blob([source],{type:"text/javascript"});return new Worker(URL.createObjectURL(blob),{type:"module"});}
function withInitLock(id,fn){if(!("locks"in navigator))return fn();return new Promise((resolve,reject)=>{navigator.locks.request(`pglite-init:${id}`,()=>withTimeout(fn(),DB_READY_TIMEOUT_MS,"Timed out creating the shared database connection.").then(resolve,reject)).catch(reject);});}
function claimPrimaryRole(id){if(!("locks"in navigator))return Promise.resolve(true);return new Promise((resolve)=>{navigator.locks.request(`pglite-primary:${id}`,{ifAvailable:true},(lock)=>{if(!lock){resolve(false);return;}
resolve(true);return new Promise(()=>{});}).catch(()=>resolve(false));});}
let primaryRoleClaim=null;function claimPrimaryRoleOnce(id){if(!primaryRoleClaim)primaryRoleClaim=claimPrimaryRole(id);return primaryRoleClaim;}
let isPrimaryInstance=false;async function createDatabase(options={}){if(useSharedDb){const PGliteWorker=await loadPGliteWorker();return withInitLock(DB_ID,async()=>{isPrimaryInstance=await claimPrimaryRoleOnce(DB_ID);return PGliteWorker.create(createPGliteBlobWorker(),{id:DB_ID,dataDir:DATA_DIR,...options,});});}
isPrimaryInstance=true;const PGlite=await loadPGlite();return PGlite.create(options);}
function deleteIndexedDb(name){return new Promise((resolve,reject)=>{const req=indexedDB.deleteDatabase(name);req.onsuccess=()=>resolve();req.onerror=()=>reject(req.error||new Error(`Failed to delete IndexedDB "${name}"`));req.onblocked=()=>{console.warn(`IndexedDB "${name}" delete blocked by other connections`);};});}
async function wipeCurrentDatabaseStore(){if(unsubLeaderChange){unsubLeaderChange();unsubLeaderChange=null;}
if(pg){try{await pg.close();}catch(_){}
pg=null;}
if(useSharedDb){await deleteIndexedDb(DB_ID);}}
function bindLeaderChange(instance){if(unsubLeaderChange){unsubLeaderChange();unsubLeaderChange=null;}
if(!useSharedDb||!instance||typeof instance.onLeaderChange!=="function")return;unsubLeaderChange=instance.onLeaderChange(()=>{pendingTablesRefresh=refreshTables().catch(()=>{});});}
async function switchDatabase(factory,label,{showLoadingOverlay=true,overlayText}={}){setBusy(true);setStatus("Loading database…");if(showLoadingOverlay){setDataLoading(true,overlayText||(label&&label!=="in-memory"?`Loading "${label}"…`:"Loading database…"));}
try{const next=await factory();if(pg){try{await pg.close();}catch(_){}}
pg=next;await waitForDbReady();bindLeaderChange(pg);hasShownSchemaForCurrentDb=false;erdState=null;erdPositions=new Map();setDbLabel(label);setStatus("Ready");await refreshTables();clearResults("Run a query to see results here.");clearUnsavedChanges();}catch(err){console.error(err);setStatus("Failed to load database");showToast("Could not load database: "+err.message,"error");}finally{setBusy(false);if(showLoadingOverlay)setDataLoading(false);}}
async function maybeSetDatabaseReadOnly(){if(!useReadOnly||!pg)return;await pg.query("SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY;");}
async function importIntoCurrentDatabase(file){if(!pg){await switchDatabase(()=>createDatabase(),DEFAULT_DB_LABEL,{showLoadingOverlay:false});}
const kind=extOf(file.name);setDataLoading(true,kind==="sql"?"Importing SQL…":kind==="csv"?"Importing CSV…":"Importing workbook…");try{if(kind==="sql"){const text=await file.text();await pg.exec(text);}else if(kind==="csv"){await importCsvIntoDatabase(pg,file);}else if(kind==="excel"){await importExcelIntoDatabase(pg,file);}else{throw new Error("Unsupported file type. Use .sql, .csv or Excel.");}
markUnsavedChanges();}finally{setDataLoading(false);}}
function isMetaCommand(text){return typeof text==="string"&&text.trim().startsWith("\\");}
const READ_ONLY_SQL_RE=/^\s*(select|with|explain|show|table)\b/i;function sqlLooksReadOnly(sql){return sql.split(";").map((stmt)=>stmt.trim()).filter(Boolean).every((stmt)=>READ_ONLY_SQL_RE.test(stmt));}
function markUnsavedChanges(){hasUnsavedChanges=true;}
function clearUnsavedChanges(){hasUnsavedChanges=false;}
function normalizeMetaObjectName(value){const trimmed=String(value||"").trim();if(!trimmed)return"";const withoutSchema=trimmed.replace(/^public\./i,"");return withoutSchema.replace(/^"(.*)"$/,"$1").split(".").pop()||"";}
function buildResultSet(result){const fields=Array.isArray(result.fields)?result.fields.map((field)=>({name:field.name||field.column_name||field.column||String(field)})):Object.keys(result.rows?.[0]||{}).map((name)=>({name}));const rows=Array.isArray(result.rows)?result.rows:[];return{fields,rows};}
async function runMetaCommand(text){const trimmed=text.trim();const[commandToken,...rest]=trimmed.split(/\s+/);const command=commandToken.toLowerCase();const arg=rest.join(" ").trim();const objectName=normalizeMetaObjectName(arg);if(command==="\\d"||command==="\\d+"){if(objectName){const{rows,fields}=await pg.query(`select column_name as column_name, data_type as data_type, is_nullable as is_nullable
from information_schema.columns
where table_schema = 'public' and table_name = $1
order by ordinal_position;`,[objectName]);return[buildResultSet({fields,rows})];}
const{rows,fields}=await pg.query(`select table_name as name, 'table' as kind
from information_schema.tables
where table_schema = 'public' and table_type = 'BASE TABLE'
union all
select table_name as name, 'view' as kind
from information_schema.views
where table_schema = 'public'
order by name;`);return[buildResultSet({fields,rows})];}
if(command==="\\dt"){if(objectName){const{rows,fields}=await pg.query(`select table_name as name, 'table' as kind
from information_schema.tables
where table_schema = 'public' and table_type = 'BASE TABLE' and table_name = $1;`,[objectName]);return[buildResultSet({fields,rows})];}
const{rows,fields}=await pg.query(`select table_name as name, 'table' as kind
from information_schema.tables
where table_schema = 'public' and table_type = 'BASE TABLE'
order by table_name;`);return[buildResultSet({fields,rows})];}
if(command==="\\z"){if(objectName){const{rows,fields}=await pg.query(`select grantee, privilege_type
from information_schema.role_table_grants
where table_schema = 'public' and table_name = $1
order by grantee, privilege_type;`,[objectName]);return[buildResultSet({fields,rows})];}
const{rows,fields}=await pg.query(`select table_schema as schema_name, table_name as object_name, grantee, privilege_type
from information_schema.role_table_grants
where table_schema = 'public'
order by table_name, grantee, privilege_type;`);return[buildResultSet({fields,rows})];}
if(command.startsWith("\\")){const{rows,fields}=await pg.query(`select 'Unsupported meta command' as message, $1 as command;`,[commandToken]);return[buildResultSet({fields,rows})];}
throw new Error(`Unsupported meta command: ${commandToken}`);}
function shrinkEditorToFitQuery(){if(!editor||!monacoRef)return;const minHeight=80;const lineCount=editor.getModel().getLineCount();const lineHeight=editor.getOption(monacoRef.editor.EditorOption.lineHeight);const padding=editor.getOption(monacoRef.editor.EditorOption.padding)||{top:0,bottom:0};const fitHeight=lineCount*lineHeight+padding.top+padding.bottom;const targetHeight=Math.max(fitHeight,minHeight);const currentHeight=el.editorPane.getBoundingClientRect().height;if(targetHeight{await switchDatabase(()=>createDatabase(),DEFAULT_DB_LABEL,{showLoadingOverlay:true,overlayText:"Preparing database…",});if(isPrimaryInstance){await applyDataUrlParameter();}})();}
return deferredDbBootPromise;}
async function userRunQuery(){userHasRunQuery=true;await ensureDeferredDbBooted();runQuery();}
async function runQuery(){if(!pg)return;const sql=editor.getValue().trim();if(!sql)return;shrinkEditorToFitQuery();setBusy(true);setStatus("Running…");const startedAt=performance.now();try{const isMeta=isMetaCommand(sql);const results=isMeta?await runMetaCommand(sql):await pg.exec(sql);if(!isMeta&&!sqlLooksReadOnly(sql))markUnsavedChanges();const elapsed=Math.round(performance.now()-startedAt);addQueryHistoryEntry({sql,ok:true,elapsedMs:elapsed});currentResultSetsSql=sql;renderResults(results,elapsed);setStatus("Ready");pendingTablesRefresh=refreshTables();}catch(err){console.error(err);addQueryHistoryEntry({sql,ok:false,error:err.message||String(err)});renderError(err);setStatus("Query failed");}finally{setBusy(false);}}
function clearResults(message){currentResultSets=[];currentResultSetsSql="";resultPageByIdx=new Map();lastResultsHtml=`${escapeHtml(c)} `).join("")}null `;const text=v instanceof Date?formatDateValue(v):typeof v==="object"?JSON.stringify(v):String(v);if(c.toLowerCase().endsWith("_html")){return`${text} `;}
const cellClass=isNumericValue(v)?"cell-numeric":"";return`${escapeHtml(text)} `;}).join("");return`${offset + i + 1} ${cells}${head}${body}
`;}
function markOverflowingCells(){const cells=el.resultsBody.querySelectorAll("td.cell-truncatable");cells.forEach((td)=>{if(td.querySelector(".cell-expand-btn"))return;const span=td.querySelector(".cell-text");if(!span||span.scrollWidth<=span.clientWidth+1)return;const btn=document.createElement("button");btn.className="cell-expand-btn";btn.title="Show full text";btn.textContent="…";td.appendChild(btn);});}
function escapeHtml(str){return String(str).replace(/&/g,"&").replace(//g,">").replace(/"/g,""");}
function csvCell(value){if(value===null||value===undefined)return"";const str=typeof value==="object"?JSON.stringify(value):String(value);return/[",\r\n]/.test(str)?`"${str.replace(/"/g, '""')}"`:str;}
function resultToCsv(res){const cols=res.fields.map((f)=>f.name);const lines=[cols.map(csvCell).join(",")];for(const row of res.rows){lines.push(cols.map((c)=>csvCell(row[c])).join(","));}
return lines.join("\r\n");}
function downloadResultAsCsv(idx){const res=currentResultSets[idx];if(!res)return;const csv=resultToCsv(res);const filename=currentResultSets.length>1?`query-results-${idx + 1}.csv`:"query-results.csv";const blob=new Blob([csv],{type:"text/csv;charset=utf-8;"});const url=URL.createObjectURL(blob);const a=document.createElement("a");a.href=url;a.download=filename;document.body.appendChild(a);a.click();a.remove();URL.revokeObjectURL(url);showToast(`Downloaded "${filename}"`,"success");}
function downloadResultAsJson(idx){const res=currentResultSets[idx];if(!res)return;const json=JSON.stringify({sql:currentResultSetsSql,data:currentLabel,...res},null,2);const filename=currentResultSets.length>1?`query-result-${idx + 1}.json`:"query-result.json";const blob=new Blob([json],{type:"application/json;charset=utf-8;"});const url=URL.createObjectURL(blob);const a=document.createElement("a");a.href=url;a.download=filename;document.body.appendChild(a);a.click();a.remove();URL.revokeObjectURL(url);showToast(`Downloaded "${filename}"`,"success");}
async function refreshTables(){if(!pg)return;columnsCache.clear();try{const{rows}=await pg.query(`select table_name, table_type from information_schema.tables
where table_schema = 'public' order by (table_type = 'VIEW'), table_name;`);if(!rows.length){el.tableList.innerHTML=`
`;}).join("");el.tableList.querySelectorAll(".table-name").forEach((node)=>{node.addEventListener("click",()=>{const name=node.getAttribute("data-table");editor.setValue(`select * from "${name}" limit 100;`);userRunQuery();});});el.tableList.querySelectorAll(".table-toggle").forEach((btn)=>{btn.addEventListener("click",()=>toggleTableColumns(btn));});}
if(resultsViewMode==="erd"){renderErd({rebuild:true}).catch(console.error);}}catch(err){console.error(err);}
await refreshSavedQueries();}
async function refreshSavedQueries(){if(!pg)return;try{const{rows}=await pg.query(`select name, sql from ${SAVED_QUERIES_TABLE} order by name;`);savedQueriesCache=rows;}catch(_){savedQueriesCache=[];}
if(!savedQueriesCache.length){el.savedQueriesList.innerHTML=`
`;}).join("");el.resultsBody.className="results-body history-body";el.resultsBody.innerHTML=`
`;}
function loadHistoryQuery(id){const entry=loadQueryHistory().find((item)=>item.id===id);if(!entry||!editor)return;editor.setValue(entry.sql);editor.focus();userRunQuery();}
function clearQueryHistory(){saveQueryHistory([]);if(resultsViewMode==="history")renderQueryHistory();showToast("Query history cleared");}
function firstBuildOfSchema(){}
async function fetchErdSchema(){const tablesRes=await pg.query(`select table_name
from information_schema.tables
where table_schema = 'public' and table_type = 'BASE TABLE'
order by table_name;`);const columnsRes=await pg.query(`select table_name, column_name, data_type, ordinal_position
from information_schema.columns
where table_schema = 'public'
order by table_name, ordinal_position;`);const pkRes=await pg.query(`select kcu.table_name, kcu.column_name
from information_schema.table_constraints tc
join information_schema.key_column_usage kcu
on tc.constraint_schema = kcu.constraint_schema
and tc.constraint_name = kcu.constraint_name
where tc.constraint_type = 'PRIMARY KEY'
and tc.table_schema = 'public';`);const fkRes=await pg.query(`select
kcu.table_name as from_table,
kcu.column_name as from_column,
kcu2.table_name as to_table,
kcu2.column_name as to_column,
col.is_nullable
from information_schema.table_constraints tc
join information_schema.key_column_usage kcu
on tc.constraint_schema = kcu.constraint_schema
and tc.constraint_name = kcu.constraint_name
join information_schema.referential_constraints rc
on tc.constraint_schema = rc.constraint_schema
and tc.constraint_name = rc.constraint_name
join information_schema.key_column_usage kcu2
on rc.unique_constraint_schema = kcu2.constraint_schema
and rc.unique_constraint_name = kcu2.constraint_name
and kcu.position_in_unique_constraint = kcu2.ordinal_position
join information_schema.columns col
on col.table_schema = kcu.table_schema
and col.table_name = kcu.table_name
and col.column_name = kcu.column_name
where tc.constraint_type = 'FOREIGN KEY'
and tc.table_schema = 'public';`);const pkSet=new Set(pkRes.rows.map((r)=>`${r.table_name}.${r.column_name}`));const fkCols=new Set(fkRes.rows.map((r)=>`${r.from_table}.${r.from_column}`));const tables=new Map();for(const row of tablesRes.rows){tables.set(row.table_name,{name:row.table_name,columns:[]});}
for(const row of columnsRes.rows){const table=tables.get(row.table_name);if(!table)continue;const key=`${row.table_name}.${row.column_name}`;table.columns.push({name:row.column_name,type:formatErdType(row.data_type),isPk:pkSet.has(key),isFk:fkCols.has(key),});}
return{tables:[...tables.values()],relationships:fkRes.rows.map((r)=>{const fkOptional=r.is_nullable==="YES";return{fromTable:r.from_table,fromColumn:r.from_column,toTable:r.to_table,toColumn:r.to_column,manyKind:"zero-or-many",oneKind:fkOptional?"zero-or-one":"one",};}),};}
function formatErdType(dataType){const map={"character varying":"VARCHAR","timestamp with time zone":"TIMESTAMPTZ","timestamp without time zone":"TIMESTAMP","double precision":"FLOAT8",integer:"INT",bigint:"BIGINT",smallint:"SMALLINT",boolean:"BOOL",text:"TEXT",numeric:"NUMERIC",real:"REAL",date:"DATE",uuid:"UUID",json:"JSON",jsonb:"JSONB",};return map[dataType]||String(dataType||"").toUpperCase();}
function erdTableHeight(table){return ERD_HEADER_HEIGHT+Math.max(table.columns?.length||0,1)*ERD_ROW_HEIGHT+8;}
function assignErdLayers(names,relationships){const nameSet=new Set(names);const childrenOf=new Map(names.map((n)=>[n,[]]));const parentsOf=new Map(names.map((n)=>[n,[]]));const indegree=new Map(names.map((n)=>[n,0]));for(const rel of relationships){if(!nameSet.has(rel.fromTable)||!nameSet.has(rel.toTable))continue;if(rel.fromTable===rel.toTable)continue;const parent=rel.toTable;const child=rel.fromTable;if(childrenOf.get(parent).includes(child))continue;childrenOf.get(parent).push(child);parentsOf.get(child).push(parent);indegree.set(child,indegree.get(child)+1);}
const queue=names.filter((n)=>indegree.get(n)===0);const indeg=new Map(indegree);const topo=[];while(queue.length){const n=queue.shift();topo.push(n);for(const child of childrenOf.get(n)){indeg.set(child,indeg.get(child)-1);if(indeg.get(child)===0)queue.push(child);}}
for(const n of names){if(!topo.includes(n))topo.push(n);}
const layer=new Map();for(const n of topo){const parents=parentsOf.get(n);layer.set(n,parents.length?Math.max(...parents.map((p)=>layer.get(p)||0))+1:0);}
return{layer,childrenOf,parentsOf};}
function orderErdLayers(names,layerMap,parentsOf,childrenOf,relationships,tables){const maxLayer=names.reduce((max,n)=>Math.max(max,layerMap.get(n)||0),0);const layers=Array.from({length:maxLayer+1},()=>[]);for(const n of names)layers[layerMap.get(n)||0].push(n);for(const group of layers)group.sort((a,b)=>a.localeCompare(b));const columnIndex=new Map();for(const table of tables){const cols=new Map();(table.columns||[]).forEach((col,idx)=>cols.set(col.name,idx));columnIndex.set(table.name,cols);}
function barycenter(related,neighbor){const hits=related.filter((r)=>neighbor.includes(r));if(!hits.length)return neighbor.length/2;return hits.reduce((sum,r)=>sum+neighbor.indexOf(r),0)/hits.length;}
function parentCrossingKey(parent,childLayer){const rels=relationships.filter((r)=>r.toTable===parent&&childLayer.includes(r.fromTable));if(!rels.length)return barycenter(childrenOf.get(parent)||[],childLayer);return(rels.reduce((sum,r)=>{const childIdx=childLayer.indexOf(r.fromTable);const colIdx=columnIndex.get(r.fromTable)?.get(r.fromColumn)??0;return sum+childIdx*1000+colIdx;},0)/rels.length);}
for(let pass=0;pass<4;pass+=1){for(let i=1;i{const diff=barycenter(parentsOf.get(a)||[],layers[i-1])-
barycenter(parentsOf.get(b)||[],layers[i-1]);return diff||a.localeCompare(b);});}
for(let i=layers.length-2;i>=0;i-=1){layers[i].sort((a,b)=>{const diff=parentCrossingKey(a,layers[i+1])-parentCrossingKey(b,layers[i+1]);return diff||a.localeCompare(b);});}}
return layers;}
function placeErdLayers(tables,layers){const byName=new Map(tables.map((t)=>[t.name,t]));const positions=new Map();const layerHeights=layers.map((group)=>{if(!group.length)return 0;return group.reduce((sum,name,idx)=>{const h=byName.get(name)?.height||ERD_HEADER_HEIGHT;return sum+h+(idx>0?ERD_GAP_Y:0);},0);});const tallest=Math.max(0,...layerHeights);let maxX=ERD_PAD_X;let maxY=ERD_PAD_Y;for(let li=0;li({...table,height:erdTableHeight(table),}));const names=prepared.map((t)=>t.name);const linkedNames=new Set();for(const rel of relationships){if(names.includes(rel.fromTable))linkedNames.add(rel.fromTable);if(names.includes(rel.toTable))linkedNames.add(rel.toTable);}
const linked=names.filter((n)=>linkedNames.has(n));const isolated=names.filter((n)=>!linkedNames.has(n)).sort((a,b)=>a.localeCompare(b));let autoPos=new Map();let maxX=ERD_PAD_X;let maxY=ERD_PAD_Y;if(linked.length){const{layer,childrenOf,parentsOf}=assignErdLayers(linked,relationships);const layers=orderErdLayers(linked,layer,parentsOf,childrenOf,relationships,prepared.filter((t)=>linkedNames.has(t.name)));const placed=placeErdLayers(prepared.filter((t)=>linkedNames.has(t.name)),layers);autoPos=placed.positions;maxX=placed.maxX;maxY=placed.maxY;}
if(isolated.length){const startY=linked.length?maxY+ERD_GAP_Y:ERD_PAD_Y;let x=ERD_PAD_X;let y=startY;let rowMaxHeight=0;const colsPerRow=Math.max(1,Math.ceil(Math.sqrt(isolated.length)));let col=0;for(const name of isolated){const table=prepared.find((t)=>t.name===name);const height=table?.height||ERD_HEADER_HEIGHT;if(col>=colsPerRow){x=ERD_PAD_X;y+=rowMaxHeight+ERD_GAP_Y;rowMaxHeight=0;col=0;}
autoPos.set(name,{x,y});maxX=Math.max(maxX,x+ERD_NODE_WIDTH);maxY=Math.max(maxY,y+height);rowMaxHeight=Math.max(rowMaxHeight,height);x+=ERD_NODE_WIDTH+ERD_GAP_X;col+=1;}}
const nodes=new Map();const tableNames=new Set(prepared.map((t)=>t.name));for(const name of[...erdPositions.keys()]){if(!tableNames.has(name))erdPositions.delete(name);}
for(const table of prepared){const saved=erdPositions.get(table.name);const auto=autoPos.get(table.name)||{x:ERD_PAD_X,y:ERD_PAD_Y};const pos=saved||auto;nodes.set(table.name,{...table,x:pos.x,y:pos.y,width:ERD_NODE_WIDTH,});maxX=Math.max(maxX,pos.x+ERD_NODE_WIDTH);maxY=Math.max(maxY,pos.y+table.height);}
return{nodes,width:maxX+ERD_PAD_X,height:maxY+ERD_PAD_Y,};}
function erdColumnCenterY(node,columnName){const idx=node.columns.findIndex((c)=>c.name===columnName);const row=idx>=0?idx:0;return node.y+ERD_HEADER_HEIGHT+row*ERD_ROW_HEIGHT+ERD_ROW_HEIGHT/2;}
function erdMarkerDepth(kind){if(kind==="one")return 12;if(kind==="one-or-many")return 10;if(kind==="zero-or-many"||kind==="zero-or-one"){return 8+ERD_MARKER_CIRCLE_R*2;}
return 8+ERD_MARKER_CIRCLE_R*2;}
function erdRelationshipPath(fromNode,fromColumn,toNode,toColumn,fromDepth,toDepth){const fromY=erdColumnCenterY(fromNode,fromColumn);const toY=erdColumnCenterY(toNode,toColumn);const fromCenterX=fromNode.x+fromNode.width/2;const toCenterX=toNode.x+toNode.width/2;const goRight=toCenterX>=fromCenterX;const x1=goRight?fromNode.x+fromNode.width:fromNode.x;const x2=goRight?toNode.x:toNode.x+toNode.width;const fromToward=goRight?-1:1;const toToward=goRight?1:-1;const fromLineX=x1-fromToward*fromDepth;const toLineX=x2-toToward*toDepth;const midX=(fromLineX+toLineX)/2;return{d:`M ${fromLineX} ${fromY} C ${midX} ${fromY}, ${midX} ${toY}, ${toLineX} ${toY}`,fromX:x1,fromY,toX:x2,toY,fromToward,toToward,};}
function renderErdCardinalityMarker(x,y,towardEntity,kind){const spread=ERD_CROWSFOOT_SPREAD;const r=ERD_MARKER_CIRCLE_R;const depth=erdMarkerDepth(kind);const along=(offset)=>x-towardEntity*offset;const crowfoot=(tipOffset,heelOffset)=>{const tipX=along(tipOffset);const heelX=along(heelOffset);return`M ${heelX} ${y} L ${tipX} ${y - spread} M ${heelX} ${y} L ${tipX} ${y} M ${heelX} ${y} L ${tipX} ${y + spread}`;};const bar=(offset)=>{const bx=along(offset);return`M ${bx} ${y - spread} L ${bx} ${y + spread}`;};const circleAt=(centerOffset)=>{const cx=along(centerOffset);return`
`;}
function renderErdNode(node){const rows=node.columns.length?node.columns.map((col,i)=>{const y=ERD_HEADER_HEIGHT+i*ERD_ROW_HEIGHT;const textY=y+15;const classes=["erd-column-row"];if(col.isPk)classes.push("primary-key");if(col.isFk)classes.push("foreign-key");const key=col.isPk||col.isFk?erdKeyIcon(node.width-44,y+6,col.isPk):"";return`
${escapeHtml(col.name)} - ${escapeHtml(col.type)}
${key}
`;}).join(""):`(no columns) `;return`
${escapeHtml(node.name)}
${rows}
`;}
function renderErdRelationship(rel,nodes,index){const fromNode=nodes.get(rel.fromTable);const toNode=nodes.get(rel.toTable);if(!fromNode||!toNode)return"";const manyKind=rel.manyKind||"zero-or-many";const oneKind=rel.oneKind||"one";const path=erdRelationshipPath(fromNode,rel.fromColumn,toNode,rel.toColumn,erdMarkerDepth(manyKind),erdMarkerDepth(oneKind));const manyMarker=renderErdCardinalityMarker(path.fromX,path.fromY,path.fromToward,manyKind);const oneMarker=renderErdCardinalityMarker(path.toX,path.toY,path.toToward,oneKind);const selected=erdSelectedRelIndex===index?" is-selected":"";return`
`;}
function selectErdRelationship(index){erdSelectedRelIndex=index;const group=el.resultsBody.querySelector("#erd-relationships");if(!group)return;group.querySelectorAll(".erd-relationship.is-selected").forEach((relEl)=>{relEl.classList.remove("is-selected");});if(index==null)return;const selected=group.querySelector(`.erd-relationship[data-rel-index="${index}"]`);if(!selected){erdSelectedRelIndex=null;return;}
selected.classList.add("is-selected");group.appendChild(selected);}
function pointerToErdCoords(event){const scroll=el.resultsBody.querySelector(".erd-scroll");if(!scroll)return{x:0,y:0};const rect=scroll.getBoundingClientRect();return{x:(event.clientX-rect.left+scroll.scrollLeft)/erdZoom,y:(event.clientY-rect.top+scroll.scrollTop)/erdZoom,};}
function syncErdCanvasSize(){if(!erdState)return;const svg=el.resultsBody.querySelector(".erd-svg");const scroll=el.resultsBody.querySelector(".erd-scroll");if(!svg||!scroll)return;let maxX=0;let maxY=0;for(const node of erdState.nodes.values()){maxX=Math.max(maxX,node.x+node.width);maxY=Math.max(maxY,node.y+node.height);}
const width=Math.max(Math.ceil(maxX+ERD_PAD_X),Math.ceil(scroll.clientWidth/erdZoom));const height=Math.max(Math.ceil(maxY+ERD_PAD_Y),Math.ceil(scroll.clientHeight/erdZoom));svg.setAttribute("width",String(width));svg.setAttribute("height",String(height));svg.setAttribute("viewBox",`0 0 ${width} ${height}`);svg.style.width=`${width}px`;svg.style.height=`${height}px`;}
function updateErdRelationships(){if(!erdState)return;const group=el.resultsBody.querySelector("#erd-relationships");if(!group)return;group.innerHTML=erdState.relationships.map((rel,index)=>renderErdRelationship(rel,erdState.nodes,index)).join("");selectErdRelationship(erdSelectedRelIndex);}
function bindErdRelationshipHandlers(){const svg=el.resultsBody.querySelector(".erd-svg");if(!svg||svg.dataset.relClickBound==="1")return;svg.dataset.relClickBound="1";svg.addEventListener("click",(event)=>{if(erdDrag)return;const relEl=event.target.closest(".erd-relationship");if(relEl){const index=Number(relEl.getAttribute("data-rel-index"));if(!Number.isFinite(index))return;selectErdRelationship(erdSelectedRelIndex===index?null:index);if(erdMode==="sql"){const rel=erdState?.relationships?.[index];if(rel){const fragment=`${rel.fromTable} JOIN ${rel.toTable} ON ${rel.fromTable}.${rel.fromColumn} = ${rel.toTable}.${rel.toColumn}`;erdAmendSql(fragment,"link",event.ctrlKey);}}
event.stopPropagation();return;}
selectErdRelationship(null);});}
function findErdNodeEl(tableName){return[...el.resultsBody.querySelectorAll(".erd-node")].find((nodeEl)=>nodeEl.getAttribute("data-table")===tableName);}
function moveErdNode(tableName,x,y){if(!erdState)return;const node=erdState.nodes.get(tableName);if(!node)return;node.x=Math.max(ERD_MIN_POS,x);node.y=Math.max(ERD_MIN_POS,y);erdPositions.set(tableName,{x:node.x,y:node.y});const nodeEl=findErdNodeEl(tableName);if(nodeEl)nodeEl.setAttribute("transform",`translate(${node.x} ${node.y})`);updateErdRelationships();syncErdCanvasSize();}
function logErdTablePositions(){if(!erdState)return null;const positions={};const stmt=[]
for(const[name,node]of erdState.nodes){positions[name]={x:node.x,y:node.y};}
stmt.push("create schema system;")
stmt.push("create table system.erd(data text);")
stmt.push("insert into system.erd values('")
stmt.push(JSON.stringify(positions,null,2))
stmt.push("');");console.log(stmt.join("\n"));return positions;}
function mergeErdPositions(positions){if(!positions||typeof positions!=="object")return;for(const[name,pos]of Object.entries(positions)){if(pos&&typeof pos.x==="number"&&typeof pos.y==="number"){erdPositions.set(name,{x:pos.x,y:pos.y});}}}
function applyErdTablePositions(positions){mergeErdPositions(positions);if(erdState){for(const node of erdState.nodes.values()){const pos=erdPositions.get(node.name);if(pos){node.x=pos.x;node.y=pos.y;}}}
if(resultsViewMode==="erd")renderErdView();}
async function fetchErdPositionsFromDb(){const{rows:matches}=await pg.query(`select 1 from information_schema.tables where table_schema = 'system' and table_name = 'erd';`);if(!matches.length)return null;const{rows,fields}=await pg.query(`select * from system.erd limit 1;`);if(!rows.length||!fields.length)return null;const raw=rows[0][fields[0].name];if(raw==null)return null;try{return typeof raw==="string"?JSON.parse(raw):raw;}catch(err){console.error("Failed to parse system.erd positions",err);return null;}}
window.applyErdTablePositions=applyErdTablePositions;const ERD_NUMERIC_TYPES=new Set(["INT","BIGINT","SMALLINT","NUMERIC","REAL","FLOAT8"]);let erdQuery={select:[],from:[],tables:[],where:[],orderBy:[]};let erdQueryHistory=[];let erdQueryHistoryPosition=0;function erdQualifyForRender(entry){if(entry.indexOf(".")===-1)return entry;return erdQuery.tables.length===1?entry.split(".")[1]:entry;}
function erdGetLocalSql(){let sql="SELECT ";for(let x=0;x0)sql+="\n ,";sql+=erdQualifyForRender(erdQuery.select[x]);}
for(let x=0;x`${erdQualifyForRender(w.column)} = ${w.numeric ? w.value : `'${w.value}'`}`).join("\n AND ");}
if(erdQuery.orderBy.length){sql+="\nORDER BY "+erdQuery.orderBy.map(erdQualifyForRender).join(", ");}
return sql.trim()==="SELECT"?"":sql;}
function erdAddField(table,field){if(erdQuery.select.indexOf(table+"."+field)>-1)return;if(erdQuery.tables.length===0){erdQuery.select.push(table+"."+field);erdQuery.tables.push(table);erdQuery.from.push(table);return;}
if(erdQuery.tables.indexOf(table)===-1){return`Cannot add table to query. Try clicking ON a link instead.`;}
if(field==="*"){return`Cannot add table to query. Try clicking ON a field instead.`;}
if(erdQuery.select.length===1&&erdQuery.select[0]==="*"){erdQuery.select[0]=table+"."+field;return;}
if(erdQuery.select.length===1&&erdQuery.select[0].endsWith(".*")){erdQuery.select.shift();}
erdQuery.select.push(table+"."+field);}
function erdAddOrderBy(table,field){if(erdQuery.tables.indexOf(table)===-1){return`Add "${table}" to the query before ordering by one of its columns.`;}
const qualified=table+"."+field;if(erdQuery.orderBy.indexOf(qualified)===-1){erdQuery.orderBy.push(qualified);}}
async function erdAddWhere(table,field){if(erdQuery.tables.indexOf(table)===-1){return`Add "${table}" to the query before filtering on one of its columns.`;}
const raw=await showPromptDialog(`Value for ${table}.${field} =`);if(raw===null)return;const column=erdState?.nodes.get(table)?.columns.find((c)=>c.name===field);const numeric=!!column&&ERD_NUMERIC_TYPES.has(column.type);erdQuery.where.push({column:table+"."+field,value:numeric?raw.trim():raw.replace(/'/g,"''"),numeric,});}
function erdWriteQuery(){if(editor)editor.setValue(erdGetLocalSql());}
async function erdAmendSql(fragment,kind,ctrlKey){if(!editor)return;const current=editor.getValue();if(current.trim().length===0){erdQuery={select:[],from:[],tables:[],where:[],orderBy:[]};erdQueryHistory=[];erdQueryHistoryPosition=0;}
if(current.trim()!==erdGetLocalSql()){let insert=fragment;if(kind==="table"){if(!ctrlKey){const parts=insert.split(".");insert=parts[parts.length-1];}
insert=", "+insert;}else if(kind==="field"&&ctrlKey){insert=", "+insert;}
editor.trigger("erd","type",{text:insert});editor.focus();return;}
let msg;if(fragment.indexOf(".")===-1){msg=erdAddField(fragment,"*");}else if(fragment.indexOf(" JOIN ")===-1){const table=fragment.split(".")[0];const field=fragment.split(".")[1];if(erdClause==="orderby"){msg=erdAddOrderBy(table,field);}else if(erdClause==="where"){msg=await erdAddWhere(table,field);}else{msg=erdAddField(table,field);}}else{const temp=fragment.replace(" JOIN "," ").split(" ");const table1=temp[0];const table2=temp[1];if(erdQuery.tables.length===0){msg=erdAddField(table1,"*");if(!msg){const onClause=fragment.split(" ON ")[1];erdQuery.from.push(" JOIN "+table2+"\n ON "+onClause);erdQuery.tables.push(table2);}}else{let matchCount=0;let tableToAdd;for(const tname of erdQuery.tables){if(tname===table1){matchCount++;tableToAdd=table2;}
if(tname===table2){matchCount++;tableToAdd=table1;}}
if(matchCount===0){msg=`Neither "${table1}" nor "${table2}" is already in the query, so we cannot add the selected join.`;}else if(matchCount===1){erdQuery.from.push(" JOIN "+tableToAdd+"\n ON "+fragment.split(" ON ")[1].replace(/ AND /g,"\n AND "));erdQuery.tables.push(tableToAdd);}else{msg=`Both "${table1}" and "${table2}" are already in the query, so we cannot add the selected join.`;}}}
if(msg){showToast(msg,"error");return;}
if(erdQueryHistoryPosition{if(erdMode==="sql"){nodeEl.addEventListener("click",(event)=>{const tableName=nodeEl.getAttribute("data-table");if(!tableName)return;const columnName=event.target.closest(".erd-column-row")?.getAttribute("data-column");if(columnName){erdAmendSql(`${tableName}.${columnName}`,"field",event.ctrlKey);}else{erdAmendSql(tableName,"table",event.ctrlKey);}});return;}
nodeEl.addEventListener("pointerdown",(event)=>startErdDrag(event,nodeEl));nodeEl.addEventListener("pointermove",onErdPointerMove);nodeEl.addEventListener("pointerup",endErdDrag);nodeEl.addEventListener("pointercancel",endErdDrag);});}
function applyErdZoom(){const svg=el.resultsBody.querySelector(".erd-svg");if(svg)svg.style.transform=`scale(${erdZoom})`;el.erdZoomLabel.textContent=`${Math.round(erdZoom * 100)}%`;syncErdCanvasSize();}
function clampErdZoom(zoom){return Math.min(ERD_ZOOM_MAX,Math.max(ERD_ZOOM_MIN,zoom));}
function setErdZoomAt(clientX,clientY,nextZoom){const scroll=el.resultsBody.querySelector(".erd-scroll");if(!scroll){erdZoom=clampErdZoom(nextZoom);applyErdZoom();return;}
const zoom=clampErdZoom(nextZoom);if(zoom===erdZoom){applyErdZoom();return;}
const rect=scroll.getBoundingClientRect();const contentX=(clientX-rect.left+scroll.scrollLeft)/erdZoom;const contentY=(clientY-rect.top+scroll.scrollTop)/erdZoom;erdZoom=zoom;applyErdZoom();scroll.scrollLeft=contentX*erdZoom-(clientX-rect.left);scroll.scrollTop=contentY*erdZoom-(clientY-rect.top);}
function changeErdZoom(delta){const scroll=el.resultsBody.querySelector(".erd-scroll");const nextZoom=Math.round((erdZoom+delta)*10)/10;if(!scroll){erdZoom=clampErdZoom(nextZoom);applyErdZoom();return;}
const rect=scroll.getBoundingClientRect();setErdZoomAt(rect.left+rect.width/2,rect.top+rect.height/2,nextZoom);}
function cancelErdDrag(){if(!erdDrag)return;const nodeEl=findErdNodeEl(erdDrag.tableName);if(nodeEl)nodeEl.classList.remove("is-dragging");const scroll=el.resultsBody.querySelector(".erd-scroll");if(scroll)scroll.classList.remove("is-dragging");erdDrag=null;}
function onErdTouchStart(event){if(event.touches.length!==2){if(event.touches.length<2)erdPinch=null;return;}
cancelErdDrag();const a=event.touches[0];const b=event.touches[1];const distance=Math.hypot(b.clientX-a.clientX,b.clientY-a.clientY);if(distance<8)return;erdPinch={distance,zoom:erdZoom};}
function onErdTouchMove(event){if(event.touches.length!==2||!erdPinch)return;event.preventDefault();const a=event.touches[0];const b=event.touches[1];const distance=Math.hypot(b.clientX-a.clientX,b.clientY-a.clientY);if(distance<8)return;setErdZoomAt((a.clientX+b.clientX)/2,(a.clientY+b.clientY)/2,erdPinch.zoom*(distance/erdPinch.distance));}
function onErdTouchEnd(event){if(event.touches.length<2)erdPinch=null;}
function onErdWheelZoom(event){if(!event.ctrlKey&&!event.metaKey)return;event.preventDefault();const factor=Math.exp(-event.deltaY*0.01);setErdZoomAt(event.clientX,event.clientY,erdZoom*factor);}
function onErdGestureStart(event){event.preventDefault();erdGestureStartZoom=erdZoom;}
function onErdGestureChange(event){event.preventDefault();if(erdGestureStartZoom==null||typeof event.scale!=="number")return;const clientX=typeof event.clientX==="number"?event.clientX:0;const clientY=typeof event.clientY==="number"?event.clientY:0;setErdZoomAt(clientX,clientY,erdGestureStartZoom*event.scale);}
function onErdGestureEnd(event){event.preventDefault();erdGestureStartZoom=null;}
function bindErdZoomHandlers(){const scroll=el.resultsBody.querySelector(".erd-scroll");if(!scroll)return;erdPinch=null;erdGestureStartZoom=null;scroll.addEventListener("touchstart",onErdTouchStart,{passive:true});scroll.addEventListener("touchmove",onErdTouchMove,{passive:false});scroll.addEventListener("touchend",onErdTouchEnd);scroll.addEventListener("touchcancel",onErdTouchEnd);scroll.addEventListener("wheel",onErdWheelZoom,{passive:false});scroll.addEventListener("gesturestart",onErdGestureStart);scroll.addEventListener("gesturechange",onErdGestureChange);scroll.addEventListener("gestureend",onErdGestureEnd);}
async function buildErdState(){const schema=await fetchErdSchema();if(!schema.tables.length){erdState=null;return false;}
mergeErdPositions(await fetchErdPositionsFromDb());const{nodes}=layoutErdNodes(schema.tables,schema.relationships);erdState={nodes,relationships:schema.relationships,tableCount:schema.tables.length,};return true;}
function renderErdView(){erdDrag=null;erdPinch=null;erdGestureStartZoom=null;erdSelectedRelIndex=null;el.resultsBody.className="results-body erd-body";if(!erdState){el.resultsBody.innerHTML=`
`;const relCount=relationships.length;el.resultsMeta.textContent=`${tableCount} table(s) · ${relCount} relationship(s)`;bindErdDragHandlers();bindErdRelationshipHandlers();bindErdZoomHandlers();applyErdZoom();}
async function renderErd({rebuild=false}={}){if(!pg)return;if(rebuild||!erdState){el.resultsBody.className="results-body erd-body";el.resultsBody.innerHTML=`cell.length>0))rows.push(row);row=[];field="";}else if(ch!=="\r"){field+=ch;}}
if(inQuotes){row.push(field);}else{row.push(field);}
if(row.some((cell)=>cell.length>0))rows.push(row);return rows;}
function inferSqlType(values){const nonEmpty=values.filter((value)=>value!==null&&value!==undefined&&String(value).trim()!=="");if(!nonEmpty.length)return"text";const normalized=nonEmpty.map((value)=>String(value).trim());if(normalized.every((value)=>/^(true|false)$/i.test(value)))return"boolean";if(normalized.every((value)=>/^[-+]?\d+$/.test(value)))return"integer";if(normalized.every((value)=>/^[-+]?(?:\d+\.?\d*|\.\d+)(?:[eE][-+]?\d+)?$/.test(value)))return"double precision";if(normalized.every((value)=>/^\d{4}-\d{2}-\d{2}$/.test(value)))return"date";if(normalized.every((value)=>/^\d{4}-\d{2}-\d{2}[T]\d{2}:\d{2}(?::\d{2}(?:\.\d{1,6})?)?$/.test(value)))return"timestamp";return"text";}
function sqlLiteral(value){if(value===null||value===undefined||String(value).trim()==="")return"NULL";if(typeof value==="number")return String(value);if(typeof value==="boolean")return value?"true":"false";return"'"+String(value).replace(/'/g,"''")+"'";}
async function importTabularRowsIntoDatabase(db,tableName,rows){const nonEmptyRows=rows.filter((row)=>Array.isArray(row)&&row.some((cell)=>cell!==null&&cell!==undefined&&String(cell).trim()!==""));if(!nonEmptyRows.length){await db.exec(`create table ${tableName} (value text);`);return;}
const headers=nonEmptyRows[0];const dataRows=nonEmptyRows.slice(1);const normalizedHeaders=[];const usedNames=new Set();headers.forEach((header,index)=>{let name=sanitizeIdentifier(header||`column_${index + 1}`);if(usedNames.has(name)){let suffix=2;while(usedNames.has(`${name}_${suffix}`))suffix+=1;name=`${name}_${suffix}`;}
usedNames.add(name);normalizedHeaders.push(name);});const columnTypes=normalizedHeaders.map((_,index)=>inferSqlType(dataRows.map((row)=>row[index]??"")));const columnsSql=normalizedHeaders.map((name,index)=>`${name} ${columnTypes[index]}`).join(", ");await db.exec(`create table ${tableName} (${columnsSql});`);for(const row of dataRows){const values=normalizedHeaders.map((_,index)=>sqlLiteral(row[index]??""));await db.exec(`insert into ${tableName} (${normalizedHeaders.join(", ")}) values (${values.join(", ")});`);}}
async function importCsvTextIntoDatabase(db,text,tableName){const rows=parseCsv(text);if(!rows.length)throw new Error("CSV file is empty.");await importTabularRowsIntoDatabase(db,sanitizeIdentifier(tableName)||"uploaded_data",rows);}
async function importCsvIntoDatabase(db,file){const text=await file.text();const tableName=file.name.replace(/\.[^.]+$/,"");await importCsvTextIntoDatabase(db,text,tableName);}
async function importExcelIntoDatabase(db,file){if(!window.XLSX)throw new Error("Excel parsing library is not available.");const arrayBuffer=await file.arrayBuffer();const workbook=window.XLSX.read(arrayBuffer,{type:"array"});const sheetNames=workbook.SheetNames||[];if(!sheetNames.length)throw new Error("Workbook has no worksheets.");for(const sheetName of sheetNames){const sheet=workbook.Sheets[sheetName];if(!sheet)continue;const rows=window.XLSX.utils.sheet_to_json(sheet,{header:1,defval:""});const tableName=sanitizeIdentifier(sheetName)||"worksheet";await importTabularRowsIntoDatabase(db,tableName,rows);}}
function extOf(name){const lower=name.toLowerCase();if(lower.endsWith(".tar.gz")||lower.endsWith(".tgz"))return"tarball";if(lower.endsWith(".tar"))return"tarball";if(lower.endsWith(".sql"))return"sql";if(lower.endsWith(".csv"))return"csv";if(lower.endsWith(".xlsx")||lower.endsWith(".xlsm")||lower.endsWith(".xls"))return"excel";return"unknown";}
async function importGistCsvs(gistId){if(!gistId||!gistId.trim())throw new Error("Please provide a gist ID.");if(!pg){await switchDatabase(()=>createDatabase(),DEFAULT_DB_LABEL,{showLoadingOverlay:false});}
const id=gistId.trim();setDataLoading(true,`Importing gist ${id}…`);try{const response=await fetch(`https://api.github.com/gists/${id}`,{headers:{Accept:"application/vnd.github+json"},});if(!response.ok)throw new Error(`Could not load gist ${id}.`);const gist=await response.json();const csvFiles=Object.entries(gist.files||{}).filter(([,meta])=>meta&&typeof meta.filename==="string"&&/\.csv$/i.test(meta.filename));if(!csvFiles.length)throw new Error("No CSV files were found in that gist.");for(const[filename,meta]of csvFiles){const rawUrl=meta.raw_url;if(!rawUrl)throw new Error(`Missing raw URL for ${filename}.`);const rawResponse=await fetch(rawUrl);if(!rawResponse.ok)throw new Error(`Could not fetch ${filename}.`);const text=await rawResponse.text();await importCsvTextIntoDatabase(pg,text,filename.replace(/\.csv$/i,""));}
markUnsavedChanges();await refreshTables();clearResults("Run a query to see results here.");await maybeSetDatabaseReadOnly();showToast(`Imported ${csvFiles.length} CSV file${csvFiles.length === 1 ? "" : "s"} from gist ${id}`,"success");}finally{setDataLoading(false);}}
async function handleUpload(file){const kind=extOf(file.name);setDataLoading(true,kind==="sql"?"Importing SQL…":kind==="csv"?"Importing CSV…":kind==="excel"?"Importing workbook…":"Loading data file…");try{if(kind==="tarball"){await wipeCurrentDatabaseStore();await switchDatabase(()=>createDatabase({loadDataDir:file}),file.name);await maybeSetDatabaseReadOnly();showToast(`Loaded "${file.name}"`,"success");return true;}
if(kind==="sql"||kind==="csv"||kind==="excel"){await importIntoCurrentDatabase(file);setDbLabel(currentFileLabel===DEFAULT_DB_LABEL?file.name:currentFileLabel);await refreshTables();clearResults("Run a query to see results here.");await maybeSetDatabaseReadOnly();showToast(`Imported "${file.name}"`,"success");return true;}
showToast("Unsupported file type. Use .tar, .tar.gz, .sql, .csv or Excel","error");return false;}catch(err){console.error(err);showToast(`Could not import "${file.name}": ${err.message}`,"error");return false;}finally{setDataLoading(false);}}
function escapeCsvValue(value){if(value===null||value===undefined)return"";const text=String(value);if(/[",\n]/.test(text)){return`"${text.replace(/"/g, '""')}"`;}
return text;}
function rowsToCsv(rows,fields){const headers=(fields||[]).map((field)=>field.name||"");const lines=[];lines.push(headers.map(escapeCsvValue).join(","));for(const row of rows||[]){lines.push(headers.map((header)=>escapeCsvValue(row[header]??"")).join(","));}
return lines.join("\n");}
async function handleSaveToGist(){if(!pg)return;const token=await showPromptDialog("Enter your GitHub personal access token:");if(!token)return;const description=(await showPromptDialog("Optional gist description:"))||"PGlite Studio export";setBusy(true);setStatus("Saving tables to gist…");try{const{rows:tables}=await pg.query(`select table_name from information_schema.tables where table_schema = 'public' order by table_name;`);if(!tables.length){throw new Error("There are no tables to save.");}
const files={};for(const table of tables){const tableName=table.table_name;const{rows,fields}=await pg.query(`select * from "${tableName}";`);const filename=`${sanitizeIdentifier(tableName) || "table"}.csv`;files[filename]={content:rowsToCsv(rows,fields)};}
const response=await fetch("https://api.github.com/gists",{method:"POST",headers:{Authorization:`token ${token}`,Accept:"application/vnd.github+json","Content-Type":"application/json",},body:JSON.stringify({description,public:false,files,}),});if(!response.ok){const text=await response.text();throw new Error(text||"Failed to create gist.");}
const result=await response.json();const gistUrl=result.html_url||result.url||"";setStatus("Ready");showToast(`Saved ${tables.length} table${tables.length === 1 ? "" : "s"} to gist`,"success");if(gistUrl){window.open(gistUrl,"_blank","noopener,noreferrer");}}catch(err){console.error(err);setStatus("Ready");showToast("Could not save to gist: "+err.message,"error");}finally{setBusy(false);}}
async function handleDownload(){if(!pg)return;setBusy(true);setStatus("Preparing download…");try{const blob=await pg.dumpDataDir();const base=currentFileLabel.replace(/\.(tar(\.gz)?|tgz|sql)$/i,"")||"database";const filename=`${base}.tar.gz`;const url=URL.createObjectURL(blob);const a=document.createElement("a");a.href=url;a.download=filename;document.body.appendChild(a);a.click();a.remove();URL.revokeObjectURL(url);setStatus("Ready");clearUnsavedChanges();showToast(`Downloaded "${filename}"`,"success");}catch(err){console.error(err);showToast("Download failed: "+err.message,"error");setStatus("Ready");}finally{setBusy(false);}}
async function generateSchemaSql(){const{rows:tableRows}=await pg.query(`select c.relname as table_name
from pg_class c
join pg_namespace n on n.oid = c.relnamespace
where n.nspname = 'public' and c.relkind = 'r'
order by c.relname;`);if(!tableRows.length){throw new Error("There are no tables in this database.");}
const{rows:columnRows}=await pg.query(`select c.relname as table_name, a.attname as column_name,
format_type(a.atttypid, a.atttypmod) as data_type,
a.attnotnull as not_null,
pg_get_expr(ad.adbin, ad.adrelid) as default_expr
from pg_class c
join pg_namespace n on n.oid = c.relnamespace
join pg_attribute a on a.attrelid = c.oid
left join pg_attrdef ad on ad.adrelid = a.attrelid and ad.adnum = a.attnum
where n.nspname = 'public' and c.relkind = 'r'
and a.attnum > 0 and not a.attisdropped
order by c.relname, a.attnum;`);const{rows:constraintRows}=await pg.query(`select c.relname as table_name, con.conname, con.contype,
pg_get_constraintdef(con.oid) as def
from pg_constraint con
join pg_class c on c.oid = con.conrelid
join pg_namespace n on n.oid = c.relnamespace
where n.nspname = 'public'
order by c.relname,
case con.contype when 'p' then 1 when 'u' then 2 when 'f' then 3 else 4 end;`);const{rows:indexRows}=await pg.query(`select tablename, indexname, indexdef
from pg_indexes
where schemaname = 'public'
order by tablename, indexname;`);const{rows:viewRows}=await pg.query(`select viewname, definition
from pg_views
where schemaname = 'public'
order by viewname;`);const columnsByTable=new Map();for(const row of columnRows){if(!columnsByTable.has(row.table_name))columnsByTable.set(row.table_name,[]);columnsByTable.get(row.table_name).push(row);}
const constraintsByTable=new Map();const constraintNames=new Set();for(const row of constraintRows){if(!constraintsByTable.has(row.table_name))constraintsByTable.set(row.table_name,[]);constraintsByTable.get(row.table_name).push(row);if(row.contype==="p"||row.contype==="u")constraintNames.add(row.conname);}
const parts=[];for(const{table_name}of tableRows){const columns=columnsByTable.get(table_name)||[];const columnLines=columns.map((col)=>{let line=` "${col.column_name}" ${col.data_type}`;if(col.not_null)line+=" NOT NULL";if(col.default_expr)line+=` DEFAULT ${col.default_expr}`;return line;});parts.push(`CREATE TABLE "${table_name}" (\n${columnLines.join(",\n")}\n);`);}
for(const{table_name}of tableRows){for(const con of constraintsByTable.get(table_name)||[]){parts.push(`ALTER TABLE "${table_name}" ADD CONSTRAINT "${con.conname}" ${con.def};`);}}
for(const idx of indexRows){if(constraintNames.has(idx.indexname))continue;parts.push(`${idx.indexdef};`);}
for(const view of viewRows){parts.push(`CREATE VIEW "${view.viewname}" AS\n${view.definition.trim().replace(/;$/, "")};`);}
return parts.join("\n\n")+"\n";}
async function handleCopySchemaForAi(){if(!pg)return;setBusy(true);setStatus("Building schema…");try{const sql=await generateSchemaSql();const messageForAi=`Here's a schema script for a postgresql database I'm wroking with.\n\n${sql}\n\n I'm going to ask for help writing some quereries once you unstand the structure. I'll ask the questions in natural language. You produce the SQL to answer the question using the given schema.`;await navigator.clipboard.writeText(messageForAi);setStatus("Ready");showToast("Schema copied to clipboard","success");}catch(err){console.error(err);setStatus("Ready");showToast("Could not copy schema: "+err.message,"error");}finally{setBusy(false);}}
async function handleExportExcel(){if(!pg||!window.XLSX){showToast("Excel export is unavailable right now.","error");return;}
setBusy(true);setStatus("Exporting workbook…");try{const{rows:tables}=await pg.query(`select table_name from information_schema.tables where table_schema = 'public' order by table_name;`);const workbook=window.XLSX.utils.book_new();for(const table of tables){const tableName=table.table_name;const{rows,fields}=await pg.query(`select * from "${tableName}";`);const headers=(fields||[]).map((field)=>field.name);const sheetRows=[headers,...rows.map((row)=>headers.map((header)=>row[header]??""))];const worksheet=window.XLSX.utils.aoa_to_sheet(sheetRows);window.XLSX.utils.book_append_sheet(workbook,worksheet,tableName);}
const base=currentFileLabel.replace(/\.(tar(\.gz)?|tgz|sql|csv|xlsx|xlsm|xls)$/i,"")||"database";const filename=`${base}.xlsx`;const buffer=window.XLSX.write(workbook,{bookType:"xlsx",type:"array"});const blob=new Blob([buffer],{type:"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"});const url=URL.createObjectURL(blob);const a=document.createElement("a");a.href=url;a.download=filename;document.body.appendChild(a);a.click();a.remove();URL.revokeObjectURL(url);setStatus("Ready");showToast(`Exported "${filename}"`,"success");}catch(err){console.error(err);setStatus("Ready");showToast("Excel export failed: "+err.message,"error");}finally{setBusy(false);}}
function initResizer(){let dragging=false;el.resizer.addEventListener("mousedown",()=>{dragging=true;document.body.style.cursor="row-resize";});window.addEventListener("mousemove",(e)=>{if(!dragging)return;const layout=el.editorPane.parentElement.getBoundingClientRect();const relative=e.clientY-layout.top;const min=80;const max=layout.height-100;const clamped=Math.max(min,Math.min(max,relative));el.editorPane.style.flex=`0 0 ${clamped}px`;});window.addEventListener("mouseup",()=>{if(!dragging)return;dragging=false;document.body.style.cursor="";});}
function initEventListeners(){window.addEventListener("beforeunload",(e)=>{if(!hasUnsavedChanges)return;e.preventDefault();e.returnValue="";});el.splashRetry?.addEventListener("click",()=>window.location.reload());el.run.addEventListener("click",()=>userRunQuery());el.resultsBody.addEventListener("click",async(e)=>{const expandBtn=e.target.closest(".cell-expand-btn");if(expandBtn){const td=expandBtn.closest("td");const expanded=td.classList.toggle("cell-expanded");expandBtn.title=expanded?"Show less":"Show full text";return;}
const csvBtn=e.target.closest(".csv-btn");if(csvBtn){downloadResultAsCsv(Number(csvBtn.getAttribute("data-idx")));return;}
const jsonBtn=e.target.closest(".result-json-btn");if(jsonBtn){downloadResultAsJson(Number(jsonBtn.getAttribute("data-idx")));return;}
const saveQueryBtn=e.target.closest(".save-query-btn");if(saveQueryBtn){handleSaveQuery();return;}
const pageBtn=e.target.closest(".page-btn");if(pageBtn){goToResultPage(Number(pageBtn.getAttribute("data-idx")),pageBtn.getAttribute("data-dir"));return;}
const clearHistoryBtn=e.target.closest(".history-clear-btn");if(clearHistoryBtn){if(await showConfirmDialog("Clear all queries from this session's history?"))clearQueryHistory();return;}
const loadBtn=e.target.closest(".history-load-btn");const historyItem=e.target.closest(".history-item");const historyId=(loadBtn||historyItem)?.getAttribute("data-history-id");if(historyId)loadHistoryQuery(historyId);});el.menuButton.addEventListener("click",()=>{const isOpen=!el.menuPanel.hidden;setMenuOpen(!isOpen);});document.addEventListener("click",(e)=>{if(!el.menu.contains(e.target))setMenuOpen(false);});el.menuLoadData.addEventListener("click",()=>{setMenuOpen(false);showDatasets();});el.menuUpload.addEventListener("click",()=>{setMenuOpen(false);el.fileInput.click();});el.menuDownload.addEventListener("click",()=>{setMenuOpen(false);handleDownload();});el.menuExportExcel.addEventListener("click",()=>{setMenuOpen(false);handleExportExcel();});el.menuSchemaAi.addEventListener("click",()=>{setMenuOpen(false);handleCopySchemaForAi();});el.menuOpenNewTab.addEventListener("click",()=>{setMenuOpen(false);handleOpenInNewTab();});if(el.openNewTab){el.openNewTab.addEventListener("click",()=>handleOpenInNewTab());}
el.menuTheme.addEventListener("click",()=>{setMenuOpen(false);const current=document.documentElement.getAttribute("data-theme")||"light";applyTheme(current==="dark"?"light":"dark");});el.menuClear.addEventListener("click",async()=>{setMenuOpen(false);if(await showConfirmDialog("Start a new, empty database? Any unsaved changes will be lost.",{danger:true})){await wipeCurrentDatabaseStore();await switchDatabase(()=>createDatabase(),DEFAULT_DB_LABEL,{showLoadingOverlay:false});}});el.menuAbout.addEventListener("click",()=>{setMenuOpen(false);window.open("https://www.websql.org/2000/01/about.html","_blank","noopener");});el.fileInput.addEventListener("change",async()=>{const file=el.fileInput.files[0];el.fileInput.value="";if(file)await handleUpload(file);});el.refreshTables.addEventListener("click",()=>{pendingTablesRefresh=refreshTables();});el.viewResults.addEventListener("click",()=>setResultsView("results"));el.viewHistory.addEventListener("click",()=>setResultsView("history"));el.viewErd.addEventListener("click",()=>setResultsView("erd"));el.erdModeSelect.addEventListener("change",()=>{erdMode=el.erdModeSelect.value;el.erdClauseSelect.hidden=erdMode!=="sql";if(resultsViewMode==="erd")renderErdView();});el.erdClauseSelect.hidden=erdMode!=="sql";el.erdClauseSelect.addEventListener("change",()=>{erdClause=el.erdClauseSelect.value;});el.erdZoomIn.addEventListener("click",()=>changeErdZoom(ERD_ZOOM_STEP));el.erdZoomOut.addEventListener("click",()=>changeErdZoom(-ERD_ZOOM_STEP));el.erdRefresh.addEventListener("click",()=>{if(resultsViewMode==="erd")renderErd({rebuild:true}).catch(console.error);});el.erdLogPositions.addEventListener("click",()=>logErdTablePositions());el.toggleSidebar.addEventListener("click",()=>{const collapsed=el.sidebar.classList.toggle("collapsed");el.toggleSidebar.textContent=collapsed?"»":"«";el.toggleSidebar.title=collapsed?"Expand sidebar":"Collapse sidebar";});}
async function main(){scheduleSplashRetryReveal();initEventListeners();initResizer();await initMonaco();if(useDeferredDb){hideSplash();applySqlUrlParameter();if(resultLabel){setStatus("Loading preview…");loadResultFromBlog(resultLabel);}
return;}
await switchDatabase(()=>createDatabase(),DEFAULT_DB_LABEL,{showLoadingOverlay:false});hideSplash();applySqlUrlParameter();if(isPrimaryInstance){applyDataUrlParameter();}}
window.pgliteReady=main();
");return`
${description}
Available Datasets
${itemsHtml || "
No datasets found.
"}Run a query to see results here.
`;let lastResultsMeta="";let lastResultsClassName="results-body";let resultPageByIdx=new Map();const el={run:document.getElementById("btn-run"),openNewTab:document.getElementById("btn-open-new-tab"),menu:document.getElementById("menu"),menuButton:document.getElementById("btn-menu"),menuPanel:document.getElementById("menu-panel"),menuUpload:document.getElementById("menu-upload"),menuDownload:document.getElementById("menu-download"),menuExportExcel:document.getElementById("menu-export-excel"),menuSchemaAi:document.getElementById("menu-schema-ai"),menuOpenNewTab:document.getElementById("menu-open-new-tab"),menuTheme:document.getElementById("menu-theme"),themeIcon:document.getElementById("theme-icon"),themeLabel:document.getElementById("theme-label"),menuClear:document.getElementById("menu-clear"),menuAbout:document.getElementById("menu-about"),menuLoadData:document.getElementById("menu-load-data"),fileInput:document.getElementById("file-input"),refreshTables:document.getElementById("btn-refresh-tables"),toggleSidebar:document.getElementById("btn-toggle-sidebar"),sidebar:document.getElementById("sidebar"),tableList:document.getElementById("table-list"),savedQueriesList:document.getElementById("saved-queries-list"),resultsBody:document.getElementById("results-body"),resultsMeta:document.getElementById("results-meta"),viewResults:document.getElementById("btn-view-results"),viewHistory:document.getElementById("btn-view-history"),viewErd:document.getElementById("btn-view-erd"),erdToolbar:document.getElementById("erd-toolbar"),erdModeSelect:document.getElementById("erd-mode-select"),erdClauseSelect:document.getElementById("erd-clause-select"),erdZoomIn:document.getElementById("btn-erd-zoom-in"),erdZoomOut:document.getElementById("btn-erd-zoom-out"),erdZoomLabel:document.getElementById("erd-zoom-label"),erdRefresh:document.getElementById("btn-erd-refresh"),erdLogPositions:document.getElementById("btn-erd-log-positions"),statusText:document.getElementById("status-text"),loadingIndicator:document.getElementById("loading-indicator"),statusBar:document.getElementById("statusbar"),dbName:document.getElementById("db-name"),toast:document.getElementById("toast"),resizer:document.getElementById("resizer"),editorPane:document.getElementById("editor-pane"),dbLoadingOverlay:document.getElementById("db-loading-overlay"),dbLoadingTitle:document.getElementById("db-loading-title"),dbLoadingSubtitle:document.getElementById("db-loading-subtitle"),brandName:document.getElementById("brand-name")||document.querySelector(".brand-name"),splash:document.getElementById("splash"),splashRetry:document.getElementById("splash-retry"),appShell:document.getElementById("app-shell"),};applyTheme(getPreferredTheme());setStatusBarVisible(false);el.erdLogPositions.hidden=!isAdminMode;if(el.openNewTab)el.openNewTab.hidden=!isMinimalStyle;function setStatus(text){el.statusText.textContent=text;}
function stripFileExtension(name){return name.replace(/\.(tar\.gz|tgz|[a-z0-9]+)$/i,"");}
function setDbLabel(label){currentFileLabel=label;const displayLabel=stripFileExtension(label);if(el.dbName)el.dbName.textContent=displayLabel;if(el.brandName)el.brandName.textContent=displayLabel;}
function setMenuOpen(isOpen){el.menuPanel.hidden=!isOpen;el.menuButton.setAttribute("aria-expanded",String(isOpen));}
function monacoThemeFor(theme){return theme==="dark"?"vs-dark":"vs";}
function getPreferredTheme(){const saved=localStorage.getItem(THEME_KEY);if(saved==="light"||saved==="dark")return saved;return"dark";}
function applyTheme(theme){document.documentElement.setAttribute("data-theme",theme);localStorage.setItem(THEME_KEY,theme);if(monacoRef&&editor)monacoRef.editor.setTheme(monacoThemeFor(theme));const next=theme==="dark"?"light":"dark";el.themeIcon.textContent=next==="dark"?"🌙":"☀️";el.themeLabel.textContent=next==="dark"?"Dark Mode":"Light Mode";}
function showToast(message,kind){el.toast.textContent=message;el.toast.className="toast"+(kind?" "+kind:"");el.toast.hidden=false;clearTimeout(showToast._t);showToast._t=setTimeout(()=>{el.toast.hidden=true;},3200);}
function createAppDialogShell(title){const overlay=document.createElement("div");overlay.className="loading-overlay app-dialog-overlay";const dialog=document.createElement("div");dialog.className="loading-dialog app-dialog";dialog.innerHTML=`${escapeHtml(title)}
`;overlay.appendChild(dialog);document.body.appendChild(overlay);return{overlay,dialog};}
function showConfirmDialog(message,opts={}){const{confirmLabel="OK",cancelLabel="Cancel",danger=false}=opts;return new Promise((resolve)=>{const{overlay,dialog}=createAppDialogShell(message);dialog.insertAdjacentHTML("beforeend",`${escapeHtml(message)}
`;lastResultsMeta="";lastResultsClassName="results-body";setResultsView("results");}
function renderError(err){currentResultSets=[];currentResultSetsSql="";resultPageByIdx=new Map();lastResultsHtml=`${escapeHtml(err.message || String(err))}
`;lastResultsMeta="";lastResultsClassName="results-body has-error";setResultsView("results");}
function buildResultBlockHtml(res,i){const hasRows=res.fields&&res.fields.length>0;const labelText=currentResultSets.length>1?`Statement ${i + 1}`:"";const csvBtn=hasRows?``:"";const jsonBtn=hasRows&&isAdminMode?``:"";const saveQueryBtn=``;const actions=`${csvBtn}${jsonBtn}${saveQueryBtn}
`;const header=labelText||actions?`${labelText}${actions}
`:"";if(!hasRows){const affected=typeof res.affectedRows==="number"?res.affectedRows:0;return`${header}
`;}
const totalRows=res.rows.length;const totalPages=Math.max(1,Math.ceil(totalRows/RESULTS_PAGE_SIZE));const page=Math.min(resultPageByIdx.get(i)||0,totalPages-1);const start=page*RESULTS_PAGE_SIZE;const pageRows=totalRows>RESULTS_PAGE_SIZE?res.rows.slice(start,start+RESULTS_PAGE_SIZE):res.rows;const pager=totalRows>RESULTS_PAGE_SIZE?renderPager(i,page,totalPages,totalRows,start,start+pageRows.length):"";return`OK — ${affected} row(s) affected.
${header}${renderTable(res, pageRows, start)}${pager}
`;}
function renderPager(idx,page,totalPages,totalRows,rangeStart,rangeEnd){return`
Rows ${rangeStart + 1}–${rangeEnd} of ${totalRows}
`;}
function renderResults(results,elapsedMs){currentResultSets=results||[];resultPageByIdx=new Map();if(!results||results.length===0){clearResults("No results.");return;}
lastResultsHtml=currentResultSets.map((res,i)=>buildResultBlockHtml(res,i)).join("");const totalRows=results.reduce((sum,r)=>sum+(r.rows?r.rows.length:0),0);lastResultsMeta=`${totalRows} row(s) · ${elapsedMs} ms`;lastResultsClassName="results-body";setResultsView("results");}
function goToResultPage(idx,dir){const res=currentResultSets[idx];if(!res||!res.rows)return;const totalPages=Math.max(1,Math.ceil(res.rows.length/RESULTS_PAGE_SIZE));const current=Math.min(resultPageByIdx.get(idx)||0,totalPages-1);const next=dir==="next"?current+1:current-1;if(next<0||next>=totalPages)return;resultPageByIdx.set(idx,next);lastResultsHtml=currentResultSets.map((res,i)=>buildResultBlockHtml(res,i)).join("");if(resultsViewMode==="results"){el.resultsBody.innerHTML=lastResultsHtml;markOverflowingCells();}}
function isNumericValue(value){return typeof value==="number"||(typeof value==="string"&&/^[-+]?(?:\d+\.?\d*|\.\d+)(?:[eE][-+]?\d+)?$/.test(value.trim()));}
function formatDateValue(d){const iso=d.toISOString();if(iso.endsWith("T00:00:00.000Z"))return iso.slice(0,10);return iso.replace("T"," ").replace("Z","");}
function renderTable(res,rows,rowOffset){const cols=res.fields.map((f)=>f.name);const displayRows=rows||res.rows;const offset=rowOffset||0;const head=`
Page ${page + 1} of ${totalPages}
No tables yet
`;}else{el.tableList.innerHTML=rows.map((r)=>{const isView=r.table_type==="VIEW";return`
▦${escapeHtml(r.table_name)}
No saved queries
`;return;}
el.savedQueriesList.innerHTML=savedQueriesCache.map((q,i)=>`
🔖${escapeHtml(q.name)}
`).join("");el.savedQueriesList.querySelectorAll(".saved-query-name").forEach((node)=>{node.addEventListener("click",()=>{const query=savedQueriesCache[Number(node.getAttribute("data-idx"))];if(!query)return;editor.setValue(query.sql);runQuery();});});el.savedQueriesList.querySelectorAll(".saved-query-delete-btn").forEach((btn)=>{btn.addEventListener("click",async(e)=>{e.stopPropagation();const query=savedQueriesCache[Number(btn.getAttribute("data-idx"))];if(!query)return;if(!(await showConfirmDialog(`Delete saved query "${query.name}"?`,{confirmLabel:"Delete",danger:true})))return;try{await pg.query(`delete from ${SAVED_QUERIES_TABLE} where name = $1;`,[query.name]);showToast(`Deleted query "${query.name}"`,"success");await refreshSavedQueries();}catch(err){console.error(err);showToast(err.message||"Failed to delete query","error");}});});}
async function handleSaveQuery(){if(!pg)return;const sql=editor.getValue().trim();if(!sql)return;await pendingTablesRefresh;const name=await showPromptDialog("Save query as:");if(!name)return;try{await pg.exec(`create schema if not exists ${SAVED_QUERIES_SCHEMA};
create table if not exists ${SAVED_QUERIES_TABLE} (name text primary key, sql text not null);`);const{rows:existingRows}=await pg.query(`select 1 from ${SAVED_QUERIES_TABLE} where name = $1;`,[name]);if(existingRows.length&&!(await showConfirmDialog(`A saved query named "${name}" already exists. Replace it?`,{confirmLabel:"Replace"}))){return;}
await pg.query(`insert into ${SAVED_QUERIES_TABLE} (name, sql) values ($1, $2)
on conflict (name) do update set sql = excluded.sql;`,[name,sql]);markUnsavedChanges();showToast(`Saved query "${name}"`,"success");await refreshSavedQueries();}catch(err){console.error(err);showToast(err.message||"Failed to save query","error");}}
async function toggleTableColumns(btn){const name=btn.getAttribute("data-table");const container=btn.closest(".table-group").querySelector(".table-columns");if(!container.hidden){container.hidden=true;btn.textContent="▸";return;}
container.hidden=false;btn.textContent="▾";if(container.dataset.loaded)return;container.innerHTML=`Loading…
`;try{const columns=await fetchColumns(name);renderColumns(container,columns);container.dataset.loaded="1";}catch(err){container.innerHTML=`${escapeHtml(err.message)}
`;}}
async function fetchColumns(tableName){if(columnsCache.has(tableName))return columnsCache.get(tableName);const{rows}=await pg.query(`select column_name, data_type from information_schema.columns
where table_schema = 'public' and table_name = $1
order by ordinal_position;`,[tableName]);columnsCache.set(tableName,rows);return rows;}
function renderColumns(container,columns){if(!columns.length){container.innerHTML=`No columns
`;return;}
container.innerHTML=columns.map((c)=>`${escapeHtml(c.column_name)}${escapeHtml(c.data_type)}
`).join("");}
const ERD_NODE_WIDTH=230;const ERD_HEADER_HEIGHT=42;const ERD_ROW_HEIGHT=22;const ERD_PAD_X=48;const ERD_PAD_Y=34;const ERD_GAP_X=70;const ERD_GAP_Y=48;const ERD_ZOOM_MIN=0.5;const ERD_ZOOM_MAX=2;const ERD_ZOOM_STEP=0.1;const ERD_MIN_POS=8;const ERD_CROWSFOOT_SPREAD=7;const ERD_MARKER_CIRCLE_R=3.5;function setResultsView(mode){resultsViewMode=mode;const showingErd=mode==="erd";const showingHistory=mode==="history";el.viewResults.classList.toggle("is-active",mode==="results");el.viewHistory.classList.toggle("is-active",showingHistory);el.viewErd.classList.toggle("is-active",showingErd);el.viewResults.setAttribute("aria-selected",String(mode==="results"));el.viewHistory.setAttribute("aria-selected",String(showingHistory));el.viewErd.setAttribute("aria-selected",String(showingErd));el.erdToolbar.hidden=!showingErd;if(showingErd){if(!hasShownSchemaForCurrentDb){hasShownSchemaForCurrentDb=true;firstBuildOfSchema();}
renderErd().catch((err)=>{console.error(err);el.resultsBody.className="results-body has-error";el.resultsBody.innerHTML=`${escapeHtml(err.message || String(err))}
`;el.resultsMeta.textContent="";});return;}
if(showingHistory){renderQueryHistory();return;}
el.resultsBody.className=lastResultsClassName;el.resultsBody.innerHTML=lastResultsHtml;el.resultsMeta.textContent=lastResultsMeta;markOverflowingCells();}
function loadQueryHistory(){try{const raw=sessionStorage.getItem(QUERY_HISTORY_KEY);if(!raw)return[];const parsed=JSON.parse(raw);return Array.isArray(parsed)?parsed:[];}catch{return[];}}
function saveQueryHistory(entries){try{sessionStorage.setItem(QUERY_HISTORY_KEY,JSON.stringify(entries));}catch(err){console.warn("Could not save query history:",err);}}
function addQueryHistoryEntry(entry){const history=loadQueryHistory();history.unshift({id:`${Date.now()}-${Math.random().toString(36).slice(2, 8)}`,sql:entry.sql,at:Date.now(),ok:entry.ok,elapsedMs:entry.elapsedMs,error:entry.error,});saveQueryHistory(history.slice(0,QUERY_HISTORY_MAX));if(resultsViewMode==="history")renderQueryHistory();}
function formatHistoryTime(at){try{return new Date(at).toLocaleString(undefined,{month:"short",day:"numeric",hour:"numeric",minute:"2-digit",second:"2-digit",});}catch{return"";}}
function historySqlPreview(sql){return sql.replace(/\s+/g," ").trim();}
function renderQueryHistory(){const history=loadQueryHistory();el.resultsMeta.textContent=history.length?`${history.length} quer${history.length === 1 ? "y" : "ies"} this session`:"";if(history.length===0){el.resultsBody.className="results-body";el.resultsBody.innerHTML=`No queries yet this session. Run a query to start a history log.
`;return;}
const items=history.map((entry)=>{const statusClass=entry.ok?"is-ok":"is-error";const statusLabel=entry.ok?"OK":"Error";const metaParts=[formatHistoryTime(entry.at)];if(entry.ok&&typeof entry.elapsedMs==="number")metaParts.push(`${entry.elapsedMs} ms`);if(!entry.ok&&entry.error)metaParts.push(entry.error);return`
${statusLabel}
${escapeHtml(metaParts.join(" · "))}
${escapeHtml(historySqlPreview(entry.sql))}
- ${items}
No tables yet. Create or upload tables to see an ERD.
`;el.resultsMeta.textContent="0 tables";return;}
const{nodes,relationships,tableCount}=erdState;let maxX=0;let maxY=0;for(const node of nodes.values()){maxX=Math.max(maxX,node.x+node.width);maxY=Math.max(maxY,node.y+node.height);}
const width=maxX+ERD_PAD_X;const height=maxY+ERD_PAD_Y;const relationshipsSvg=relationships.map((rel,index)=>renderErdRelationship(rel,nodes,index)).join("");const nodesSvg=[...nodes.values()].map((node)=>renderErdNode(node)).join("");el.resultsBody.innerHTML=`
Loading ERD…
`;el.resultsMeta.textContent="";await buildErdState();}
renderErdView();}
function sanitizeIdentifier(value){const base=String(value||"column").trim().normalize("NFKD").replace(/[\u0300-\u036f]/g,"").replace(/[^a-zA-Z0-9_]+/g,"_").replace(/^_+|_+$/g,"").replace(/^(\d)/,"_$1");return base||"column";}
function parseCsv(text){const rows=[];let row=[];let field="";let inQuotes=false;for(let i=0;i