import { config } from 'dotenv';

import { Hono } from 'hono';
import { serveStatic } from 'hono/bun';
import { cors } from 'hono/cors';
import PocketBase from 'pocketbase';
import { Client } from '@microsoft/microsoft-graph-client';
import { listTeamChannels, listChannelMessages, listTeamChannelsWithToken, listChannelMessagesWithToken, deleteChannelMessage, deleteChannelMessageWithToken } from './services/teams/messages.ts';
import { mkdir, appendFile } from 'fs/promises';
import { existsSync } from 'fs';
import path from 'path';
import { processNewJobRecord } from './extracted-graph-logic/post-record-integration.js';

// Load secrets from absolute path (not in project directory)
config({ path: '/home/admin/secrets/.env' });

// ============================================================================
// ENVIRONMENT SETTINGS (Toggle between TEST and PROD here)
// ============================================================================
const MODE = 'TEST'; // CHANGE THIS TO 'PROD' TO GO LIVE

const CONFIG = {
  TEST: {
    pbCollection: 'Job_Info_TestEnv',
    excelTable: 'Test_Table',
    plannerGroupId: 'e45f2188-dc65-42d6-b7b0-d0a873e07472',
    plannerPlanId: '87nMEU5OqUqro1xLjY58-2UAD4uj',
    plannerBucketId: '9VuH0OQEa0GQsCHtPgUCFmUACHVF',
    description: '🧪 TEST ENVIRONMENT'
  },
  PROD: {
    pbCollection: process.env.PB_COLLECTION || 'Job_Info_Prod',
    excelTable: process.env.EXCEL_TABLE_NAME || 'Job_List',
    plannerGroupId: process.env.PLANNER_GROUP_ID || 'e45f2188-dc65-42d6-b7b0-d0a873e07472',
    plannerPlanId: process.env.PLANNER_PLAN_ID || '87nMEU5OqUqro1xLjY58-2UAD4uj',
    plannerBucketId: process.env.PLANNER_BUCKET_ID || '9VuH0OQEa0GQsCHtPgUCFmUACHVF',
    description: '🚀 PRODUCTION ENVIRONMENT'
  }
};

const activeConfig = MODE === 'PROD' ? CONFIG.PROD : CONFIG.TEST;
console.log('\n' + '*'.repeat(60));
    console.log('  RUNNING IN: ' + activeConfig.description);
  console.log('  PocketBase: ' + activeConfig.pbCollection);
  console.log('  Excel Table: ' + activeConfig.excelTable);
  console.log('  Planner Plan: ' + activeConfig.plannerPlanId);
  console.log('  Planner Bucket: ' + activeConfig.plannerBucketId);
  console.log('  Planner Plan: ' + activeConfig.plannerPlanId);
  console.log('  Planner Bucket: ' + activeConfig.plannerBucketId);
console.log('*'.repeat(60) + '\n');

const app = new Hono();
// Environment config endpoint
app.get('/api/config', (c) => {
  return c.json({
    mode: MODE,
    description: activeConfig.description
  });
});


// ============================================================================
// ERROR LOGGING
// ============================================================================

const LOG_DIR = path.join(process.cwd(), 'logs');
const ERROR_LOG_PATH = path.join(LOG_DIR, 'error.log');

// Ensure logs directory exists
if (!existsSync(LOG_DIR)) {
  await mkdir(LOG_DIR, { recursive: true });
}

async function logError(context: string, error: any, additionalData?: any) {
  const timestamp = new Date().toISOString();
  const logEntry = {
    timestamp,
    context,
    error: {
      message: error?.message || String(error),
      stack: error?.stack,
      status: error?.status || error?.statusCode,
    },
    data: additionalData
  };
  
  const logLine = `${timestamp} [ERROR] ${context}: ${JSON.stringify(logEntry)}\n`;
  
  try {
    await appendFile(ERROR_LOG_PATH, logLine);
    console.error(`✗ [${context}] ${error?.message || error}`);
  } catch (writeError) {
    console.error('Failed to write to error log:', writeError);
    console.error('Original error:', error);
  }
}

// ============================================================================
// TEAMS WEBHOOK NOTIFICATION (Power Automate)
// ============================================================================

function toIsoDateOnly(val: any): string {
  if (!val) return '';
  const d = new Date(val);
  if (isNaN(d.getTime())) return '';
  const yyyy = d.getUTCFullYear();
  const mm = String(d.getUTCMonth() + 1).padStart(2, '0');
  const dd = String(d.getUTCDate()).padStart(2, '0');
  return `${yyyy}-${mm}-${dd}`;
}

function buildPowerAutomateExcelItem(record: any) {
  return {
    '@odata.context': '',
    '@odata.etag': '',
    ItemInternalId: record.id || '',
    'Job_Number': record.Job_Number || record.job_number || '',
    'Job_Name': record.Job_Name || record.job_name || '',
    'Job Address': record.Job_Address || record.job_address || '',
    'Job Type': record.Job_Type || record.job_type || '',
    'Job Status': record.Job_Status || record.job_status || '',
    'Job Division': record.Job_Division || record.job_division || '',
    'Estimator': record.Estimator || record.estimator || '',
    'Office Rep': record.Office_Rep || record.office_rep || '',
    'Manager': record.Manager || record.manager || '',
    'Company/Client': record.Company_Client || record.company_client || '',
    'Contact Person': record.Contact_Person || record.contact_person || '',
    'Phone Number': record.Phone_Number || record.phone_number || '',
    'Email': record.Email || record.email || '',
    'Due Date': toIsoDateOnly(record.Due_Date || record.due_date || ''),
    'Due Date Source': record.Due_Date_Source || record.due_date_source || '',
    'Due Time': record.Due_Time || record.due_time || '',
    'Start Date': toIsoDateOnly(record.Start_Date || record.start_date || '')
  };
}

