export const MAX_FILE=2*1024*1024, MAX_SQL=16384, MAX_ROWS=500, MAX_OUTPUT=1024*1024;
export function sqlLiteral(value){return "'"+String(value).replaceAll("'","''")+"'";}
export function selectQuery(sql){
 if(new TextEncoder().encode(sql).length>MAX_SQL)throw new Error('SQL длиннее 16 КиБ. Сократите запрос.');
 const s=sql.trim();let depth=0,code='',end=-1;
 // Lexical containment only: the real DuckDB parser validates the SELECT grammar.
 // Balanced parentheses and no statement separators prevent escaping the wrapper.
 for(let i=0;i<s.length;){
  if(s.startsWith('--',i)){const next=s.indexOf('\n',i+2);i=next<0?s.length:next+1;code+=' ';continue;}
  if(s.startsWith('/*',i)){let level=1;i+=2;while(i<s.length&&level){if(s.startsWith('/*',i)){level++;i+=2;}else if(s.startsWith('*/',i)){level--;i+=2;}else i++;}if(level)throw new Error('Не закрыт SQL-комментарий.');code+=' ';continue;}
  const quote=s[i],dollar=quote==='$'?s.slice(i).match(/^\$(?:[A-Za-z_][A-Za-z_0-9]*)?\$/)?.[0]:null;
  if(quote==="'"||quote==='"'||dollar){
   if(end>=0)throw new Error('Разрешён только один SELECT или WITH.');
   if(dollar){const close=s.indexOf(dollar,i+dollar.length);if(close<0)throw new Error('Не закрыта строка SQL.');i=close+dollar.length;}
   else{const escaped=quote==="'"&&i>0&&/[eE]/.test(s[i-1])&&(i<2||!/[\w]/.test(s[i-2]));i++;let closed=false;while(i<s.length){if(escaped&&s[i]==='\\'){i+=2;continue;}if(s[i]===quote){if(s[i+1]===quote){i+=2;continue;}i++;closed=true;break;}i++;}if(!closed)throw new Error('Не закрыта строка или имя SQL.');}
   code+=' value ';continue;
  }
  if(s[i]===';'){if(depth!==0||end>=0)throw new Error('Разрешён только один SELECT или WITH.');end=i;i++;continue;}
  if(end>=0&&!/\s/.test(s[i]))throw new Error('Разрешён только один SELECT или WITH.');
  if(s[i]==='(')depth++;if(s[i]===')'&&--depth<0)throw new Error('Нарушен баланс скобок SQL.');
  code+=s[i++];
 }
 if(depth!==0)throw new Error('Нарушен баланс скобок SQL.');
 if(!/^(?:\s|\()*(SELECT|WITH)\b/i.test(code))throw new Error('Введите SELECT или WITH.');
 return `SELECT * FROM (\n${end>=0?s.slice(0,end):s}\n) AS workshop_result LIMIT ${MAX_ROWS+1}`;
}
export function plain(value){
 if(value===null||value===undefined)return null;
 if(typeof value==='bigint')return value.toString();
 if(typeof value==='number'&&!Number.isFinite(value))return String(value);
 if(value instanceof Date)return value.toISOString();
 if(ArrayBuffer.isView(value))return Array.from(value,plain);
 if(Array.isArray(value))return value.map(plain);
 if(typeof value==='object')return Object.fromEntries(Object.entries(value.toJSON?.()??value).map(([k,v])=>[k,plain(v)]));
 return value;
}
export function arrowPlain(value,type){
 if(value===null||value===undefined)return null;
 // Apache Arrow Type.Decimal = 7. Decimal BigNum contains an unscaled integer;
 // its scale belongs to the field schema, not to the typed array value.
 if(type?.typeId===7){
  const raw=String(value),scale=type.scale;
  if(!/^-?\d+$/.test(raw)||!Number.isInteger(scale)||scale<0)throw new Error('Не удалось точно прочитать DECIMAL. Явно преобразуйте столбец в VARCHAR.');
  const sign=raw.startsWith('-')?'-':'',digits=(sign?raw.slice(1):raw).padStart(scale+1,'0');
  return sign+(scale?digits.slice(0,-scale)+'.'+digits.slice(-scale):digits);
 }
 // Arrow Struct and List retain child schemas (including nested DECIMAL).
 if(type?.typeId===13)return Object.fromEntries(type.children.map(field=>[field.name,arrowPlain(value[field.name],field.type)]));
 if(type?.typeId===12||type?.typeId===16)return Array.from(value,v=>arrowPlain(v,type.children[0].type));
 return plain(value);
}
export function arrowRows(table){
 const fields=table.schema.fields;
 return table.toArray().map(row=>Object.fromEntries(fields.map(field=>[field.name,arrowPlain(row[field.name],field.type)])));
}
export function bounded(rows){
 if(rows.length>MAX_ROWS)throw new Error('Больше 500 строк. Добавьте WHERE или LIMIT 500: результат не скачивается частично.');
 if(new TextEncoder().encode(JSON.stringify(rows)).length>MAX_OUTPUT)throw new Error('Результат больше 1 МиБ. Выберите меньше строк или столбцов.');
 return rows;
}
export function csv(rows,columns){return '\uFEFF'+[columns,...rows.map(r=>columns.map(k=>r[k]))].map(row=>row.map(v=>{
 let s=v===null?'':typeof v==='object'?JSON.stringify(v):String(v);
 if(typeof v==='string'&&/^[\s]*[=+@-]/.test(s))s="'"+s;
 return '"'+s.replaceAll('"','""')+'"';
 }).join(',')).join('\r\n')+'\r\n';}
export function checkedFile(name,size){
 if(!/\.(csv|json)$/i.test(name))throw new Error('Выберите CSV или JSON, закодированный в UTF-8.');
 if(size>MAX_FILE||size===0)throw new Error('Размер файла должен быть от 1 байта до 2 МиБ.');
 return name.toLowerCase().endsWith('.csv')?'csv':'json';
}
export function parameters(start,end,min){
 const valid=s=>/^\d{4}-\d{2}-\d{2}$/.test(s)&&!Number.isNaN(Date.parse(s+'T00:00:00Z'))&&new Date(s+'T00:00:00Z').toISOString().slice(0,10)===s;
 if(!valid(start)||!valid(end)||start>=end)throw new Error('Задайте корректный период: дата начала меньше даты конца.');
 if(!/^\d{1,15}$/.test(min))throw new Error('Минимум — целое число копеек от 0 до 999999999999999.');
 return [start,end,min];
}
export const setupSQL=[
 `CREATE TABLE orders_raw AS SELECT * FROM read_csv('orders.csv', header=true, all_varchar=true)`,
 `CREATE TABLE clients AS SELECT * FROM read_csv('clients.csv', header=true, columns={'client_id':'INTEGER','name':'VARCHAR','segment':'VARCHAR'})`,
 `CREATE TABLE orders_clean AS WITH typed AS (
 SELECT rowid AS source_row, order_id, TRY_CAST(order_date AS DATE) AS order_date,
 TRY_CAST(client_id AS INTEGER) AS client_id, city, manager,
 TRY_CAST(amount_minor AS BIGINT) AS amount_minor, status,
 row_number() OVER(PARTITION BY order_id ORDER BY rowid) AS duplicate_number
 FROM orders_raw)
 SELECT order_id,order_date,client_id,city,manager,amount_minor,status FROM typed
 WHERE order_id IS NOT NULL AND trim(order_id)<>'' AND order_date IS NOT NULL
 AND amount_minor IS NOT NULL AND amount_minor>=0 AND duplicate_number=1`,
 `CREATE TABLE events AS SELECT * FROM read_json('events.json', format='array')`,
 `CREATE TABLE parquet_orders AS SELECT * FROM read_parquet('orders.parquet')`
];
