/************************************************************

* PADELOS ENTERPRISE IDENTITY & MANAGEMENT CORE v4.2

* Gekoppeld aan Spreadsheet: 1Rap-6ENmSOwF-lkSZuwxxkJfVWVyfNqRsSD0h9L4uF8

************************************************************/

const P12345 = Object.freeze({

 VERSION: '4.2.0',

 SPREADSHEET_ID: '1Rap-6ENmSOwF-lkSZuwxxkJfVWVyfNqRsSD0h9L4uF8',

 ADMIN_EMAIL: 'padelstijn@gmail.com',

 CODE_MINUTES: 10,

 SESSION_HOURS: 24,

 TABS: {

   BERICHTEN_SPELERS: ['ID','GESPREK_ID','AFZENDER_ID','ONTVANGER_ID','GROEP_ID','LES_ID','ONDERWERP','BERICHT','KANAAL','GELEZEN_AT','STATUS','CREATED_AT','UPDATED_AT','TEAM_ID','BERICHT_TYPE','MEDIA_URL','LINKS'],

   BERICHTEN_TEAM: ['ID','GESPREK_ID','AFZENDER_ID','ONTVANGER_ID','GROEP_ID','LES_ID','ONDERWERP','BERICHT','KANAAL','GELEZEN_AT','STATUS','CREATED_AT','UPDATED_AT','TEAM_ID','BERICHT_TYPE','MEDIA_URL','LINKS'],

   BERICHTEN_ADMIN: ['ID','GESPREK_ID','AFZENDER_ID','ONTVANGER_ID','GROEP_ID','LES_ID','ONDERWERP','BERICHT','KANAAL','GELEZEN_AT','STATUS','CREATED_AT','UPDATED_AT','TEAM_ID','BERICHT_TYPE','MEDIA_URL','LINKS'],

   BERICHTEN_FINANCIEEL: ['ID','GESPREK_ID','AFZENDER_ID','ONTVANGER_ID','LES_ID','ONDERWERP','BERICHT','DATUM','STARTTIJD','EINDTIJD','LESDUUR','PRIJS_EXCL_BTW','BTW_TARIEF','PRIJS_INCL_BTW','TRAINER_VERGOEDING','BAAN_HUURKOSTEN','MATERIAAL_KOSTEN','GEWENSTE_MATERIALEN','AANWEZIGE_MATERIALEN','SLEUTEL_INFO','INGANG_CODES','BETAALSTATUS','BEVESTIGING_STATUS','CREATED_AT','UPDATED_AT'],

   GEBRUIKERS: ['ID','EMAIL','VOORNAAM','ACHTERNAAM','TELEFOON','PASSWORD_HASH','PASSWORD_SALT','EMAIL_VERIFIED','LAST_LOGIN','STATUS','CREATED_AT','UPDATED_AT'],

   GEBRUIKER_ROLLEN: ['ID','USER_ID','ROLE_CODE','STATUS','CREATED_AT','UPDATED_AT'],

   LOCATIES: ['ID','NAAM','ADRES','POSTCODE','PLAATS','CONTACTPERSOON','TELEFOON','EMAIL','STATUS','CREATED_AT','UPDATED_AT'],

   BANEN: ['ID','LOCATIE_ID','NAAM','NUMMER','TYPE','INDOOR','OVERDEKT','STATUS','CREATED_AT','UPDATED_AT'],

   BOEKINGEN: ['ID','USER_ID','DATUM','TIJD','LOCATIE','TYPE','STATUS','TRAINER_ID','OPMERKING','CREATED_AT','UPDATED_AT'],

   PROFIELEN: ['ID','USER_ID','WEERGAVENAAM','FOTO_URL','BIO','TAAL','LETTERGROOTTE','THEMA','BG_KLEUR','KAART_KLEUR','TEKST_KLEUR','ACCENT_KLEUR','QUOTE','SPEELSTERKTE','BONDSNUMMER','STATUS','CREATED_AT','UPDATED_AT'],

   ROLLEN: ['ID','CODE','NAAM','OMSCHRIJVING','STATUS','CREATED_AT','UPDATED_AT'],

   SESSIES: ['ID','USER_ID','TOKEN_HASH','EXPIRES_AT','LAST_USED_AT','STATUS','CREATED_AT','UPDATED_AT'],

   LOGIN_CODES: ['ID','EMAIL','CODE_HASH','PURPOSE','EXPIRES_AT','ATTEMPTS','USED_AT','STATUS','CREATED_AT','UPDATED_AT'],

   TRAINER_BESCHIKBAARHEID: ['ID','USER_ID','TYPE','START_DATUM','EIND_DATUM','TIJD','STATUS','TOELICHTING','CREATED_AT','UPDATED_AT']

 }

});