function buildAdaptiveCard(jobData: any) {
  const jobName = jobData.Job_Name || '';
  const jobNumber = jobData.Job_Number || '';
  const jobAddress = jobData.Job_Address || '';
  const jobDivision = jobData.Job_Division || '';

  // Format timestamp for the card
  const now = new Date();
  const timestamp = now.toLocaleString('en-US', {
    month: 'short',
    day: 'numeric',
    year: 'numeric',
    hour: 'numeric',
    minute: '2-digit',
    hour12: true
  });

  return {
    $schema: 'http://adaptivecards.io/schemas/adaptive-card.json',
    type: 'AdaptiveCard',
    version: '1.4',
    body: [
      {
        type: 'Container',
        style: 'emphasis',
        items: [
          { type: 'TextBlock', text: 'New Job!', weight: 'Bolder', size: 'Large', spacing: 'None' },
          { type: 'TextBlock', text: `**Number:** ${jobNumber || 'N/A'}`, spacing: 'Small' },
          { type: 'TextBlock', text: `**Name:** ${jobName || 'N/A'}`, spacing: 'None' },
          { type: 'TextBlock', text: `**Address:** ${jobAddress || 'N/A'}`, spacing: 'None' },
          { type: 'TextBlock', text: `**Division:** ${jobDivision || 'N/A'}`, spacing: 'None' },
          { type: 'TextBlock', text: timestamp, size: 'Small', isSubtle: true, spacing: 'Small' }
        ]
      }
    ]
  };
}

async function notifyTeamsChannel(jobData: any) {
  const webhook = process.env.TEAMS_WEBHOOK_URL;
  
  if (!webhook) {
    console.log('⚠️  TEAMS_WEBHOOK_URL not configured');
    return;
  }

  try {
    const adaptiveCard = buildAdaptiveCard(jobData);
    const payload = {
      type: 'message',
      attachments: [
        {
          contentType: 'application/vnd.microsoft.card.adaptive',
          contentUrl: null,
          content: adaptiveCard
        }
      ]
    };

    console.log(`[Teams Webhook] Sending notification for Job ${jobData.Job_Number || ''}:`, JSON.stringify(payload, null, 2));

    const response = await fetch(webhook, {
      method: 'POST',
      headers: {
        'Content-Type': 'application/json'
      },
      body: JSON.stringify(payload)
    });

    if (!response.ok) {
      const text = await response.text();
      console.warn(`⚠️  Teams notification failed: ${response.status} ${response.statusText}`);
      console.warn(`   Response: ${text}`);
    } else {
      console.log(`✓ Teams notification sent for Job ${jobData.Job_Number || ''} (${response.status})`);
    }
  } catch (error: any) {
    console.error('⚠️  Failed to send Teams notification:', error.message);
  }
}

// Enable CORS
app.use('/*', cors());

// Serve static files from frontend directory
app.use('/*', serveStatic({ root: './frontend' }));

// PocketBase client
const pb = new PocketBase(process.env.PB_DB || 'https://pocketbase.ccllc.pro');

// (Removed) formula seeding logic: we rely on Excel's table calculated columns defined in the workbook

// Microsoft Graph authentication
async function getGraphToken() {
  const tokenEndpoint = `https://login.microsoftonline.com/${process.env.TENANT_ID}/oauth2/v2.0/token`;
  
  const params = new URLSearchParams();
  params.append('client_id', process.env.CLIENT_ID!);
  params.append('client_secret', process.env.CLIENT_SECRET!);
  params.append('scope', 'https://graph.microsoft.com/.default');
  params.append('grant_type', 'client_credentials');

  const response = await fetch(tokenEndpoint, {
    method: 'POST',
    headers: {
      'Content-Type': 'application/x-www-form-urlencoded',
    },
    body: params,
  });

  if (!response.ok) {
    const text = await response.text();
    const error = new Error(`Graph token request failed: ${response.status} ${response.statusText} - ${text}`);
    await logError('Graph API Token', error, { endpoint: tokenEndpoint });
    throw error;
  }

  type TokenResponse = { access_token: string };
  const data = await response.json() as TokenResponse;
  if (!data || typeof data.access_token !== 'string' || !data.access_token) {
    const error = new Error('Graph token response missing access_token');
    await logError('Graph API Token', error, { response: data });
    throw error;
  }
  return data.access_token;
}

// Create Microsoft Graph client
async function getGraphClient() {
  const token = await getGraphToken();
  
  return Client.init({
    authProvider: (done) => {
      done(null, token);
    },
  });
}

// Use user's PocketBase token (passed from authenticated frontend)
function setUserPocketBaseAuth(token: string) {
  pb.authStore.save(token);
}

// Get Excel table column names
async function getExcelColumns(client: any, excelTablePath: string) {
  const colsResp = await client.api(`${excelTablePath}/columns`).get();
  const columns = colsResp?.value || [];
  const columnNames: string[] = columns.map((c: any) => c.name);
  return columnNames;
}

// Cache for table structure (queried once, reused for all submissions)
let tableCache: {
  columnNames: string[];
} | null = null;

// Resolve the Excel table name from env; prefers EXCEL_TABLE_NAME (just the table's name),
// and falls back to parsing EXCEL_TABLE if provided as a full path, else default to Test_Table.
function resolveTableName(): string {
  return activeConfig.excelTable;
}

// ============================================================================
// CALCULATED COLUMN FUNCTIONS (server-side formula equivalents)
// ============================================================================
// These replace Excel table formulas with TypeScript calculations on entry
// Ensures consistent, calculated values added to each new row

/**
 * Job_Full_Name = CONCAT([@[Job_Name]]," - ",[@[Job_Address]]," - ",[@[Company_Client]])
 */
function calculateJobFullName(record: any): string {
  const jobName = record.Job_Name || record.job_name || '';
  const jobAddress = record.Job_Address || record.job_address || '';
  const companyClient = record.Company_Client || record.company_client || '';
  
  const parts = [jobName, jobAddress, companyClient].filter(p => p && String(p).trim());
  return parts.map(p => String(p).trim()).join(' - ').trim();
}

