/** * SpectOcular Pilot Google Workflow Backend * * Deploy from admin@spectocularhealth.com inside the SpectOcular Google Workspace account. * This Workspace account has a signed BAA and MFA/2-Step Verification enabled. * Pilot principle: all PHI, master linkage, consent storage, invoices, and notifications remain inside admin@spectocularhealth.com. * Do not deploy from a consumer Gmail account. */ const ROOT_FOLDER_NAME = 'SpectOcular Pilot Database'; const CENTRAL_EMAIL = 'admin@spectocularhealth.com'; const INTERPRETER_NAME = 'Sai Chavala, MD'; const MONTHLY_SITE_STIPEND = 400; const FOLDERS = { MASTER: '00_Master_Database_RESTRICTED', SITES: '01_Sites', RIQ_REPORTS: '02_Retina_IQ_Reports', INVOICES: '03_Monthly_Invoices', AUDIT_EXPORTS: '04_Audit_Exports', TEMPLATES: '05_Templates', ADMIN: '99_Admin' }; const SHEETS = { CONFIG: 'Config', SITES: 'Sites', SUBJECTS: 'Subjects_Deidentified', LINKAGE: 'Patient_Linkage_RESTRICTED_MFA_REQUIRED', DOCUMENTS: 'Document_Uploads', RETINA_IQ_REQUESTS: 'Retina_IQ_Requests', RETINA_IQ_REPORTS: 'Retina_IQ_Reports', INVOICES: 'Monthly_Invoices', AGREEMENTS: 'Eyecare_Professional_Agreements', MONTHLY_ATTESTATIONS: 'Monthly_Attestations', CLINICAL_ENROLLMENTS: 'Clinical_Research_Enrollments', AUDIT: 'Audit_Log' }; function setupSpectOcularGoogleWorkflow() { const props = PropertiesService.getScriptProperties(); let root = props.getProperty('ROOT_FOLDER_ID') ? DriveApp.getFolderById(props.getProperty('ROOT_FOLDER_ID')) : DriveApp.createFolder(ROOT_FOLDER_NAME); props.setProperty('ROOT_FOLDER_ID', root.getId()); const folderMap = ensureRootFolders_(root); let ss = props.getProperty('DATABASE_SPREADSHEET_ID') ? SpreadsheetApp.openById(props.getProperty('DATABASE_SPREADSHEET_ID')) : SpreadsheetApp.create('SpectOcular_Master_Database_RESTRICTED_MFA_REQUIRED'); props.setProperty('DATABASE_SPREADSHEET_ID', ss.getId()); const dbFile = DriveApp.getFileById(ss.getId()); const masterFolder = DriveApp.getFolderById(folderMap[FOLDERS.MASTER]); try { masterFolder.addFile(dbFile); DriveApp.getRootFolder().removeFile(dbFile); } catch(e) {} ensureSheet_(ss, SHEETS.CONFIG, ['Key','Value','Notes']); setConfig_(ss, 'workspace_admin_account', CENTRAL_EMAIL, 'All pilot activity uses this account only.'); setConfig_(ss, 'baa_status', 'Signed', 'PHI may be stored only in covered Workspace services under SpectOcular control.'); setConfig_(ss, 'mfa_required', 'Yes', 'Master linkage database and Drive folders require MFA/2-Step Verification.'); setConfig_(ss, 'central_notification_email', CENTRAL_EMAIL, 'All automated notices stay inside SpectOcular Workspace.'); setConfig_(ss, 'interpreter', INTERPRETER_NAME, 'Retina IQ imaging interpreter during pilot.'); ensureSheet_(ss, SHEETS.SITES, [ 'SiteID','SiteName','PracticeName','PrimaryContactName','SiteEmail','Phone','Address','City','State','Zip', 'LastSubjectSequence','SiteFolderId','SiteFolderUrl','Active','CreatedAt','UpdatedAt' ]); ensureSheet_(ss, SHEETS.SUBJECTS, [ 'SubjectID','SiteID','SubjectSequence','ClinicPatientID_Deidentified','SubjectFolderId','SubjectFolderUrl', 'ConsentStatus','RetinaIQStatus','ImagingStatus','CreatedAt','UpdatedAt','CreatedBy' ]); ensureSheet_(ss, SHEETS.LINKAGE, [ 'SubjectID','SiteID','SubjectSequence','ClinicPatientID','PatientFirstName','PatientLastName','DOB','Phone','Email', 'Address','City','State','Zip','PreferredCommunication','PCPFirstName','PCPLastName','PCPAddress','PCPCity','PCPState','PCPZip','PCPPhoneFax', 'PharmacyName','PharmacyAddress','PharmacyCity','PharmacyState','PharmacyZip','PharmacyPhoneFax', 'SubjectFolderUrl','CreatedAt','UpdatedAt','CreatedBy','MFAControlNote' ]); ensureSheet_(ss, SHEETS.DOCUMENTS, [ 'DocumentID','SubjectID','SiteID','DocumentType','FileName','MimeType','FileId','FileUrl','UploadedAt','UploadedBy','Notes' ]); ensureSheet_(ss, SHEETS.RETINA_IQ_REQUESTS, [ 'RequestID','SiteID','SiteName','PracticeName','SiteEmail','SitePhone','ClinicPatientID_Deidentified','PatientName','DOB','PatientContact','PCPFirstName','PCPLastName','PCPPractice','PCPAddress','PCPCity','PCPState','PCPZip','PCPPhoneFax','RequestDate','Priority','DateOfScan','ModalitiesSubmitted','RequestingProfessional','RequestingRole','ProfessionalEmail','ProfessionalPhoneFax','MedicalHistory','OcularHistory','ClinicalQuestion','Interpreter','Status','RequestFolderId','RequestFolderUrl','CreatedAt','CreatedBy' ]); ensureSheet_(ss, SHEETS.RETINA_IQ_REPORTS, [ 'ReportID','SiteID','ClinicPatientID_Deidentified','DateOfScan','BillableImages','RequestingProfessional','Interpreter','ReportFileUrl','Status','CreatedAt','CreatedBy' ]); ensureSheet_(ss, SHEETS.INVOICES, [ 'InvoiceID','SiteID','InvoiceMonth','BillableImages','TierRate','RetinaIQCharge','MonthlyStipend','OffsetAmount','NetSitePayment','DueDate','InvoiceFileUrl','InvoiceEmail','Status','CreatedAt' ]); ensureSheet_(ss, SHEETS.AGREEMENTS, [ 'AgreementID','SiteID','ProfessionalName','LicenseType','LicenseNumber','NPI','Email','PracticeName','PracticeAddress','City','StateZip','Phone','EffectiveDate','SignatureDate','CompanySigner','CompanyEmail','SubmittedAt','Status','SignatureFileUrl','CompanySignatureFileUrl','Notes','Source' ]); ensureSheet_(ss, SHEETS.MONTHLY_ATTESTATIONS, [ 'AttestationID','SiteID','ProfessionalName','Email','PracticeName','MonthYear','SignatureDate','TypedSignature','SubmittedAt','AvailabilityCertification','ServicesCertification','CompensationCertification','MonthlyNotes','PdfFileUrl','EmailedToAdmin','Source' ]); ensureSheet_(ss, SHEETS.CLINICAL_ENROLLMENTS, [ 'EnrollmentID','SubjectID','SiteID','SiteName','PracticeName','SubjectSequence','ClinicPatientID','PatientFirstName','PatientLastName','DOB','PatientPhone','PatientEmail','PatientAddress','PatientCity','PatientState','PatientZip','PreferredCommunication','PCPFirstName','PCPLastName','PCPAddress','PCPCity','PCPState','PCPZip','PCPPhoneFax','PharmacyName','PharmacyAddress','PharmacyCity','PharmacyState','PharmacyZip','PharmacyPhoneFax','MedicalReleaseSelections','PatientSignatureDate','ConsentorName','ConsentorRole','ConsentorSignatureDate','ConsentProcessDateTime','PacketDate','IdentityDocumentType','IdentityVerified','NoIDRetained','OpportunityQuestions','VoluntaryAcknowledged','SignatureApplies','SubjectFolderUrl','UploadedPacketLink','SubmittedAt','CreatedBy','Source' ]); ensureSheet_(ss, SHEETS.AUDIT, ['Timestamp','Actor','Action','EntityType','EntityID','Details']); // Remove redundant PCP full-name columns from early alpha versions. // PCP names are now stored only as PCPFirstName and PCPLastName. [SHEETS.LINKAGE, SHEETS.RETINA_IQ_REQUESTS, SHEETS.CLINICAL_ENROLLMENTS].forEach(function(sheetName) { removeColumnsIfPresent_(ss.getSheetByName(sheetName), ['PCPFullName', 'PCPName']); }); seedPilotSites_(ss); props.setProperty('PORTAL_TOKEN', props.getProperty('PORTAL_TOKEN') || Utilities.getUuid()); logAudit_(CENTRAL_EMAIL, 'setup', 'System', 'setup', 'Google workflow initialized under admin-only pilot configuration'); return { rootFolderUrl: root.getUrl(), masterDatabaseUrl: ss.getUrl(), portalToken: props.getProperty('PORTAL_TOKEN'), centralEmail: CENTRAL_EMAIL, folders: Object.fromEntries(Object.entries(folderMap).map(([name,id]) => [name, DriveApp.getFolderById(id).getUrl()])) }; } function doGet(e) { return json_({ ok: true, service: 'SpectOcular Google Workflow', centralEmail: CENTRAL_EMAIL, time: new Date().toISOString() }); } function doPost(e) { try { const payload = JSON.parse(e.postData.contents || '{}'); assertAuthorized_(payload.token); const action = payload.action; if (action === 'createSubject') return json_(createSubject_(payload.data || {})); if (action === 'uploadFile') return json_(uploadFile_(payload.data || {})); if (action === 'logRetinaIQReport') return json_(logRetinaIQReport_(payload.data || {})); if (action === 'generateMonthlyInvoice') return json_(generateMonthlyInvoice_(payload.data || {})); if (action === 'getSiteSummary') return json_(getSiteSummary_(payload.data || {})); if (action === 'listSites') return json_(listSites_(payload.data || {})); if (action === 'upsertSite') return json_(upsertSite_(payload.data || {})); if (action === 'createRetinaIQRequest') return json_(createRetinaIQRequest_(payload.data || {})); if (action === 'uploadRetinaIQImaging') return json_(uploadRetinaIQImaging_(payload.data || {})); if (action === 'uploadRetinaIQConsentPacket') return json_(uploadRetinaIQConsentPacket_(payload.data || {})); if (action === 'saveAgreementInfo') return json_(saveAgreementInfo_(payload.data || {})); if (action === 'saveMonthlyAttestation') return json_(saveMonthlyAttestation_(payload.data || {})); if (action === 'saveStudyEnrollment') return json_(saveStudyEnrollment_(payload.data || {})); throw new Error('Unknown action: ' + action); } catch (err) { return json_({ ok: false, error: String(err && err.message ? err.message : err) }); } } function createSubject_(data) { const lock = LockService.getScriptLock(); lock.waitLock(30000); try { const ss = getDb_(); const siteId = cleanSiteId_(data.siteId || data.siteNumber || '01'); const site = getOrCreateSite_(ss, siteId, data); const nextSeq = Number(site.LastSubjectSequence || 0) + 1; const subjectSequence = pad5_(nextSeq); const subjectId = siteId + '-' + subjectSequence; const siteFolder = DriveApp.getFolderById(site.SiteFolderId); const subjectsFolder = ensureSubfolder_(siteFolder, 'Subjects'); const subjectFolder = subjectsFolder.createFolder(subjectId); ['01_Intake','02_Signed_Consents','03_Medical_Records','04_Labs','05_Retinal_Imaging_ZIP','06_Retina_IQ_Reports','07_Invoices','08_Audit_Log'].forEach(n => ensureSubfolder_(subjectFolder, n)); appendObject_(ss.getSheetByName(SHEETS.SUBJECTS), { SubjectID: subjectId, SiteID: siteId, SubjectSequence: subjectSequence, ClinicPatientID_Deidentified: data.clinicPatientIdentifier || data.clinicPatientId || '', SubjectFolderId: subjectFolder.getId(), SubjectFolderUrl: subjectFolder.getUrl(), ConsentStatus: 'Created - awaiting signed upload', RetinaIQStatus: '', ImagingStatus: '', CreatedAt: now_(), UpdatedAt: now_(), CreatedBy: data.createdBy || CENTRAL_EMAIL }); appendObject_(ss.getSheetByName(SHEETS.LINKAGE), { SubjectID: subjectId, SiteID: siteId, SubjectSequence: subjectSequence, ClinicPatientID: data.clinicPatientIdentifier || data.clinicPatientId || '', PatientFirstName: data.patientFirstName || '', PatientLastName: data.patientLastName || '', DOB: data.patientDOB || data.dob || '', Phone: data.patientPhone || '', Email: data.patientEmail || '', Address: data.patientStreetAddress || '', City: data.patientCity || '', State: data.patientState || '', Zip: data.patientZip || '', PreferredCommunication: data.preferredCommunication || '', PCPFirstName: data.pcpFirstName || '', PCPLastName: data.pcpLastName || '', PCPAddress: data.pcpAddress || '', PCPCity: data.pcpCity || '', PCPState: data.pcpState || '', PCPZip: data.pcpZip || '', PCPPhoneFax: data.pcpPhoneFax || '', PharmacyName: data.pharmacyName || '', PharmacyAddress: data.pharmacyAddress || '', PharmacyCity: data.pharmacyCity || '', PharmacyState: data.pharmacyState || '', PharmacyZip: data.pharmacyZip || '', PharmacyPhoneFax: data.pharmacyPhoneFax || '', SubjectFolderUrl: subjectFolder.getUrl(), CreatedAt: now_(), UpdatedAt: now_(), CreatedBy: data.createdBy || CENTRAL_EMAIL, MFAControlNote: 'Restricted admin Workspace account only; MFA/2SV required.' }); updateSiteSequence_(ss, siteId, nextSeq); logAudit_(data.createdBy || CENTRAL_EMAIL, 'createSubject', 'Subject', subjectId, 'Created subject folder and restricted linkage row'); return { ok: true, subjectId, subjectSequence, subjectFolderUrl: subjectFolder.getUrl(), siteFolderUrl: siteFolder.getUrl(), masterDatabaseUrl: ss.getUrl(), centralEmail: CENTRAL_EMAIL }; } finally { lock.releaseLock(); } } function saveStudyEnrollment_(data) { const ss = getDb_(); const sheet = ensureSheet_(ss, SHEETS.CLINICAL_ENROLLMENTS, [ 'EnrollmentID','SubjectID','SiteID','SiteName','PracticeName','SubjectSequence','ClinicPatientID','PatientFirstName','PatientLastName','DOB','PatientPhone','PatientEmail','PatientAddress','PatientCity','PatientState','PatientZip','PreferredCommunication','PCPFirstName','PCPLastName','PCPAddress','PCPCity','PCPState','PCPZip','PCPPhoneFax','PharmacyName','PharmacyAddress','PharmacyCity','PharmacyState','PharmacyZip','PharmacyPhoneFax','MedicalReleaseSelections','PatientSignatureDate','ConsentorName','ConsentorRole','ConsentorSignatureDate','ConsentProcessDateTime','PacketDate','IdentityDocumentType','IdentityVerified','NoIDRetained','OpportunityQuestions','VoluntaryAcknowledged','SignatureApplies','SubjectFolderUrl','UploadedPacketLink','SubmittedAt','CreatedBy','Source' ]); let subjectId = String(data.subjectId || data.generatedSubjectId || '').trim(); let subjectFolderUrl = data.packetFolderUrl || ''; let subjectSequence = data.subjectSequence || ''; if (!subjectId || !findRowObject_(ss.getSheetByName(SHEETS.SUBJECTS), 'SubjectID', subjectId)) { const created = createSubject_(data); subjectId = created.subjectId; subjectSequence = created.subjectSequence; subjectFolderUrl = created.subjectFolderUrl; } else { const subjectRow = findRowObject_(ss.getSheetByName(SHEETS.SUBJECTS), 'SubjectID', subjectId); subjectFolderUrl = subjectFolderUrl || (subjectRow ? subjectRow.SubjectFolderUrl : ''); subjectSequence = subjectSequence || (subjectRow ? subjectRow.SubjectSequence : ''); } const siteId = cleanSiteId_(data.siteId || data.siteNumber || (subjectId ? subjectId.split('-')[0] : '01')); const enrollmentId = 'ENR-' + Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'yyyyMMdd-HHmmss') + '-' + Utilities.getUuid().slice(0,8); const patientSignatureDate = data.patientESignDateTime || data.signatureDate || data.packetDate || ''; const consentorSignatureDate = data.consentorESignDateTime || data.consentProcessDateTime || data.packetDate || ''; appendObject_(sheet, { EnrollmentID: enrollmentId, SubjectID: subjectId, SiteID: siteId, SiteName: data.siteName || '', PracticeName: data.practiceName || '', SubjectSequence: subjectSequence || data.subjectSequence || '', ClinicPatientID: data.clinicPatientIdentifier || data.clinicPatientId || data.clinicId || '', PatientFirstName: data.patientFirstName || '', PatientLastName: data.patientLastName || '', DOB: data.patientDOB || data.dob || '', PatientPhone: data.patientPhone || '', PatientEmail: data.patientEmail || '', PatientAddress: data.patientStreetAddress || '', PatientCity: data.patientCity || '', PatientState: data.patientState || '', PatientZip: data.patientZip || '', PreferredCommunication: data.preferredCommunication || '', PCPFirstName: data.pcpFirstName || '', PCPLastName: data.pcpLastName || '', PCPAddress: data.pcpAddress || '', PCPCity: data.pcpCity || '', PCPState: data.pcpState || '', PCPZip: data.pcpZip || '', PCPPhoneFax: data.pcpPhoneFax || '', PharmacyName: data.pharmacyName || '', PharmacyAddress: data.pharmacyAddress || '', PharmacyCity: data.pharmacyCity || '', PharmacyState: data.pharmacyState || '', PharmacyZip: data.pharmacyZip || '', PharmacyPhoneFax: data.pharmacyPhoneFax || '', MedicalReleaseSelections: data.workflowMedicalReleaseSelections || data.release || '', PatientSignatureDate: patientSignatureDate, ConsentorName: data.consentObtainerName || data.consentorName || [data.consentObtainerFirstName, data.consentObtainerLastName].filter(Boolean).join(' '), ConsentorRole: data.consentObtainerRole || data.siteStaffRole || '', ConsentorSignatureDate: consentorSignatureDate, ConsentProcessDateTime: data.consentProcessDateTime || '', PacketDate: data.packetDate || '', IdentityDocumentType: data.identityDocumentType || '', IdentityVerified: data.identity_verified || data.identityVerified || '', NoIDRetained: data.no_id_retained || data.noIdRetained || '', OpportunityQuestions: data.opportunity_questions || data.opportunityQuestions || '', VoluntaryAcknowledged: data.voluntary_acknowledged || data.voluntaryAcknowledged || '', SignatureApplies: data.signature_applies_to_documents || data.signatureApplies || '', SubjectFolderUrl: subjectFolderUrl, UploadedPacketLink: data.uploadedPacketLink || '', SubmittedAt: now_(), CreatedBy: data.createdBy || data.consentObtainerName || CENTRAL_EMAIL, Source: data.source || 'Authorized Study Enrollment Workflow' }); logAudit_(data.createdBy || CENTRAL_EMAIL, 'saveStudyEnrollment', 'ClinicalResearchEnrollment', enrollmentId, 'Clinical research enrollment information saved to master database'); return { ok: true, enrollmentId, subjectId, siteId, subjectSequence, subjectFolderUrl, masterDatabaseUrl: ss.getUrl(), signatureDate: patientSignatureDate, centralEmail: CENTRAL_EMAIL }; } function uploadFile_(data) { const subjectId = String(data.subjectId || '').trim(); if (!subjectId) throw new Error('Missing subjectId'); if (!data.base64) throw new Error('Missing file base64 payload'); const ss = getDb_(); const subject = findRowObject_(ss.getSheetByName(SHEETS.SUBJECTS), 'SubjectID', subjectId); if (!subject) throw new Error('Subject not found: ' + subjectId); const subjectFolder = DriveApp.getFolderById(subject.SubjectFolderId); const documentType = data.documentType || 'Other'; const folderName = folderForDocumentType_(documentType); const destFolder = ensureSubfolder_(subjectFolder, folderName); const bytes = Utilities.base64Decode(data.base64); const safeName = safeFileName_((data.fileName || 'upload.pdf')); const fileName = subjectId + '_' + safeName; const blob = Utilities.newBlob(bytes, data.mimeType || 'application/octet-stream', fileName); const file = destFolder.createFile(blob); const docId = Utilities.getUuid(); appendObject_(ss.getSheetByName(SHEETS.DOCUMENTS), { DocumentID: docId, SubjectID: subjectId, SiteID: subject.SiteID, DocumentType: documentType, FileName: fileName, MimeType: data.mimeType || '', FileId: file.getId(), FileUrl: file.getUrl(), UploadedAt: now_(), UploadedBy: data.uploadedBy || CENTRAL_EMAIL, Notes: data.notes || '' }); logAudit_(data.uploadedBy || CENTRAL_EMAIL, 'uploadFile', 'Document', docId, documentType + ' uploaded for ' + subjectId); return { ok: true, documentId: docId, fileUrl: file.getUrl(), subjectFolderUrl: subject.SubjectFolderUrl }; } function logRetinaIQReport_(data) { const ss = getDb_(); const reportId = data.reportId || ('RIQ-' + Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'yyyyMMdd-HHmmss') + '-' + Math.floor(Math.random()*1000)); appendObject_(ss.getSheetByName(SHEETS.RETINA_IQ_REPORTS), { ReportID: reportId, SiteID: cleanSiteId_(data.siteId || '01'), ClinicPatientID_Deidentified: data.clinicPatientId || data.clinicPatientIdentifier || '', DateOfScan: data.dateOfScan || '', BillableImages: Number(data.billableImages || 1), RequestingProfessional: data.requestingProfessional || '', Interpreter: INTERPRETER_NAME, ReportFileUrl: data.reportFileUrl || '', Status: data.status || 'Generated', CreatedAt: now_(), CreatedBy: data.createdBy || CENTRAL_EMAIL }); logAudit_(data.createdBy || CENTRAL_EMAIL, 'logRetinaIQReport', 'RetinaIQReport', reportId, 'BillableImages=' + (data.billableImages || 1)); return { ok: true, reportId }; } function generateMonthlyInvoice_(data) { const ss = getDb_(); const siteId = cleanSiteId_(data.siteId || '01'); const month = data.invoiceMonth || Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'yyyy-MM'); const reports = getRows_(ss.getSheetByName(SHEETS.RETINA_IQ_REPORTS)).filter(r => r.SiteID === siteId && String(r.DateOfScan || r.CreatedAt || '').slice(0,7) === month); const billableImages = reports.reduce((sum, r) => sum + Number(r.BillableImages || 0), 0); const tierRate = tierRate_(billableImages); const charge = billableImages * tierRate; const stipend = MONTHLY_SITE_STIPEND; const offset = Math.min(charge, stipend); const netSitePayment = stipend - offset; const dueDate = Utilities.formatDate(new Date(new Date().getTime() + 30*24*3600*1000), Session.getScriptTimeZone(), 'yyyy-MM-dd'); const site = findRowObject_(ss.getSheetByName(SHEETS.SITES), 'SiteID', siteId) || {}; const invoiceId = `RIQ-${siteId}-${month}`; const invoiceFolder = ensureSiteInvoiceFolder_(siteId); const doc = DocumentApp.create(invoiceId + ' Monthly Retina IQ Invoice'); const body = doc.getBody(); body.appendParagraph('Retina IQ Monthly Activity + Offset Statement').setHeading(DocumentApp.ParagraphHeading.HEADING1); body.appendParagraph('Invoice ID: ' + invoiceId); body.appendParagraph('Site: ' + siteId + ' ' + (site.SiteName || site.PracticeName || '')); body.appendParagraph('Invoice month: ' + month); body.appendParagraph('Payment due / offset reconciliation: net 30'); body.appendParagraph('Interpreter: ' + INTERPRETER_NAME); body.appendParagraph('Central SpectOcular account: ' + CENTRAL_EMAIL); body.appendParagraph(''); body.appendParagraph('Monthly activity is calculated based on billable images reviewed. The Retina IQ interpretation charge is offset against the $400 monthly site consulting payment under the applicable site agreement. This invoice contains de-identified clinic/patient identifiers only.'); const table = body.appendTable([['Date of Scan','De-identified Clinic ID','Report ID','Billable Images'], ...reports.map(r => [String(r.DateOfScan||''), String(r.ClinicPatientID_Deidentified||''), String(r.ReportID||''), String(r.BillableImages||'')])]); table.setBorderWidth(0.5); body.appendParagraph(''); body.appendParagraph('Billable images: ' + billableImages); body.appendParagraph('Tier rate: $' + tierRate.toFixed(2) + ' per image'); body.appendParagraph('Retina IQ charge: $' + charge.toFixed(2)); body.appendParagraph('Monthly site payment: $400.00'); body.appendParagraph('Offset/deduction from $400 site payment: $' + offset.toFixed(2)); body.appendParagraph('Net monthly payment to site after offset: $' + netSitePayment.toFixed(2)); doc.saveAndClose(); const pdf = DriveApp.getFileById(doc.getId()).getAs(MimeType.PDF).setName(invoiceId + '.pdf'); const pdfFile = invoiceFolder.createFile(pdf); DriveApp.getFileById(doc.getId()).setTrashed(true); appendObject_(ss.getSheetByName(SHEETS.INVOICES), { InvoiceID: invoiceId, SiteID: siteId, InvoiceMonth: month, BillableImages: billableImages, TierRate: tierRate, RetinaIQCharge: charge, MonthlyStipend: stipend, OffsetAmount: offset, NetSitePayment: netSitePayment, DueDate: dueDate, InvoiceFileUrl: pdfFile.getUrl(), InvoiceEmail: site.SiteEmail || CENTRAL_EMAIL, Status: 'Generated', CreatedAt: now_() }); if (data.emailInvoice && site.SiteEmail) { MailApp.sendEmail({ to: site.SiteEmail, cc: CENTRAL_EMAIL, subject: invoiceId + ' Retina IQ monthly activity + offset statement', body: 'Attached is the monthly Retina IQ activity and offset statement for Site ' + siteId + '. Payment/offset reconciliation is net 30. No patient names are included.', attachments: [pdfFile.getBlob()] }); } logAudit_(data.createdBy || CENTRAL_EMAIL, 'generateMonthlyInvoice', 'Invoice', invoiceId, 'BillableImages=' + billableImages + '; Charge=' + charge); return { ok: true, invoiceId, invoiceFileUrl: pdfFile.getUrl(), billableImages, tierRate, charge, offset, netSitePayment, dueDate }; } function getSiteSummary_(data) { const ss = getDb_(); const siteId = cleanSiteId_(data.siteId || '01'); const site = findRowObject_(ss.getSheetByName(SHEETS.SITES), 'SiteID', siteId); return { ok: true, siteId, site: site || null, databaseUrl: ss.getUrl(), centralEmail: CENTRAL_EMAIL }; } function seedPilotSites_(ss) { upsertSite_({ siteId: '01', siteName: 'Krishnan OD - Memorial Houston', practiceName: 'Krishnan OD - Memorial Houston', primaryContactName: 'Supriya Krishnan, OD', requestingProfessional: 'Supriya Krishnan, OD', siteEmail: 'drskrishnan16@gmail.com', phone: '281-741-7295', sitePhone: '281-741-7295', address: '14634 Memorial Dr.', siteAddress: '14634 Memorial Dr.', city: 'Houston', siteCity: 'Houston', state: 'TX', siteState: 'TX', zip: '77079', siteZip: '77079', active: 'Yes', createdBy: CENTRAL_EMAIL }); } function listSites_(data) { const ss = getDb_(); const rows = getRows_(ss.getSheetByName(SHEETS.SITES)).filter(r => String(r.Active || 'Yes').toLowerCase() !== 'no'); return { ok: true, sites: rows, centralEmail: CENTRAL_EMAIL }; } function upsertSite_(data) { const ss = getDb_(); const sheet = ss.getSheetByName(SHEETS.SITES); const siteId = cleanSiteId_(data.siteId || data.SiteID || '01'); const root = getRoot_(); const sitesFolder = ensureSubfolder_(root, FOLDERS.SITES); const values = sheet.getDataRange().getValues(); const headers = values[0]; let rowNum = -1; const idCol = headers.indexOf('SiteID') + 1; for (let r = 2; r <= values.length; r++) { if (String(sheet.getRange(r, idCol).getValue()) === siteId) { rowNum = r; break; } } let folderId = data.SiteFolderId || data.siteFolderId || ''; let existing = rowNum > 0 ? Object.fromEntries(headers.map((h,i)=>[h, sheet.getRange(rowNum,i+1).getValue()])) : null; if (!folderId && existing && existing.SiteFolderId) folderId = existing.SiteFolderId; let siteFolder; if (folderId) { siteFolder = DriveApp.getFolderById(folderId); } else { siteFolder = sitesFolder.createFolder('Site ' + siteId + ' - ' + (data.siteName || data.practiceName || data.SiteName || data.PracticeName || 'Practice')); } const obj = { SiteID: siteId, SiteName: data.siteName || data.SiteName || existing?.SiteName || data.practiceName || '', PracticeName: data.practiceName || data.PracticeName || existing?.PracticeName || data.siteName || '', PrimaryContactName: data.primaryContactName || data.PrimaryContactName || data.requestingProfessional || existing?.PrimaryContactName || '', SiteEmail: data.siteEmail || data.SiteEmail || data.professionalEmail || existing?.SiteEmail || '', Phone: data.phone || data.Phone || data.sitePhone || data.professionalPhoneFax || existing?.Phone || '', Address: data.address || data.Address || data.siteAddress || existing?.Address || '', City: data.city || data.City || data.siteCity || existing?.City || '', State: data.state || data.State || data.siteState || existing?.State || '', Zip: data.zip || data.Zip || data.siteZip || existing?.Zip || '', LastSubjectSequence: existing?.LastSubjectSequence || data.lastSubjectSequence || 0, SiteFolderId: siteFolder.getId(), SiteFolderUrl: siteFolder.getUrl(), Active: data.active || data.Active || 'Yes', CreatedAt: existing?.CreatedAt || now_(), UpdatedAt: now_() }; if (rowNum > 0) { headers.forEach((h,i)=>{ if (obj[h] !== undefined) sheet.getRange(rowNum, i+1).setValue(obj[h]); }); } else { appendObject_(sheet, obj); } logAudit_(data.createdBy || CENTRAL_EMAIL, 'upsertSite', 'Site', siteId, 'Site/provider saved or updated'); return { ok: true, site: obj }; } function createRetinaIQRequest_(data) { const ss = getDb_(); const requestId = data.id || ('RIQREQ-' + Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'yyyyMMdd-HHmmss') + '-' + Math.floor(Math.random()*1000)); const siteId = cleanSiteId_(data.siteId || '01'); const root = getRoot_(); const riqRoot = ensureSubfolder_(root, FOLDERS.RIQ_REPORTS); const requestsRoot = ensureSubfolder_(riqRoot, 'Retina_IQ_Requests'); const siteRequestFolder = ensureSubfolder_(requestsRoot, 'Site ' + siteId); const requestFolder = ensureSubfolder_(siteRequestFolder, safeFileName_(requestId + '_' + (data.clinicPatientId || 'clinic-id'))); upsertSite_({ siteId: siteId, siteName: data.siteName || data.practiceName || '', practiceName: data.practiceName || data.siteName || '', primaryContactName: data.requestingProfessional || '', siteEmail: data.siteEmail || data.professionalEmail || '', phone: data.sitePhone || data.professionalPhoneFax || '', address: data.siteAddress || data.address || '', city: data.siteCity || data.city || '', state: data.siteState || data.state || '', zip: data.siteZip || data.zip || '', createdBy: data.requestingProfessional || CENTRAL_EMAIL }); appendObject_(ss.getSheetByName(SHEETS.RETINA_IQ_REQUESTS), { RequestID: requestId, SiteID: siteId, SiteName: data.siteName || '', PracticeName: data.practiceName || '', SiteEmail: data.siteEmail || data.professionalEmail || '', SitePhone: data.sitePhone || data.professionalPhoneFax || '', ClinicPatientID_Deidentified: data.clinicPatientId || data.clinicPatientIdentifier || '', PatientName: data.patientName || '', DOB: data.patientDOB || '', PatientContact: data.patientContact || '', PCPFirstName: data.pcpFirstName || '', PCPLastName: data.pcpLastName || '', PCPPractice: data.pcpPractice || '', PCPAddress: data.pcpAddress || '', PCPCity: data.pcpCity || '', PCPState: data.pcpState || '', PCPZip: data.pcpZip || '', PCPPhoneFax: data.pcpPhoneFax || '', RequestDate: data.requestDate || now_().slice(0,10), Priority: data.priority || '', DateOfScan: data.scanDate || data.dateOfScan || '', ModalitiesSubmitted: data.modalitiesSubmitted || '', RequestingProfessional: data.requestingProfessional || '', RequestingRole: data.requestingRole || '', ProfessionalEmail: data.professionalEmail || '', ProfessionalPhoneFax: data.professionalPhoneFax || '', MedicalHistory: data.medicalHistory || '', OcularHistory: data.ocularHistory || '', ClinicalQuestion: data.clinicalQuestion || '', Interpreter: INTERPRETER_NAME, Status: 'Requested - awaiting interpretation', RequestFolderId: requestFolder.getId(), RequestFolderUrl: requestFolder.getUrl(), CreatedAt: now_(), CreatedBy: data.requestingProfessional || CENTRAL_EMAIL }); MailApp.sendEmail({ to: CENTRAL_EMAIL, subject: 'Retina IQ request received - ' + requestId, body: 'A Retina IQ request was submitted to the SpectOcular pilot backend.\n\nRequest ID: ' + requestId + '\nSite ID: ' + siteId + '\nPractice: ' + (data.practiceName || '') + '\nRequesting Professional: ' + (data.requestingProfessional || '') + '\nClinic Patient ID / MRN: ' + (data.clinicPatientId || '') + '\nScan Date: ' + (data.scanDate || '') + '\n\nPlease log in as admin@spectocularhealth.com to review. Interpreter: ' + INTERPRETER_NAME }); logAudit_(data.requestingProfessional || CENTRAL_EMAIL, 'createRetinaIQRequest', 'RetinaIQRequest', requestId, 'Request logged for Site ' + siteId); return { ok: true, requestId, requestFolderUrl: requestFolder.getUrl(), centralEmail: CENTRAL_EMAIL }; } function uploadRetinaIQImaging_(data) { const requestId = String(data.requestId || '').trim(); if (!requestId) throw new Error('Missing requestId'); if (!data.base64) throw new Error('Missing file base64 payload'); const ss = getDb_(); const reqSheet = ss.getSheetByName(SHEETS.RETINA_IQ_REQUESTS); const request = findRowObject_(reqSheet, 'RequestID', requestId); let destFolder; if (request && request.RequestFolderId) { destFolder = DriveApp.getFolderById(request.RequestFolderId); } else { const siteId = cleanSiteId_(data.siteId || (request && request.SiteID) || '01'); const riqRoot = ensureSubfolder_(getRoot_(), FOLDERS.RIQ_REPORTS); const requestsRoot = ensureSubfolder_(riqRoot, 'Retina_IQ_Requests'); const siteRequestFolder = ensureSubfolder_(requestsRoot, 'Site ' + siteId); destFolder = ensureSubfolder_(siteRequestFolder, safeFileName_(requestId + '_' + (data.clinicPatientId || 'clinic-id'))); } const imagingFolder = ensureSubfolder_(destFolder, 'Imaging_ZIP'); const bytes = Utilities.base64Decode(data.base64); const safeName = safeFileName_(data.fileName || 'retina-iq-imaging.zip'); const fileName = requestId + '_' + safeName; const blob = Utilities.newBlob(bytes, data.mimeType || 'application/zip', fileName); const file = imagingFolder.createFile(blob); const docId = Utilities.getUuid(); appendObject_(ss.getSheetByName(SHEETS.DOCUMENTS), { DocumentID: docId, SubjectID: requestId, SiteID: cleanSiteId_(data.siteId || (request && request.SiteID) || '01'), DocumentType: 'Retina IQ Imaging ZIP', FileName: fileName, MimeType: data.mimeType || 'application/zip', FileId: file.getId(), FileUrl: file.getUrl(), UploadedAt: now_(), UploadedBy: data.uploadedBy || CENTRAL_EMAIL, Notes: data.notes || 'Retina IQ imaging ZIP upload' }); logAudit_(data.uploadedBy || CENTRAL_EMAIL, 'uploadRetinaIQImaging', 'RetinaIQRequest', requestId, 'Imaging ZIP uploaded for Retina IQ request'); return { ok: true, requestId: requestId, documentId: docId, fileUrl: file.getUrl(), requestFolderUrl: destFolder.getUrl() }; } function uploadRetinaIQConsentPacket_(data) { const requestId = String(data.requestId || '').trim(); if (!requestId) throw new Error('Missing requestId'); if (!data.base64) throw new Error('Missing file base64 payload'); const ss = getDb_(); const reqSheet = ss.getSheetByName(SHEETS.RETINA_IQ_REQUESTS); const request = findRowObject_(reqSheet, 'RequestID', requestId); let destFolder; if (request && request.RequestFolderId) { destFolder = DriveApp.getFolderById(request.RequestFolderId); } else { const siteId = cleanSiteId_(data.siteId || (request && request.SiteID) || '01'); const riqRoot = ensureSubfolder_(getRoot_(), FOLDERS.RIQ_REPORTS); const requestsRoot = ensureSubfolder_(riqRoot, 'Retina_IQ_Requests'); const siteRequestFolder = ensureSubfolder_(requestsRoot, 'Site ' + siteId); destFolder = ensureSubfolder_(siteRequestFolder, safeFileName_(requestId + '_' + (data.clinicPatientId || 'clinic-id'))); } const consentFolder = ensureSubfolder_(destFolder, 'Signed_Consent_and_Medical_Release'); const bytes = Utilities.base64Decode(data.base64); const safeName = safeFileName_(data.fileName || 'retina-iq-consent-medical-release.pdf'); const fileName = requestId + '_' + safeName; const blob = Utilities.newBlob(bytes, data.mimeType || 'application/pdf', fileName); const file = consentFolder.createFile(blob); const docId = Utilities.getUuid(); appendObject_(ss.getSheetByName(SHEETS.DOCUMENTS), { DocumentID: docId, SubjectID: requestId, SiteID: cleanSiteId_(data.siteId || (request && request.SiteID) || '01'), DocumentType: 'Retina IQ Patient Consent and Medical Release', FileName: fileName, MimeType: data.mimeType || 'application/pdf', FileId: file.getId(), FileUrl: file.getUrl(), UploadedAt: now_(), UploadedBy: data.uploadedBy || CENTRAL_EMAIL, Notes: data.notes || 'Retina IQ signed patient consent and medical release upload' }); logAudit_(data.uploadedBy || CENTRAL_EMAIL, 'uploadRetinaIQConsentPacket', 'RetinaIQRequest', requestId, 'Signed consent and medical release uploaded for Retina IQ request'); return { ok: true, requestId: requestId, documentId: docId, fileUrl: file.getUrl(), requestFolderUrl: destFolder.getUrl() }; } function saveAgreementInfo_(data) { const ss = getDb_(); ensureSheet_(ss, SHEETS.AGREEMENTS, [ 'AgreementID','SiteID','ProfessionalName','LicenseType','LicenseNumber','NPI','Email','PracticeName','PracticeAddress','City','StateZip','Phone','EffectiveDate','SignatureDate','CompanySigner','CompanyEmail','SubmittedAt','Status','SignatureFileUrl','CompanySignatureFileUrl','Notes','Source' ]); const professionalName = String(data.professionalName || '').trim(); const email = String(data.email || '').trim(); const practiceName = String(data.practiceName || '').trim(); if (!professionalName) throw new Error('Missing eyecare professional name.'); if (!email) throw new Error('Missing eyecare professional email.'); const siteId = findOrCreateAgreementSite_(ss, data); const agreementId = 'AGR-' + siteId + '-' + Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'yyyyMMdd-HHmmss'); const agreementFolder = ensureAgreementFolder_(siteId, practiceName || professionalName); let signatureFileUrl = ''; let companySignatureFileUrl = ''; if (data.signatureData) signatureFileUrl = saveSignaturePng_(agreementFolder, agreementId + '_eyecare_professional_signature.png', data.signatureData); if (data.companySignatureData) companySignatureFileUrl = saveSignaturePng_(agreementFolder, agreementId + '_spectocular_signature.png', data.companySignatureData); const signatureDate = data.signatureDate || data.effectiveDate || Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'yyyy-MM-dd'); appendObject_(ss.getSheetByName(SHEETS.AGREEMENTS), { AgreementID: agreementId, SiteID: siteId, ProfessionalName: professionalName, LicenseType: data.licenseType || '', LicenseNumber: data.licenseNumber || '', NPI: data.npi || '', Email: email, PracticeName: practiceName, PracticeAddress: data.practiceAddress || '', City: data.city || '', StateZip: data.stateZip || '', Phone: data.phone || '', EffectiveDate: data.effectiveDate || '', SignatureDate: signatureDate, CompanySigner: data.companySigner || '', CompanyEmail: data.companyEmail || '', SubmittedAt: now_(), Status: 'Submitted electronically', SignatureFileUrl: signatureFileUrl, CompanySignatureFileUrl: companySignatureFileUrl, Notes: data.notes || '', Source: data.source || 'SpectOcular partner agreement webpage' }); logAudit_(email || CENTRAL_EMAIL, 'saveAgreementInfo', 'Agreement', agreementId, 'Partner agreement information saved to master database'); return { ok: true, agreementId: agreementId, siteId: siteId, signatureDate: signatureDate, masterDatabaseUrl: ss.getUrl(), agreementFolderUrl: agreementFolder.getUrl() }; } function findOrCreateAgreementSite_(ss, data) { const sitesSheet = ss.getSheetByName(SHEETS.SITES); const rows = getRows_(sitesSheet); const email = String(data.email || '').trim().toLowerCase(); const professionalName = String(data.professionalName || '').trim().toLowerCase(); const practiceName = String(data.practiceName || '').trim().toLowerCase(); let existing = rows.find(r => String(r.SiteEmail || '').trim().toLowerCase() === email); if (!existing && practiceName) existing = rows.find(r => String(r.PracticeName || '').trim().toLowerCase() === practiceName); if (!existing && professionalName) existing = rows.find(r => String(r.PrimaryContactName || '').trim().toLowerCase() === professionalName); const siteId = existing && existing.SiteID ? cleanSiteId_(existing.SiteID) : nextSiteId_(rows); upsertSite_({ siteId: siteId, siteName: data.practiceName || data.professionalName || ('Site ' + siteId), practiceName: data.practiceName || '', primaryContactName: data.professionalName || '', siteEmail: data.email || '', phone: data.phone || '', address: data.practiceAddress || '', city: data.city || '', state: parseStateZip_(data.stateZip).state, zip: parseStateZip_(data.stateZip).zip, active: 'Yes' }); return siteId; } function nextSiteId_(rows) { let maxId = 0; rows.forEach(r => { const n = Number(String(r.SiteID || '').replace(/\D/g,'')); if (n > maxId) maxId = n; }); return cleanSiteId_(maxId + 1); } function saveMonthlyAttestation_(data) { const ss = getDb_(); const sheet = ensureSheet_(ss, SHEETS.MONTHLY_ATTESTATIONS, [ 'AttestationID','SiteID','ProfessionalName','Email','PracticeName','MonthYear','SignatureDate','TypedSignature','SubmittedAt','AvailabilityCertification','ServicesCertification','CompensationCertification','MonthlyNotes','PdfFileUrl','EmailedToAdmin','Source' ]); const attestationId = 'ATT-' + Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'yyyyMMdd-HHmmss') + '-' + Utilities.getUuid().slice(0,8).toUpperCase(); const siteId = resolveAttestationSiteId_(ss, data); const folder = ensureMonthlyAttestationFolder_(siteId, data.practiceName || data.professionalName || 'Practice'); const pdf = createMonthlyAttestationPdf_(folder, data, attestationId, siteId); const emailAdmin = String(data.emailPdf || '').toLowerCase() === 'true' || data.emailPdf === true; if (emailAdmin) { MailApp.sendEmail({ to: CENTRAL_EMAIL, subject: 'SpectOcular Monthly Attestation - ' + (data.professionalName || 'Eyecare Professional') + ' - ' + (data.monthYear || ''), body: 'A monthly eyecare professional attestation has been completed and saved to the SpectOcular Google Drive master workflow.\n\n' + 'Professional: ' + (data.professionalName || '') + '\n' + 'Practice: ' + (data.practiceName || '') + '\n' + 'Month/Year: ' + (data.monthYear || '') + '\n' + 'Signature date: ' + (data.signatureDate || '') + '\n' + 'PDF: ' + pdf.url + '\n', attachments: [pdf.blob] }); } appendObject_(sheet, { AttestationID: attestationId, SiteID: siteId, ProfessionalName: data.professionalName || '', Email: data.email || '', PracticeName: data.practiceName || '', MonthYear: data.monthYear || '', SignatureDate: data.signatureDate || '', TypedSignature: data.typedSignature || '', SubmittedAt: now_(), AvailabilityCertification: data.availabilityCertification ? 'Yes' : '', ServicesCertification: data.servicesCertification ? 'Yes' : '', CompensationCertification: data.compensationCertification ? 'Yes' : '', MonthlyNotes: data.monthlyNotes || '', PdfFileUrl: pdf.url, EmailedToAdmin: emailAdmin ? CENTRAL_EMAIL : '', Source: data.source || 'SpectOcular monthly attestation webpage' }); logAudit_(data.email || CENTRAL_EMAIL, emailAdmin ? 'saveAndEmailMonthlyAttestation' : 'saveMonthlyAttestation', 'MonthlyAttestation', attestationId, 'Monthly attestation saved to master database'); return { ok: true, attestationId: attestationId, siteId: siteId, pdfFileUrl: pdf.url, emailedToAdmin: emailAdmin ? CENTRAL_EMAIL : '' }; } function resolveAttestationSiteId_(ss, data) { const requested = String(data.siteId || data.siteID || '').replace(/\D/g,''); if (requested) return requested.padStart(2,'0').slice(-2); const email = String(data.email || '').trim().toLowerCase(); const practice = String(data.practiceName || '').trim().toLowerCase(); const professional = String(data.professionalName || '').trim().toLowerCase(); const rows = getRows_(ss.getSheetByName(SHEETS.SITES)); const found = rows.find(r => (email && String(r.SiteEmail || '').trim().toLowerCase() === email) || (practice && String(r.PracticeName || '').trim().toLowerCase() === practice) || (professional && String(r.PrimaryContactName || '').trim().toLowerCase() === professional) ); if (found && found.SiteID) return cleanSiteId_(found.SiteID); return ''; } function ensureMonthlyAttestationFolder_(siteId, label) { const root = getRoot_(); const adminFolder = ensureSubfolder_(root, FOLDERS.ADMIN); const attFolder = ensureSubfolder_(adminFolder, 'Monthly_Attestations'); const siteLabel = siteId ? ('Site ' + siteId + ' - ' + safeFileName_(label || 'Practice')) : ('Unassigned - ' + safeFileName_(label || 'Practice')); return ensureSubfolder_(attFolder, siteLabel); } function createMonthlyAttestationPdf_(folder, data, attestationId, siteId) { const title = 'SpectOcular Monthly Attestation - ' + safeFileName_(data.professionalName || 'Professional') + ' - ' + safeFileName_(data.monthYear || '') + ' - ' + attestationId; const doc = DocumentApp.create(title); const body = doc.getBody(); body.setMarginTop(54).setMarginBottom(54).setMarginLeft(54).setMarginRight(54); body.appendParagraph('SPECTOCULAR MONTHLY EYECARE PROFESSIONAL ATTESTATION').setHeading(DocumentApp.ParagraphHeading.HEADING1); body.appendParagraph('Attestation ID: ' + attestationId).setFontSize(9); body.appendParagraph('Site ID: ' + (siteId || 'Unassigned')).setFontSize(9); body.appendParagraph(''); const table = body.appendTable([ ['Eyecare Professional Name', data.professionalName || ''], ['E-mail', data.email || ''], ['Practice / Entity', data.practiceName || ''], ['Month / Year', data.monthYear || ''], ['Typed Electronic Signature', data.typedSignature || ''], ['Signature Date', data.signatureDate || ''], ['Submitted Timestamp', now_()] ]); table.setBorderWidth(0.5); for (let i=0;i= 0 ? raw.split(',').pop() : raw; if (!base64) return ''; const blob = Utilities.newBlob(Utilities.base64Decode(base64), 'image/png', safeFileName_(fileName)); return folder.createFile(blob).getUrl(); } function assertAuthorized_(token) { const expected = PropertiesService.getScriptProperties().getProperty('PORTAL_TOKEN'); if (!expected) throw new Error('PORTAL_TOKEN is not configured. Run setupSpectOcularGoogleWorkflow first.'); if (!token || token !== expected) throw new Error('Unauthorized request'); } function getDb_() { return SpreadsheetApp.openById(PropertiesService.getScriptProperties().getProperty('DATABASE_SPREADSHEET_ID')); } function getRoot_() { return DriveApp.getFolderById(PropertiesService.getScriptProperties().getProperty('ROOT_FOLDER_ID')); } function ensureRootFolders_(root) { const out = {}; Object.values(FOLDERS).forEach(name => { out[name] = ensureSubfolder_(root, name).getId(); }); return out; } function removeColumnsIfPresent_(sheet, columnNames) { if (!sheet || sheet.getLastColumn() < 1) return; const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0].map(String); const indexes = []; columnNames.forEach(function(name) { const idx = headers.indexOf(name); if (idx >= 0) indexes.push(idx + 1); }); indexes.sort(function(a, b) { return b - a; }).forEach(function(col) { sheet.deleteColumn(col); }); } function ensureSheet_(ss, name, headers) { let s = ss.getSheetByName(name); if (!s) s = ss.insertSheet(name); if (s.getLastRow() === 0) { s.appendRow(headers); return s; } const existing = s.getRange(1,1,1,Math.max(1,s.getLastColumn())).getValues()[0].map(String); headers.forEach(h => { if (existing.indexOf(h) < 0) { s.getRange(1, s.getLastColumn()+1).setValue(h); existing.push(h); } }); return s; } function appendObject_(sheet, obj) { const headers = sheet.getRange(1,1,1,sheet.getLastColumn()).getValues()[0]; sheet.appendRow(headers.map(h => obj[h] !== undefined ? obj[h] : '')); } function getRows_(sheet) { const values = sheet.getDataRange().getValues(); if (values.length < 2) return []; const h = values[0]; return values.slice(1).map(row => Object.fromEntries(h.map((k,i)=>[k,row[i]]))); } function findRowObject_(sheet, col, value) { return getRows_(sheet).find(r => String(r[col]) === String(value)); } function setConfig_(ss, key, value, notes) { const s = ss.getSheetByName(SHEETS.CONFIG); const rows = getRows_(s); const foundIdx = rows.findIndex(r => String(r.Key) === key); if (foundIdx >= 0) { const rowNum = foundIdx + 2; s.getRange(rowNum, 2).setValue(value); s.getRange(rowNum, 3).setValue(notes || ''); } else { s.appendRow([key,value,notes||'']); } } function getOrCreateSite_(ss, siteId, data) { const sheet = ss.getSheetByName(SHEETS.SITES); let site = findRowObject_(sheet, 'SiteID', siteId); if (site) return site; const root = getRoot_(); const sitesFolder = ensureSubfolder_(root, FOLDERS.SITES); const siteFolder = sitesFolder.createFolder('Site ' + siteId + ' - ' + (data.siteName || data.practiceName || 'Practice')); const obj = { SiteID: siteId, SiteName: data.siteName || '', PracticeName: data.practiceName || '', PrimaryContactName: data.requestingProfessional || '', SiteEmail: data.siteEmail || '', Phone: data.sitePhone || '', Address: data.siteAddress || '', City: data.siteCity || '', State: data.siteState || '', Zip: data.siteZip || '', LastSubjectSequence: 0, SiteFolderId: siteFolder.getId(), SiteFolderUrl: siteFolder.getUrl(), Active: 'Yes', CreatedAt: now_(), UpdatedAt: now_() }; appendObject_(sheet, obj); return obj; } function updateSiteSequence_(ss, siteId, seq) { const s = ss.getSheetByName(SHEETS.SITES); const values = s.getDataRange().getValues(); const h = values[0]; const idCol = h.indexOf('SiteID')+1, seqCol = h.indexOf('LastSubjectSequence')+1, updatedCol = h.indexOf('UpdatedAt')+1; for (let r=2;r<=values.length;r++) if (String(s.getRange(r,idCol).getValue())===String(siteId)) { s.getRange(r,seqCol).setValue(seq); s.getRange(r,updatedCol).setValue(now_()); return; } } function ensureSubfolder_(parent, name) { const it = parent.getFoldersByName(name); return it.hasNext() ? it.next() : parent.createFolder(name); } function ensureSiteInvoiceFolder_(siteId) { const root = getRoot_(); const invoiceRoot = ensureSubfolder_(root, FOLDERS.INVOICES); return ensureSubfolder_(invoiceRoot, 'Site ' + siteId); } function folderForDocumentType_(type) { const t = String(type).toLowerCase(); if (t.includes('consent') || t.includes('signature')) return '02_Signed_Consents'; if (t.includes('lab')) return '04_Labs'; if (t.includes('record')) return '03_Medical_Records'; if (t.includes('imaging') || t.includes('dicom') || t.includes('zip')) return '05_Retinal_Imaging_ZIP'; if (t.includes('retina iq')) return '06_Retina_IQ_Reports'; if (t.includes('invoice')) return '07_Invoices'; return '01_Intake'; } function tierRate_(images) { if (images <= 100) return 15; if (images <= 300) return 12; return 10; } function cleanSiteId_(v) { return String(v || '01').replace(/\D/g,'').padStart(2,'0').slice(-2); } function pad5_(n) { return String(n).padStart(5,'0'); } function now_() { return Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "yyyy-MM-dd'T'HH:mm:ss"); } function safeFileName_(s) { return String(s).replace(/[^a-zA-Z0-9._-]/g,'_').slice(0,160); } function json_(obj) { return ContentService.createTextOutput(JSON.stringify(obj)).setMimeType(ContentService.MimeType.JSON); } function logAudit_(actor, action, entityType, entityId, details) { try { appendObject_(getDb_().getSheetByName(SHEETS.AUDIT), { Timestamp: now_(), Actor: actor || CENTRAL_EMAIL, Action: action, EntityType: entityType, EntityID: entityId, Details: details }); } catch(e) {} } /** * Helper for setup verification. Run this from the Apps Script editor after setup. * It prints the root folder URL, master database URL, portal token, and central email. */ function printSpectOcularSetupInfo() { const props = PropertiesService.getScriptProperties(); const rootFolderId = props.getProperty('ROOT_FOLDER_ID'); const databaseId = props.getProperty('DATABASE_SPREADSHEET_ID'); const portalToken = props.getProperty('PORTAL_TOKEN'); const info = { rootFolderUrl: rootFolderId ? DriveApp.getFolderById(rootFolderId).getUrl() : 'ROOT_FOLDER_ID not found. Run setupSpectOcularGoogleWorkflow first.', masterDatabaseUrl: databaseId ? SpreadsheetApp.openById(databaseId).getUrl() : 'DATABASE_SPREADSHEET_ID not found. Run setupSpectOcularGoogleWorkflow first.', portalToken: portalToken || 'PORTAL_TOKEN not found. Run setupSpectOcularGoogleWorkflow first.', centralEmail: CENTRAL_EMAIL || 'admin@spectocularhealth.com' }; Logger.log(JSON.stringify(info, null, 2)); return info; }