OldOS - See Saas 

new>

• Config.gs — database-ID, rollen en testaccounts

• Code.gs — login, sessies, rolrechten, boekingen en databasefuncties

• Index.html — login en vier rolgebonden schermen

/** PadelSchoolOS V1.1. Copy Config.gs, Code.gs, Index.html into one Apps Script project.

 * Deploy as: execute as owner; access restricted to intended users. First run setupPadelOS as owner.

 * Never share editor access to the backing spreadsheet with ordinary users.

 */

const PS_HEADERS = {

  SYSTEM_CONFIG:['ConfigID','SystemName','Version','Build','Brand','AdministratorEmail','AdministratorPhone','GabelerURL','GODOGOODURL','DatabaseSpreadsheetID','CreatedAt','UpdatedAt'],

  USERS:['UserID','Email','Name','Role','Active','Phone','Language','LastLogin','CreatedAt','UpdatedAt'],

  PERSONS:['PersonID','UserID','FirstName','LastName','DisplayName','Email','Phone','DateOfBirth','Gender','Address','PostalCode','City','Country','EmergencyContact','EmergencyPhone','Notes','Active','CreatedAt','UpdatedAt'],

  LOCATIONS:['LocationID','Name','Description','Address','PostalCode','City','Province','Country','Latitude','Longitude','ContactName','ContactEmail','ContactPhone','Website','BookingURL','OpeningHours','Timezone','Currency','Active','CreatedAt','UpdatedAt'],

  LOCATION_USERS:['ID','LocationID','UserID','Role','Active','StartDate','EndDate','CreatedAt','UpdatedAt'],

  COURT_TYPES:['CourtTypeID','Name','Description','IndoorOutdoor','GlassType','SurfaceType','Lights','Accessible','Active','CreatedAt','UpdatedAt'],

  COURTS:['CourtID','LocationID','CourtTypeID','Name','CourtNumber','CourtType','IndoorOutdoor','Lights','SurfaceType','BookingURL','Active','CreatedAt','UpdatedAt'],

  COURT_AVAILABILITY:['AvailabilityID','LocationID','CourtID','Date','StartTime','EndTime','Status','Visibility','PriorityAccess','Source','SourceID','ExternalBookingID','ImportedAt','UpdatedAt'],

  COURT_PRICES:['PriceID','LocationID','CourtID','DayOfWeek','StartTime','EndTime','Price','Currency','PriceType','MinimumDuration','Active','CreatedAt','UpdatedAt'],

  BOOKING_RULES:['RuleID','LocationID','Name','Description','Role','Priority','DaysInAdvance','HoursInAdvance','MaxDuration','MinDuration','AllowLesson','AllowSeries','AllowHold','Active','CreatedAt','UpdatedAt'],

  BOOKING_PRIORITIES:['PriorityID','LocationID','Name','Role','PriorityValue','EarlyVisibilityHours','EarlyBookingHours','Description','Active','CreatedAt','UpdatedAt'],

  IMPORT_SOURCES:['ImportID','LocationID','Name','Type','URL','FileID','SpreadsheetID','SheetName','Format','Frequency','Active','LastImport','LastStatus','LastError','RecordsImported','CreatedAt','UpdatedAt'],

  IMPORT_RUNS:['ImportRunID','ImportID','LocationID','StartedAt','FinishedAt','Status','RecordsRead','RecordsImported','RecordsSkipped','ErrorMessage','CreatedAt'],

  BOOKINGS:['BookingID','LocationID','CourtID','UserID','PersonID','Date','StartTime','EndTime','Type','Priority','Visibility','Status','Source','ExternalBookingID','Notes','CreatedAt','UpdatedAt'],

  BOOKING_HOLDS:['HoldID','BookingID','LocationID','CourtID','UserID','Date','StartTime','EndTime','ExpiresAt','Priority','Status','CreatedAt','UpdatedAt'],

  LESSONS:['LessonID','SeriesID','TrainerUserID','LocationID','CourtID','Date','StartTime','EndTime','LessonType','Level','PlayerCount','Goal','Status','Notes','CreatedAt','UpdatedAt'],

  LESSON_SERIES:['SeriesID','TrainerUserID','LocationID','Name','Description','StartDate','EndDate','DayOfWeek','StartTime','EndTime','NumberOfLessons','Players','Level','Goal','Priority','Status','Notes','CreatedAt','UpdatedAt'],

  LESSON_COURTS:['ID','LessonID','SeriesID','CourtID','BookingID','Date','StartTime','EndTime','Status','CreatedAt','UpdatedAt'],

  TRAINER_AVAILABILITY:['AvailabilityID','TrainerUserID','LocationID','Date','StartTime','EndTime','Status','LessonType','MaxPlayers','Notes','CreatedAt','UpdatedAt'],

  NOTIFICATIONS:['NotificationID','UserID','Type','Title','Message','Entity','EntityID','Read','CreatedAt','ReadAt'],

  MESSAGES:['MessageID','SenderUserID','ReceiverUserID','LocationID','Subject','Message','RelatedEntity','RelatedEntityID','Read','CreatedAt','ReadAt'],

  AUDIT_LOG:['LogID','UserEmail','UserID','Action','Entity','EntityID','Details','CreatedAt']

};

