/**
 * Google Sheets Route
 * Uses the same service account as GSC — no extra auth needed.
 * Just enable Google Sheets API in Cloud Console and share
 * each spreadsheet with the service account email.
 *
 * Supports multiple sheets. Each sheet is configured in .env as:
 *   GOOGLE_SHEETS=[{"id":"spreadsheet_id","name":"Weekly Plan","tab":"Sheet1"}]
 *
 * Auto-detects columns. Looks for a status column named any of:
 *   Status, status, STATUS, State, Progress, Done
 * And maps values to: todo / in_progress / done
 */

const express = require('express');
const { google } = require('googleapis');
const NodeCache = require('node-cache');

const router = express.Router();
const cache = new NodeCache({ stdTTL: parseInt(process.env.CACHE_TTL) || 120 });

function getAuth() {
  if (process.env.GOOGLE_SERVICE_ACCOUNT_EMAIL && process.env.GOOGLE_PRIVATE_KEY) {
    return new google.auth.JWT({
      email: process.env.GOOGLE_SERVICE_ACCOUNT_EMAIL,
      key: process.env.GOOGLE_PRIVATE_KEY.replace(/\\n/g, '\n'),
      scopes: [
        'https://www.googleapis.com/auth/spreadsheets.readonly',
        'https://www.googleapis.com/auth/drive.readonly',
      ],
    });
  }
  const oauth2Client = new google.auth.OAuth2(
    process.env.GOOGLE_CLIENT_ID,
    process.env.GOOGLE_CLIENT_SECRET
  );
  oauth2Client.setCredentials({ refresh_token: process.env.GOOGLE_REFRESH_TOKEN });
  return oauth2Client;
}

function getSheetConfigs() {
  try {
    if (process.env.GOOGLE_SHEETS) {
      return JSON.parse(process.env.GOOGLE_SHEETS);
    }
  } catch (e) {
    console.error('Invalid GOOGLE_SHEETS JSON in .env:', e.message);
  }
  return [];
}

// Normalise any status string to: todo | in_progress | done | other
function normaliseStatus(raw = '') {
  const s = raw.toLowerCase().trim();
  if (['done', 'complete', 'completed', 'finished', 'closed', '✅', 'yes'].some(v => s.includes(v))) return 'done';
  if (['in progress', 'in-progress', 'inprogress', 'doing', 'active', 'wip', 'started', '🔵'].some(v => s.includes(v))) return 'in_progress';
  if (['review', 'testing', 'qa', 'pending review', '🟡'].some(v => s.includes(v))) return 'in_review';
  if (['to do', 'todo', 'not started', 'open', 'backlog', '⬜', 'no'].some(v => s.includes(v))) return 'todo';
  return 'other';
}

// Find the index of a column by any of several possible names
function findCol(headers, candidates) {
  for (const name of candidates) {
    const idx = headers.findIndex(h => h.toLowerCase().trim() === name.toLowerCase());
    if (idx !== -1) return idx;
  }
  return -1;
}


// ── GET /api/sheets/debug ─────────────────────────────────────
// Shows exactly what the server sees — use to diagnose config issues
router.get('/debug', async (req, res) => {
  const raw = process.env.GOOGLE_SHEETS || '';
  let parsed = null;
  let parseError = null;
  try { parsed = JSON.parse(raw); } catch(e) { parseError = e.message; }

  // Test auth
  let authOk = false;
  let authError = null;
  try {
    const auth = getAuth();
    await auth.authorize();
    authOk = true;
  } catch(e) { authError = e.message; }

  // Test sheets API access if we have a sheet ID
  let sheetsApiOk = false;
  let sheetsApiError = null;
  if (parsed?.[0]?.id) {
    try {
      const auth = getAuth();
      const sheets = google.sheets({ version: 'v4', auth });
      await sheets.spreadsheets.get({ spreadsheetId: parsed[0].id });
      sheetsApiOk = true;
    } catch(e) { sheetsApiError = e.message; }
  }

  res.json({
    env_GOOGLE_SHEETS_set: !!raw,
    env_GOOGLE_SHEETS_length: raw.length,
    env_GOOGLE_SHEETS_preview: raw.slice(0, 120),
    parseOk: !parseError,
    parseError,
    sheets: parsed,
    authOk,
    authError,
    sheetsApiOk,
    sheetsApiError,
    serviceAccountEmail: process.env.GOOGLE_SERVICE_ACCOUNT_EMAIL || 'NOT SET',
  });
});