function doGet(e) {

 return HtmlService.createTemplateFromFile('Index').evaluate()

   .setTitle('PadelOS — Multi-Role Management Portal')

   .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL)

   .addMetaTag('viewport', 'width=device-width, initial-scale=1');

}

function setupPadelOS12345() {

 const lock = LockService.getScriptLock();

 lock.waitLock(30000);

 try {

   Object.entries(P12345.TABS).forEach(([n, h]) => ensureTab_(n, h));

   if (!PropertiesService.getScriptProperties().getProperty('PADELOS_SECRET')) {

     PropertiesService.getScriptProperties().setProperty('PADELOS_SECRET', token_() + token_());

   }

   const rollenLijst = [

     ['SPELER', 'Speler', 'Speler Portaal: lessen boeken, leerdoelen & chat'],

     ['TRAINER', 'Trainer', 'Trainers Portaal: Spelers, Teams, Locaties, Uren, Facturatie & Chats'],

     ['ADMIN', 'Administrator', 'Administrator Portaal: Alle tabs, administratie, banen boeken & betalen']

   ];

   rollenLijst.forEach(([code, naam, desc]) => seedRole_(code, naam, desc));

   let admin = find_('GEBRUIKERS', 'EMAIL', P12345.ADMIN_EMAIL);

   if (!admin) {

     admin = createUser_(P12345.ADMIN_EMAIL, 'Stijn', 'Gabeler');

   }

   rollenLijst.forEach(([code]) => ensureUserRole_(admin.ID, code));

   return { ok: true, version: P12345.VERSION, message: 'PadelOS succesvol geïnitialiseerd!' };

 } finally {

   lock.releaseLock();

 }

}

function bootstrap(sessionToken) {

 const auth = auth_(sessionToken);

 if (!auth) return { ok: true, version: P12345.VERSION, authenticated: false };

 return userState_(auth.userId, sessionToken);

}

function requestLoginCode(email) {

 email = normalizeEmail_(email);

 if (!validEmail_(email)) throw publicError_('Vul een geldig e-mailadres in.');

  const lock = LockService.getScriptLock();

 lock.waitLock(15000);

 try {

   const code = String(Math.floor(100000 + Math.random() * 900000));

   const now = new Date();

   append_('LOGIN_CODES', {

     ID: id_('LOGI'), EMAIL: email, CODE_HASH: hash_('code|' + email + '|' + code),

     PURPOSE: 'LOGIN_REGISTER', EXPIRES_AT: new Date(now.getTime() + P12345.CODE_MINUTES * 60000),

     ATTEMPTS: 0, USED_AT: '', STATUS: 'ACTIVE', CREATED_AT: now, UPDATED_AT: now

   });

   MailApp.sendEmail({

     to: email,

     subject: 'Jouw PadelOS Inlogcode',

     name: 'PadelOS Management',

     htmlBody: '<div style="font-family:Arial,sans-serif"><h2>🎾 PadelOS Inlogportaal</h2><p>Jouw eenmalige inlogcode:</p><p style="font-size:32px;font-weight:800;letter-spacing:6px;color:#0e6b56">' + code + '</p></div>'

   });

   return { ok: true, email, message: 'Verificatiecode verzonden per e-mail.' };

 } finally {

   lock.releaseLock();

 }

}