function doGet() { return HtmlService.createHtmlOutputFromFile('Index').setTitle('PadelSchoolOS V1.1'); }

function db_() { return SpreadsheetApp.openById(PS.DATABASE_ID); }

function now_() { return new Date().toISOString(); }

function uid_(prefix) { return prefix + '-' + Utilities.getUuid(); }

function email_(value) { return String(value||'').trim().toLowerCase(); }

function flag_(value) { return value === true || String(value).toLowerCase() === 'true'; }

function check_(condition,message) { if (!condition) throw new Error(message); }

function lock_(fn) { const lock=LockService.getScriptLock(); lock.waitLock(20000); try{return fn();}finally{lock.releaseLock();} }

function sheet_(name) { const sh=db_().getSheetByName(name); check_(sh,'Ontbrekende tab: '+name+'. Voer setupPadelOS uit.'); return sh; }

function headers_(name) { const sh=sheet_(name); return sh.getRange(1,1,1,sh.getLastColumn()).getValues()[0].map(String); }

function rows_(name) {

  const sh=sheet_(name), last=sh.getLastRow(); if(last<2)return [];

  const h=headers_(name), data=sh.getRange(2,1,last-1,h.length).getValues();

  return data.map((a,i)=>{const o={_row:i+2};h.forEach((key,j)=>o[key]=a[j] instanceof Date?Utilities.formatDate(a[j],'UTC',"yyyy-MM-dd'T'HH:mm:ss'Z'"):a[j]);return o;});

}

function put_(name,obj) { const sh=sheet_(name), h=headers_(name); sh.appendRow(h.map(k=>obj[k]===undefined?'':obj[k])); return obj; }

function update_(name,row,obj) { const sh=sheet_(name),h=headers_(name);check_(row>=2,'Ongeldige rij'); sh.getRange(row,1,1,h.length).setValues([h.map(k=>obj[k]===undefined?'':obj[k])]);return obj; }

function find_(name,key,value) { return rows_(name).find(r=>String(r[key])===String(value)); }

function safe_(value,n) {return String(value||'').trim().slice(0,n||500);}

function audit_(actor,action,entity,id) {put_('AUDIT_LOG',{LogID:uid_('LOG'),UserID:actor&&actor.UserID||'',UserEmail:actor&&actor.Email||'',Action:action,Entity:entity,EntityID:id,CreatedAt:now_()});}

function publicUser_(u){return {userId:u.UserID,email:u.Email,name:u.Name,role:u.Role};}

/** Idempotent installer: append only missing tabs/rows; never renames existing tabs. Run as owner. */

function setupPadelOS(){

  const caller=email_(Session.getActiveUser().getEmail());

  check_(caller===PS.ADMIN_EMAIL,'Alleen de administrator mag initialiseren. Voer de functie handmatig uit in de editor met het beheerdersaccount.');

  return lock_(()=>{

    const ss=db_();Object.assign(PS_HEADERS,PS.EXTRA_HEADERS);

    Object.keys(PS_HEADERS).forEach(name=>{

      let sh=ss.getSheetByName(name);if(!sh)sh=ss.insertSheet(name);

      if(sh.getLastRow()===0)sh.getRange(1,1,1,PS_HEADERS[name].length).setValues([PS_HEADERS[name]]);

      else {const existing=headers_(name);check_(PS_HEADERS[name].every(h=>existing.indexOf(h)>=0),'Kolommen ontbreken in '+name+'. Controleer eerst de tab.');}

      sh.setFrozenRows(1);

    });

    PS.TEST_USERS.forEach(([email,name,role])=>{

      if(!rows_('USERS').some(u=>email_(u.Email)===email))put_('USERS',{UserID:uid_('USR'),Email:email,Name:name,Role:role,Active:true,CreatedAt:now_(),UpdatedAt:now_()});

    });

    const owner=rows_('USERS').find(u=>email_(u.Email)==='gabeler.eu@gmail.com');

    let location=rows_('LOCATIONS').find(l=>l.Name==='Gabeler Padel Testlocatie');

    if(!location)location=put_('LOCATIONS',{LocationID:uid_('LOC'),Name:'Gabeler Padel Testlocatie',Description:'Testlocatie zonder echt adres',Timezone:'Europe/Amsterdam',Currency:'EUR',Active:true,CreatedAt:now_(),UpdatedAt:now_()});

    if(!rows_('LOCATION_USERS').some(x=>x.LocationID===location.LocationID&&x.UserID===owner.UserID))put_('LOCATION_USERS',{ID:uid_('MEM'),LocationID:location.LocationID,UserID:owner.UserID,Role:PS.ROLES.LOCATION,Active:true,CreatedAt:now_(),UpdatedAt:now_()});

    for(let i=1;i<=4;i++)if(!rows_('COURTS').some(c=>c.LocationID===location.LocationID&&c.Name==='Baan '+i))put_('COURTS',{CourtID:uid_('CRT'),LocationID:location.LocationID,Name:'Baan '+i,CourtNumber:i,Active:true,CreatedAt:now_(),UpdatedAt:now_()});

    const conf=rows_('SYSTEM_CONFIG')[0];if(!conf)put_('SYSTEM_CONFIG',{ConfigID:uid_('CFG'),SystemName:'PadelSchoolOS',Version:PS.VERSION,Build:PS.BUILD,Brand:'PadelSchool',AdministratorEmail:PS.ADMIN_EMAIL,DatabaseSpreadsheetID:PS.DATABASE_ID,CreatedAt:now_(),UpdatedAt:now_()});

    formatSpreadsheet_(50);return {ok:true,spreadsheetId:ss.getId(),tabs:ss.getSheets().length};

  });

}