/**
 * Due_Date_Counter = IF([@[Job_Status]]="Estimating",IF(ISNUMBER(SEARCH("ASAP",[@[Due_Date]])),"ASAP",IF([@[Due_Date]]<TODAY(),"Passed due",IF([@[Due_Date]]=TODAY(),"Due today",([@[Due_Date]]-TODAY())&IF([@[Due_Date]]-TODAY()=1," day"," days")))),"N/A")
 */
function calculateDueDateCounter(record: any): string {
  const jobStatus = record.Job_Status || record.job_status || '';
  const dueDateStr = record.Due_Date || record.due_date || '';
  
  if (jobStatus !== 'Estimating') {
    return 'N/A';
  }
  
  if (!dueDateStr) {
    return 'N/A';
  }
  
  // Check if "ASAP" appears in the date string
  if (String(dueDateStr).toUpperCase().includes('ASAP')) {
    return 'ASAP';
  }
  
  // Parse the date (could be ISO or MM/dd/yyyy)
  let dueDate = new Date(dueDateStr);
  if (isNaN(dueDate.getTime())) {
    return 'N/A';
  }
  
  const today = new Date();
  // Reset time portions to midnight for fair comparison
  today.setHours(0, 0, 0, 0);
  dueDate.setHours(0, 0, 0, 0);
  
  const timeDiff = dueDate.getTime() - today.getTime();
  const daysDiff = Math.floor(timeDiff / (1000 * 60 * 60 * 24));
  
  if (daysDiff < 0) {
    return 'Passed due';
  } else if (daysDiff === 0) {
    return 'Due today';
  } else {
    const dayLabel = daysDiff === 1 ? ' day' : ' days';
    return `${daysDiff}${dayLabel}`;
  }
}

/**
 * Job_QB_Link = [@[Job_Division]]&[@[Job_Number]]&[@[Job_Type]]&" - "&[@[Job_Name]]
 */
function calculateJobQbLink(record: any): string {
  const jobDivision = record.Job_Division || record.job_division || '';
  const jobNumber = record.Job_Number || record.job_number || '';
  const jobType = record.Job_Type || record.job_type || '';
  const jobName = record.Job_Name || record.job_name || '';
  
  return `${jobDivision}${jobNumber}${jobType} - ${jobName}`;
}

/**
 * Voxer_Link = [@[Job_Division]]&[@[Job_Number]]&[@[Job_Type]]&" - "&[@[Job_Name]]&" - "&[@[Job_Address]]&" - "&[@[Company_Client]]&" - "&[@[Contact_Person]]&" - "&[@[Phone_Number]]
 */
function calculateVoxerLink(record: any): string {
  const jobDivision = record.Job_Division || record.job_division || '';
  const jobNumber = record.Job_Number || record.job_number || '';
  const jobType = record.Job_Type || record.job_type || '';
  const jobName = record.Job_Name || record.job_name || '';
  const jobAddress = record.Job_Address || record.job_address || '';
  const companyClient = record.Company_Client || record.company_client || '';
  const contactPerson = record.Contact_Person || record.contact_person || '';
  const phoneNumber = record.Phone_Number || record.phone_number || '';
  
  return `${jobDivision}${jobNumber}${jobType} - ${jobName} - ${jobAddress} - ${companyClient} - ${contactPerson} - ${phoneNumber}`;
}

/**
 * Job_Codes = [@[Job_Number]]&" - "&[@[Job_Name]]
 */
function calculateJobCodes(record: any): string {
  const jobNumber = record.Job_Number || record.job_number || '';
  const jobName = record.Job_Name || record.job_name || '';
  
  return `${jobNumber} - ${jobName}`;
}

/**
 * Active = IF(OR([@[Job_Status]]="Estimating",[@[Job_Status]]="Est Sent",[@[Job_Status]]="Est Hold",[@[Job_Status]]="Follow Up",[@[Job_Status]]="Awarded",[@[Job_Status]]="In Progress",[@[Job_Status]]="Billed In Progress"),TRUE,FALSE)
 */
function calculateActive(record: any): boolean {
  const jobStatus = record.Job_Status || record.job_status || '';
  const activeStatuses = [
    'Estimating',
    'Est Sent',
    'Est Hold',
    'Follow Up',
    'Awarded',
    'In Progress',
    'Billed In Progress'
  ];
  
  return activeStatuses.includes(jobStatus);
}

// ============================================================================
// PLANNER TASK CREATION
// ============================================================================