function verifyLoginCode(email, code) {

 email = normalizeEmail_(email); code = String(code || '').trim();

 const item = rows_('LOGIN_CODES').filter(x => normalizeEmail_(x.data.EMAIL) === email && x.data.STATUS === 'ACTIVE')

   .sort((a, b) => dateMs_(b.data.CREATED_AT) - dateMs_(a.data.CREATED_AT))[0];

 if (!item || dateMs_(item.data.EXPIRES_AT) <= Date.now()) throw publicError_('Deze code is verlopen of niet gevonden.');

 if (!safeEqual_(item.data.CODE_HASH, hash_('code|' + email + '|' + code))) throw publicError_('De code klopt niet.');

  updateRow_('LOGIN_CODES', item.row, { USED_AT: new Date(), STATUS: 'USED', UPDATED_AT: new Date() });

  let user = find_('GEBRUIKERS', 'EMAIL', email);

 if (!user) user = createUser_(email, '', '');

  const token = token_();

 const now = new Date();

 append_('SESSIES', {

   ID: id_('SESS'), USER_ID: user.ID, TOKEN_HASH: hash_('session|' + token),

   EXPIRES_AT: new Date(now.getTime() + P12345.SESSION_HOURS * 3600000),

   LAST_USED_AT: now, STATUS: 'ACTIVE', CREATED_AT: now, UPDATED_AT: now

 });

  updateBy_('GEBRUIKERS', 'ID', user.ID, { EMAIL_VERIFIED: 'JA', LAST_LOGIN: now, UPDATED_AT: now });

 return userState_(user.ID, token);

}

function passwordLogin(email, password) {

 email = normalizeEmail_(email);

 const u = find_('GEBRUIKERS', 'EMAIL', email);

 if (!u) throw publicError_('Gebruiker niet gevonden. Vraag een inlogcode aan.');

  const storedHash = u.PASSWORD_HASH || '';

 const inputHash = hash_('pwd|' + password + '|' + (u.PASSWORD_SALT || ''));

  if (storedHash && storedHash !== inputHash) throw publicError_('Onjuist wachtwoord.');

  const token = token_();

 const now = new Date();

 append_('SESSIES', {

   ID: id_('SESS'), USER_ID: u.ID, TOKEN_HASH: hash_('session|' + token),

   EXPIRES_AT: new Date(now.getTime() + P12345.SESSION_HOURS * 3600000),

   LAST_USED_AT: now, STATUS: 'ACTIVE', CREATED_AT: now, UPDATED_AT: now

 });

  updateBy_('GEBRUIKERS', 'ID', u.ID, { LAST_LOGIN: now, UPDATED_AT: now });

 return userState_(u.ID, token);

}

function setPassword(sessionToken, newPassword) {

 const auth = requireAuth_(sessionToken);

 const salt = token_().slice(0, 8);

 const pwdHash = hash_('pwd|' + newPassword + '|' + salt);

 updateBy_('GEBRUIKERS', 'ID', auth.userId, { PASSWORD_HASH: pwdHash, PASSWORD_SALT: salt, UPDATED_AT: new Date() });

 return { ok: true, message: 'Wachtwoord succesvol ingesteld!' };

}

function logout(sessionToken) {

 const a = auth_(sessionToken, true);

 if (a) updateRow_('SESSIES', a.row, { STATUS: 'REVOKED', UPDATED_AT: new Date() });

 return { ok: true };

}

function getAllSheetNames(sessionToken) {

 requireAuth_(sessionToken);

 return db_().getSheets().map(s => s.getName()).sort();

}

function getTableData(sessionToken, tableName) {

 requireAuth_(sessionToken);

 const sh = db_().getSheetByName(tableName);

 if (!sh) throw publicError_('Tabblad niet gevonden.');

 const v = sh.getDataRange().getValues();

 if (v.length === 0) return { ok: true, headers: [], rows: [] };

 const headers = v[0].map(String);

 const rows = v.slice(1).map((r, i) => ({

   row: i + 2,

   data: Object.fromEntries(headers.map((k, j) => [k, r[j]]))

 }));

 return { ok: true, headers, rows };

}