function formatSpreadsheet_(maxChars){const ss=db_(),max=Math.max(10,Math.min(50,Number(maxChars)||50)); ss.getSheets().forEach(sh=>{

  const cols=sh.getLastColumn();if(!cols)return;sh.setFrozenRows(1);const count=Math.min(sh.getLastRow(),500);

  const sample=count?sh.getRange(1,1,count,cols).getDisplayValues():[];

  const range=sh.getRange(1,1,Math.max(1,sh.getLastRow()),cols);range.setWrap(true).setVerticalAlignment('top');

  for(let j=0;j<cols;j++){let length=10;sample.forEach(row=>String(row[j]||'').split(/\r?\n/).forEach(line=>length=Math.max(length,Math.min(max,line.length))));sh.setColumnWidth(j+1,Math.min(420,Math.max(90,Math.ceil(length*8+20))));}

  if(sh.getLastRow()>0)sh.autoResizeRows(1,sh.getLastRow());

});return {ok:true,tabs:ss.getSheets().length,maxChars:max};}

function adminFormatSpreadsheet(token,maxChars){requireRole_(token,[PS.ROLES.ADMIN]);return formatSpreadsheet_(maxChars);}

// Login: OTP is required on every new session, including returning accounts.

function hash_(text){return Utilities.computeDigest(Utilities.DigestAlgorithm.SHA_256,String(text)).map(x=>('0'+(x&255).toString(16)).slice(-2)).join('');}

function passwordHash_(salt,password){let v=salt+'|'+password;for(let i=0;i<12000;i++)v=hash_(v);return v;}

function codeHash_(userId,code){return hash_(PropertiesService.getScriptProperties().getProperty('PS_AUTH_PEPPER')+'|'+userId+'|'+code);}

function otpCode_(){return String(100000+(parseInt(hash_(Utilities.getUuid()).slice(0,10),16)%900000));}

function requestLoginCode(address){

  const email=email_(address);check_(/^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(email),'Ongeldig e-mailadres.');

  return lock_(()=>{

    const u=rows_('USERS').find(x=>email_(x.Email)===email&&flag_(x.Active));

    // Generic answer prevents account enumeration.

    if(!u)return {ok:true,message:'Als het account actief is, wordt een code verstuurd.'};

    const recent=rows_('LOGIN_CODES').filter(x=>x.UserID===u.UserID&&Date.now()-Date.parse(x.CreatedAt)<3600000);

    check_(recent.length<5,'Te veel codes aangevraagd. Probeer over een uur opnieuw.');

    const code=otpCode_();let pepper=PropertiesService.getScriptProperties().getProperty('PS_AUTH_PEPPER');

    if(!pepper){pepper=uid_('SECRET');PropertiesService.getScriptProperties().setProperty('PS_AUTH_PEPPER',pepper);}

    const record=put_('LOGIN_CODES',{CodeID:uid_('OTP'),UserID:u.UserID,CodeHash:codeHash_(u.UserID,code),ExpiresAt:new Date(Date.now()+10*60000).toISOString(),Attempts:0,CreatedAt:now_()});

    try{MailApp.sendEmail(email,'PadelSchoolOS inlogcode','Je inlogcode is '+code+'. Geldig gedurende 10 minuten. Deel deze code met niemand.');}

    catch(e){record.UsedAt=now_();update_('LOGIN_CODES',rows_('LOGIN_CODES').find(x=>x.CodeID===record.CodeID)._row,record);throw new Error('De e-mail kon niet worden verzonden. Probeer later opnieuw.');}

    return {ok:true,message:'Als het account actief is, wordt een code verstuurd.'};

  });

}

function verifyLoginCode(address,code){return lock_(()=>{

  const u=rows_('USERS').find(x=>email_(x.Email)===email_(address)&&flag_(x.Active));check_(u,'Code ongeldig of verlopen.');

  const recent=rows_('LOGIN_CODES').filter(x=>x.UserID===u.UserID&&!x.UsedAt&&Date.parse(x.ExpiresAt)>Date.now()).pop();

  check_(recent&&Number(recent.Attempts)<5,'Code ongeldig of verlopen.');

  recent.Attempts=Number(recent.Attempts||0)+1;update_('LOGIN_CODES',recent._row,recent);

  check_(/^\d{6}$/.test(String(code))&&recent.CodeHash===codeHash_(u.UserID,String(code)),'Code ongeldig of verlopen.');

  recent.UsedAt=now_();update_('LOGIN_CODES',recent._row,recent);

  const auth=find_('AUTH_USERS','UserID',u.UserID);const ticket=uid_('TICKET');

  CacheService.getScriptCache().put('ticket:'+hash_(ticket),u.UserID,300);

  return {ticket:ticket,firstTime:!auth||!auth.PasswordHash};

});}