// ── GET /api/sheets/list ──────────────────────────────────────
// Returns the configured sheet list from .env
router.get('/list', (req, res) => {
  const sheets = getSheetConfigs();
  res.json(sheets.map(s => ({
    id: s.id,
    name: s.name || s.id,
    tab: s.tab || null,
    color: s.color || null,
  })));
});

// ── GET /api/sheets/data?id=&tab= ─────────────────────────────
// Reads a spreadsheet and returns structured rows + progress summary
router.get('/data', async (req, res, next) => {
  const { id, tab } = req.query;
  if (!id) return res.status(400).json({ error: 'id param required' });

  const cacheKey = `sheets_${id}_${tab || 'default'}`;
  const cached = cache.get(cacheKey);
  if (cached) return res.json(cached);

  try {
    const auth = getAuth();
    const sheets = google.sheets({ version: 'v4', auth });

    // If no tab specified, get the first sheet name
    let sheetTab = tab;
    if (!sheetTab) {
      const meta = await sheets.spreadsheets.get({ spreadsheetId: id });
      sheetTab = meta.data.sheets?.[0]?.properties?.title || 'Sheet1';
    }

    const resp = await sheets.spreadsheets.values.get({
      spreadsheetId: id,
      range: sheetTab,
    });

    const rows = resp.data.values || [];
    if (rows.length === 0) return res.json({ headers: [], rows: [], summary: emptySummary(), sheetTab });

    const headers = rows[0].map(h => String(h).trim());
    const dataRows = rows.slice(1).filter(r => r.some(cell => cell?.toString().trim()));

    // Auto-detect key columns
    const statusIdx     = findCol(headers, ['status', 'state', 'progress', 'done', 'completed']);
    const taskIdx       = findCol(headers, ['task', 'title', 'name', 'item', 'description', 'topic', 'keyword', 'url', 'page']);
    const assigneeIdx   = findCol(headers, ['assignee', 'owner', 'assigned to', 'assigned', 'person', 'who']);
    const dueDateIdx    = findCol(headers, ['due date', 'due', 'deadline', 'date', 'week', 'publish date']);
    const priorityIdx   = findCol(headers, ['priority', 'importance', 'level', 'p']);
    const notesIdx      = findCol(headers, ['notes', 'note', 'comments', 'comment', 'details']);
    const siteIdx       = findCol(headers, ['site', 'website', 'domain', 'property']);

    const structured = dataRows.map((row, i) => {
      const rawStatus = statusIdx !== -1 ? (row[statusIdx] || '') : '';
      const status = normaliseStatus(rawStatus);

      // Build a clean object with detected fields + all raw columns
      const obj = {
        _rowIndex: i + 2, // 1-based, +1 for header
        task:     taskIdx     !== -1 ? (row[taskIdx]     || '').trim() : row[0] || '',
        status,
        rawStatus,
        assignee: assigneeIdx !== -1 ? (row[assigneeIdx] || '').trim() : null,
        dueDate:  dueDateIdx  !== -1 ? (row[dueDateIdx]  || '').trim() : null,
        priority: priorityIdx !== -1 ? (row[priorityIdx] || '').trim() : null,
        notes:    notesIdx    !== -1 ? (row[notesIdx]    || '').trim() : null,
        site:     siteIdx     !== -1 ? (row[siteIdx]     || '').trim() : null,
        // All raw columns for full table view
        _raw: headers.reduce((acc, h, idx) => {
          acc[h] = (row[idx] || '').trim();
          return acc;
        }, {}),
      };
      return obj;
    });

    // Progress summary
    const total      = structured.length;
    const done       = structured.filter(r => r.status === 'done').length;
    const inProgress = structured.filter(r => r.status === 'in_progress').length;
    const inReview   = structured.filter(r => r.status === 'in_review').length;
    const todo       = structured.filter(r => r.status === 'todo').length;
    const other      = structured.filter(r => r.status === 'other').length;

    const summary = {
      total,
      done,
      inProgress,
      inReview,
      todo,
      other,
      pctDone: total > 0 ? Math.round((done / total) * 100) : 0,
      pctInProgress: total > 0 ? Math.round((inProgress / total) * 100) : 0,
    };

    // Detect if overdue (dueDate in past and not done)
    const today = new Date();
    structured.forEach(r => {
      if (r.dueDate && r.status !== 'done') {
        const d = new Date(r.dueDate);
        r.overdue = !isNaN(d) && d < today;
      } else {
        r.overdue = false;
      }
    });

    const result = { headers, rows: structured, summary, sheetTab, detectedCols: { statusIdx, taskIdx, assigneeIdx, dueDateIdx, priorityIdx } };
    cache.set(cacheKey, result);
    res.json(result);
  } catch (err) {
    next(err);
  }
});

