const express = require('express');
const { pool } = require('./db');
const { encryptText } = require('./crypto');
const { discoverAppleCalendars } = require('./caldav');
const { collectEvents } = require('./calendar');
const { upsertDemoData } = require('./demo');
const { createGoogleAuthUrl, getGoogleConnection, listGoogleCalendars } = require('./google');

const providers = new Set(['ical', 'google', 'google_oauth', 'apple', 'demo']);

function validColor(value) {
  return /^#[0-9a-f]{6}$/i.test(String(value || ''));
}

function parseId(value) {
  const id = Number(value);
  return Number.isInteger(id) && id > 0 ? id : null;
}

function cleanSource(row) {
  return {
    id: row.id,
    provider: row.provider,
    label: row.label,
    url: row.url,
    username: row.username,
    calendarHref: row.calendar_href,
    syncStatus: row.sync_status,
    syncError: row.sync_error,
    lastSyncedAt: row.last_synced_at,
    nextSyncAt: row.next_sync_at
  };
}

function formatDateValue(value) {
  if (!value) {
    return null;
  }

  if (value instanceof Date) {
    const year = value.getFullYear();
    const month = String(value.getMonth() + 1).padStart(2, '0');
    const day = String(value.getDate()).padStart(2, '0');
    return `${year}-${month}-${day}`;
  }

  return String(value).slice(0, 10);
}

function cleanDueDate(value) {
  if (!value) {
    return null;
  }

  const date = String(value).trim();
  if (!/^\d{4}-\d{2}-\d{2}$/.test(date)) {
    throw new Error('Due date must be a valid date.');
  }

  const parsed = new Date(`${date}T00:00:00`);
  if (Number.isNaN(parsed.getTime()) || formatDateValue(parsed) !== date) {
    throw new Error('Due date must be a valid date.');
  }

  return date;
}

function cleanApplePassword(value) {
  return String(value || '').replace(/\s+/g, '');
}

async function createTodoList(connection, list) {
  const name = String(list?.name || '').trim();
  const color = String(list?.color || '').trim();
  if (!name) {
    throw new Error('List name is required.');
  }
  if (!validColor(color)) {
    throw new Error('Choose a valid list colour.');
  }

  const [[orderRow]] = await connection.query(
    'SELECT COALESCE(MAX(display_order), -1) + 1 AS next_order FROM todo_lists'
  );
  const [result] = await connection.query(
    'INSERT INTO todo_lists (name, color, display_order) VALUES (?, ?, ?)',
    [name, color, orderRow.next_order]
  );

  return result.insertId;
}

async function getState() {
  const [profiles] = await pool.query(`
    SELECT id, name, color, display_order
    FROM profiles
    ORDER BY display_order, id
  `);

  const [todoLists] = await pool.query(`
    SELECT id, name, color, display_order
    FROM todo_lists
    ORDER BY display_order, id
  `);

  const [todos] = await pool.query(`
    SELECT id, list_id, title, due_date, completed, display_order
    FROM todos
    WHERE archived = 0
      AND list_id IS NOT NULL
    ORDER BY display_order, id
  `);

  const [sourceRows] = await pool.query(`
    SELECT
      cs.id,
      cs.provider,
      cs.label,
      cs.url,
      cs.username,
      cs.calendar_href,
      cs.google_connection_id,
      cs.sync_status,
      cs.sync_error,
      cs.last_synced_at,
      cs.next_sync_at,
      pc.profile_id
    FROM calendar_sources cs
    LEFT JOIN profile_calendars pc ON pc.calendar_source_id = cs.id
    ORDER BY cs.label, cs.id
  `);

  const profileMap = new Map(
    profiles.map((profile) => [
      profile.id,
      {
        id: profile.id,
        name: profile.name,
        color: profile.color,
        displayOrder: profile.display_order,
        calendarSources: []
      }
    ])
  );

  const todoListMap = new Map(
    todoLists.map((list) => [
      list.id,
      {
        id: list.id,
        name: list.name,
        color: list.color,
        displayOrder: list.display_order,
        todos: []
      }
    ])
  );

  for (const todo of todos) {
    const list = todoListMap.get(todo.list_id);
    if (list) {
      list.todos.push({
        id: todo.id,
        title: todo.title,
        dueDate: formatDateValue(todo.due_date),
        completed: Boolean(todo.completed),
        displayOrder: todo.display_order
      });
    }
  }

  const sourcesById = new Map();
  for (const row of sourceRows) {
    if (!sourcesById.has(row.id)) {
      sourcesById.set(row.id, cleanSource(row));
    }

    const profile = profileMap.get(row.profile_id);
    if (profile) {
      profile.calendarSources.push(cleanSource(row));
    }
  }

  return {
    profiles: [...profileMap.values()],
    todoLists: [...todoListMap.values()],
    calendarSources: [...sourcesById.values()]
  };
}