function finishLogin(address,ticket,password,newPassword){return lock_(()=>{

  const u=rows_('USERS').find(x=>email_(x.Email)===email_(address)&&flag_(x.Active));

  const cache=CacheService.getScriptCache(),key='ticket:'+hash_(ticket);check_(u&&ticket&&cache.get(key)===u.UserID,'Verificatie verlopen. Vraag opnieuw een code aan.');

  let auth=find_('AUTH_USERS','UserID',u.UserID);

  if(!auth||!auth.PasswordHash){

    check_(typeof newPassword==='string'&&newPassword.length>=12&&newPassword.length<=128,'Kies een wachtwoord van 12 tot 128 tekens.');

    check_(newPassword!=='1234','1234 is geen veilig wachtwoord.');

    const salt=uid_('SALT');const obj={UserID:u.UserID,Email:u.Email,PasswordSalt:salt,PasswordHash:passwordHash_(salt,newPassword),EmailVerified:true,CreatedAt:auth&&auth.CreatedAt||now_(),UpdatedAt:now_()};

    if(auth)update_('AUTH_USERS',auth._row,obj);else put_('AUTH_USERS',obj);

  }else check_(typeof password==='string'&&auth.PasswordHash===passwordHash_(auth.PasswordSalt,password),'Onjuist wachtwoord.');

  cache.remove(key);const token=uid_('SESSION')+uid_('RANDOM');put_('LOGIN_SESSIONS',{SessionID:uid_('SES'),UserID:u.UserID,TokenHash:hash_(token),ExpiresAt:new Date(Date.now()+7*86400000).toISOString(),CreatedAt:now_()});

  u.LastLogin=now_();u.UpdatedAt=now_();update_('USERS',u._row,u);audit_(u,'LOGIN','USERS',u.UserID);

  return {token:token,user:publicUser_(u)};

});}

function requireUser_(token){check_(typeof token==='string'&&token.length>30,'Aanmelding vereist.');

  const s=find_('LOGIN_SESSIONS','TokenHash',hash_(token));check_(s&&!s.RevokedAt&&Date.parse(s.ExpiresAt)>Date.now(),'Sessie verlopen.');

  const u=find_('USERS','UserID',s.UserID);check_(u&&flag_(u.Active),'Account niet actief.');return u;

}

function requireRole_(token,roles){const u=requireUser_(token);check_(roles.includes(u.Role),'Geen toegang.');return u;}

function logout(token){return lock_(()=>{const s=find_('LOGIN_SESSIONS','TokenHash',hash_(token));if(s){s.RevokedAt=now_();update_('LOGIN_SESSIONS',s._row,s);}return {ok:true};});}

function getProfile(token){return publicUser_(requireUser_(token));}

function ownLocations_(u){return rows_('LOCATION_USERS').filter(x=>x.UserID===u.UserID&&flag_(x.Active)).map(x=>x.LocationID);}

function canManage_(u,id){return u.Role===PS.ROLES.ADMIN||(u.Role===PS.ROLES.LOCATION&&ownLocations_(u).includes(id));}

function manageLocation_(u,id){check_(canManage_(u,id),'Geen beheerrecht voor deze locatie.');}

function getDashboard(token){const u=requireUser_(token);const locs=rows_('LOCATIONS').filter(x=>flag_(x.Active));

  const visible=u.Role===PS.ROLES.ADMIN?locs:locs.map(l=>({LocationID:l.LocationID,Name:l.Name,City:l.City,Active:l.Active}));

  const bookingRows=getBookings(token),series=getLessonSeries(token),lessons=getLessons(token),conversations=getConversations(token);

  return {version:PS.VERSION,user:publicUser_(u),locations:visible,ownLocationIds:ownLocations_(u),courts:getCourts(token),bookings:bookingRows.slice(-100),lessonSeries:series.slice(-100),lessons:lessons.slice(-100),conversations:conversations.slice(-100),counts:{locations:visible.length,bookings:bookingRows.length,lessonSeries:series.length,lessons:lessons.length}};

}

function getLocations(token){const u=requireUser_(token);return rows_('LOCATIONS').filter(x=>flag_(x.Active)).map(x=>u.Role===PS.ROLES.ADMIN?x:{LocationID:x.LocationID,Name:x.Name,City:x.City,Active:x.Active});}

function getCourts(token){const u=requireUser_(token);return rows_('COURTS').filter(x=>flag_(x.Active)).map(x=>u.Role===PS.ROLES.ADMIN||canManage_(u,x.LocationID)?x:{CourtID:x.CourtID,LocationID:x.LocationID,Name:x.Name,CourtNumber:x.CourtNumber,Active:x.Active});}

