const SESSION_TTL_SECONDS = 60 * 60 * 8;
const LEGACY_1234_HASH = '57d8e6db7a890895ec253d1f6558df972f5a4ca65933e6e8c71b0305f00cf034';
const BASE_SECTIONS = ['customers','customer_report_by_day','budget','business_profitability','other_revenue_dashboard','restaurant_results_dashboard','document_approval','contracts','users','settings','section_builder'];
const BASE_SECTION_META = [
  { key:'customers', title:'Saken Customers Report', tag:'LIVE', desc:'Outstanding, Aging, Due Amounts and Customers Dashboard.', custom:false },
  { key:'customer_report_by_day', title:'Customers Report Offline', tag:'CUSTOMERS DAY', desc:'Daily customers report snapshot uploaded as a separate section.', custom:false },
  { key:'budget', title:'Budget vs Actual 2026', tag:'BUDGET', desc:'Budget performance, actuals, variance and analyst report.', custom:false },
  { key:'business_profitability', title:'Business Profitability Dashboard', tag:'P&L', desc:'Business performance, profitability, P&L, cost, budget, revenue and quarterly analysis.', custom:false },
  { key:'other_revenue_dashboard', title:'Other Revenue Dashboard', tag:'REVENUE', desc:'Miscellaneous revenue performance analysis 2023-2026.', custom:false },
  { key:'restaurant_results_dashboard', title:'Restaurant Results Dashboard', tag:'RESTAURANT', desc:'Restaurant revenue, cost, profit, margin and detailed results dashboard.', custom:false },
  { key:'document_approval', title:'Document Approval & E-Signature', tag:'APPROVALS', desc:'PDF approval workflows, signatures, stamps and direct approval links.', custom:false },
  { key:'contracts', title:'Contracts Section', tag:'COMING NEXT', desc:'Contract creation and automation.', custom:false },
  { key:'users', title:'Users & Permissions', tag:'ADMIN', desc:'Manage users and permissions.', custom:false },
  { key:'settings', title:'Settings', tag:'ADMIN', desc:'Platform settings.', custom:false },
  { key:'section_builder', title:'Upload HTML Sections', tag:'ADMIN', desc:'Upload HTML reports and convert them into platform sections.', custom:false }
];

export default {
  async fetch(request, env) {
    try {
      const url = new URL(request.url);
      if (request.method === 'OPTIONS') return jsonResponse({ ok: true }, 200);
      if (!url.pathname.startsWith('/api')) return jsonResponse({ ok:false, error:'Not Found' }, 404);
      if (!env.DB) return jsonResponse({ ok:false, error:'D1 binding DB is missing. Add D1 binding with variable name DB.' }, 500);
      await ensureCustomSectionsTable(env);
      await ensureApprovalProfilesTable(env);
      await ensureApprovalStorageTables(env);
      const path = url.pathname.replace(/^\/api/, '') || '/';
      if (path === '/health' && request.method === 'GET') return jsonResponse({ ok:true, service:'saken-platform-api', version:'2026-07-22 Phase 7.29 Central Approval Storage', r2_bound:!!env.APPROVAL_FILES, status:'healthy' });
      if (path === '/login' && request.method === 'POST') return handleLogin(request, env);
      if (path === '/logout' && request.method === 'POST') return handleLogout(request, env);
      if (path === '/me' && request.method === 'GET') return handleMe(request, env);
      if (path === '/change-password' && request.method === 'POST') return handleChangePassword(request, env);
      if (path === '/approval/users' && request.method === 'GET') return handleApprovalUsers(request, env);
      if (path === '/approval/users' && request.method === 'POST') return handleUpsertApprovalUser(request, env);
      if (path === '/approval/bootstrap' && request.method === 'GET') return handleApprovalBootstrap(request, env);
      if (path === '/approval/assets' && request.method === 'GET') return handleApprovalAssetList(request, env);
      if (path === '/approval/assets' && request.method === 'POST') return handleApprovalAssetUpload(request, env);
      const approvalAssetFileMatch = path.match(/^\/approval\/assets\/(signature|stamp)\/file$/);
      if (approvalAssetFileMatch && request.method === 'GET') return handleApprovalAssetFile(request, env, approvalAssetFileMatch[1]);
      if (path === '/approval/documents' && request.method === 'GET') return handleApprovalDocumentList(request, env);
      if (path === '/approval/documents' && request.method === 'POST') return handleApprovalDocumentCreate(request, env);
      const approvalDocFileMatch = path.match(/^\/approval\/documents\/([^\/]+)\/file$/);
      if (approvalDocFileMatch && request.method === 'GET') return handleApprovalDocumentFile(request, env, decodeURIComponent(approvalDocFileMatch[1]), url.searchParams.get('version') || 'current');
      const approvalDocApproveMatch = path.match(/^\/approval\/documents\/([^\/]+)\/approve$/);
      if (approvalDocApproveMatch && request.method === 'POST') return handleApprovalDocumentApprove(request, env, decodeURIComponent(approvalDocApproveMatch[1]));
      const approvalDocActionMatch = path.match(/^\/approval\/documents\/([^\/]+)\/action$/);
      if (approvalDocActionMatch && request.method === 'POST') return handleApprovalDocumentAction(request, env, decodeURIComponent(approvalDocActionMatch[1]));
      const approvalDocLinkMatch = path.match(/^\/approval\/documents\/([^\/]+)\/link$/);
      if (approvalDocLinkMatch && request.method === 'POST') return handleApprovalDocumentLink(request, env, decodeURIComponent(approvalDocLinkMatch[1]));
      const approvalDocMatch = path.match(/^\/approval\/documents\/([^\/]+)$/);
      if (approvalDocMatch && request.method === 'GET') return handleApprovalDocumentGet(request, env, decodeURIComponent(approvalDocMatch[1]), url);
      if (path === '/sections' && request.method === 'GET') return handleSections(request, env);
      if (path === '/users' && request.method === 'GET') return handleListUsers(request, env);
      if (path === '/users' && request.method === 'POST') return handleCreateOrUpdateUser(request, env);
      if (path === '/users' && request.method === 'DELETE') return handleDisableUser(request, env, Number(url.searchParams.get('id') || 0));
      const userMatch = path.match(/^\/users\/(\d+)$/);
      if (userMatch && request.method === 'PUT') return handleUpdateUserById(request, env, Number(userMatch[1]));
      if (userMatch && request.method === 'DELETE') return handleDisableUser(request, env, Number(userMatch[1]));
      const permissionMatch = path.match(/^\/users\/(\d+)\/permissions$/);
      if (permissionMatch && request.method === 'PUT') return handleUpdatePermissions(request, env, Number(permissionMatch[1]));
      if (path === '/custom-sections' && request.method === 'GET') return handleListCustomSections(request, env);
      if (path === '/custom-sections' && request.method === 'POST') return handleCreateOrUpdateCustomSection(request, env);
      if (path === '/custom-sections' && request.method === 'DELETE') return handleDeleteCustomSection(request, env, Number(url.searchParams.get('id') || 0));
      const htmlMatch = path.match(/^\/custom-sections\/([^\/]+)\/html$/);
      if (htmlMatch && request.method === 'GET') return handleCustomSectionHtml(request, env, decodeURIComponent(htmlMatch[1]));
      return jsonResponse({ ok:false, error:'API endpoint not found', path, method:request.method }, 404);
    } catch (err) {
      return jsonResponse({ ok:false, error:err && err.message ? err.message : String(err), stack:err && err.stack ? err.stack : '' }, 500);
    }
  }
};