async function createPlannerTask({
  jobNumber,
  jobFullName,
  dueDate,
}: {
  jobNumber: string;
  jobFullName: string;
  dueDate?: string;
}) {
  try {
    const client = await getGraphClient();

    // Hardcoded assignee for testing: aewing@cardoza.construction
    const assigneeId = '1a6e9d10-138a-43e4-a09e-1f50463148c9';
    const assigneeName = 'Alex Ewing'; // Display name

    // Parse due date if provided (ISO format to Date object)
    let dueDateObj: any = undefined;
    if (dueDate) {
      const d = new Date(dueDate);
      if (!isNaN(d.getTime())) {
        dueDateObj = d.toISOString();
      }
    }

    // Task title: "Disposition New Job Folder for [Job_Number] [Job_Full_Name]"
    const title = `Disposition New Job Folder for ${jobNumber} ${jobFullName}`;

                // Create the Planner task
      const groupId = activeConfig.plannerGroupId;
      const planId = activeConfig.plannerPlanId;
      const bucketId = activeConfig.plannerBucketId;

      if (!groupId || !planId || !bucketId) {
        throw new Error('Missing Planner configuration in activeConfig');
      }

    const newTask = await client.api('/planner/tasks').post({
        planId,
        bucketId,
        title,
        assignments: {
          [assigneeId]: {
            '@odata.type': 'microsoft.graph.plannerAssignment',
            orderHint: ' !',
          },
        },
        ...(dueDateObj ? { dueDateTime: dueDateObj } : {}),
      });

      console.log(`✓ Planner task created in Plan ID: ${planId}`);

      console.log(`✓ Planner task created in Plan ID: ${planId}`);

    // Build checklist object with unique IDs
    const checklist: Record<string, { '@odata.type': string; title: string; isChecked: boolean; orderHint: string }> = {};
    const checklistItems = ['Create Voxer Chat', 'Create Quickbooks', 'Upload plans'];
    checklistItems.forEach((item, index) => {
      const itemId = `checklist_${Date.now()}_${index}`;
      checklist[itemId] = {
        '@odata.type': 'microsoft.graph.plannerChecklistItem',
        title: item,
        isChecked: false,
        orderHint: ` ${index}!`,
      };
    });

    // Set the Notes/Description AND checklist via task details
    const taskDetails = await client.api(`/planner/tasks/${newTask.id}/details`).get();
    const detailsEtag = taskDetails['@odata.etag'];
    console.log(`[Planner] Task created ${newTask.id}, fetched details ETag: ${detailsEtag}`);
    await client
      .api(`/planner/tasks/${newTask.id}/details`)
      .header('If-Match', detailsEtag)
      .patch({
        description: 'Move the new folder to the correct Job Pages folder.',
        checklist: checklist,
      });
    console.log(`[Planner] Successfully patched description and added ${checklistItems.length} checklist items`);

    // Add "plum" label (category6) to task
    const taskForLabel = await client.api(`/planner/tasks/${newTask.id}`).get();
    const taskEtag = taskForLabel['@odata.etag'];
    await client
      .api(`/planner/tasks/${newTask.id}`)
      .header('If-Match', taskEtag)
      .patch({
        appliedCategories: {
          category6: true,
        },
      });
    console.log(`[Planner] Added plum label to task`);

    console.log(`✓ Planner task created: "${title}" assigned to ${assigneeName}`);
    return { success: true, taskId: newTask.id, title };
  } catch (error: any) {
    console.error('✗ Planner task creation failed:', error.message);
    await logError('Planner Task Creation', error, {
      jobNumber,
      jobFullName,
    });
    throw error;
  }
}

// Initialize table cache on startup
async function initializeTableCache() {
  const client = await getGraphClient();
  const tableName = resolveTableName();
  const excelTablePath = `/drives/${process.env.DRIVE_ID}/items/${process.env.ITEM_ID}/workbook/tables/${tableName}`;

  try {
    const colsResp = await client.api(`${excelTablePath}/columns`).get();
    const columns = colsResp?.value || [];
    const columnNames: string[] = columns.map((c: any) => c.name);

    tableCache = { columnNames };
    console.log(`✓ Table cache initialized with ${columnNames.length} columns`);
  } catch (error: any) {
    console.error('✗ Failed to initialize table cache:', error);
    await logError('Table Cache Initialization', error, { 
      driveId: process.env.DRIVE_ID, 
      itemId: process.env.ITEM_ID 
    });
    throw error;
  }
}

// Add record to Excel (only columns that exist in table)
async function addToExcel(record: any, pbId: string) {
  const client = await getGraphClient();
  const tableName = resolveTableName();
  const excelTablePath = `/drives/${process.env.DRIVE_ID}/items/${process.env.ITEM_ID}/workbook/tables/${tableName}`;

  // Initialize cache if not already done
  if (!tableCache) {
    await initializeTableCache();
  }

  const { columnNames } = tableCache!;

  try {
    // Build row values in column order
    // Helper: format PocketBase UTC/ISO dates to MM/dd/yyyy for Excel display
    function formatPbDateToShort(val: any): string {
      if (!val) return '';
      const d = new Date(val);
      if (isNaN(d.getTime())) return '';
      // Use UTC accessors to preserve date (don't convert to local time)
      const mm = String(d.getUTCMonth() + 1).padStart(2, '0');
      const dd = String(d.getUTCDate()).padStart(2, '0');
      const yyyy = String(d.getUTCFullYear());
      return `${mm}/${dd}/${yyyy}`;
    }

    const rowValues = columnNames.map((col) => {
      const colLower = col.toLowerCase();
      
      // Special case: pb_id column gets the record.id
      if (colLower === 'pb_id') {
        return pbId || '';
      }
      
      // ========================================================================
      // CALCULATED COLUMNS: compute values server-side on entry
      // ========================================================================
      
      // Job_Full_Name: CONCAT(Job_Name, " - ", Job_Address, " - ", Company_Client)
      if (colLower === 'job_full_name') {
        return calculateJobFullName(record);
      }
      
      // Due_Date_Counter: Status-aware due date counter (ASAP, Passed due, Due today, X days)
      if (colLower === 'due_date_counter') {
        // Send Excel formula so the table auto-calculates
        return '=IF([@[Job_Status]]<>"Estimating","N/A",IF(ISNUMBER(SEARCH("ASAP",[@[Due_Date]])),"ASAP",IF([@[Due_Date]]<TODAY(),"Passed due",IF([@[Due_Date]]=TODAY(),"Due today",[@[Due_Date]]-TODAY()&IF([@[Due_Date]]-TODAY()=1," day"," days")))))';
      }
      
      // Job_QB_Link: Job_Division + Job_Number + Job_Type + " - " + Job_Name
      if (colLower === 'job_qb_link') {
        return calculateJobQbLink(record);
      }
      
      // Voxer_Link: Long concatenation with division, number, type, name, address, client, contact, phone
      if (colLower === 'voxer_link') {
        return calculateVoxerLink(record);
      }
      
      // Job_Codes: Job_Number + " - " + Job_Name
      if (colLower === 'job_codes') {
        return calculateJobCodes(record);
      }
      
      // Active: boolean based on Job_Status membership in active statuses list
      if (colLower === 'active') {
        // Send Excel formula so the table auto-calculates
        return '=OR([@[Job_Status]]="Estimating",[@[Job_Status]]="Est Sent",[@[Job_Status]]="Est Hold",[@[Job_Status]]="Follow Up",[@[Job_Status]]="Awarded",[@[Job_Status]]="In Progress",[@[Job_Status]]="Billed In Progress")';
      }
      
      // ========================================================================
      // REGULAR COLUMNS: format dates, fall back to direct value
      // ========================================================================
      
      // Try exact match first, then case-insensitive
      let val = record[col];
      if (val === undefined) {
        const recordKeys = Object.keys(record);
        const matchingKey = recordKeys.find(k => k.toLowerCase() === colLower);
        if (matchingKey) {
          val = record[matchingKey];
        }
      }
      
      // Format known date columns from PB to Excel-friendly short dates
      if (['due_date','start_date','submission_date'].includes(colLower)) {
        return formatPbDateToShort(val);
      }

      return val == null ? '' : val;
    });

    // Add row to Excel table at index 0 (first position after headers)
    await client.api(`${excelTablePath}/rows`).post({
      index: 0,
      values: [rowValues]
    });

    console.log(`✓ Synced to Excel (pb_id: ${pbId})`);

    // DISABLED: formula copy causing issues - skip for now
    // Previous logic was attempting to copy formulas which may corrupt or duplicate rows

    return { success: true };
  } catch (error: any) {
    console.error('✗ Failed to add record to Excel:', error);
    await logError('Excel Sync', error, { 
      pbId, 
      jobNumber: record.Job_Number || record.job_number,
      driveId: process.env.DRIVE_ID,
      itemId: process.env.ITEM_ID 
    });
    throw error;
  }
}