function getUsers(token){const u=requireUser_(token);if(u.Role===PS.ROLES.ADMIN)return rows_('USERS').map(publicUser_);

  if(u.Role===PS.ROLES.LOCATION)return rows_('USERS').filter(x=>x.Role===PS.ROLES.TRAINER&&flag_(x.Active)).map(publicUser_);

  if(u.Role===PS.ROLES.TRAINER){const ids=new Set(rows_('CONVERSATIONS').filter(c=>c.TrainerUserID===u.UserID).map(c=>c.PlayerUserID));return rows_('USERS').filter(x=>ids.has(x.UserID)).map(publicUser_);}return [publicUser_(u)];}

function getBookings(token){const u=requireUser_(token);return rows_('BOOKINGS').filter(x=>u.Role===PS.ROLES.ADMIN||(u.Role===PS.ROLES.LOCATION?ownLocations_(u).includes(x.LocationID):x.UserID===u.UserID)).map(x=>u.Role===PS.ROLES.LOCATION?{BookingID:x.BookingID,LocationID:x.LocationID,CourtID:x.CourtID,Date:x.Date,StartTime:x.StartTime,EndTime:x.EndTime,Type:x.Type,Status:x.Status}:x);}

function getLessonSeries(token){const u=requireUser_(token);if(u.Role===PS.ROLES.LOCATION)return [];

  return rows_('LESSON_SERIES').filter(x=>u.Role===PS.ROLES.ADMIN||(u.Role===PS.ROLES.TRAINER&&x.TrainerUserID===u.UserID));}

function getLessons(token){const u=requireUser_(token);if(u.Role===PS.ROLES.LOCATION)return [];

  const own=rows_('CONVERSATIONS').filter(x=>x.PlayerUserID===u.UserID).map(x=>x.TrainerUserID);

  return rows_('LESSONS').filter(x=>u.Role===PS.ROLES.ADMIN||(u.Role===PS.ROLES.TRAINER&&x.TrainerUserID===u.UserID)||(u.Role===PS.ROLES.PLAYER&&own.includes(x.TrainerUserID)&&rows_('BOOKINGS').some(b=>b.UserID===u.UserID&&b.Date===x.Date&&b.CourtID===x.CourtID)));

}

function getAvailability(token,locationId,date){const u=requireUser_(token);check_(rows_('LOCATIONS').some(l=>l.LocationID===locationId&&flag_(l.Active)),'Locatie onbekend.');

  return rows_('COURT_AVAILABILITY').filter(a=>a.LocationID===locationId&&(!date||a.Date===date)).map(a=>u.Role===PS.ROLES.ADMIN||canManage_(u,locationId)?a:{CourtID:a.CourtID,Date:a.Date,StartTime:a.StartTime,EndTime:a.EndTime,Status:a.Status});}

function saveAvailability(token,data){const u=requireUser_(token),d=data||{};manageLocation_(u,d.LocationID);

  check_(find_('COURTS','CourtID',d.CourtID)?.LocationID===d.LocationID,'Baan hoort niet bij locatie.');validateSlot_(d);

  const obj={AvailabilityID:uid_('AVL'),LocationID:d.LocationID,CourtID:d.CourtID,Date:d.Date,StartTime:d.StartTime,EndTime:d.EndTime,Status:'AVAILABLE',Visibility:'PUBLIC',Source:'MANUAL',ImportedAt:now_(),UpdatedAt:now_()};put_('COURT_AVAILABILITY',obj);audit_(u,'AVAILABILITY_CREATED','COURT_AVAILABILITY',obj.AvailabilityID);return obj;}

function validateSlot_(d){check_(/^\d{4}-\d{2}-\d{2}$/.test(d.Date||'')&&/^([01]\d|2[0-3]):[0-5]\d$/.test(d.StartTime||'')&&/^([01]\d|2[0-3]):[0-5]\d$/.test(d.EndTime||'')&&d.StartTime<d.EndTime,'Ongeldige datum of tijd.');}

function bookingDays_(id,role){const rule=rows_('BOOKING_RULES').filter(x=>x.LocationID===id&&x.Role===role&&flag_(x.Active)).pop();return rule?Number(rule.DaysInAdvance):PS.DEFAULT_BOOKING_DAYS[role];}

function slotAvailable_(d){const slots=rows_('COURT_AVAILABILITY').filter(a=>a.CourtID===d.CourtID&&a.LocationID===d.LocationID&&a.Date===d.Date);

  return slots.some(a=>a.Status==='AVAILABLE'&&a.StartTime<=d.StartTime&&a.EndTime>=d.EndTime)&&!slots.some(a=>a.Status==='BLOCKED'&&a.StartTime<d.EndTime&&a.EndTime>d.StartTime);}

function overlaps_(a,d){return a.Date===d.Date&&a.CourtID===d.CourtID&&a.StartTime<d.EndTime&&a.EndTime>d.StartTime;}