async function ensureCustomSectionsTable(env){
  await env.DB.prepare(`CREATE TABLE IF NOT EXISTS custom_sections (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    section_key TEXT NOT NULL UNIQUE,
    title TEXT NOT NULL,
    tag TEXT DEFAULT 'HTML',
    description TEXT DEFAULT '',
    html_content TEXT NOT NULL,
    active INTEGER NOT NULL DEFAULT 1,
    sort_order INTEGER NOT NULL DEFAULT 100,
    created_by INTEGER,
    created_at TEXT DEFAULT (datetime('now')),
    updated_at TEXT DEFAULT (datetime('now'))
  )`).run();
}
async function ensureApprovalProfilesTable(env){
  await env.DB.prepare(`CREATE TABLE IF NOT EXISTS approval_profiles (
    user_id INTEGER PRIMARY KEY,
    approval_active INTEGER NOT NULL DEFAULT 1,
    can_request INTEGER NOT NULL DEFAULT 1,
    can_approve INTEGER NOT NULL DEFAULT 1,
    can_stamp INTEGER NOT NULL DEFAULT 0,
    can_manage_users INTEGER NOT NULL DEFAULT 0,
    can_view_all_documents INTEGER NOT NULL DEFAULT 0,
    role_en TEXT DEFAULT '',
    role_ar TEXT DEFAULT '',
    updated_at TEXT DEFAULT (datetime('now'))
  )`).run();
}

async function ensureApprovalStorageTables(env){
  await env.DB.batch([
    env.DB.prepare(`CREATE TABLE IF NOT EXISTS approval_assets (
      user_id INTEGER NOT NULL,
      asset_kind TEXT NOT NULL CHECK(asset_kind IN ('signature','stamp')),
      object_key TEXT NOT NULL,
      mime_type TEXT NOT NULL,
      file_name TEXT DEFAULT '',
      size_bytes INTEGER NOT NULL DEFAULT 0,
      updated_at TEXT DEFAULT (datetime('now')),
      PRIMARY KEY(user_id,asset_kind)
    )`),
    env.DB.prepare(`CREATE TABLE IF NOT EXISTS approval_documents (
      id TEXT PRIMARY KEY,
      title TEXT NOT NULL,
      document_type TEXT DEFAULT '',
      company TEXT DEFAULT '',
      transaction_type TEXT DEFAULT '',
      reference_no TEXT DEFAULT '',
      notes TEXT DEFAULT '',
      workflow_key TEXT NOT NULL,
      workflow_json TEXT NOT NULL,
      placements_json TEXT NOT NULL,
      creator_id INTEGER NOT NULL,
      current_assignee_id INTEGER,
      current_step_index INTEGER NOT NULL DEFAULT 0,
      status TEXT NOT NULL,
      file_name TEXT NOT NULL,
      mime_type TEXT NOT NULL DEFAULT 'application/pdf',
      original_key TEXT NOT NULL,
      current_key TEXT NOT NULL,
      version INTEGER NOT NULL DEFAULT 0,
      current_token TEXT DEFAULT '',
      created_at TEXT DEFAULT (datetime('now')),
      updated_at TEXT DEFAULT (datetime('now')),
      completed_at TEXT
    )`),
    env.DB.prepare(`CREATE TABLE IF NOT EXISTS approval_history (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      document_id TEXT NOT NULL,
      action TEXT NOT NULL,
      actor_id INTEGER NOT NULL,
      note TEXT DEFAULT '',
      step_index INTEGER,
      created_at TEXT DEFAULT (datetime('now'))
    )`),
    env.DB.prepare(`CREATE INDEX IF NOT EXISTS idx_approval_documents_creator ON approval_documents(creator_id,updated_at)`),
    env.DB.prepare(`CREATE INDEX IF NOT EXISTS idx_approval_documents_assignee ON approval_documents(current_assignee_id,status,updated_at)`),
    env.DB.prepare(`CREATE INDEX IF NOT EXISTS idx_approval_history_document ON approval_history(document_id,created_at)`),
    env.DB.prepare(`CREATE INDEX IF NOT EXISTS idx_approval_history_actor ON approval_history(actor_id,document_id)`)
  ]);
}