// Update the Excel row's Job_Folder_Link for the given pb_id
async function updateExcelFolderLink(pbId: string, link: string) {
  const client = await getGraphClient();
  const tableName = resolveTableName();
  const excelTablePath = `/drives/${process.env.DRIVE_ID}/items/${process.env.ITEM_ID}/workbook/tables/${tableName}`;

  // Initialize cache if not already done
  if (!tableCache) {
    await initializeTableCache();
  }
  const { columnNames } = tableCache!;
  const lower = columnNames.map((n) => n.toLowerCase());
  const linkColIndex = lower.indexOf('job_folder_link');
  const pbIdColIndex = lower.indexOf('pb_id');
  if (linkColIndex === -1 || pbIdColIndex === -1) {
    console.warn('⚠️ Excel table missing Job_Folder_Link or pb_id column');
    return;
  }

  const rowsResp = await client.api(`${excelTablePath}/rows`).get();
  const rows = rowsResp?.value || [];
  const target = rows.find((row: any) => {
    const vals = row?.values?.[0];
    return vals && vals[pbIdColIndex] === pbId;
  });
  if (!target) {
    // Row was just inserted at index 0, try updating that directly
    console.warn('⚠️ Excel row not found by pb_id, trying index 0 (just inserted)');
    try {
      await client.api(`${excelTablePath}/rows/itemAt(index=0)/range/cell(row=0,column=${linkColIndex})`)
        .patch({
          values: [[link || '']]
        });
      console.log('✓ Updated Excel Job_Folder_Link cell at index 0 for pb_id:', pbId);
      return;
    } catch (fallbackErr: any) {
      console.error('⚠️ Fallback update at index 0 also failed:', fallbackErr.message);
      return;
    }
  }

  // Update only the Job_Folder_Link cell to preserve formulas in other columns (e.g., Active)
  // Use the table row's range and cell() method to target just the one cell
  // This avoids hardcoding worksheet names and works through the table structure
  await client.api(`${excelTablePath}/rows/itemAt(index=${target.index})/range/cell(row=0,column=${linkColIndex})`)
    .patch({
      values: [[link || '']]
    });
  
  console.log('✓ Updated Excel Job_Folder_Link cell for pb_id:', pbId);
}