function createBooking(token,data){const u=requireUser_(token),d=data||{};validateSlot_(d);

  const court=find_('COURTS','CourtID',d.CourtID);check_(court&&flag_(court.Active)&&court.LocationID===d.LocationID,'Baan niet beschikbaar.');

  const day=Math.floor((Date.parse(d.Date+'T00:00:00Z')-Date.parse(Utilities.formatDate(new Date(),'Europe/Amsterdam','yyyy-MM-dd')+'T00:00:00Z'))/86400000);

  check_(day>=0&&day<=bookingDays_(d.LocationID,u.Role),'Deze datum valt buiten jouw boekingsvenster.');

  check_(u.Role!==PS.ROLES.LOCATION||canManage_(u,d.LocationID),'Geen beheerrecht voor deze locatie.');

  check_(u.Role!==PS.ROLES.PLAYER||d.Type!=='LESSON','Alleen trainers beheren lesboekingen.');

  return lock_(()=>{check_(slotAvailable_(d),'Er is geen vrijgegeven tijdslot voor deze baan.');

    check_(!rows_('BOOKINGS').some(b=>!['CANCELLED','EXPIRED'].includes(b.Status)&&overlaps_(b,d)),'Baan is al geboekt.');

    check_(!rows_('BOOKING_HOLDS').some(b=>b.Status==='ACTIVE'&&Date.parse(b.ExpiresAt)>Date.now()&&overlaps_(b,d)),'Baan is tijdelijk gereserveerd.');

    const obj={BookingID:uid_('BKG'),LocationID:d.LocationID,CourtID:d.CourtID,UserID:u.UserID,Date:d.Date,StartTime:d.StartTime,EndTime:d.EndTime,Type:u.Role===PS.ROLES.TRAINER?'LESSON':'PLAY',Priority:({ADMINISTRATOR:100,TRAINER:90,LOCATIEBEHEERDER:80,SPELER:20})[u.Role],Visibility:'PRIVATE',Status:'CONFIRMED',Source:'PADELOS',CreatedAt:now_(),UpdatedAt:now_()};put_('BOOKINGS',obj);audit_(u,'BOOKING_CREATED','BOOKINGS',obj.BookingID);return obj;

  });

}

function createLessonSeries(token,data){const u=requireRole_(token,[PS.ROLES.ADMIN,PS.ROLES.TRAINER]),d=data||{};

  check_(find_('LOCATIONS','LocationID',d.LocationID)&&safe_(d.Name,120)&&/^\d{4}-\d{2}-\d{2}$/.test(d.StartDate||'')&&/^\d{4}-\d{2}-\d{2}$/.test(d.EndDate||''),'Vul locatie, naam en datums in.');validateSlot_({Date:d.StartDate,StartTime:d.StartTime,EndTime:d.EndTime});

  const obj={SeriesID:uid_('SER'),TrainerUserID:u.UserID,LocationID:d.LocationID,Name:safe_(d.Name,120),Description:safe_(d.Description,1000),StartDate:d.StartDate,EndDate:d.EndDate,DayOfWeek:safe_(d.DayOfWeek,30),StartTime:d.StartTime,EndTime:d.EndTime,NumberOfLessons:Number(d.NumberOfLessons)||1,Players:Number(d.Players)||1,Level:safe_(d.Level,50),Goal:safe_(d.Goal,500),Status:'REQUESTED',CreatedAt:now_(),UpdatedAt:now_()};put_('LESSON_SERIES',obj);return obj;}

function getConversations(token){const u=requireUser_(token);if(u.Role===PS.ROLES.LOCATION)return [];

  return rows_('CONVERSATIONS').filter(c=>u.Role===PS.ROLES.ADMIN||c.TrainerUserID===u.UserID||c.PlayerUserID===u.UserID);}

function getConversationMessages(token,conversationId){const u=requireUser_(token),c=find_('CONVERSATIONS','ConversationID',conversationId);

  check_(c&&(u.Role===PS.ROLES.ADMIN||c.TrainerUserID===u.UserID||c.PlayerUserID===u.UserID),'Geen toegang tot gesprek.');

  return rows_('CONVERSATION_MESSAGES').filter(m=>m.ConversationID===conversationId).slice(-200);}

function startConversation(token,playerUserId,subject){const u=requireRole_(token,[PS.ROLES.ADMIN,PS.ROLES.TRAINER]);

  const p=find_('USERS','UserID',playerUserId);check_(p&&p.Role===PS.ROLES.PLAYER,'Speler onbekend.');

  if(u.Role===PS.ROLES.TRAINER)check_(rows_('CONVERSATIONS').some(c=>c.TrainerUserID===u.UserID&&c.PlayerUserID===p.UserID),'De administrator moet deze speler eerst aan jou koppelen.');

  const c=put_('CONVERSATIONS',{ConversationID:uid_('CON'),TrainerUserID:u.UserID,PlayerUserID:p.UserID,Subject:safe_(subject,120),CreatedAt:now_(),UpdatedAt:now_()});return c;}

function createTrainerConversation(token,trainerId,playerId,subject){const u=requireRole_(token,[PS.ROLES.ADMIN]);

  const t=find_('USERS','UserID',trainerId),p=find_('USERS','UserID',playerId);

  check_(t&&t.Role===PS.ROLES.TRAINER&&p&&p.Role===PS.ROLES.PLAYER,'Kies een trainer en een speler.');

  const c=put_('CONVERSATIONS',{ConversationID:uid_('CON'),TrainerUserID:t.UserID,PlayerUserID:p.UserID,Subject:safe_(subject,120),CreatedAt:now_(),UpdatedAt:now_()});audit_(u,'CONVERSATION_CREATED','CONVERSATIONS',c.ConversationID);return c;}