async function createCalendarSource(connection, calendar) {
  if (!calendar || calendar.mode === 'none') {
    return null;
  }

  if (calendar.mode === 'existing') {
    const sourceId = parseId(calendar.sourceId);
    if (!sourceId) {
      throw new Error('Choose a saved calendar source.');
    }

    const [sources] = await connection.query('SELECT id FROM calendar_sources WHERE id = ? LIMIT 1', [sourceId]);
    if (!sources.length) {
      throw new Error('Saved calendar source was not found.');
    }

    return sourceId;
  }

  const provider = String(calendar.provider || '').toLowerCase();
  if (!providers.has(provider) || provider === 'demo') {
    throw new Error('Choose a supported calendar provider.');
  }

  const label = String(calendar.label || '').trim();
  if (!label) {
    throw new Error('Calendar label is required.');
  }

  if (provider === 'apple') {
    const username = String(calendar.username || '').trim();
    const password = cleanApplePassword(calendar.appPassword);
    const href = String(calendar.href || '').trim();
    if (!username || !password || !href) {
      throw new Error('Apple username, app-specific password, and calendar selection are required.');
    }

    const [result] = await connection.query(
      `INSERT INTO calendar_sources (provider, label, username, encrypted_password, calendar_href)
       VALUES (?, ?, ?, ?, ?)`,
      [provider, label, username, encryptText(password), href]
    );
    return result.insertId;
  }

  if (provider === 'google_oauth') {
    const connectionId = parseId(calendar.connectionId);
    const calendarId = String(calendar.calendarId || '').trim();
    if (!connectionId || !calendarId) {
      throw new Error('Connect Google and choose a calendar.');
    }

    const googleConnection = await getGoogleConnection(connectionId);
    const [result] = await connection.query(
      `INSERT INTO calendar_sources (provider, label, username, calendar_href, google_connection_id)
       VALUES (?, ?, ?, ?, ?)`,
      [provider, label, googleConnection.account_email, calendarId, connectionId]
    );
    return result.insertId;
  }

  const url = String(calendar.url || '').trim();
  if (!/^webcal:\/\//i.test(url) && !/^https?:\/\//i.test(url)) {
    throw new Error('Calendar link must be an http, https, or webcal URL.');
  }

  const [result] = await connection.query(
    'INSERT INTO calendar_sources (provider, label, url) VALUES (?, ?, ?)',
    [provider, label, url]
  );
  return result.insertId;
}