// API endpoint to submit job
app.post('/api/submit', async (c) => {
  const pbCollection = activeConfig.pbCollection;
  let data: any;
  try {
    data = await c.req.json();
    const userToken = data.pbToken;

    if (!userToken) {
      return c.json({ 
        success: false, 
        message: 'User authentication required. Please log in first.' 
      }, 401);
    }

    // Use user's PocketBase token
    setUserPocketBaseAuth(userToken);

    // Convert picker values to UTC ISO strings before PocketBase create
    function toIsoUtc(val: any): string | undefined {
      if (!val) return undefined;
      // If already ISO-like, try Date parsing
      let d = new Date(val);
      if (isNaN(d.getTime())) {
        // Try m/d/Y
        const m = /^\s*(\d{1,2})\/(\d{1,2})\/(\d{2,4})\s*$/.exec(String(val));
        if (m) {
          const month = Number(m[1]);
          const day = Number(m[2]);
          const year = Number(m[3].length === 2 ? `20${m[3]}` : m[3]);
          // Assume local midnight, then convert to UTC
          d = new Date(year, month - 1, day, 0, 0, 0);
        } else {
          return undefined;
        }
      }
      // Normalize to UTC midnight (strip time if present)
      const utc = new Date(Date.UTC(d.getFullYear(), d.getMonth(), d.getDate(), 0, 0, 0));
      return utc.toISOString();
    }

    const dueDateRaw = data.Due_Date ?? data.due_date ?? data.DueDate ?? data.dueDate;
    const startDateRaw = data.Start_Date ?? data.start_date ?? data.StartDate ?? data.startDate;
    const dueIso = toIsoUtc(dueDateRaw);
    const startIso = toIsoUtc(startDateRaw);
    if (dueIso) data.Due_Date = dueIso;
    if (startIso) data.Start_Date = startIso;

    // Address validation: require plausible address, coordinates, OR Address_Notes provided
    const addr = (data.Job_Address || '').toString().trim();
    const addrNotes = (data.Address_Notes || '').toString().trim();
    
    // Check if it looks like coordinates: pattern "lat, lon" or "lat,lon"
    const coordPattern = /^-?\d+\.?\d*\s*,\s*-?\d+\.?\d*$/;
    const isCoordinates = coordPattern.test(addr);
    
    // Check if it looks like a real address: has digit(s) and letter(s), at least 5 chars
    const hasDigit = /\d/.test(addr);
    const hasAlpha = /[A-Za-z]/.test(addr);
    const isAddress = addr.length >= 5 && hasDigit && hasAlpha;
    
    const addressLooksValid = isCoordinates || isAddress;
    if (!addressLooksValid && addrNotes.length < 5) {
      return c.json({
        success: false,
        message: 'Provide a real address (e.g., 123 Main St, City, State), coordinates (e.g., 34.0522, -118.2437), or enter directions in Address Notes.'
      }, 400);
    }
    data.Job_Address = addr;
    data.Address_Notes = addrNotes;

// Job name validation: must be at least 4 characters, contain letters, not symbols-only, not "n/a"
    const jobName = (data.Job_Name || '').toString().trim();
    const invalidLiterals = ['n/a', 'na', 'n.a.', 'n\\a', 'none'];
    const onlySymbols = /^[^A-Za-z0-9]+$/.test(jobName);
    const hasLetters = /[A-Za-z]/.test(jobName);
    const isInvalidLiteral = invalidLiterals.includes(jobName.toLowerCase());
    const isLongEnough = jobName.length >= 4 || jobName.toUpperCase() === "UNK";
    if (!isLongEnough || (!hasLetters && jobName.toUpperCase() !== "UNK") || onlySymbols || isInvalidLiteral) {
      return c.json({
        success: false,
        message: 'Job name must be at least 4 characters with letters (no symbols-only, no N/A).'
        }, 400);
      }
      data.Job_Name = jobName;

    // Enforce Job_Type and Job_Division selection
    const jobType = (data.Job_Type || '').toString().trim();
    const jobDivision = (data.Job_Division || '').toString().trim();
    if (!jobType || !jobDivision) {
      return c.json({
        success: false,
        message: 'Please select both Job Type and Job Division.'
      }, 400);
    }

    // Get all existing job numbers and find the max
    const existingJobs = await pb.collection(activeConfig.pbCollection).getFullList({
      fields: 'Job_Number',
      sort: '-created',
    });

    let maxJobNumber = 0;
    existingJobs.forEach((job: any) => {
      const jobNum = parseInt(job.Job_Number, 10);
      if (!isNaN(jobNum) && jobNum > maxJobNumber) {
        maxJobNumber = jobNum;
      }
    });

    // Create new job number as string
    const newJobNumber = String(maxJobNumber + 1);
    data.Job_Number = newJobNumber;

    // Remove pbToken from data before creating record
    delete data.pbToken;

    // Create the record in PocketBase as the authenticated user
    const record = await pb.collection(activeConfig.pbCollection).create(data);
    console.log(`✓ Created PocketBase record with Job_Number: ${newJobNumber}, ID: ${record.id}`);

    // Persist calculated fields to PocketBase immediately after creation
    try {
      const fullName = calculateJobFullName(record);
      const qbLink = calculateJobQbLink(record);
      const voxerLinkVal = calculateVoxerLink(record);
      const jobCodesVal = calculateJobCodes(record);

      await pb.collection(activeConfig.pbCollection).update(record.id, {
        Job_Full_Name: fullName,
        Job_QB_Link: qbLink,
        Voxer_Link: voxerLinkVal,
        Job_Codes: jobCodesVal
      });

      // Keep local record in sync for Excel add
      (record as any).Job_Full_Name = fullName;
      (record as any).Job_QB_Link = qbLink;
      (record as any).Voxer_Link = voxerLinkVal;
      (record as any).Job_Codes = jobCodesVal;
      console.log('✓ Updated PB with calculated fields (Full Name, QB, Voxer, Codes)');
    } catch (pbUpdateErr: any) {
      console.error('⚠️ Failed to update PB calculated fields:', pbUpdateErr);
      await logError('PocketBase Calculated Fields Update', pbUpdateErr, { recordId: record.id });
    }

    // Add to Excel first with pb_id = record.id (dynamic formulas for Due_Date_Counter, Active)
    try {
      await addToExcel(record, record.id);
      console.log('✓ Excel row created');
    } catch (excelError: any) {
      console.error('⚠️ Excel sync failed, but PocketBase record was created:', excelError);
      return c.json({ 
        success: true, 
        warning: 'Job created in PocketBase but Excel sync failed',
        message: `Job created successfully with Job Number: ${newJobNumber}`,
        jobId: record.id,
        jobNumber: newJobNumber,
        jobFolderLink: null,
        excelError: excelError.message
      });
    }

    // After Excel is added, create folder structure and capture share link
    let jobFolderLink = '';
    try {
      const graphToken = await getGraphToken();
      const folderResult = await processNewJobRecord(record, graphToken) as any;
    
      if (folderResult.success) {
        jobFolderLink = folderResult.shareLink || '';
        console.log(`✓ Folder created and share link captured: ${jobFolderLink}`);
        console.log(`📁 Open folder: ${jobFolderLink}`);
      } else {
        console.warn(`⚠️ Folder creation failed: ${folderResult.error}`);
      }
    } catch (folderError: any) {
      console.error('⚠️ Folder creation failed, continuing without folder link:', folderError);
      await logError('Folder Creation', folderError, {
        jobNumber: newJobNumber,
        recordId: record.id
      });
    }

    // Update Excel row with Job_Folder_Link (if available)
    if (jobFolderLink) {
      try {
        await updateExcelFolderLink(record.id, jobFolderLink);
      } catch (excelUpdateErr: any) {
        console.error('⚠️ Failed to update Excel with folder link:', excelUpdateErr);
        await logError('Excel Update Folder Link', excelUpdateErr, { recordId: record.id, jobFolderLink });
      }
    }

    // Persist static values back to PocketBase: Job_Folder_Link, Due_Date_Counter, Active
    try {
      const dueStatic = calculateDueDateCounter(record);
      const activeStatic = calculateActive(record);
      await pb.collection(activeConfig.pbCollection).update(record.id, {
        Job_Folder_Link: jobFolderLink || '',
        Due_Date_Counter: dueStatic,
        Active: activeStatic
      });
      (record as any).Job_Folder_Link = jobFolderLink || '';
      (record as any).Due_Date_Counter = dueStatic;
      (record as any).Active = activeStatic;
      console.log('✓ Updated PB with folder link, due date counter (static), and active (boolean)');
    } catch (pbFinalizeErr: any) {
      console.error('⚠️ Failed to update PB with final static values:', pbFinalizeErr);
      await logError('PocketBase Final Update', pbFinalizeErr, { recordId: record.id, jobFolderLink });
    }

    // Create Planner task for the new job (assigned to aewing@cardoza.construction)
    try {
      const jobFullName = (record as any).Job_Full_Name || '';

      await createPlannerTask({
        jobNumber: newJobNumber,
        jobFullName,
        dueDate: data.Due_Date || undefined,
      });
      console.log('✓ Planner task created successfully');
    } catch (plannerError: any) {
      console.error('⚠️ Planner task creation failed, continuing:', plannerError.message);
      await logError('Planner Task Creation', plannerError, {
        jobNumber: newJobNumber,
        recordId: record.id,
      });
      // Don't fail the response; Planner is optional
    }

    // Send Teams notification (fire and forget, don't block response)
    notifyTeamsChannel(record).catch(err => {
      console.error('⚠️ Teams notification error:', err.message);
    });

    return c.json({ 
      success: true, 
      message: `Job created successfully with Job Number: ${newJobNumber} and synced to Excel`,
      jobId: record.id,
      jobNumber: newJobNumber,
      jobFolderLink: jobFolderLink || null
    });
  } catch (error: any) {
    console.error('✗ Error submitting job:', error);
    await logError('Job Submission', error, {
      jobName: data?.Job_Name,
      userId: pb.authStore.record?.id
    });
    return c.json({ 
      success: false, 
      message: error.message || 'Failed to create job' 
    }, 400);
  }
});

