const SESSION_TTL_SECONDS = 60 * 60 * 8;
const LEGACY_1234_HASH = '57d8e6db7a890895ec253d1f6558df972f5a4ca65933e6e8c71b0305f00cf034';
const BASE_SECTIONS = ['customers','budget','business_profitability','other_revenue_dashboard','restaurant_results_dashboard','contracts','cashflow','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:'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:'contracts', title:'Contracts Section', tag:'COMING NEXT', desc:'Contract creation and automation.', custom:false },
  { key:'cashflow', title:'Cash Flow Dashboard', tag:'COMING NEXT', desc:'Cash flow and bank movement dashboard.', 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);
      const path = url.pathname.replace(/^\/api/, '') || '/';
      if (path === '/health' && request.method === 'GET') return jsonResponse({ ok:true, service:'saken-platform-api', version:'2026-07-06 Phase 6.7 SAKEN Platform Clean Labels', time:new Date().toISOString() });
      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 === '/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 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 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','budget','business_profitability','other_revenue_dashboard','restaurant_results_dashboard','contracts','cashflow','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';}