function requireApprovalBucket(env){
  if(!env.APPROVAL_FILES) return jsonResponse({ok:false,error:'R2 binding APPROVAL_FILES is missing. Create an R2 bucket and bind it to the Worker with variable name APPROVAL_FILES.'},500);
  return null;
}
async function getApprovalProfile(env,userId){
  return env.DB.prepare(`SELECT approval_active,can_request,can_approve,can_stamp,can_manage_users,can_view_all_documents,role_en,role_ar FROM approval_profiles WHERE user_id=? LIMIT 1`).bind(userId).first();
}
async function requireApprovalCapability(request,env,capability){
  const auth=await requirePermission(request,env,'document_approval','can_view'); if(!auth.ok)return auth;
  const p=await getApprovalProfile(env,auth.user.id);
  const isAdmin=String(auth.user.role||'').toLowerCase()==='admin';
  if(!p && !isAdmin)return {ok:false,response:jsonResponse({ok:false,error:'Approval profile is not active for this user.'},403)};
  if(p && Number(p.approval_active)!==1 && !isAdmin)return {ok:false,response:jsonResponse({ok:false,error:'Approval access is disabled.'},403)};
  const map={request:'can_request',approve:'can_approve',stamp:'can_stamp',manage:'can_manage_users',viewAll:'can_view_all_documents'};
  if(capability && !isAdmin && Number(p?.[map[capability]]||0)!==1)return {ok:false,response:jsonResponse({ok:false,error:'Approval capability denied.',capability},403)};
  auth.approvalProfile=p||{approval_active:1,can_request:1,can_approve:1,can_stamp:1,can_manage_users:1,can_view_all_documents:1};
  auth.isApprovalAdmin=isAdmin||Number(p?.can_manage_users||0)===1||((auth.permissions||{}).document_approval||{}).can_manage===true;
  auth.canViewAll=isAdmin||Number(p?.can_view_all_documents||0)===1;
  return auth;
}
function safeJsonParse(value,fallback){try{return JSON.parse(value)}catch(_){return fallback}}
function approvalDocumentId(){
  const y=new Date().getUTCFullYear();
  const b=new Uint8Array(5);crypto.getRandomValues(b);
  return `APR-${y}-${Array.from(b).map(x=>x.toString(16).padStart(2,'0')).join('').toUpperCase()}`;
}
function approvalStageToken(){const b=new Uint8Array(24);crypto.getRandomValues(b);return Array.from(b).map(x=>x.toString(16).padStart(2,'0')).join('')}
function normalizeWorkflowPayload(workflow){
  const w=(workflow&&typeof workflow==='object')?workflow:{};
  const steps=Array.isArray(w.steps)?w.steps:[];
  w.steps=steps.map((step,index)=>({
    ...step,
    assigneePlatformId:Number(step.assigneePlatformId||step.assignee_platform_id||0),
    status:clean(step.status||`pending_step_${index+1}`),
    assets:Array.isArray(step.assets)?step.assets:[],
    placements:Array.isArray(step.placements)?step.placements:[]
  })).filter(step=>step.assigneePlatformId>0);
  return w;
}
async function loadApprovalHistory(env,documentId){
  const r=await env.DB.prepare(`SELECT h.action,h.actor_id,h.note,h.step_index,h.created_at,u.username,u.display_name FROM approval_history h LEFT JOIN users u ON u.id=h.actor_id WHERE h.document_id=? ORDER BY h.id`).bind(documentId).all();
  return (r.results||[]).map(x=>({action:x.action,actorId:x.actor_id,actorUsername:x.username||'',actorName:x.display_name||x.username||'',note:x.note||'',stepIndex:x.step_index,at:x.created_at}));
}
async function serializeApprovalDocument(env,row,includeHistory=true){
  const workflow=safeJsonParse(row.workflow_json,{steps:[]});
  const placements=safeJsonParse(row.placements_json,{});
  const creator=await env.DB.prepare(`SELECT username,display_name FROM users WHERE id=? LIMIT 1`).bind(row.creator_id).first();
  const assignee=row.current_assignee_id?await env.DB.prepare(`SELECT username,display_name FROM users WHERE id=? LIMIT 1`).bind(row.current_assignee_id).first():null;
  return {
    id:row.id,title:row.title,type:row.document_type||'',company:row.company||'',transaction:row.transaction_type||'',reference:row.reference_no||'',notes:row.notes||'',workflow:row.workflow_key,workflowData:workflow,placements,
    createdByPlatformId:row.creator_id,creatorUsername:creator?.username||'',creatorName:creator?.display_name||creator?.username||'',
    currentAssigneePlatformId:row.current_assignee_id||null,currentAssigneeUsername:assignee?.username||'',currentAssigneeName:assignee?.display_name||assignee?.username||'',
    currentStepIndex:Number(row.current_step_index||0),status:row.status,fileName:row.file_name,mimeType:row.mime_type,version:Number(row.version||0),createdAt:row.created_at,updatedAt:row.updated_at,completedAt:row.completed_at||null,
    history:includeHistory?await loadApprovalHistory(env,row.id):[]
  };
}
async function canAccessApprovalDocument(env,auth,row,original=false){
  if(auth.canViewAll)return true;
  if(Number(row.creator_id)===Number(auth.user.id))return true;
  if(!original && Number(row.current_assignee_id||0)===Number(auth.user.id))return true;
  if(!original){
    const h=await env.DB.prepare(`SELECT 1 AS ok FROM approval_history WHERE document_id=? AND actor_id=? LIMIT 1`).bind(row.id,auth.user.id).first();
    if(h)return true;
  }
  return false;
}
async function loadApprovalDocumentRow(env,id){return env.DB.prepare(`SELECT * FROM approval_documents WHERE id=? LIMIT 1`).bind(id).first()}
async function handleApprovalBootstrap(request,env){
  const auth=await requireApprovalCapability(request,env,null);if(!auth.ok)return auth.response;
  const usersResponse=await handleApprovalUsers(request,env);const usersData=await usersResponse.json();
  const docsResponse=await handleApprovalDocumentList(request,env);const docsData=await docsResponse.json();
  const assetsResponse=await handleApprovalAssetList(request,env);const assetsData=await assetsResponse.json();
  return jsonResponse({ok:true,user:auth.user,profile:auth.approvalProfile,users:usersData.users||[],documents:docsData.documents||[],assets:assetsData.assets||[],r2_bound:!!env.APPROVAL_FILES});
}
async function handleApprovalAssetList(request,env){
  const auth=await requireApprovalCapability(request,env,null);if(!auth.ok)return auth.response;
  const r=await env.DB.prepare(`SELECT asset_kind,mime_type,file_name,size_bytes,updated_at FROM approval_assets WHERE user_id=? ORDER BY asset_kind`).bind(auth.user.id).all();
  return jsonResponse({ok:true,assets:(r.results||[]).map(x=>({kind:x.asset_kind,mimeType:x.mime_type,fileName:x.file_name,size:Number(x.size_bytes||0),updatedAt:x.updated_at,available:true}))});
}
async function handleApprovalAssetUpload(request,env){
  const auth=await requireApprovalCapability(request,env,'approve');if(!auth.ok)return auth.response;
  const missing=requireApprovalBucket(env);if(missing)return missing;
  const form=await request.formData();const kind=clean(form.get('kind')).toLowerCase();const file=form.get('file');
  if(!['signature','stamp'].includes(kind))return jsonResponse({ok:false,error:'Asset kind must be signature or stamp.'},400);
  if(kind==='stamp' && Number(auth.approvalProfile?.can_stamp||0)!==1 && !auth.isApprovalAdmin)return jsonResponse({ok:false,error:'Stamp permission is not enabled for this user.'},403);
  if(!(file instanceof File))return jsonResponse({ok:false,error:'Image file is required.'},400);
  if(!['image/png','image/jpeg'].includes(file.type))return jsonResponse({ok:false,error:'Only PNG or JPG images are allowed.'},400);
  if(file.size>3*1024*1024)return jsonResponse({ok:false,error:'Image must be 3 MB or smaller.'},400);
  const ext=file.type==='image/png'?'png':'jpg',key=`approval-assets/${auth.user.id}/${kind}.${ext}`;
  await env.APPROVAL_FILES.put(key,await file.arrayBuffer(),{httpMetadata:{contentType:file.type},customMetadata:{owner:String(auth.user.id),kind}});
  await env.DB.prepare(`INSERT INTO approval_assets (user_id,asset_kind,object_key,mime_type,file_name,size_bytes,updated_at) VALUES (?,?,?,?,?,?,datetime('now')) ON CONFLICT(user_id,asset_kind) DO UPDATE SET object_key=excluded.object_key,mime_type=excluded.mime_type,file_name=excluded.file_name,size_bytes=excluded.size_bytes,updated_at=datetime('now')`).bind(auth.user.id,kind,key,file.type,file.name,file.size).run();
  return jsonResponse({ok:true,kind,fileName:file.name,size:file.size});
}
async function handleApprovalAssetFile(request,env,kind){
  const auth=await requireApprovalCapability(request,env,'approve');if(!auth.ok)return auth.response;
  const missing=requireApprovalBucket(env);if(missing)return missing;
  const row=await env.DB.prepare(`SELECT object_key,mime_type FROM approval_assets WHERE user_id=? AND asset_kind=? LIMIT 1`).bind(auth.user.id,kind).first();
  if(!row)return jsonResponse({ok:false,error:'Asset not uploaded.'},404);
  const object=await env.APPROVAL_FILES.get(row.object_key);if(!object)return jsonResponse({ok:false,error:'Stored asset file is missing.'},404);
  return new Response(object.body,{headers:{'Content-Type':row.mime_type||'application/octet-stream','Cache-Control':'private, no-store','Content-Disposition':'inline'}});
}
async function handleApprovalDocumentList(request,env){
  const auth=await requireApprovalCapability(request,env,null);if(!auth.ok)return auth.response;
  let result;
  if(auth.canViewAll){result=await env.DB.prepare(`SELECT * FROM approval_documents ORDER BY datetime(updated_at) DESC LIMIT 500`).all()}
  else result=await env.DB.prepare(`SELECT DISTINCT d.* FROM approval_documents d LEFT JOIN approval_history h ON h.document_id=d.id WHERE d.creator_id=? OR d.current_assignee_id=? OR h.actor_id=? ORDER BY datetime(d.updated_at) DESC LIMIT 500`).bind(auth.user.id,auth.user.id,auth.user.id).all();
  const documents=[];for(const row of result.results||[])documents.push(await serializeApprovalDocument(env,row,true));
  return jsonResponse({ok:true,documents});
}
async function handleApprovalDocumentCreate(request,env){
  const auth=await requireApprovalCapability(request,env,'request');if(!auth.ok)return auth.response;
  const missing=requireApprovalBucket(env);if(missing)return missing;
  const form=await request.formData(),file=form.get('file'),payload=safeJsonParse(String(form.get('payload')||'{}'),{});
  if(!(file instanceof File)||file.type!=='application/pdf')return jsonResponse({ok:false,error:'A PDF file is required.'},400);
  if(file.size>25*1024*1024)return jsonResponse({ok:false,error:'PDF must be 25 MB or smaller.'},400);
  const title=clean(payload.title);if(!title)return jsonResponse({ok:false,error:'Document title is required.'},400);
  const workflow=normalizeWorkflowPayload(payload.workflowData||payload.workflow_data||{});if(!workflow.steps.length)return jsonResponse({ok:false,error:'At least one valid approval step is required.'},400);
  for(const step of workflow.steps){const u=await env.DB.prepare(`SELECT u.id,u.active,COALESCE(ap.approval_active,0) AS approval_active,COALESCE(ap.can_approve,0) AS can_approve FROM users u LEFT JOIN approval_profiles ap ON ap.user_id=u.id WHERE u.id=? LIMIT 1`).bind(step.assigneePlatformId).first();if(!u||Number(u.active)!==1||Number(u.approval_active)!==1||Number(u.can_approve)!==1)return jsonResponse({ok:false,error:`Approver ${step.assigneePlatformId} is inactive or cannot approve.`},400)}
  const id=approvalDocumentId(),originalKey=`approval-documents/${id}/original.pdf`,currentKey=`approval-documents/${id}/current.pdf`,bytes=await file.arrayBuffer();
  await env.APPROVAL_FILES.put(originalKey,bytes,{httpMetadata:{contentType:'application/pdf'},customMetadata:{document:id,version:'original'}});
  await env.APPROVAL_FILES.put(currentKey,bytes,{httpMetadata:{contentType:'application/pdf'},customMetadata:{document:id,version:'0'}});
  const first=workflow.steps[0],token=approvalStageToken();
  await env.DB.prepare(`INSERT INTO approval_documents (id,title,document_type,company,transaction_type,reference_no,notes,workflow_key,workflow_json,placements_json,creator_id,current_assignee_id,current_step_index,status,file_name,mime_type,original_key,current_key,version,current_token,created_at,updated_at) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,0,?,datetime('now'),datetime('now'))`).bind(id,title,clean(payload.type),clean(payload.company),clean(payload.transaction),clean(payload.reference),clean(payload.notes),clean(payload.workflow||'general_custom'),JSON.stringify(workflow),JSON.stringify(payload.placements||{}),auth.user.id,first.assigneePlatformId,0,first.status||'pending_step_1',file.name,'application/pdf',originalKey,currentKey,token).run();
  await env.DB.prepare(`INSERT INTO approval_history (document_id,action,actor_id,note,step_index,created_at) VALUES (?,?,?,?,?,datetime('now'))`).bind(id,'submitted',auth.user.id,clean(payload.submittedNote||`Submitted — ${workflow.description||''}`),-1).run();
  const row=await loadApprovalDocumentRow(env,id);return jsonResponse({ok:true,document:await serializeApprovalDocument(env,row,true)},201);
}
async function handleApprovalDocumentGet(request,env,id,url){
  const auth=await requireApprovalCapability(request,env,null);if(!auth.ok)return auth.response;
  const row=await loadApprovalDocumentRow(env,id);if(!row)return jsonResponse({ok:false,error:'Document not found.'},404);
  if(!(await canAccessApprovalDocument(env,auth,row,false)))return jsonResponse({ok:false,error:'Access denied for this document.'},403);
  const stage=clean(url.searchParams.get('stage')),token=clean(url.searchParams.get('token'));
  if(stage||token){if(stage!==row.status||!token||token!==row.current_token||Number(row.current_assignee_id)!==Number(auth.user.id))return jsonResponse({ok:false,error:'Direct approval link is invalid, expired, or assigned to another user.'},403)}
  return jsonResponse({ok:true,document:await serializeApprovalDocument(env,row,true)});
}
async function handleApprovalDocumentFile(request,env,id,version){
  const auth=await requireApprovalCapability(request,env,null);if(!auth.ok)return auth.response;
  const missing=requireApprovalBucket(env);if(missing)return missing;
  const row=await loadApprovalDocumentRow(env,id);if(!row)return jsonResponse({ok:false,error:'Document not found.'},404);
  const original=String(version).toLowerCase()==='original';if(!(await canAccessApprovalDocument(env,auth,row,original)))return jsonResponse({ok:false,error:'Access denied for this file.'},403);
  const key=original?row.original_key:row.current_key,object=await env.APPROVAL_FILES.get(key);if(!object)return jsonResponse({ok:false,error:'PDF file is missing from R2.'},404);
  const suffix=original?'ORIGINAL':(row.status==='approved'?'APPROVED':'CURRENT');
  return new Response(object.body,{headers:{'Content-Type':'application/pdf','Cache-Control':'private, no-store','Content-Disposition':`inline; filename="${row.id}_${suffix}.pdf"`}});
}
async function handleApprovalDocumentLink(request,env,id){
  const auth=await requireApprovalCapability(request,env,null);if(!auth.ok)return auth.response;
  const row=await loadApprovalDocumentRow(env,id);if(!row)return jsonResponse({ok:false,error:'Document not found.'},404);
  if(!(await canAccessApprovalDocument(env,auth,row,false)))return jsonResponse({ok:false,error:'Access denied.'},403);
  if(!String(row.status||'').startsWith('pending_')||!row.current_assignee_id)return jsonResponse({ok:false,error:'Document has no active approval stage.'},400);
  let token=row.current_token;if(!token){token=approvalStageToken();await env.DB.prepare(`UPDATE approval_documents SET current_token=?,updated_at=datetime('now') WHERE id=?`).bind(token,id).run()}
  return jsonResponse({ok:true,id,stage:row.status,token,assigneePlatformId:row.current_assignee_id});
}
async function handleApprovalDocumentApprove(request,env,id){
  const auth=await requireApprovalCapability(request,env,'approve');if(!auth.ok)return auth.response;
  const missing=requireApprovalBucket(env);if(missing)return missing;
  const row=await loadApprovalDocumentRow(env,id);if(!row)return jsonResponse({ok:false,error:'Document not found.'},404);
  if(Number(row.current_assignee_id)!==Number(auth.user.id))return jsonResponse({ok:false,error:'This stage is assigned to another user.'},403);
  const form=await request.formData(),file=form.get('file'),stage=clean(form.get('stage')),token=clean(form.get('token'));
  if(stage!==row.status)return jsonResponse({ok:false,error:'The approval stage has changed. Refresh the document.'},409);
  if(token && token!==row.current_token)return jsonResponse({ok:false,error:'Approval link token is invalid or expired.'},403);
  if(!(file instanceof File)||file.type!=='application/pdf')return jsonResponse({ok:false,error:'Updated PDF file is required.'},400);
  if(file.size>25*1024*1024)return jsonResponse({ok:false,error:'PDF must be 25 MB or smaller.'},400);
  const workflow=safeJsonParse(row.workflow_json,{steps:[]}),steps=Array.isArray(workflow.steps)?workflow.steps:[],index=Number(row.current_step_index||0),step=steps[index];if(!step||Number(step.assigneePlatformId)!==Number(auth.user.id))return jsonResponse({ok:false,error:'Workflow step mismatch.'},409);
  const next=steps[index+1]||null,newVersion=Number(row.version||0)+1,versionKey=`approval-documents/${id}/versions/${newVersion}.pdf`,bytes=await file.arrayBuffer();
  await env.APPROVAL_FILES.put(versionKey,bytes,{httpMetadata:{contentType:'application/pdf'},customMetadata:{document:id,version:String(newVersion),approvedBy:String(auth.user.id)}});
  await env.APPROVAL_FILES.put(row.current_key,bytes,{httpMetadata:{contentType:'application/pdf'},customMetadata:{document:id,version:String(newVersion)}});
  const nextStatus=next?(next.status||`pending_step_${index+2}`):'approved',nextAssignee=next?Number(next.assigneePlatformId):null,nextToken=next?approvalStageToken():'';
  await env.DB.prepare(`UPDATE approval_documents SET current_step_index=?,current_assignee_id=?,status=?,version=?,current_token=?,updated_at=datetime('now'),completed_at=CASE WHEN ?='approved' THEN datetime('now') ELSE completed_at END WHERE id=? AND status=? AND current_assignee_id=?`).bind(index+1,nextAssignee,nextStatus,newVersion,nextToken,nextStatus,id,row.status,auth.user.id).run();
  await env.DB.prepare(`INSERT INTO approval_history (document_id,action,actor_id,note,step_index,created_at) VALUES (?,?,?,?,?,datetime('now'))`).bind(id,clean(step.action||'approved'),auth.user.id,clean(step.doneNote||step.label||'Approved'),index).run();
  const updated=await loadApprovalDocumentRow(env,id);return jsonResponse({ok:true,document:await serializeApprovalDocument(env,updated,true)});
}
async function handleApprovalDocumentAction(request,env,id){
  const auth=await requireApprovalCapability(request,env,'approve');if(!auth.ok)return auth.response;
  const row=await loadApprovalDocumentRow(env,id);if(!row)return jsonResponse({ok:false,error:'Document not found.'},404);
  if(Number(row.current_assignee_id)!==Number(auth.user.id))return jsonResponse({ok:false,error:'This stage is assigned to another user.'},403);
  const body=await readJson(request),action=clean(body.action).toLowerCase(),stage=clean(body.stage),token=clean(body.token),note=clean(body.note);
  if(!['returned','rejected'].includes(action))return jsonResponse({ok:false,error:'Action must be returned or rejected.'},400);
  if(stage&&stage!==row.status)return jsonResponse({ok:false,error:'The approval stage has changed.'},409);
  if(token&&token!==row.current_token)return jsonResponse({ok:false,error:'Approval link token is invalid or expired.'},403);
  await env.DB.prepare(`UPDATE approval_documents SET status=?,current_assignee_id=NULL,current_token='',updated_at=datetime('now'),completed_at=datetime('now') WHERE id=? AND current_assignee_id=?`).bind(action,id,auth.user.id).run();
  await env.DB.prepare(`INSERT INTO approval_history (document_id,action,actor_id,note,step_index,created_at) VALUES (?,?,?,?,?,datetime('now'))`).bind(id,action,auth.user.id,note||(action==='returned'?'Returned for correction':'Rejected'),Number(row.current_step_index||0)).run();
  const updated=await loadApprovalDocumentRow(env,id);return jsonResponse({ok:true,document:await serializeApprovalDocument(env,updated,true)});
}

