document.addEventListener('DOMContentLoaded', () => { const spreadsheetDiv = document.getElementById('spreadsheet'); const commandInput = document.getElementById('commandInput'); const sendBtn = document.getElementById('sendBtn'); const saveBtn = document.getElementById('saveBtn'); const exportBtn = document.getElementById('exportBtn'); const loadingOverlay = document.getElementById('loadingOverlay'); const statsDisplay = document.getElementById('statsDisplay'); const micBtn = document.getElementById('micBtn'); let spreadsheet = null; // --- ISO Codes & Data --- const statusOptions = ['To-do', 'Booked', 'In Progress', 'Done']; // Full ISO 3166-1 alpha-2 Country Codes const countryOptions = [ { id: 'US', name: 'United States' }, { id: 'GB', name: 'United Kingdom' }, { id: 'CA', name: 'Canada' }, { id: 'AU', name: 'Australia' }, { id: 'DE', name: 'Germany' }, { id: 'FR', name: 'France' }, { id: 'IT', name: 'Italy' }, { id: 'ES', name: 'Spain' }, { id: 'JP', name: 'Japan' }, { id: 'CN', name: 'China' }, { id: 'IN', name: 'India' }, { id: 'BR', name: 'Brazil' }, { id: 'MX', name: 'Mexico' }, { id: 'RU', name: 'Russia' }, { id: 'ZA', name: 'South Africa' }, { id: 'NL', name: 'Netherlands' }, { id: 'SE', name: 'Sweden' }, { id: 'NO', name: 'Norway' }, { id: 'DK', name: 'Denmark' }, { id: 'FI', name: 'Finland' }, { id: 'KR', name: 'South Korea' }, { id: 'SG', name: 'Singapore' }, { id: 'NZ', name: 'New Zealand' }, { id: 'IE', name: 'Ireland' }, { id: 'CH', name: 'Switzerland' }, { id: 'AT', name: 'Austria' }, { id: 'BE', name: 'Belgium' }, { id: 'PL', name: 'Poland' }, { id: 'PT', name: 'Portugal' }, { id: 'GR', name: 'Greece' }, { id: 'TR', name: 'Turkey' }, { id: 'AE', name: 'United Arab Emirates' }, { id: 'SA', name: 'Saudi Arabia' }, { id: 'IL', name: 'Israel' }, { id: 'AR', name: 'Argentina' }, { id: 'CL', name: 'Chile' }, { id: 'CO', name: 'Colombia' }, { id: 'PE', name: 'Peru' }, { id: 'TH', name: 'Thailand' }, { id: 'VN', name: 'Vietnam' }, { id: 'ID', name: 'Indonesia' }, { id: 'MY', name: 'Malaysia' }, { id: 'PH', name: 'Philippines' } // Add more as needed or fetch from an API ]; // Full ISO 639-1 Language Codes const languageOptions = [ { id: 'EN', name: 'English' }, { id: 'FR', name: 'French' }, { id: 'DE', name: 'German' }, { id: 'IT', name: 'Italian' }, { id: 'ES', name: 'Spanish' }, { id: 'PT', name: 'Portuguese' }, { id: 'RU', name: 'Russian' }, { id: 'ZH', name: 'Chinese' }, { id: 'JA', name: 'Japanese' }, { id: 'KO', name: 'Korean' }, { id: 'AR', name: 'Arabic' }, { id: 'HI', name: 'Hindi' }, { id: 'BN', name: 'Bengali' }, { id: 'NL', name: 'Dutch' }, { id: 'SV', name: 'Swedish' }, { id: 'NO', name: 'Norwegian' }, { id: 'DA', name: 'Danish' }, { id: 'FI', name: 'Finnish' }, { id: 'TR', name: 'Turkish' }, { id: 'PL', name: 'Polish' }, { id: 'EL', name: 'Greek' }, { id: 'HE', name: 'Hebrew' }, { id: 'TH', name: 'Thai' }, { id: 'ID', name: 'Indonesian' }, { id: 'MS', name: 'Malay' }, { id: 'VI', name: 'Vietnamese' } ]; // --- Data Hierarchy --- const hierarchy = { 'Digital': { 'Online Advertising': ['Banner', 'Rich Media', 'Landing Page', 'Static Image', 'Video', 'Push notifications', '.com'], 'Social': ['GIF', 'Video', 'Static Image', 'Multi-Asset Build'], 'Community management': [], // No sub-media 'POS': ['Digital'] }, 'Print': { 'POS': ['Print'], 'Out of Home': ['Print'], 'Direct Mail': ['Print'], 'Packaging': ['Print'] }, 'Out of Home': { 'Out of Home': ['Print', 'Digital'] }, 'Video': { 'POS': ['Video'], 'Online Advertising': ['Video'], 'Social': ['Video'], 'Broadcast': ['TV', 'Cinema', 'Radio', 'VOD'] // Assumed 'War Cost' -> Broadcast } }; const categoryOptions = Object.keys(hierarchy); const allMediaOptions = [...new Set(Object.values(hierarchy).flatMap(Object.keys))].sort(); const allSubMediaOptions = [...new Set(Object.values(hierarchy).flatMap(m => Object.values(m)).flat())].sort(); // --- Sheet Management --- let activeSheetId = localStorage.getItem('activeSheetId') || null; let sheets = []; async function loadSheetList() { try { const response = await fetch('api.php?action=list_sheets'); const result = await response.json(); if (result.success) { sheets = result.sheets; renderSheetList(); } } catch (error) { console.error('Error loading sheets:', error); } } function renderSheetList() { const sheetList = document.getElementById('sheetList'); if (sheets.length === 0) { sheetList.innerHTML = '
No sheets yet. Click + to create one!
'; return; } sheetList.innerHTML = sheets.map(sheet => `
${sheet.name}
${sheet.itemCount} items • ${new Date(sheet.modified).toLocaleDateString()}
`).join(''); // Add click handlers document.querySelectorAll('.sheet-item').forEach(item => { const sheetId = item.dataset.id; // Click on the card itself (but not on action buttons) item.addEventListener('click', (e) => { if (!e.target.closest('.sheet-item-actions')) { loadSheet(sheetId); } }); // Action button handlers item.querySelectorAll('.sheet-action-btn').forEach(btn => { btn.addEventListener('click', (e) => { e.stopPropagation(); // Prevent card click const action = btn.dataset.action; if (action === 'rename') { renameSheet(sheetId); } else if (action === 'duplicate') { duplicateSheet(sheetId); } else if (action === 'delete') { deleteSheet(sheetId); } }); }); }); } async function loadSheet(sheetId) { try { const response = await fetch(`api.php?action=load_sheet&id=${sheetId}`); const result = await response.json(); if (result.success) { activeSheetId = sheetId; localStorage.setItem('activeSheetId', sheetId); initSpreadsheet(result.data); updateStats(result.data.length); renderSheetList(); // Update active state } } catch (error) { console.error('Error loading sheet:', error); } } async function createNewSheet() { const name = prompt('Enter sheet name:', 'Untitled Sheet - ' + new Date().toLocaleDateString()); if (!name) return; try { const response = await fetch('api.php?action=save_sheet', { method: 'POST', headers: { 'Content-Type': 'application/json' }, body: JSON.stringify({ name }) }); const result = await response.json(); if (result.success) { await loadSheetList(); loadSheet(result.sheet.id); } } catch (error) { console.error('Error creating sheet:', error); } } async function duplicateSheet(sheetId) { try { const response = await fetch('api.php?action=duplicate_sheet', { method: 'POST', headers: { 'Content-Type': 'application/json' }, body: JSON.stringify({ id: sheetId }) }); const result = await response.json(); if (result.success) { await loadSheetList(); loadSheet(result.sheet.id); } } catch (error) { console.error('Error duplicating sheet:', error); } } async function deleteSheet(sheetId) { if (!confirm('Are you sure you want to delete this sheet? This cannot be undone.')) return; try { const response = await fetch('api.php?action=delete_sheet', { method: 'POST', headers: { 'Content-Type': 'application/json' }, body: JSON.stringify({ id: sheetId }) }); const result = await response.json(); if (result.success) { if (activeSheetId === sheetId) { activeSheetId = null; localStorage.removeItem('activeSheetId'); initSpreadsheet([]); // Clear grid } await loadSheetList(); } } catch (error) { console.error('Error deleting sheet:', error); } } async function renameSheet(sheetId) { const sheet = sheets.find(s => s.id === sheetId); const newName = prompt('Enter new name:', sheet?.name || ''); if (!newName) return; try { const response = await fetch('api.php?action=rename_sheet', { method: 'POST', headers: { 'Content-Type': 'application/json' }, body: JSON.stringify({ id: sheetId, name: newName }) }); const result = await response.json(); if (result.success) { await loadSheetList(); } } catch (error) { console.error('Error renaming sheet:', error); } } function showSheetContextMenu(e, sheetId) { // Simple context menu for now const action = confirm('Duplicate this sheet? (Cancel to delete)'); if (action) { duplicateSheet(sheetId); } else { deleteSheet(sheetId); } } // New Sheet Button document.getElementById('newSheetBtn').addEventListener('click', createNewSheet); // --- Initial Load --- loadSheetList(); loadData(); // --- API Interactions --- async function loadData() { showLoading(true); try { let rawData; // If there's an active sheet, load from that sheet if (activeSheetId) { const response = await fetch(`api.php?action=load_sheet&id=${activeSheetId}&t=${Date.now()}`); const result = await response.json(); if (result.success) { rawData = result.data; } else { console.error('Failed to load sheet:', result.message); rawData = []; } } else { // Fallback: load from data.json (legacy) const response = await fetch(`api.php?action=load&t=${Date.now()}`); rawData = await response.json(); } if (!rawData) { initSpreadsheet([]); return; } // Transform JSON object array to Array of Arrays for Jspreadsheet if needed, // BUT Jspreadsheet supports JSON directly if we define columns matching keys. // Let's use JSON support for simplicity. initSpreadsheet(rawData); updateStats(rawData.length); } catch (error) { console.error('Error loading data:', error); alert('Failed to load data.'); } finally { showLoading(false); } } function initSpreadsheet(data) { if (spreadsheet) { spreadsheet.destroy(); } spreadsheet = jspreadsheet(spreadsheetDiv, { data: data, columns: [ { type: 'hidden', title: 'Number', width: 100, readOnly: true, name: 'Number' }, // Hidden but present for logic { type: 'text', title: 'Title', width: 300, name: 'Title' }, { type: 'dropdown', title: 'Status', width: 100, name: 'Status', source: statusOptions }, { type: 'dropdown', title: 'Category', width: 120, name: 'Category', source: categoryOptions }, { type: 'dropdown', title: 'Media', width: 120, name: 'Media', source: allMediaOptions, filter: function (instance, cell, c, r, source) { const jexcel = instance.jexcel || instance; const categoryValue = jexcel.getValueFromCoords(3, r); // Category is at index 3 if (hierarchy[categoryValue]) { return Object.keys(hierarchy[categoryValue]); } return source; } }, { type: 'dropdown', title: 'Sub-media', width: 150, name: 'Sub-media', source: allSubMediaOptions, filter: function (instance, cell, c, r, source) { const jexcel = instance.jexcel || instance; const categoryValue = jexcel.getValueFromCoords(3, r); // Category is at index 3 const mediaValue = jexcel.getValueFromCoords(4, r); // Media is at index 4 if (hierarchy[categoryValue] && hierarchy[categoryValue][mediaValue]) { return hierarchy[categoryValue][mediaValue]; } return []; // Return empty if invalid combination } }, { type: 'text', title: 'Format', width: 120, name: 'Format' }, { type: 'calendar', title: 'Supply date', width: 100, name: 'Supply date', options: { format: 'YYYY-MM-DD' } }, { type: 'calendar', title: 'Live date', width: 100, name: 'Live date', options: { format: 'YYYY-MM-DD' } }, { type: 'dropdown', title: 'Language', width: 100, name: 'Language', source: languageOptions }, { type: 'dropdown', title: 'Country', width: 100, name: 'Country', source: countryOptions }, { type: 'hidden', title: 'Quantity', width: 50, name: 'Quantity' } ], defaultColWidth: 100, tableOverflow: true, tableWidth: '100%', tableHeight: '70vh', // Fixed height for scrolling // Context Menu contextMenu: function (obj, x, y, e) { var items = []; if (y == null) { // Header context menu if (obj.options.allowInsertColumn == true) { items.push({ title: obj.options.text.insertNewColumnBefore, onclick: function () { obj.insertColumn(1, parseInt(x), 1); } }); items.push({ title: obj.options.text.insertNewColumnAfter, onclick: function () { obj.insertColumn(1, parseInt(x), 0); } }); } } else { // Row context menu items.push({ title: 'Delete Selected Rows', onclick: function () { // Use the same logic as the button document.getElementById('deleteSelectedBtn').click(); } }); items.push({ title: 'Insert New Row', onclick: function () { obj.insertRow(1, parseInt(y)); } }); } return items; }, // Events onchange: function (instance, cell, x, y, value) { // Auto-save on change? Or wait? // User asked for "real time", but constant saving might be heavy. // Let's save on every change for now to be safe. saveData(); }, oninsertrow: function () { saveData(); }, ondeleterow: function () { saveData(); } }); } async function saveData(dataOverride = null) { // showLoading(true); // Don't block UI for auto-save try { const data = dataOverride || spreadsheet.getJson(); // If there's an active sheet, save to that sheet if (activeSheetId) { const response = await fetch('api.php?action=update_sheet', { method: 'POST', headers: { 'Content-Type': 'application/json' }, body: JSON.stringify({ id: activeSheetId, data: data }) }); const result = await response.json(); if (result.success) { console.log('Sheet saved.'); updateStats(data.length); await loadSheetList(); // Refresh sheet list to update item counts } else { console.error('Save failed:', result.message); } } else { // Fallback: save to data.json (legacy behavior) const response = await fetch('api.php?action=save', { method: 'POST', headers: { 'Content-Type': 'application/json' }, body: JSON.stringify({ data: data }) }); const result = await response.json(); if (result.success) { console.log('Saved to data.json.'); updateStats(data.length); } else { console.error('Save failed:', result.message); } } } catch (error) { console.error('Error saving data:', error); } finally { // showLoading(false); } } let lastCommandText = ''; let conversationHistory = []; // Track AI conversation async function sendCommand(overrideCommand = null, forceYolo = false) { const command = overrideCommand || commandInput.value.trim(); if (!command) return; lastCommandText = command; const yoloMode = forceYolo || document.getElementById('yoloToggle').checked; const aiOutput = document.getElementById('aiOutput'); const timestamp = new Date().toLocaleTimeString(); aiOutput.textContent += `\n[${timestamp}] Sending command: "${command}" (YOLO: ${yoloMode})...\n`; // Clear input box if (!overrideCommand) { commandInput.value = ''; } showLoading(true); try { // Build history string const historyText = conversationHistory.map(h => `${h.role}: ${h.text}`).join('\n'); const response = await fetch('api.php?action=command', { method: 'POST', headers: { 'Content-Type': 'application/json' }, body: JSON.stringify({ command: command, yolo_mode: yoloMode, history: historyText, sheet_id: activeSheetId // Pass active sheet ID }) }); // Check if response is OK if (!response.ok) { throw new Error(`HTTP error! status: ${response.status}`); } // Try to parse JSON const text = await response.text(); let result; try { result = JSON.parse(text); } catch (e) { aiOutput.textContent += `> Error: Invalid JSON response from server.\n`; aiOutput.textContent += `> Raw response: ${text.substring(0, 500)}\n`; showLoading(false); return; } if (result.success) { // Add user command to history conversationHistory.push({ role: 'User', text: command }); if (result.question) { // AI has a question - Show Modal conversationHistory.push({ role: 'AI', text: result.question }); // Log to activity log for history aiOutput.textContent += `\n[${new Date().toLocaleTimeString()}] 🤖 AI Question: ${result.question}\n`; aiOutput.scrollTop = aiOutput.scrollHeight; showAiQuestionModal(result.question); } else { // Success - clear history for fresh start conversationHistory = []; await loadData(); // Reload grid commandInput.value = ''; aiOutput.textContent += `✅ Success! Processed ${result.count || 0} items.\n`; if (result.debug_llm) { aiOutput.textContent += `\n--- AI Response ---\n${result.debug_llm}\n`; } } } else { aiOutput.textContent += `❌ Error: ${result.message}\n`; if (result.message && result.message.includes('Debug:')) { aiOutput.textContent += `\n--- Debug Info ---\n${result.message}\n`; } if (result.debug_llm) { aiOutput.textContent += `\n--- AI Response ---\n${result.debug_llm}\n`; } } // Auto-scroll to bottom aiOutput.scrollTop = aiOutput.scrollHeight; } catch (error) { console.error('Error sending command:', error); aiOutput.textContent += `❌ Critical Error: ${error.message}\n`; aiOutput.scrollTop = aiOutput.scrollHeight; } finally { showLoading(false); } } // Clear Log Button document.getElementById('clearLogBtn').addEventListener('click', () => { document.getElementById('aiOutput').textContent = 'Waiting for command...'; }); function updateStats(count) { statsDisplay.textContent = `${count} Deliverables`; } function showLoading(show) { if (show) loadingOverlay.classList.add('active'); else loadingOverlay.classList.remove('active'); } // --- Event Listeners --- const deleteSelectedBtn = document.getElementById('deleteSelectedBtn'); // Clear Sheet Logic const clearSheetBtn = document.getElementById('clearSheetBtn'); if (clearSheetBtn) { clearSheetBtn.addEventListener('click', () => { if (confirm('Are you sure you want to clear the ENTIRE sheet? This cannot be undone.')) { spreadsheet.setData([]); saveData([]); // Force save empty array updateStats(0); // Clear AI Log and History document.getElementById('aiOutput').textContent = 'Waiting for command...'; conversationHistory = []; } }); } // Delete Selected Logic deleteSelectedBtn.addEventListener('click', () => { const selected = spreadsheet.getSelected(); // [c1, r1, c2, r2] if (!selected) { alert('Please select a cell to delete its row.'); return; } // Normalize coordinates (handle reverse selection) const y1 = parseInt(selected[1]); const y2 = parseInt(selected[3]); const startRow = Math.min(y1, y2); const endRow = Math.max(y1, y2); if (isNaN(startRow) || isNaN(endRow)) { alert('Invalid selection.'); return; } const numberOfRows = endRow - startRow + 1; const totalRows = spreadsheet.getData().length; if (confirm(`Delete ${numberOfRows} row(s)?`)) { // Special case: If deleting ALL rows (or the last remaining row), // Jspreadsheet might block it or leave 1 empty row. // We should just clear the data. if (numberOfRows === totalRows) { spreadsheet.setData([]); // Clear everything saveData([]); // Force save empty array } else { spreadsheet.deleteRow(startRow, numberOfRows); saveData(); } spreadsheet.resetSelection(); } }); sendBtn.addEventListener('click', () => stopMicAndSend()); commandInput.addEventListener('keydown', (e) => { if (e.key === 'Enter') { e.preventDefault(); stopMicAndSend(); } }); saveBtn.addEventListener('click', () => { saveData(); alert('Saved!'); }); // Custom CSV Export if (exportBtn) { exportBtn.addEventListener('click', function () { const data = spreadsheet.getData(); // Strict Header Requirement const headers = [ "Number", "Title", "Status", "Category", "Media", "Sub media", "Destination", "Format", "Supply date", "Live date", "End date", "Special instructions", "Language", "Country", "Quantity" ]; let csvContent = "Sep=,\n" + headers.map(h => `"${h}"`).join(",") + "\n"; data.forEach(row => { // Map Jspreadsheet row indices to the required CSV format // Row structure in Jspreadsheet: // 0:Number, 1:Title, 2:Status, 3:Category, 4:Media, 5:Sub-media, // 6:Format (New Index!), 7:Supply date, 8:Live date, 9:Language, 10:Country, 11:Quantity const csvRow = [ "", // Number (Cleared as requested) row[1] || "", // Title row[2] || "", // Status row[3] || "", // Category row[4] || "", // Media row[5] || "", // Sub media "", // Destination (Empty) row[6] || "", // Format (Now mapped!) row[7] || "", // Supply date row[8] || "", // Live date "", // End date (Empty) "", // Special instructions (Empty) row[9] || "", // Language row[10] || "", // Country "1.00" // Quantity ]; // Escape quotes and wrap in quotes const escapedRow = csvRow.map(field => { const stringField = String(field); if (stringField.includes('"')) { return `"${stringField.replace(/"/g, '""')}"`; } return `"${stringField}"`; }); csvContent += escapedRow.join(",") + "\n"; }); const blob = new Blob([csvContent], { type: 'text/csv;charset=utf-8;' }); const link = document.createElement("a"); const url = URL.createObjectURL(blob); link.setAttribute("href", url); link.setAttribute("download", "activation_calendar_export.csv"); link.style.visibility = 'hidden'; document.body.appendChild(link); link.click(); document.body.removeChild(link); }); } // --- Voice Recognition --- let recognition; if ('webkitSpeechRecognition' in window) { recognition = new webkitSpeechRecognition(); recognition.continuous = true; recognition.interimResults = true; recognition.lang = 'en-US'; micBtn.addEventListener('click', () => { if (micBtn.classList.contains('listening')) { recognition.stop(); } else { recognition.start(); } }); recognition.onstart = () => { micBtn.classList.add('listening'); commandInput.placeholder = "Listening... (Press Enter to send)"; }; recognition.onend = () => { micBtn.classList.remove('listening'); commandInput.placeholder = "Type or speak a command..."; }; recognition.onresult = (event) => { let finalTranscript = ''; let interimTranscript = ''; for (let i = 0; i < event.results.length; ++i) { if (event.results[i].isFinal) { finalTranscript += event.results[i][0].transcript; } else { interimTranscript += event.results[i][0].transcript; } } commandInput.value = finalTranscript + interimTranscript; }; } else { micBtn.style.display = 'none'; } function stopMicAndSend() { if (recognition && micBtn.classList.contains('listening')) { recognition.stop(); } sendCommand(); } // --- AI Question Modal Logic --- const aiModal = document.getElementById('aiQuestionModal'); const aiModalInput = document.getElementById('aiModalInput'); const aiModalMicBtn = document.getElementById('aiModalMicBtn'); const aiModalSendBtn = document.getElementById('aiModalSendBtn'); const aiModalYoloBtn = document.getElementById('aiModalYoloBtn'); window.showAiQuestionModal = function (question) { document.getElementById('aiQuestionText').textContent = question; aiModal.classList.add('active'); aiModalInput.value = ''; aiModalInput.focus(); }; window.closeAiModal = function () { aiModal.classList.remove('active'); if (recognition) recognition.stop(); }; // Modal Send aiModalSendBtn.addEventListener('click', () => { const answer = aiModalInput.value.trim(); if (answer) { closeAiModal(); sendCommand(answer); } }); // Modal Enter Key aiModalInput.addEventListener('keydown', (e) => { if (e.key === 'Enter') { e.preventDefault(); aiModalSendBtn.click(); } }); // Modal YOLO aiModalYoloBtn.addEventListener('click', () => { closeAiModal(); sendCommand(lastCommandText, true); // Resend last command with YOLO forced }); // Modal Mic if (recognition) { aiModalMicBtn.addEventListener('click', () => { if (aiModalMicBtn.classList.contains('listening')) { recognition.stop(); } else { // Temporarily override onresult for modal recognition.onresult = (event) => { let finalTranscript = ''; for (let i = 0; i < event.results.length; ++i) { if (event.results[i].isFinal) { finalTranscript += event.results[i][0].transcript; } } aiModalInput.value = finalTranscript; }; recognition.onend = () => { aiModalMicBtn.classList.remove('listening'); // Restore original onresult restoreMainMicHandler(); }; recognition.start(); aiModalMicBtn.classList.add('listening'); } }); } else { aiModalMicBtn.style.display = 'none'; } function restoreMainMicHandler() { recognition.onresult = (event) => { let finalTranscript = ''; let interimTranscript = ''; for (let i = 0; i < event.results.length; ++i) { if (event.results[i].isFinal) { finalTranscript += event.results[i][0].transcript; } else { interimTranscript += event.results[i][0].transcript; } } commandInput.value = finalTranscript + interimTranscript; }; recognition.onend = () => { micBtn.classList.remove('listening'); commandInput.placeholder = "Type or speak a command..."; }; } });