// ── GET /api/sheets/all ───────────────────────────────────────
// Fetches all configured sheets in one call
router.get('/all', async (req, res, next) => {
  const configs = getSheetConfigs();
  if (configs.length === 0) return res.json([]);

  try {
    const results = await Promise.all(
      configs.map(async cfg => {
        try {
          const auth = getAuth();
          const sheets = google.sheets({ version: 'v4', auth });

          let sheetTab = cfg.tab;
          if (!sheetTab) {
            const meta = await sheets.spreadsheets.get({ spreadsheetId: cfg.id });
            sheetTab = meta.data.sheets?.[0]?.properties?.title || 'Sheet1';
          }

          const resp = await sheets.spreadsheets.values.get({
            spreadsheetId: cfg.id,
            range: sheetTab,
          });

          const rows = resp.data.values || [];
          if (rows.length === 0) return { ...cfg, sheetTab, rows: [], summary: emptySummary(), error: null };

          const headers = rows[0].map(h => String(h).trim());
          const dataRows = rows.slice(1).filter(r => r.some(cell => cell?.toString().trim()));
          const statusIdx = findCol(headers, ['status', 'state', 'progress', 'done', 'completed']);
          const taskIdx   = findCol(headers, ['task', 'title', 'name', 'item', 'description', 'topic', 'keyword', 'url', 'page']);
          const dueDateIdx= findCol(headers, ['due date', 'due', 'deadline', 'date', 'week', 'publish date']);
          const assigneeIdx = findCol(headers, ['assignee', 'owner', 'assigned to', 'person']);
          const priorityIdx = findCol(headers, ['priority', 'importance', 'level']);
          const notesIdx  = findCol(headers, ['notes', 'note', 'comments', 'details']);

          const structured = dataRows.map((row, i) => {
            const rawStatus = statusIdx !== -1 ? (row[statusIdx] || '') : '';
            return {
              _rowIndex: i + 2,
              task:     taskIdx     !== -1 ? (row[taskIdx]     || '').trim() : row[0] || '',
              status:   normaliseStatus(rawStatus),
              rawStatus,
              assignee: assigneeIdx !== -1 ? (row[assigneeIdx] || '').trim() : null,
              dueDate:  dueDateIdx  !== -1 ? (row[dueDateIdx]  || '').trim() : null,
              priority: priorityIdx !== -1 ? (row[priorityIdx] || '').trim() : null,
              notes:    notesIdx    !== -1 ? (row[notesIdx]    || '').trim() : null,
              overdue:  false,
              _raw: headers.reduce((acc, h, idx) => { acc[h] = (row[idx] || '').trim(); return acc; }, {}),
            };
          });

          const today = new Date();
          structured.forEach(r => {
            if (r.dueDate && r.status !== 'done') {
              const d = new Date(r.dueDate);
              r.overdue = !isNaN(d) && d < today;
            }
          });

          const total = structured.length;
          const done  = structured.filter(r => r.status === 'done').length;
          const inProgress = structured.filter(r => r.status === 'in_progress').length;
          const inReview   = structured.filter(r => r.status === 'in_review').length;
          const todo       = structured.filter(r => r.status === 'todo').length;

          return {
            id: cfg.id, name: cfg.name || sheetTab, sheetTab,
            color: cfg.color || null,
            headers, rows: structured,
            summary: {
              total, done, inProgress, inReview, todo,
              other: total - done - inProgress - inReview - todo,
              pctDone: total > 0 ? Math.round((done / total) * 100) : 0,
              pctInProgress: total > 0 ? Math.round((inProgress / total) * 100) : 0,
            },
            error: null,
          };
        } catch (e) {
          return { id: cfg.id, name: cfg.name || cfg.id, rows: [], summary: emptySummary(), error: e.message };
        }
      })
    );
    res.json(results);
  } catch (err) {
    next(err);
  }
});

function emptySummary() {
  return { total: 0, done: 0, inProgress: 0, inReview: 0, todo: 0, other: 0, pctDone: 0, pctInProgress: 0 };
}

module.exports = router;