function sendConversationMessage(token,conversationId,message){const u=requireUser_(token),c=find_('CONVERSATIONS','ConversationID',conversationId);

  check_(c&&(u.Role===PS.ROLES.ADMIN||c.TrainerUserID===u.UserID||c.PlayerUserID===u.UserID),'Geen toegang tot gesprek.');

  check_(safe_(message,4000).length>0,'Bericht ontbreekt.');const m=put_('CONVERSATION_MESSAGES',{MessageID:uid_('MSG'),ConversationID:conversationId,SenderUserID:u.UserID,Message:safe_(message,4000),CreatedAt:now_()});return m;}

function getAdminData(token){requireRole_(token,[PS.ROLES.ADMIN]);return {users:rows_('USERS').map(publicUser_),locations:rows_('LOCATIONS'),audit:rows_('AUDIT_LOG').slice(-100),config:rows_('SYSTEM_CONFIG'),importSources:rows_('IMPORT_SOURCES')};}

function setBookingRule(token,d){const u=requireRole_(token,[PS.ROLES.ADMIN]),loc=find_('LOCATIONS','LocationID',d.LocationID);

  check_(loc&&Object.values(PS.ROLES).includes(d.Role)&&Number.isInteger(Number(d.DaysInAdvance))&&Number(d.DaysInAdvance)>=0&&Number(d.DaysInAdvance)<=365,'Ongeldige regel.');

  return lock_(()=>{let r=rows_('BOOKING_RULES').find(x=>x.LocationID===d.LocationID&&x.Role===d.Role&&flag_(x.Active));

    if(r){r.DaysInAdvance=Number(d.DaysInAdvance);r.UpdatedAt=now_();update_('BOOKING_RULES',r._row,r);}

    else r=put_('BOOKING_RULES',{RuleID:uid_('RULE'),LocationID:d.LocationID,Name:'Boekingsvenster '+d.Role,Role:d.Role,DaysInAdvance:Number(d.DaysInAdvance),Active:true,CreatedAt:now_(),UpdatedAt:now_()});audit_(u,'RULE_UPDATED','BOOKING_RULES',r.RuleID);return r;});}

function saveCourt(token,d){const u=requireUser_(token);manageLocation_(u,d.LocationID);check_(find_('LOCATIONS','LocationID',d.LocationID),'Locatie onbekend.');check_(safe_(d.Name,80),'Baan heeft een naam nodig.');

  return put_('COURTS',{CourtID:uid_('CRT'),LocationID:d.LocationID,Name:safe_(d.Name,80),CourtNumber:safe_(d.CourtNumber,20),Active:true,CreatedAt:now_(),UpdatedAt:now_()});}

function savePrice(token,d){const u=requireUser_(token);manageLocation_(u,d.LocationID);check_(find_('COURTS','CourtID',d.CourtID)?.LocationID===d.LocationID,'Baan onbekend.');

  const p=Number(d.Price);check_(Number.isFinite(p)&&p>=0,'Ongeldige prijs.');return put_('COURT_PRICES',{PriceID:uid_('PRC'),LocationID:d.LocationID,CourtID:d.CourtID,DayOfWeek:safe_(d.DayOfWeek,20),StartTime:safe_(d.StartTime,5),EndTime:safe_(d.EndTime,5),Price:p,Currency:'EUR',Active:true,CreatedAt:now_(),UpdatedAt:now_()});}

// CSV and JSON are mapped to canonical availability fields. URL import is admin-only and source host must be allowlisted separately.

function importAvailabilityCsv(token,locationId,csv){const u=requireUser_(token);manageLocation_(u,locationId);check_(typeof csv==='string'&&csv.length<=300000,'CSV is te groot (maximaal 300 KB).');

  const matrix=Utilities.parseCsv(csv),header=(matrix.shift()||[]).map(x=>x.trim());

  check_(['CourtID','Date','StartTime','EndTime'].every(k=>header.includes(k)),'CSV verwacht CourtID,Date,StartTime,EndTime.');

  const records=matrix.filter(r=>r.some(Boolean)).map(r=>Object.fromEntries(header.map((h,i)=>[h,r[i]||''])));

  return importAvailabilityRecords_(u,locationId,records,'CSV');}

function importAvailabilityJson(token,locationId,json){const u=requireUser_(token);manageLocation_(u,locationId);check_(typeof json==='string'&&json.length<=300000,'JSON is te groot.');

  const records=JSON.parse(json);check_(Array.isArray(records),'JSON moet een lijst zijn.');return importAvailabilityRecords_(u,locationId,records,'JSON');}