// Health check endpoint
app.get('/api/health', (c) => {
  return c.json({ 
    status: 'ok',
    service: 'job-creation-with-excel-sync',
    timestamp: new Date().toISOString()
  });
});

// List channels in a Team (to obtain Channel IDs)
app.get('/api/teams/:teamId/channels', async (c) => {
  const teamId = c.req.param('teamId');
  try {
    const authHeader = c.req.header('authorization') || c.req.header('Authorization');
    const bearer = authHeader?.startsWith('Bearer ') ? authHeader.slice(7) : null;
    const channels = bearer
      ? await listTeamChannelsWithToken(teamId, bearer)
      : await listTeamChannels(teamId);
    return c.json({ success: true, teamId, channels });
  } catch (err: any) {
    console.error('⚠️ Failed to list channels:', err);
    await logError('Graph List Channels', err, { teamId });
    return c.json({ success: false, message: err.message || 'Failed to list channels' }, 500);
  }
});

// List messages in a channel (to obtain message IDs)
app.get('/api/teams/:teamId/channels/:channelId/messages', async (c) => {
  const teamId = c.req.param('teamId');
  const channelId = c.req.param('channelId');
  const top = Number(c.req.query('top') || '50');
  try {
    const authHeader = c.req.header('authorization') || c.req.header('Authorization');
    const bearer = authHeader?.startsWith('Bearer ') ? authHeader.slice(7) : null;
    const messages = bearer
      ? await listChannelMessagesWithToken(teamId, channelId, Math.min(top, 50), bearer)
      : await listChannelMessages(teamId, channelId, Math.min(top, 50));
    return c.json({ success: true, teamId, channelId, count: messages.length, messages });
  } catch (err: any) {
    console.error('⚠️ Failed to list messages:', err);
    await logError('Graph List Messages', err, { teamId, channelId });
    return c.json({ success: false, message: err.message || 'Failed to list messages' }, 500);
  }
});

// Delete a message in a channel (requires appropriate Graph permissions)
app.delete('/api/teams/:teamId/channels/:channelId/messages/:messageId', async (c) => {
  const teamId = c.req.param('teamId');
  const channelId = c.req.param('channelId');
  const messageId = c.req.param('messageId');
  try {
    const authHeader = c.req.header('authorization') || c.req.header('Authorization');
    const bearer = authHeader?.startsWith('Bearer ') ? authHeader.slice(7) : null;
    const tokenType = bearer ? 'Bearer token provided' : 'Using app credentials';
    console.log(`🗑️ DELETE request: ${tokenType} | Team: ${teamId} | Channel: ${channelId} | Message: ${messageId}`);
    if (bearer) {
      await deleteChannelMessageWithToken(teamId, channelId, messageId, bearer);
    } else {
      await deleteChannelMessage(teamId, channelId, messageId);
    }
    console.log(`✅ Successfully deleted message: ${messageId}`);
    return c.json({ success: true, teamId, channelId, messageId });
  } catch (err: any) {
    const errMsg = err?.message || JSON.stringify(err);
    const errStatus = err?.status || err?.statusCode;
    
    // Check if it's the Teams API limitation error
    const isTeamsLimitation = errMsg?.includes('Teams API limitation') || errMsg?.includes('not supported');
    
    console.error(`${isTeamsLimitation ? '⚠️' : '❌'} Failed to delete message: ${errMsg} | Status: ${errStatus}`);
    if (isTeamsLimitation) {
      console.log(`📝 Note: This is a Microsoft Teams platform limitation, not an application error`);
    }
    
    await logError('Graph Delete Message', err, { 
      teamId, 
      channelId, 
      messageId, 
      error: errMsg, 
      status: errStatus,
      isTeamsLimitation 
    });
    
    return c.json({ 
      success: false, 
      message: errMsg || 'Failed to delete message',
      isTeamsLimitation: isTeamsLimitation || false
    }, 500);
  }
});