function saveTableRow(sessionToken, tableName, rowId, rowData) {

 requireAuth_(sessionToken);

 const sh = sheet_(tableName);

 const headers = headers_(tableName);

 const now = new Date();

  if (!rowId) {

   rowId = id_(tableName.slice(0, 4).toUpperCase());

   rowData.ID = rowId;

   if (headers.includes('CREATED_AT')) rowData.CREATED_AT = now;

   if (headers.includes('UPDATED_AT')) rowData.UPDATED_AT = now;

   if (headers.includes('STATUS')) rowData.STATUS = rowData.STATUS || 'ACTIVE';

   append_(tableName, rowData);

 } else {

   if (headers.includes('UPDATED_AT')) rowData.UPDATED_AT = now;

   updateBy_(tableName, 'ID', rowId, rowData);

 }

 return { ok: true, message: 'Succesvol opgeslagen!' };

}

function sendSegmentedChatMessage(sessionToken, data) {

 const auth = requireAuth_(sessionToken);

 const now = new Date();

 let targetTab = 'BERICHTEN_TEAM';

 if (data.chatType === 'SPELERS') targetTab = 'BERICHTEN_SPELERS';

 if (data.chatType === 'ADMIN') targetTab = 'BERICHTEN_ADMIN';

 if (data.chatType === 'FINANCIEEL') targetTab = 'BERICHTEN_FINANCIEEL';

 const berichtId = id_('BERI');

 const urlRegex = /(https?:\/\/[^\s]+)/g;

 const gevondenLinks = data.bericht && data.bericht.match(urlRegex) ? data.bericht.match(urlRegex).join(', ') : '';

 if (targetTab === 'BERICHTEN_FINANCIEEL') {

   append_(targetTab, {

     ID: berichtId, GESPREK_ID: data.gesprekId || 'FIN-BOEKING', AFZENDER_ID: auth.userId,

     ONTVANGER_ID: data.ontvangerId || '', LES_ID: data.lesId || '', ONDERWERP: data.onderwerp || 'Baanreservering & Facturatie',

     BERICHT: data.bericht || '', DATUM: data.datum || '', STARTTIJD: data.starttijd || '', EINDTIJD: data.eindtijd || '',

     LESDUUR: data.lesduur || '60 min', PRIJS_EXCL_BTW: data.prijsExcl || 0, BTW_TARIEF: data.btwTarief || '21%',

     PRIJS_INCL_BTW: data.prijsIncl || 0, TRAINER_VERGOEDING: data.trainerVergoeding || 0, BAAN_HUURKOSTEN: data.baanHuur || 0,

     MATERIAAL_KOSTEN: data.materiaalKosten || 0, GEWENSTE_MATERIALEN: data.gewensteMaterialen || '', AANWEZIGE_MATERIALEN: data.aanwezigeMaterialen || '',

     SLEUTEL_INFO: data.sleutelInfo || '', INGANG_CODES: data.ingangCodes || '', BETAALSTATUS: data.betaalstatus || 'OPEN',

     BEVESTIGING_STATUS: data.bevestigingStatus || 'BEVESTIGD', CREATED_AT: now, UPDATED_AT: now

   });

 } else {

   append_(targetTab, {

     ID: berichtId, GESPREK_ID: data.gesprekId || 'GENERAL', AFZENDER_ID: auth.userId, ONTVANGER_ID: data.ontvangerId || '',

     GROEP_ID: data.groepId || 'CLUB-TEAM', LES_ID: data.lesId || '', ONDERWERP: data.onderwerp || 'Chat',

     BERICHT: data.bericht || '', KANAAL: 'PORTAL', GELEZEN_AT: '', STATUS: 'ACTIVE', CREATED_AT: now, UPDATED_AT: now,

     TEAM_ID: data.teamId || '', BERICHT_TYPE: data.mediaUrl ? 'MEDIA' : 'TEKST', MEDIA_URL: data.mediaUrl || '', LINKS: gevondenLinks

   });

 }

 return { ok: true, message: 'Opgeslagen in ' + targetTab };

}