async function getAllPermissionKeys(env){
  const rows = await env.DB.prepare(`SELECT section_key FROM custom_sections ORDER BY sort_order, title`).all();
  const set = new Set(BASE_SECTIONS);
  for(const r of rows.results || []) if(r.section_key) set.add(r.section_key);
  return Array.from(set);
}

async function handleChangePassword(request, env){
  const auth=await requireAuth(request,env); if(!auth.ok)return auth.response;
  const body=await readJson(request), current=String(body.current_password||body.currentPassword||''), next=String(body.new_password||body.newPassword||'');
  if(next.length<8)return jsonResponse({ok:false,error:'New password must be at least 8 characters.'},400);
  const row=await env.DB.prepare(`SELECT password_hash FROM users WHERE id=? LIMIT 1`).bind(auth.user.id).first();
  if(!row || !(await verifyPassword(current,row.password_hash)))return jsonResponse({ok:false,error:'Current password is incorrect.'},400);
  const passwordHash=await sha256(next);
  await env.DB.prepare(`UPDATE users SET password_hash=?, updated_at=datetime('now') WHERE id=?`).bind(passwordHash,auth.user.id).run();
  await env.DB.prepare(`DELETE FROM sessions WHERE user_id=? AND token<>?`).bind(auth.user.id,auth.token).run();
  return jsonResponse({ok:true,message:'Password updated successfully.'});
}
async function handleApprovalUsers(request, env){
  const auth=await requirePermission(request,env,'document_approval','can_view'); if(!auth.ok)return auth.response;
  const sectionPermission=(auth.permissions||{}).document_approval||{};
  const canManage=String(auth.user.role||'').toLowerCase()==='admin'||sectionPermission.can_manage===true;
  const visibilityWhere=canManage
    ? `u.active=1`
    : `u.active=1 AND (COALESCE(p.can_view,0)=1 OR COALESCE(p.can_manage,0)=1 OR ap.user_id IS NOT NULL)`;
  const result=await env.DB.prepare(`
    SELECT u.id,u.username,u.display_name,u.role,u.active,
      COALESCE(ap.approval_active,CASE WHEN COALESCE(p.can_view,0)=1 OR COALESCE(p.can_manage,0)=1 THEN 1 ELSE 0 END) AS approval_active,
      COALESCE(ap.can_request,0) AS can_request,
      COALESCE(ap.can_approve,0) AS can_approve,
      COALESCE(ap.can_stamp,0) AS can_stamp,
      COALESCE(ap.can_manage_users,CASE WHEN lower(u.role)='admin' OR COALESCE(p.can_manage,0)=1 THEN 1 ELSE 0 END) AS can_manage_users,
      COALESCE(ap.can_view_all_documents,CASE WHEN lower(u.role)='admin' THEN 1 ELSE 0 END) AS can_view_all_documents,
      COALESCE(NULLIF(ap.role_en,''),u.role,'') AS role_en,
      COALESCE(ap.role_ar,'') AS role_ar,
      COALESCE(p.can_manage,0) AS section_can_manage
    FROM users u
    LEFT JOIN permissions p ON p.user_id=u.id AND p.section_key='document_approval'
    LEFT JOIN approval_profiles ap ON ap.user_id=u.id
    WHERE ${visibilityWhere}
    ORDER BY u.display_name,u.username`).all();
  const users=(result.results||[]).map(r=>({
    id:r.id,username:r.username,email:r.username,display_name:r.display_name,name:r.display_name||r.username,role:r.role,
    role_en:r.role_en||r.role||'',role_ar:r.role_ar||'',active:Number(r.active)===1&&Number(r.approval_active)===1,
    platform_active:Number(r.active)===1,
    approval_linked:Number(r.approval_active)===1,
    permissions:{access:Number(r.approval_active)===1,request:Number(r.can_request)===1,approve:Number(r.can_approve)===1,stamp:Number(r.can_stamp)===1,manageUsers:Number(r.can_manage_users)===1||Number(r.section_can_manage)===1||String(r.role).toLowerCase()==='admin',viewAllDocuments:Number(r.can_view_all_documents)===1||String(r.role).toLowerCase()==='admin'}
  }));
  return jsonResponse({ok:true,users,current_user_id:auth.user.id,can_manage_users:canManage});
}
async function handleUpsertApprovalUser(request, env){
  const auth=await requirePermission(request,env,'document_approval','can_manage'); if(!auth.ok)return auth.response;
  const body=await readJson(request), userId=Number(body.user_id||body.userId||0), username=clean(body.username||body.email).toLowerCase();
  let target=null;
  if(userId)target=await env.DB.prepare(`SELECT id,username,display_name,role,active FROM users WHERE id=? LIMIT 1`).bind(userId).first();
  if(!target && username)target=await env.DB.prepare(`SELECT id,username,display_name,role,active FROM users WHERE lower(username)=? LIMIT 1`).bind(username).first();
  if(!target)return jsonResponse({ok:false,error:'User not found in Saken All. Create the platform user first.'},404);
  const access=body.access===false||Number(body.access)===0?0:1;
  const canRequest=boolToInt(body.can_request ?? body.request ?? true), canApprove=boolToInt(body.can_approve ?? body.approve ?? true), canStamp=boolToInt(body.can_stamp ?? body.stamp), canManage=boolToInt(body.can_manage_users ?? body.manageUsers), canViewAll=boolToInt(body.can_view_all_documents ?? body.viewAllDocuments);
  const roleEn=clean(body.role_en||body.roleEn||target.role), roleAr=clean(body.role_ar||body.roleAr||'');
  await env.DB.prepare(`INSERT INTO approval_profiles (user_id,approval_active,can_request,can_approve,can_stamp,can_manage_users,can_view_all_documents,role_en,role_ar,updated_at) VALUES (?,?,?,?,?,?,?,?,?,datetime('now')) ON CONFLICT(user_id) DO UPDATE SET approval_active=excluded.approval_active,can_request=excluded.can_request,can_approve=excluded.can_approve,can_stamp=excluded.can_stamp,can_manage_users=excluded.can_manage_users,can_view_all_documents=excluded.can_view_all_documents,role_en=excluded.role_en,role_ar=excluded.role_ar,updated_at=datetime('now')`).bind(target.id,access,canRequest,canApprove,canStamp,canManage,canViewAll,roleEn,roleAr).run();
  await env.DB.prepare(`INSERT INTO permissions (user_id,section_key,can_view,can_refresh,can_manage) VALUES (?,?,?,?,?) ON CONFLICT(user_id,section_key) DO UPDATE SET can_view=excluded.can_view,can_manage=excluded.can_manage`).bind(target.id,'document_approval',access,0,canManage).run();
  return jsonResponse({ok:true,message:'Approval profile updated.',user_id:target.id});
}
async function handleSections(request, env){
  const rows = await env.DB.prepare(`SELECT id, section_key AS key, title, tag, description AS desc, active, sort_order FROM custom_sections WHERE active = 1 ORDER BY sort_order, title`).all();
  const custom = (rows.results || []).map(r => ({...r, custom:true}));
  return jsonResponse({ ok:true, sections:[...BASE_SECTION_META, ...custom] });
}
async function handleLogin(request, env) {
  const body = await readJson(request);
  const username = clean(body.username).toLowerCase();
  const password = String(body.password || '');
  if (!username || !password) return jsonResponse({ ok:false, error:'Username and password are required.' }, 400);
  const user = await env.DB.prepare(`SELECT id, username, display_name, role, active, password_hash FROM users WHERE lower(username)=? LIMIT 1`).bind(username).first();
  if (!user || Number(user.active)!==1) return jsonResponse({ ok:false, error:'Invalid username or password.' }, 401);
  const passwordOk = await verifyPassword(password, user.password_hash);
  if (!passwordOk) return jsonResponse({ ok:false, error:'Invalid username or password.' }, 401);
  const token = createToken();
  const expiresAt = Math.floor(Date.now()/1000) + SESSION_TTL_SECONDS;
  await env.DB.prepare(`INSERT INTO sessions (token,user_id,expires_at) VALUES (?,?,?)`).bind(token, user.id, expiresAt).run();
  const permissions = await getUserPermissions(env, user.id);
  const safeUser = { id:user.id, username:user.username, display_name:user.display_name, role:user.role, active:Number(user.active)===1, permissions };
  return jsonResponse({ ok:true, token, user:safeUser, permissions, expires_at:expiresAt }, 200, { 'Set-Cookie': makeCookie(token) });
}
async function handleLogout(request, env){ const token=getTokenFromRequest(request); if(token) await env.DB.prepare(`DELETE FROM sessions WHERE token=?`).bind(token).run(); return jsonResponse({ok:true,message:'Logged out.'},200,{'Set-Cookie':clearCookie()}); }
async function handleMe(request, env){ const auth=await requireAuth(request,env); if(!auth.ok)return auth.response; return jsonResponse({ok:true,user:{...auth.user,permissions:auth.permissions},permissions:auth.permissions}); }
async function handleListUsers(request, env){
  const auth=await requirePermission(request,env,'users','can_manage'); if(!auth.ok)return auth.response;
  const result=await env.DB.prepare(`SELECT id, username, display_name, role, active, created_at, updated_at FROM users ORDER BY id ASC`).all();
  const users=result.results||[]; for(const user of users){ user.active=Number(user.active)===1; user.permissions=await getUserPermissions(env,user.id); }
  return jsonResponse({ok:true,users});
}
async function handleCreateOrUpdateUser(request, env){
  const auth=await requirePermission(request,env,'users','can_manage'); if(!auth.ok)return auth.response;
  const body=await readJson(request);
  const incomingId=Number(body.id||0), username=clean(body.username).toLowerCase(), displayName=clean(body.display_name||body.displayName||username), role=clean(body.role||'Viewer'), password=String(body.password||'').trim(), active=body.active===false||Number(body.active)===0?0:1;
  if(!username)return jsonResponse({ok:false,error:'Username is required.'},400);
  let existing=null;
  if(incomingId) existing=await env.DB.prepare(`SELECT id,username,display_name,role,active FROM users WHERE id=? LIMIT 1`).bind(incomingId).first();
  if(!existing) existing=await env.DB.prepare(`SELECT id,username,display_name,role,active FROM users WHERE lower(username)=? LIMIT 1`).bind(username).first();
  const permissions=await getIncomingPermissions(env, body);
  if(existing && existing.id){
    await env.DB.prepare(`UPDATE users SET display_name=?, role=?, active=?, updated_at=datetime('now') WHERE id=?`).bind(displayName||existing.display_name, role||existing.role, active, existing.id).run();
    if(password){ const passwordHash=await sha256(password); await env.DB.prepare(`UPDATE users SET password_hash=?, updated_at=datetime('now') WHERE id=?`).bind(passwordHash, existing.id).run(); }
    await savePermissions(env, existing.id, permissions);
    return jsonResponse({ok:true,mode:'updated',user:await loadUserWithPermissions(env, existing.id)});
  }
  const passwordHash=await sha256(password||'1234');
  await env.DB.prepare(`INSERT INTO users (username,display_name,role,active,password_hash,created_at,updated_at) VALUES (?,?,?,?,?,datetime('now'),datetime('now'))`).bind(username,displayName,role,active,passwordHash).run();
  const created=await env.DB.prepare(`SELECT id FROM users WHERE lower(username)=? LIMIT 1`).bind(username).first();
  if(!created || !created.id)return jsonResponse({ok:false,error:'User was inserted but could not be loaded.'},500);
  await savePermissions(env, created.id, permissions);
  return jsonResponse({ok:true,mode:'created',user:await loadUserWithPermissions(env, created.id)});
}
async function handleUpdateUserById(request, env, userId){ const body=await readJson(request); body.id=userId; const newRequest=new Request(request.url,{method:'POST',headers:request.headers,body:JSON.stringify(body)}); return handleCreateOrUpdateUser(newRequest,env); }
async function handleDisableUser(request, env, userId){
  const auth=await requirePermission(request,env,'users','can_manage'); if(!auth.ok)return auth.response;
  if(!userId)return jsonResponse({ok:false,error:'User id is required.'},400);
  if(userId===auth.user.id)return jsonResponse({ok:false,error:'You cannot disable your own account.'},400);
  await env.DB.prepare(`UPDATE users SET active=0, updated_at=datetime('now') WHERE id=?`).bind(userId).run();
  await env.DB.prepare(`DELETE FROM sessions WHERE user_id=?`).bind(userId).run();
  return jsonResponse({ok:true,message:'User disabled.'});
}
async function handleUpdatePermissions(request, env, userId){ const auth=await requirePermission(request,env,'users','can_manage'); if(!auth.ok)return auth.response; const body=await readJson(request); const permissions=await getIncomingPermissions(env, body); await savePermissions(env,userId,permissions); return jsonResponse({ok:true,permissions:await getUserPermissions(env,userId)}); }
async function handleListCustomSections(request, env){
  const auth=await requirePermission(request,env,'section_builder','can_manage'); if(!auth.ok)return auth.response;
  const rows=await env.DB.prepare(`SELECT id, section_key, title, tag, description, html_content, active, sort_order, created_at, updated_at FROM custom_sections ORDER BY sort_order, title`).all();
  return jsonResponse({ok:true,sections:rows.results||[]});
}
async function handleCreateOrUpdateCustomSection(request, env){
  const auth=await requirePermission(request,env,'section_builder','can_manage'); if(!auth.ok)return auth.response;
  const body=await readJson(request);
  const id=Number(body.id||0), key=slug(body.section_key||body.key), title=clean(body.title), tag=clean(body.tag||'HTML'), description=clean(body.description||body.desc||''), html=String(body.html_content||body.html||''), active=body.active===false||Number(body.active)===0?0:1;
  if(!key||!title||!html.trim())return jsonResponse({ok:false,error:'Section key, title and HTML content are required.'},400);
  if(BASE_SECTIONS.includes(key))return jsonResponse({ok:false,error:'This section key is reserved.'},400);
  let existing=null; if(id) existing=await env.DB.prepare(`SELECT id FROM custom_sections WHERE id=? LIMIT 1`).bind(id).first();
  if(!existing) existing=await env.DB.prepare(`SELECT id FROM custom_sections WHERE section_key=? LIMIT 1`).bind(key).first();
  if(existing && existing.id){
    await env.DB.prepare(`UPDATE custom_sections SET section_key=?, title=?, tag=?, description=?, html_content=?, active=?, updated_at=datetime('now') WHERE id=?`).bind(key,title,tag,description,html,active,existing.id).run();
    await env.DB.prepare(`UPDATE permissions SET section_key=? WHERE section_key IN (SELECT section_key FROM custom_sections WHERE id=?)`).bind(key, existing.id).run().catch(()=>{});
    return jsonResponse({ok:true,mode:'updated',section:await loadCustomSection(env,key)});
  }
  await env.DB.prepare(`INSERT INTO custom_sections (section_key,title,tag,description,html_content,active,sort_order,created_by,created_at,updated_at) VALUES (?,?,?,?,?,?,100,?,datetime('now'),datetime('now'))`).bind(key,title,tag,description,html,active,auth.user.id).run();
  // Give admin/creator immediate permission.
  await env.DB.prepare(`INSERT INTO permissions (user_id,section_key,can_view,can_refresh,can_manage) VALUES (?,?,?,?,?) ON CONFLICT(user_id,section_key) DO UPDATE SET can_view=1, can_manage=1`).bind(auth.user.id,key,1,0,1).run();
  return jsonResponse({ok:true,mode:'created',section:await loadCustomSection(env,key)});
}
async function handleDeleteCustomSection(request, env, id){
  const auth=await requirePermission(request,env,'section_builder','can_manage'); if(!auth.ok)return auth.response;
  if(!id)return jsonResponse({ok:false,error:'Section id is required.'},400);
  const row=await env.DB.prepare(`SELECT section_key FROM custom_sections WHERE id=? LIMIT 1`).bind(id).first();
  if(row && row.section_key) await env.DB.prepare(`DELETE FROM permissions WHERE section_key=?`).bind(row.section_key).run();
  await env.DB.prepare(`DELETE FROM custom_sections WHERE id=?`).bind(id).run();
  return jsonResponse({ok:true,message:'Section deleted.'});
}
async function handleCustomSectionHtml(request, env, key){
  const auth=await requirePermission(request,env,key,'can_view'); if(!auth.ok)return auth.response;
  const row=await env.DB.prepare(`SELECT title, html_content, active FROM custom_sections WHERE section_key=? LIMIT 1`).bind(slug(key)).first();
  if(!row || Number(row.active)!==1)return new Response('<h2>Section not found or inactive.</h2>',{status:404,headers:{'Content-Type':'text/html; charset=utf-8','Cache-Control':'no-store'}});
  return new Response(row.html_content,{status:200,headers:{'Content-Type':'text/html; charset=utf-8','Cache-Control':'no-store','X-Frame-Options':'SAMEORIGIN'}});
}
async function loadCustomSection(env,key){ return env.DB.prepare(`SELECT id, section_key, title, tag, description, active, sort_order, created_at, updated_at FROM custom_sections WHERE section_key=? LIMIT 1`).bind(key).first(); }
async function requirePermission(request, env, sectionKey, permissionKey){
  const auth=await requireAuth(request,env); if(!auth.ok)return auth;
  if(auth.user.role==='Admin')return auth;
  const section=(auth.permissions||{})[sectionKey]||{};
  if(!section[permissionKey])return {ok:false,response:jsonResponse({ok:false,error:'Access denied.',required:{section:sectionKey,permission:permissionKey}},403)};
  return auth;
}
async function requireAuth(request, env){
  const token=getTokenFromRequest(request); if(!token)return {ok:false,response:jsonResponse({ok:false,error:'Not authenticated.'},401)};
  const now=Math.floor(Date.now()/1000);
  const session=await env.DB.prepare(`SELECT s.token,s.user_id,s.expires_at,u.id AS id,u.username AS username,u.display_name AS display_name,u.role AS role,u.active AS active FROM sessions s JOIN users u ON u.id=s.user_id WHERE s.token=? AND s.expires_at>? AND u.active=1 LIMIT 1`).bind(token,now).first();
  if(!session)return {ok:false,response:jsonResponse({ok:false,error:'Session expired or invalid.'},401,{'Set-Cookie':clearCookie()})};
  const user={id:session.id,username:session.username,display_name:session.display_name,role:session.role,active:Number(session.active)===1};
  const permissions=await getUserPermissions(env,user.id);
  return {ok:true,user,permissions,token};
}
async function loadUserWithPermissions(env,userId){ const user=await env.DB.prepare(`SELECT id, username, display_name, role, active, created_at, updated_at FROM users WHERE id=? LIMIT 1`).bind(userId).first(); if(!user)return null; user.active=Number(user.active)===1; user.permissions=await getUserPermissions(env,user.id); return user; }
async function getUserPermissions(env,userId){
  const keys=await getAllPermissionKeys(env);
  const rows=await env.DB.prepare(`SELECT section_key,can_view,can_refresh,can_manage FROM permissions WHERE user_id=?`).bind(userId).all();
  const permissions={}; for(const key of keys)permissions[key]={can_view:false,can_refresh:false,can_manage:false};
  for(const row of rows.results||[])permissions[row.section_key]={can_view:Number(row.can_view)===1,can_refresh:Number(row.can_refresh)===1,can_manage:Number(row.can_manage)===1};
  return permissions;
}
async function savePermissions(env,userId,permissions){
  const keys=await getAllPermissionKeys(env);
  const extra=Object.keys(permissions||{}).filter(k=>!keys.includes(k));
  for(const section of [...keys,...extra]){ const p=permissions[section]||{}; await env.DB.prepare(`INSERT INTO permissions (user_id,section_key,can_view,can_refresh,can_manage) VALUES (?,?,?,?,?) ON CONFLICT(user_id,section_key) DO UPDATE SET can_view=excluded.can_view, can_refresh=excluded.can_refresh, can_manage=excluded.can_manage`).bind(userId,section,boolToInt(p.can_view),boolToInt(p.can_refresh),boolToInt(p.can_manage)).run(); }
}
async function getIncomingPermissions(env, body){
  const raw=body.permissions||body.perms||{};
  const keys=await getAllPermissionKeys(env);
  const output={}; for(const section of keys) output[section]={can_view:false,can_refresh:false,can_manage:false};
  if(Array.isArray(raw)){
    for(const key of raw){
      if(key==='refresh_customers'){output.customers.can_view=true;output.customers.can_refresh=true;continue;}
      if(output[key]){output[key].can_view=true;if(key==='users'||key==='settings'||key==='section_builder')output[key].can_manage=true;}
    }
    return output;
  }
  for(const section of keys){ const p=raw[section]||{}; output[section]={can_view:toBool(p.can_view)||p===true,can_refresh:toBool(p.can_refresh),can_manage:toBool(p.can_manage)}; }
  const shortcuts=['customers','customer_report_by_day','budget','business_profitability','other_revenue_dashboard','restaurant_results_dashboard','document_approval','contracts','users','settings','section_builder']; for(const s of shortcuts){ if(raw[s]===true||raw[s]===1||raw[s]==='1'){output[s].can_view=true;if(s==='users'||s==='settings'||s==='section_builder')output[s].can_manage=true;} }
  if(raw.refresh_customers===true||raw.refresh_customers===1||raw.refresh_customers==='1'){output.customers.can_view=true;output.customers.can_refresh=true;}
  return output;
}
async function verifyPassword(password,storedHash){ const currentHash=await sha256(password); if(currentHash===storedHash)return true; if(password==='1234'&&storedHash===LEGACY_1234_HASH)return true; return false; }
async function sha256(text){ const data=new TextEncoder().encode(String(text)); const hashBuffer=await crypto.subtle.digest('SHA-256',data); return Array.from(new Uint8Array(hashBuffer)).map(b=>b.toString(16).padStart(2,'0')).join(''); }
function createToken(){ const a=crypto.randomUUID(); const array=new Uint8Array(24); crypto.getRandomValues(array); const b=Array.from(array).map(x=>x.toString(16).padStart(2,'0')).join(''); return a+'.'+b; }
function getTokenFromRequest(request){ const auth=request.headers.get('Authorization')||''; if(auth.toLowerCase().startsWith('bearer '))return auth.slice(7).trim(); const cookie=request.headers.get('Cookie')||''; for(const part of cookie.split(';').map(x=>x.trim())) if(part.startsWith('saken_token='))return decodeURIComponent(part.slice('saken_token='.length)); return ''; }
function makeCookie(token){ return ['saken_token='+encodeURIComponent(token),'Path=/','Max-Age='+SESSION_TTL_SECONDS,'HttpOnly','Secure','SameSite=Lax'].join('; '); }
function clearCookie(){ return ['saken_token=','Path=/','Max-Age=0','HttpOnly','Secure','SameSite=Lax'].join('; '); }
async function readJson(request){ try{return await request.json();}catch(err){return{};} }
function jsonResponse(data,status,extraHeaders){ return new Response(JSON.stringify(data,null,2),{status:status||200,headers:{'Content-Type':'application/json; charset=utf-8','Cache-Control':'no-store','Access-Control-Allow-Origin':'*','Access-Control-Allow-Methods':'GET,POST,PUT,DELETE,OPTIONS','Access-Control-Allow-Headers':'Content-Type,Authorization',...(extraHeaders||{})}}); }
function clean(value){return String(value||'').trim();}
function toBool(value){return value===true||value===1||value==='1'||value==='true';}
function boolToInt(value){return toBool(value)?1:0;}
function slug(s){return String(s||'section').toLowerCase().trim().replace(/[^a-z0-9]+/g,'_').replace(/^_+|_+$/g,'').slice(0,60)||'section';}