// Test route: sends an exact sample Adaptive Card payload (no envelope)
app.post('/api/test-notification', async (c) => {
  const webhook = process.env.POWER_AUTOMATE_WEBHOOK_URL || process.env.TEAMS_WEBHOOK_URL;
  if (!webhook) {
    return c.json({ success: false, message: 'Webhook not configured' }, 400);
  }

  const payload = {
    $schema: 'http://adaptivecards.io/schemas/adaptive-card.json',
    type: 'AdaptiveCard',
    version: '1.4',
    body: [
      { type: 'TextBlock', text: 'New Job!', weight: 'Bolder', size: 'Large' },
      { type: 'TextBlock', text: '4766 - Teams Card v1.4', wrap: true },
      {
        type: 'FactSet',
        facts: [
          { title: 'Number', value: '4766' },
          { title: 'Name', value: 'Teams Card v1.4' },
          { title: 'Address', value: 'Blue eye' },
          { title: 'Client', value: 'ClientCo' },
          { title: 'Division', value: 'C#' },
          { title: 'Type', value: 'PWEX' },
          { title: 'Contact', value: 'Wyatt Gann - (417) 429-1417 x117' }
        ]
      }
    ],
    actions: [
      {
        type: 'Action.OpenUrl',
        title: 'Open Folder',
        url: 'https://czflex.sharepoint.com/:f:/s/Team/IgCD37kQLFb6Q7BsoWjFb5rlAWkqMkqyX2zhoQpRMh9hUiI'
      }
    ]
  };

  const resp = await fetch(webhook, {
    method: 'POST',
    headers: { 'Content-Type': 'application/json' },
    body: JSON.stringify(payload)
  });

  const ok = resp.ok;
  const text = await resp.text();
  return c.json({ success: ok, status: resp.status, body: text });
});

// Test route: sends the same sample card wrapped in a Teams message envelope
app.post('/api/test-notification-envelope', async (c) => {
  const webhook = process.env.POWER_AUTOMATE_WEBHOOK_URL || process.env.TEAMS_WEBHOOK_URL;
  if (!webhook) {
    return c.json({ success: false, message: 'Webhook not configured' }, 400);
  }

  const card = {
    $schema: 'http://adaptivecards.io/schemas/adaptive-card.json',
    type: 'AdaptiveCard',
    version: '1.4',
    body: [
      { type: 'TextBlock', text: 'New Job!', weight: 'Bolder', size: 'Large' },
      { type: 'TextBlock', text: '4766 - Teams Card v1.4', wrap: true },
      {
        type: 'FactSet',
        facts: [
          { title: 'Number', value: '4766' },
          { title: 'Name', value: 'Teams Card v1.4' },
          { title: 'Address', value: 'Blue eye' },
          { title: 'Client', value: 'ClientCo' },
          { title: 'Division', value: 'C#' },
          { title: 'Type', value: 'PWEX' },
          { title: 'Contact', value: 'Wyatt Gann - (417) 429-1417 x117' }
        ]
      }
    ],
    actions: [
      {
        type: 'Action.OpenUrl',
        title: 'Open Folder',
        url: 'https://czflex.sharepoint.com/:f:/s/Team/IgCD37kQLFb6Q7BsoWjFb5rlAWkqMkqyX2zhoQpRMh9hUiI'
      }
    ]
  };

  const payload = {
    type: 'message',
    text: 'New Job!',
    attachments: [
      {
        contentType: 'application/vnd.microsoft.card.adaptive',
        content: card
      }
    ]
  };

  const resp = await fetch(webhook, {
    method: 'POST',
    headers: { 'Content-Type': 'application/json' },
    body: JSON.stringify(payload)
  });

  const ok = resp.ok;
  const text = await resp.text();
  return c.json({ success: ok, status: resp.status, body: text });
});

// Test route: post exact sample card directly to Teams Incoming Webhook (no envelope)
app.post('/api/test-teams-webhook', async (c) => {
  const webhook = process.env.TEAMS_WEBHOOK_URL;
  if (!webhook) {
    return c.json({ success: false, message: 'TEAMS_WEBHOOK_URL not configured' }, 400);
  }

  // Format timestamp for the card
  const now = new Date();
  const timestamp = now.toLocaleString('en-US', {
    month: 'short',
    day: 'numeric',
    year: 'numeric',
    hour: 'numeric',
    minute: '2-digit',
    hour12: true
  });

  const adaptiveCard = {
    $schema: 'http://adaptivecards.io/schemas/adaptive-card.json',
    type: 'AdaptiveCard',
    version: '1.4',
    body: [
      {
        type: 'Container',
        style: 'emphasis',
        items: [
          { type: 'TextBlock', text: 'New Job!', weight: 'Bolder', size: 'Large', spacing: 'None' },
          { type: 'TextBlock', text: '**Number:** 4738', spacing: 'Small' },
          { type: 'TextBlock', text: '**Name:** New VO-AG Facility & Early Childhood Addition', spacing: 'None' },
          { type: 'TextBlock', text: '**Address:** Blue eye', spacing: 'None' },
          { type: 'TextBlock', text: '**Division:** C#', spacing: 'None' },
          { type: 'TextBlock', text: timestamp, size: 'Small', isSubtle: true, spacing: 'Small' }
        ]
      }
    ]
  };

  const payload = {
    type: 'message',
    attachments: [
      {
        contentType: 'application/vnd.microsoft.card.adaptive',
        contentUrl: null,
        content: adaptiveCard
      }
    ]
  };

  const resp = await fetch(webhook, {
    method: 'POST',
    headers: { 'Content-Type': 'application/json' },
    body: JSON.stringify(payload)
  });

  const ok = resp.ok;
  const text = await resp.text();
  return c.json({ success: ok, status: resp.status, body: text });
});

// Serve static files from frontend directory
app.use('/*', serveStatic({ root: './frontend' }));

const PORT = Number(process.env.PORT || 3020);

console.log(`Job Creation with Excel Sync server running at http://localhost:${PORT}`);

// Initialize table cache only (non-blocking)
initializeTableCache().catch((error) => {
  console.error('✗ Failed to initialize table cache:', error);
});

export default {
  port: PORT,
  fetch: app.fetch,
};