function getSegmentedChatMessages(sessionToken, chatType, gesprekId) {

 requireAuth_(sessionToken);

 let targetTab = 'BERICHTEN_TEAM';

 if (chatType === 'SPELERS') targetTab = 'BERICHTEN_SPELERS';

 if (chatType === 'ADMIN') targetTab = 'BERICHTEN_ADMIN';

 if (chatType === 'FINANCIEEL') targetTab = 'BERICHTEN_FINANCIEEL';

 const messages = rows_(targetTab).filter(r => !gesprekId || r.data.GESPREK_ID === gesprekId);

 return { ok: true, messages: messages.map(m => m.data) };

}

function userState_(userId, rawToken) {

 const u = find_('GEBRUIKERS', 'ID', userId);

 const p = find_('PROFIELEN', 'USER_ID', userId) || {};

 const roles = table_('GEBRUIKER_ROLLEN').filter(r => r.USER_ID === userId && r.STATUS === 'ACTIVE').map(r => r.ROLE_CODE);

  return {

   ok: true, version: P12345.VERSION, authenticated: true, sessionToken: rawToken,

   user: {

     id: u.ID, email: u.EMAIL, firstName: u.VOORNAAM || '', lastName: u.ACHTERNAAM || '',

     phone: u.TELEFOON || '', displayName: p.WEERGAVENAAM || u.VOORNAAM || u.EMAIL.split('@')[0]

   },

   roles, interfaces: interfacesFor_(roles)

 };

}

function interfacesFor_(roles) {

 const registry = {

   SPELER: { icon: '🎾', title: 'Speler Portaal', text: 'Boek lessen, bekijk leerdoelen en neem deel aan de chat.', table: 'BOEKINGEN' },

   TRAINER: { icon: '🧑‍🏫', title: 'Trainers Portaal', text: 'Beheer Spelers, Teams, Locaties, Uren, Facturaties & Communicatie.', table: 'TRAINER_BESCHIKBAARHEID' },

   ADMIN: { icon: '⚡', title: 'Administrator Portaal', text: 'Beheer alle tabs, administratie, banen boeken, betalen en plannen.', table: 'PROFIELEN' }

 };

 return roles.map(code => Object.assign({ code }, registry[code] || { icon: '🔹', title: code, text: 'PadelOS Module', table: 'PROFIELEN' }));

}

function createUser_(email, first, last) {

 const now = new Date(), id = id_('GEBR');

 append_('GEBRUIKERS', { ID: id, EMAIL: email, VOORNAAM: first, ACHTERNAAM: last, TELEFOON: '', PASSWORD_HASH: '', PASSWORD_SALT: '', EMAIL_VERIFIED: 'JA', LAST_LOGIN: '', STATUS: 'ACTIVE', CREATED_AT: now, UPDATED_AT: now });

 append_('PROFIELEN', { ID: id_('PROF'), USER_ID: id, WEERGAVENAAM: email.split('@')[0], FOTO_URL: '', BIO: '', QUOTE: 'Jij bepaalt je eigen koers.', SPEELSTERKTE: '5', STATUS: 'ACTIVE', CREATED_AT: now, UPDATED_AT: now });

 ensureUserRole_(id, 'SPELER');

 return find_('GEBRUIKERS', 'ID', id);

}

function ensureUserRole_(uid, role) {

 const x = rows_('GEBRUIKER_ROLLEN').find(r => r.data.USER_ID === uid && r.data.ROLE_CODE === role);

 if (x) return;

 const n = new Date();

 append_('GEBRUIKER_ROLLEN', { ID: id_('UROL'), USER_ID: uid, ROLE_CODE: role, STATUS: 'ACTIVE', CREATED_AT: n, UPDATED_AT: n });

}