function importAvailabilitySheet(token,locationId,spreadsheetId,tabName){const u=requireUser_(token);manageLocation_(u,locationId);

  check_(/^[\w-]{20,}$/.test(spreadsheetId)&&safe_(tabName,100),'Ongeldige Sheet-bron.');

  const sh=SpreadsheetApp.openById(spreadsheetId).getSheetByName(tabName);check_(sh,'Brontab niet gevonden.');check_(sh.getLastRow()<=1001,'Maximaal 1000 rijen per import.');

  const values=sh.getDataRange().getDisplayValues(),headers=values.shift(),records=values.map(r=>Object.fromEntries(headers.map((h,i)=>[h,r[i]])));

  return importAvailabilityRecords_(u,locationId,records,'SHEET');}

function importAvailabilityRecords_(u,locationId,records,source){check_(records.length<=1000,'Maximaal 1000 rijen per import.');

  const courtIds=new Set(rows_('COURTS').filter(c=>c.LocationID===locationId).map(c=>c.CourtID));

  records.forEach(r=>{check_(courtIds.has(r.CourtID),'Onbekende CourtID in import.');validateSlot_(r);check_(!r.Status||['AVAILABLE','BLOCKED'].includes(r.Status),'Status moet AVAILABLE of BLOCKED zijn.');});

  return lock_(()=>{const existing=new Set(rows_('COURT_AVAILABILITY').filter(a=>a.LocationID===locationId).map(a=>[a.CourtID,a.Date,a.StartTime,a.EndTime,a.Source].join('|')));let added=0;

    records.forEach(r=>{const key=[r.CourtID,r.Date,r.StartTime,r.EndTime,source].join('|');if(existing.has(key))return;existing.add(key);put_('COURT_AVAILABILITY',{AvailabilityID:uid_('AVL'),LocationID:locationId,CourtID:r.CourtID,Date:r.Date,StartTime:r.StartTime,EndTime:r.EndTime,Status:r.Status||'AVAILABLE',Visibility:'PUBLIC',Source:source,ImportedAt:now_(),UpdatedAt:now_()});added++;});

    put_('IMPORT_RUNS',{ImportRunID:uid_('RUN'),LocationID:locationId,StartedAt:now_(),FinishedAt:now_(),Status:'SUCCESS',RecordsRead:records.length,RecordsImported:added,RecordsSkipped:records.length-added,CreatedAt:now_()});audit_(u,'AVAILABILITY_IMPORTED','LOCATIONS',locationId);return {ok:true,read:records.length,imported:added};});}

function getPadelOSSystemInfo(token){requireRole_(token,[PS.ROLES.ADMIN]);const ss=db_();return {version:PS.VERSION,spreadsheetId:ss.getId(),sheets:ss.getSheets().map(s=>({name:s.getName(),rows:s.getLastRow(),columns:s.getLastColumn()}))};}

<!doctype html>

<html lang="nl"><head><meta charset="utf-8"><meta name="viewport" content="width=device-width,initial-scale=1">

<title>PadelSchoolOS V1.1</title>

<style>

:root{font-family:system-ui,-apple-system,Segoe UI,sans-serif;color:#152b3a;background:#eef6f3}*{box-sizing:border-box}body{margin:0}header{background:#083b42;color:white;padding:1rem max(1rem,calc((100vw - 1160px)/2));display:flex;justify-content:space-between;align-items:center;gap:1rem;flex-wrap:wrap}h1{margin:0;font-size:1.3rem}h2{margin-top:0}main{max-width:1160px;margin:1.4rem auto;padding:0 1rem}section,.card{background:white;border-radius:16px;padding:1.2rem;margin-bottom:1rem;box-shadow:0 3px 16px #103d3114}button{border:0;border-radius:9px;background:#087568;color:white;padding:.7rem 1rem;cursor:pointer;font:inherit}button:hover{background:#065d53}button.secondary{background:#e3efeb;color:#173a38}button.danger{background:#ad3443}button:disabled{opacity:.5;cursor:wait}input,select,textarea{width:100%;padding:.7rem;border:1px solid #b8cbc7;border-radius:9px;font:inherit;background:white}label{display:block;margin:.65rem 0;font-weight:600}label small{font-weight:400}textarea{min-height:110px}.grid{display:grid;grid-template-columns:repeat(auto-fit,minmax(220px,1fr));gap:1rem}.row{display:flex;gap:.6rem;align-items:center;flex-wrap:wrap}.row>*{flex:1}.nav{display:flex;gap:.4rem;flex-wrap:wrap;margin:1rem 0}.nav button{flex:none}.muted{color:#546b69}.error{color:#a32634}.good{color:#086448}#status{min-height:1.5em}.stat{font-size:1.7rem;font-weight:800;color:#075e56}.small{font-size:.9rem}table{width:100%;border-collapse:collapse;display:block;overflow:auto}th,td{text-align:left;padding:.6rem;border-bottom:1px solid #d9e5e1;vertical-align:top;white-space:nowrap}th{background:#edf6f2}.hidden{display:none!important}pre{white-space:pre-wrap;overflow-wrap:anywhere}.pill{border-radius:20px;background:#e6f2ec;padding:.2rem .6rem}.account{display:flex;justify-content:space-between;gap:.6rem;align-items:center;margin:.5rem 0;padding:.6rem;border:1px solid #dce7e2;border-radius:9px}