function createApiRouter() {
  const router = express.Router();

  router.get('/state', async (req, res, next) => {
    try {
      res.json(await getState());
    } catch (error) {
      next(error);
    }
  });

  router.post('/profiles', async (req, res, next) => {
    const name = String(req.body.name || '').trim();
    const color = String(req.body.color || '').trim();
    if (!name) {
      res.status(400).json({ error: 'Name is required.' });
      return;
    }
    if (!validColor(color)) {
      res.status(400).json({ error: 'Choose a valid profile colour.' });
      return;
    }

    const connection = await pool.getConnection();
    try {
      await connection.beginTransaction();
      const [[orderRow]] = await connection.query(
        'SELECT COALESCE(MAX(display_order), -1) + 1 AS next_order FROM profiles'
      );
      const [profileResult] = await connection.query(
        'INSERT INTO profiles (name, color, display_order) VALUES (?, ?, ?)',
        [name, color, orderRow.next_order]
      );

      const sourceId = await createCalendarSource(connection, req.body.calendar);
      if (sourceId) {
        await connection.query(
          'INSERT IGNORE INTO profile_calendars (profile_id, calendar_source_id, display_order) VALUES (?, ?, 0)',
          [profileResult.insertId, sourceId]
        );
      }

      await connection.commit();
      res.status(201).json(await getState());
    } catch (error) {
      await connection.rollback();
      res.status(400).json({ error: error.message });
    } finally {
      connection.release();
    }
  });

  router.patch('/profiles/order', async (req, res, next) => {
    const ids = Array.isArray(req.body.ids) ? req.body.ids.map(parseId).filter(Boolean) : [];
    if (!ids.length) {
      res.status(400).json({ error: 'Profile order is required.' });
      return;
    }

    const connection = await pool.getConnection();
    try {
      await connection.beginTransaction();
      for (const [index, id] of ids.entries()) {
        await connection.query('UPDATE profiles SET display_order = ? WHERE id = ?', [index, id]);
      }
      await connection.commit();
      res.json(await getState());
    } catch (error) {
      await connection.rollback();
      next(error);
    } finally {
      connection.release();
    }
  });

  router.get('/google/auth-url', (req, res) => {
    try {
      res.json(createGoogleAuthUrl());
    } catch (error) {
      res.status(503).json({ error: error.message });
    }
  });

  router.get('/google/calendars', async (req, res) => {
    const connectionId = parseId(req.query.connectionId);
    if (!connectionId) {
      res.status(400).json({ error: 'Google connection is required.' });
      return;
    }

    try {
      res.json({ calendars: await listGoogleCalendars(connectionId) });
    } catch (error) {
      res.status(400).json({ error: error.message });
    }
  });

  router.post('/calendar/discover', async (req, res) => {
    const provider = String(req.body.provider || '').toLowerCase();
    if (provider !== 'apple') {
      res.status(400).json({ error: 'Apple is the only provider that needs discovery in this MVP.' });
      return;
    }

    const username = String(req.body.username || '').trim();
    const password = cleanApplePassword(req.body.appPassword);
    if (!username || !password) {
      res.status(400).json({ error: 'Apple username and app-specific password are required.' });
      return;
    }

    try {
      res.json({ calendars: await discoverAppleCalendars(username, password) });
    } catch (error) {
      res.status(400).json({ error: error.message });
    }
  });

  router.get('/events', async (req, res, next) => {
    const rangeStart = new Date(req.query.start);
    const rangeEnd = new Date(req.query.end);
    if (Number.isNaN(rangeStart.getTime()) || Number.isNaN(rangeEnd.getTime()) || rangeStart >= rangeEnd) {
      res.status(400).json({ error: 'Valid start and end query parameters are required.' });
      return;
    }

    try {
      res.json(await collectEvents(rangeStart, rangeEnd));
    } catch (error) {
      next(error);
    }
  });

  router.post('/todos', async (req, res, next) => {
    let listId = parseId(req.body.listId);
    const title = String(req.body.title || '').trim();
    let dueDate;
    try {
      dueDate = cleanDueDate(req.body.dueDate);
    } catch (error) {
      res.status(400).json({ error: error.message });
      return;
    }

    if (!title) {
      res.status(400).json({ error: 'Todo title is required.' });
      return;
    }

    const connection = await pool.getConnection();
    try {
      await connection.beginTransaction();

      if (!listId) {
        listId = await createTodoList(connection, req.body.list);
      } else {
        const [lists] = await connection.query('SELECT id FROM todo_lists WHERE id = ? LIMIT 1', [listId]);
        if (!lists.length) {
          throw new Error('Todo list was not found.');
        }
      }

      const [[orderRow]] = await connection.query(
        'SELECT COALESCE(MAX(display_order), -1) + 1 AS next_order FROM todos WHERE list_id = ? AND archived = 0',
        [listId]
      );
      await connection.query(
        'INSERT INTO todos (list_id, title, due_date, display_order) VALUES (?, ?, ?, ?)',
        [listId, title, dueDate, orderRow.next_order]
      );

      await connection.commit();
      res.status(201).json(await getState());
    } catch (error) {
      await connection.rollback();
      res.status(400).json({ error: error.message });
    } finally {
      connection.release();
    }
  });

  router.patch('/todos/order', async (req, res, next) => {
    const listId = parseId(req.body.listId);
    const ids = Array.isArray(req.body.ids) ? req.body.ids.map(parseId).filter(Boolean) : [];
    if (!listId || !ids.length) {
      res.status(400).json({ error: 'List and todo order are required.' });
      return;
    }

    const connection = await pool.getConnection();
    try {
      await connection.beginTransaction();
      for (const [index, id] of ids.entries()) {
        await connection.query(
          'UPDATE todos SET display_order = ? WHERE id = ? AND list_id = ?',
          [index, id, listId]
        );
      }
      await connection.commit();
      res.json(await getState());
    } catch (error) {
      await connection.rollback();
      next(error);
    } finally {
      connection.release();
    }
  });

  router.patch('/todos/:id', async (req, res, next) => {
    const id = parseId(req.params.id);
    if (!id) {
      res.status(400).json({ error: 'Todo id is required.' });
      return;
    }

    const connection = await pool.getConnection();
    try {
      await connection.beginTransaction();

      const [todoRows] = await connection.query('SELECT id, list_id FROM todos WHERE id = ? LIMIT 1 FOR UPDATE', [id]);
      const todoRow = todoRows[0];
      if (!todoRow) {
        await connection.rollback();
        res.status(404).json({ error: 'Todo was not found.' });
        return;
      }

      const fields = [];
      const params = [];
      if (Object.prototype.hasOwnProperty.call(req.body, 'title')) {
        const title = String(req.body.title || '').trim();
        if (!title) {
          await connection.rollback();
          res.status(400).json({ error: 'Todo title cannot be empty.' });
          return;
        }
        fields.push('title = ?');
        params.push(title);
      }
      if (Object.prototype.hasOwnProperty.call(req.body, 'dueDate')) {
        try {
          fields.push('due_date = ?');
          params.push(cleanDueDate(req.body.dueDate));
        } catch (error) {
          await connection.rollback();
          res.status(400).json({ error: error.message });
          return;
        }
      }
      if (Object.prototype.hasOwnProperty.call(req.body, 'listId')) {
        const listId = parseId(req.body.listId);
        if (!listId) {
          await connection.rollback();
          res.status(400).json({ error: 'Todo list is required.' });
          return;
        }

        const [lists] = await connection.query('SELECT id FROM todo_lists WHERE id = ? LIMIT 1', [listId]);
        if (!lists.length) {
          await connection.rollback();
          res.status(400).json({ error: 'Todo list was not found.' });
          return;
        }

        fields.push('list_id = ?');
        params.push(listId);
        if (Number(todoRow.list_id) !== listId) {
          const [[orderRow]] = await connection.query(
            'SELECT COALESCE(MAX(display_order), -1) + 1 AS next_order FROM todos WHERE list_id = ? AND archived = 0',
            [listId]
          );
          fields.push('display_order = ?');
          params.push(orderRow.next_order);
        }
      }
      if (Object.prototype.hasOwnProperty.call(req.body, 'completed')) {
        fields.push('completed = ?');
        params.push(req.body.completed ? 1 : 0);
        fields.push('completed_at = ?');
        params.push(req.body.completed ? new Date() : null);
      }
      if (Object.prototype.hasOwnProperty.call(req.body, 'archived')) {
        fields.push('archived = ?');
        params.push(req.body.archived ? 1 : 0);
      }

      if (!fields.length) {
        await connection.rollback();
        res.status(400).json({ error: 'No todo changes were provided.' });
        return;
      }

      await connection.query(`UPDATE todos SET ${fields.join(', ')} WHERE id = ?`, [...params, id]);
      await connection.commit();
      res.json(await getState());
    } catch (error) {
      await connection.rollback();
      next(error);
    } finally {
      connection.release();
    }
  });

  router.patch('/todo-lists/order', async (req, res, next) => {
    const ids = Array.isArray(req.body.ids) ? req.body.ids.map(parseId).filter(Boolean) : [];
    if (!ids.length) {
      res.status(400).json({ error: 'List order is required.' });
      return;
    }

    const connection = await pool.getConnection();
    try {
      await connection.beginTransaction();
      for (const [index, id] of ids.entries()) {
        await connection.query('UPDATE todo_lists SET display_order = ? WHERE id = ?', [index, id]);
      }
      await connection.commit();
      res.json(await getState());
    } catch (error) {
      await connection.rollback();
      next(error);
    } finally {
      connection.release();
    }
  });

  router.post('/demo/seed', async (req, res, next) => {
    try {
      await upsertDemoData();
      res.status(201).json(await getState());
    } catch (error) {
      next(error);
    }
  });

  router.use((error, req, res, next) => {
    console.error(error);
    res.status(500).json({ error: 'Something went wrong.' });
  });

  return router;
}

module.exports = { createApiRouter, getState };