function seedRole_(code, name, desc) {

 if (find_('ROLLEN', 'CODE', code)) return;

 const n = new Date();

 append_('ROLLEN', { ID: id_('ROLL'), CODE: code, NAAM: name, OMSCHRIJVING: desc, STATUS: 'ACTIVE', CREATED_AT: n, UPDATED_AT: n });

}

function auth_(token, allowExpired) {

 token = String(token || '');

 if (token.length < 16) return null;

 const h = hash_('session|' + token);

 const x = rows_('SESSIES').find(r => safeEqual_(r.data.TOKEN_HASH, h));

 if (!x || x.data.STATUS !== 'ACTIVE' || (!allowExpired && dateMs_(x.data.EXPIRES_AT) <= Date.now())) return null;

 return { userId: x.data.USER_ID, row: x.row };

}

function requireAuth_(t) { const a = auth_(t); if (!a) throw publicError_('Sessie verlopen.'); return a; }

function db_() { return SpreadsheetApp.openById(P12345.SPREADSHEET_ID); }

function ensureTab_(name, headers) {

 let sh = db_().getSheetByName(name);

 if (!sh) sh = db_().insertSheet(name);

 if (sh.getLastRow() === 0 && headers && headers.length > 0) {

   sh.getRange(1, 1, 1, headers.length).setValues([headers]);

   sh.setFrozenRows(1);

   sh.getRange(1, 1, 1, headers.length).setBackground('#0e6b56').setFontColor('#ffffff').setFontWeight('bold');

 }

 return sh;

}

function sheet_(n) { return db_().getSheetByName(n) || ensureTab_(n, P12345.TABS[n] || []); }

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

function rows_(n) {

 const sh = sheet_(n), v = sh.getDataRange().getValues();

 if (v.length < 2) return [];

 const h = v[0].map(String);

 return v.slice(1).map((r, i) => ({ row: i + 2, data: Object.fromEntries(h.map((k, j) => [k, r[j]])) }));

}

function table_(n) { return rows_(n).map(x => x.data); }

function append_(n, o) { const sh = sheet_(n), h = headers_(n); sh.appendRow(h.map(k => o[k] === undefined ? '' : o[k])); }

function updateRow_(n, row, o) {

 const sh = sheet_(n), h = headers_(n);

 Object.entries(o).forEach(([k, v]) => {

   let i = h.indexOf(k);

   if (i >= 0) sh.getRange(row, i + 1).setValue(v);

 });

}

function updateBy_(n, k, v, o) { const x = rows_(n).find(r => String(r.data[k]) === String(v)); if (x) updateRow_(n, x.row, o); }

function find_(n, k, v) { return table_(n).find(r => String(r[k]).toLowerCase() === String(v).toLowerCase()); }

function hash_(v) {

 const secret = PropertiesService.getScriptProperties().getProperty('PADELOS_SECRET') || 'PADELOS';

 return Utilities.base64EncodeWebSafe(Utilities.computeHmacSha256Signature(String(v), secret)).replace(/=+$/, '');

}

function token_() { return Utilities.base64EncodeWebSafe(Utilities.computeDigest(Utilities.DigestAlgorithm.SHA_256, Utilities.getUuid() + Date.now())).replace(/=+$/, ''); }

function safeEqual_(a, b) { a = String(a || ''); b = String(b || ''); if (a.length !== b.length) return false; let x = 0; for (let i = 0; i < a.length; i++) x |= a.charCodeAt(i) ^ b.charCodeAt(i); return x === 0; }

function id_(p) { return p + '-' + Utilities.getUuid().replace(/-/g, '').slice(0, 16).toUpperCase(); }

function dateMs_(v) { return v instanceof Date ? v.getTime() : new Date(v).getTime() || 0; }

function normalizeEmail_(v) { return String(v || '').trim().toLowerCase(); }

function validEmail_(v) { return /^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(v); }

function clean_(v, max) { return String(v || '').trim().slice(0, max); }

function publicError_(m) { return new Error(m); }