import express from 'express';
import cors from 'cors';
import sqlite3 from 'sqlite3';
import path from 'path';
import { fileURLToPath } from 'url';
import moment from 'moment-timezone';
import dotenv from 'dotenv';
import multer from 'multer';
import fs from 'fs';

// Load environment variables
dotenv.config();

const __filename = fileURLToPath(import.meta.url);
const __dirname = path.dirname(__filename);

const app = express();
const port = 3000;

// Load webhook URLs from environment (kept server-side only!)
const ADMIN_WEBHOOK_URL = process.env.ADMIN_WEBHOOK_URL;
const USER_WEBHOOK_URL = process.env.USER_WEBHOOK_URL;

// Read version from package.json
const pkg = JSON.parse(fs.readFileSync(path.resolve(__dirname, 'package.json'), 'utf-8'));
const APP_VERSION = pkg.version || '0.0.0';

app.use(cors());
app.use(express.json());
app.use(express.urlencoded({ extended: false }));
// Never cache version.js — ensures auto cache-busting works
app.get('/version.js', (req, res, next) => {
  res.set('Cache-Control', 'no-store, no-cache, must-revalidate');
  next();
});
app.use(express.static(path.join(__dirname, 'public')));
app.use('/assets', express.static(path.join(__dirname, 'assets')));

// --- Profile Picture Upload (supports user + admin) ---
function createPfpUpload(filename) {
  const storage = multer.diskStorage({
    destination: path.join(__dirname, 'assets'),
    filename: (_req, _file, cb) => cb(null, filename),
  });
  return multer({
    storage,
    limits: { fileSize: 5 * 1024 * 1024 },
    fileFilter: (_req, file, cb) => {
      if (file.mimetype.startsWith('image/')) cb(null, true);
      else cb(new Error('Only image files are allowed'));
    },
  });
}

// --- Categories Config ---
const categoriesPath = path.resolve(__dirname, 'categories.json');
function loadCategories() {
  try {
    const data = fs.readFileSync(categoriesPath, 'utf-8');
    return JSON.parse(data).categories || [];
  } catch {
    return ['HELP', 'IT-Support', 'Suggestion...', 'Other'];
  }
}

// --- Database ---
const dbPath = path.resolve(__dirname, 'data', 'tickets.db');
const db = new sqlite3.Database(dbPath, err => {
  if (err) console.error('Failed to connect to DB:', err.message);
  else console.log('Connected to SQLite DB.');
});

// Enable WAL mode for better concurrent reads
db.run('PRAGMA journal_mode=WAL');

// Promisified DB helpers
function dbAll(sql, params = []) {
  return new Promise((resolve, reject) => {
    db.all(sql, params, (err, rows) => (err ? reject(err) : resolve(rows)));
  });
}
function dbGet(sql, params = []) {
  return new Promise((resolve, reject) => {
    db.get(sql, params, (err, row) => (err ? reject(err) : resolve(row)));
  });
}
function dbRun(sql, params = []) {
  return new Promise((resolve, reject) => {
    db.run(sql, params, function (err) {
      if (err) reject(err);
      else resolve({ lastID: this.lastID, changes: this.changes });
    });
  });
}

// Create tables
db.serialize(() => {
  db.run(`
    CREATE TABLE IF NOT EXISTS tickets (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      title TEXT NOT NULL,
      requester TEXT NOT NULL,
      description TEXT,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      status TEXT DEFAULT 'Open',
      priority TEXT DEFAULT 'Medium',
      category TEXT,
      total_time INTEGER DEFAULT 0
    )
  `);

  db.run(`
    CREATE TABLE IF NOT EXISTS time_logs (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      ticket_id INTEGER NOT NULL,
      minutes INTEGER NOT NULL,
      comment TEXT,
      author TEXT NOT NULL,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      FOREIGN KEY(ticket_id) REFERENCES tickets(id) ON DELETE CASCADE
    )
  `);

  db.run(`
    CREATE TABLE IF NOT EXISTS history (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      ticket_id INTEGER NOT NULL,
      action TEXT NOT NULL,
      message TEXT,
      author TEXT,
      role TEXT,
      timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
      FOREIGN KEY(ticket_id) REFERENCES tickets(id) ON DELETE CASCADE
    )
  `);

  db.run('CREATE INDEX IF NOT EXISTS idx_time_logs_ticket ON time_logs(ticket_id)');
  db.run('CREATE INDEX IF NOT EXISTS idx_history_ticket ON history(ticket_id)');
  db.run('PRAGMA foreign_keys = ON');
});

// --- Helpers ---
function utcNow() {
  return moment().utc().format('YYYY-MM-DD HH:mm:ss');
}

function logHistory(ticketId, action, message, author = 'system', role = 'user') {
  db.run(
    'INSERT INTO history (ticket_id, action, message, author, role, timestamp) VALUES (?, ?, ?, ?, ?, ?)',
    [ticketId, action, message, author, role, utcNow()]
  );
}

// --- Server-side webhook sending (URLs never exposed to frontend) ---
async function sendWebhook(url, payload) {
  if (!url) return;
  try {
    await fetch(url, {
      method: 'POST',
      headers: { 'Content-Type': 'application/json' },
      body: JSON.stringify(payload),
    });
  } catch (err) {
    console.error('Webhook send failed:', err.message);
  }
}

// Discord embed colors
const COLORS = {
  green: 0x00ff00,
  orange: 0xffa500,
  blue: 0x4a90e2,
  red: 0xd0021b,
  purple: 0x9b59b6,
  yellow: 0xf5a623,
};

// --- Admin Notifications ---
function notifyAdmin(ticket, event = 'created') {
  if (!ADMIN_WEBHOOK_URL) return;

  const embeds = {
    created: {
      title: '🎫 New Ticket Created',
      color: COLORS.green,
      description: `**Ticket #${ticket.id}**: ${ticket.title}`,
      fields: [
        { name: '📊 Status', value: ticket.status || 'Open', inline: true },
        { name: '⚡ Priority', value: ticket.priority || 'None', inline: true },
        { name: '📂 Category', value: ticket.category || 'None', inline: true },
        ...(ticket.description ? [{
          name: '📝 Description',
          value: ticket.description.length > 200 ? ticket.description.substring(0, 200) + '...' : ticket.description,
        }] : []),
      ],
    },
    comment: {
      title: '💬 New User Comment',
      color: COLORS.blue,
      description: `**Ticket #${ticket.id}**: ${ticket.title}`,
      fields: [
        { name: '📊 Status', value: ticket.status || '-', inline: true },
        { name: '💬 Comment', value: ticket.commentContent?.length > 200 ? ticket.commentContent.substring(0, 200) + '...' : (ticket.commentContent || '-') },
      ],
    },
  };

  const embed = embeds[event] || embeds.created;
  embed.timestamp = new Date().toISOString();
  embed.footer = { text: 'Ticket System' };

  sendWebhook(ADMIN_WEBHOOK_URL, { embeds: [embed] });
}

// --- User Notifications ---
function notifyUser(type, data = {}) {
  if (!USER_WEBHOOK_URL) return;

  const embeds = {
    statusChanged: {
      title: '📊 Status Changed',
      color: data.newStatus === 'Closed' ? COLORS.red : COLORS.orange,
      description: `**Ticket #${data.ticketId}**: ${data.title || ''}`,
      fields: [
        { name: 'Previous', value: data.oldStatus || '-', inline: true },
        { name: 'New', value: data.newStatus || '-', inline: true },
      ],
    },
    priorityChanged: {
      title: '⚡ Priority Changed',
      color: COLORS.yellow,
      description: `**Ticket #${data.ticketId}**: ${data.title || ''}`,
      fields: [
        { name: 'Previous', value: data.oldPriority || '-', inline: true },
        { name: 'New', value: data.newPriority || '-', inline: true },
      ],
    },
    comment: {
      title: '💬 New Admin Comment',
      color: COLORS.purple,
      description: `**Ticket #${data.ticketId}**: ${data.title || ''}`,
      fields: [
        { name: '💬 Comment', value: data.content?.length > 200 ? data.content.substring(0, 200) + '...' : (data.content || '-') },
      ],
    },
    timeLogged: {
      title: '⏱ Time Booked',
      color: COLORS.blue,
      description: `**Ticket #${data.ticketId}**: ${data.title || ''}`,
      fields: [
        { name: '⏱ Duration', value: `${data.minutes || 0} min`, inline: true },
        ...(data.comment ? [{ name: '📝 Note', value: data.comment }] : []),
      ],
    },
  };

  const embed = embeds[type];
  if (!embed) return;

  embed.timestamp = new Date().toISOString();
  embed.footer = { text: 'Ticket System' };

  sendWebhook(USER_WEBHOOK_URL, { embeds: [embed] });
}

// --- Input validation middleware ---
function validateTicketBody(req, res, next) {
  const { title, requester } = req.body;
  if (!title?.trim()) return res.status(400).json({ error: 'Title is required' });
  if (!requester?.trim()) return res.status(400).json({ error: 'Requester is required' });
  next();
}

// ==================== API ROUTES ====================

// GET webhook status (only tells frontend if configured, NOT the URLs)
app.get('/api/webhooks', (_req, res) => {
  res.json({
    configured: {
      admin: !!ADMIN_WEBHOOK_URL,
      user: !!USER_WEBHOOK_URL,
    },
  });
});

// Upload profile picture (user)
app.post('/api/upload-pfp', createPfpUpload('user.jpg').single('pfp'), (req, res) => {
  if (!req.file) return res.status(400).json({ error: 'No file uploaded' });
  res.json({ message: 'Profile picture updated', filename: req.file.filename });
});

// Upload profile picture (admin)
app.post('/api/upload-admin-pfp', createPfpUpload('admin.jpg').single('pfp'), (req, res) => {
  if (!req.file) return res.status(400).json({ error: 'No file uploaded' });
  res.json({ message: 'Admin profile picture updated', filename: req.file.filename });
});

// GET categories
app.get('/api/categories', (_req, res) => {
  res.json(loadCategories());
});

// GET app version
app.get('/api/version', (_req, res) => {
  res.set('Cache-Control', 'no-store');
  res.json({ version: APP_VERSION });
});

// GET all tickets
app.get('/api/tickets', async (_req, res) => {
  try {
    const rows = await dbAll(`
      SELECT id, title, requester, description, status, priority, category, total_time,
             strftime('%Y-%m-%dT%H:%M:%S', created_at) AS created_at,
             strftime('%Y-%m-%dT%H:%M:%S', updated_at) AS updated_at
      FROM tickets ORDER BY created_at DESC
    `);
    res.json(rows);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// GET single ticket
app.get('/api/tickets/:id', async (req, res) => {
  try {
    const row = await dbGet('SELECT * FROM tickets WHERE id = ?', [req.params.id]);
    if (!row) return res.status(404).json({ error: 'Ticket not found' });
    res.json(row);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// POST new ticket
app.post('/api/tickets', validateTicketBody, async (req, res) => {
  const { title, requester, description, status, priority, category } = req.body;
  const ts = utcNow();
  try {
    const result = await dbRun(
      `INSERT INTO tickets (title, requester, description, status, priority, category, created_at, updated_at)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
      [title.trim(), requester.trim(), description?.trim() || '', status || 'Open', priority || 'Medium', category || '', ts, ts]
    );
    const ticket = { id: result.lastID, title, requester, description, status: status || 'Open', priority: priority || 'Medium', category };
    logHistory(result.lastID, 'Ticket Created', `Ticket "${title}" created`, requester);
    notifyAdmin(ticket, 'created');
    res.status(201).json({ id: result.lastID });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// PUT update ticket
app.put('/api/tickets/:id', async (req, res) => {
  const { title, requester, description, status, priority, category, author } = req.body;
  const ts = utcNow();
  try {
    // Fetch old ticket for change detection
    const old = await dbGet('SELECT * FROM tickets WHERE id = ?', [req.params.id]);
    if (!old) return res.status(404).json({ error: 'Ticket not found' });

    await dbRun(
      `UPDATE tickets SET title=?, requester=?, description=?, status=?, priority=?, category=?, updated_at=? WHERE id=?`,
      [title, requester || old.requester, description, status, priority, category, ts, req.params.id]
    );

    const actor = author || 'system';

    // Log specific changes to history
    if (status !== old.status) {
      logHistory(req.params.id, 'Status Changed', `Status changed from "${old.status}" to "${status}"`, actor);
      notifyUser('statusChanged', { ticketId: req.params.id, title: old.title, oldStatus: old.status, newStatus: status, actor });
    }
    if (priority !== old.priority) {
      logHistory(req.params.id, 'Priority Changed', `Priority changed from "${old.priority}" to "${priority}"`, actor);
      notifyUser('priorityChanged', { ticketId: req.params.id, title: old.title, oldPriority: old.priority, newPriority: priority, actor });
    }
    if (category !== (old.category || '')) {
      logHistory(req.params.id, 'Category Changed', `Category changed from "${old.category || 'none'}" to "${category}"`, actor);
    }
    if (title !== old.title || description !== old.description) {
      logHistory(req.params.id, 'Ticket Updated', 'Ticket details updated', actor);
    }

    res.json({ message: 'Ticket updated' });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// DELETE ticket
app.delete('/api/tickets/:id', async (req, res) => {
  try {
    const result = await dbRun('DELETE FROM tickets WHERE id = ?', [req.params.id]);
    if (result.changes === 0) return res.status(404).json({ error: 'Ticket not found' });
    res.json({ message: 'Ticket deleted' });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// POST comment
app.post('/api/tickets/:id/comments', async (req, res) => {
  const { author, role, content } = req.body;
  if (!author?.trim() || !content?.trim()) {
    return res.status(400).json({ error: 'Author and content required' });
  }
  try {
    const result = await dbRun(
      'INSERT INTO history (ticket_id, action, message, author, role, timestamp) VALUES (?, "Comment", ?, ?, ?, ?)',
      [req.params.id, content.trim(), author.trim(), role || 'user', utcNow()]
    );

    // Server-side webhook for comments
    const ticket = await dbGet('SELECT * FROM tickets WHERE id = ?', [req.params.id]);
    const ticketTitle = ticket?.title || '';

    if (role === 'admin') {
      notifyUser('comment', { ticketId: req.params.id, title: ticketTitle, author, content: content.trim() });
    } else {
      notifyAdmin({
        id: req.params.id,
        title: ticketTitle,
        requester: ticket?.requester || author,
        status: ticket?.status || '-',
        commentAuthor: author,
        commentContent: content.trim(),
      }, 'comment');
    }

    res.status(201).json({ id: result.lastID });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// GET comments
app.get('/api/tickets/:id/comments', async (req, res) => {
  try {
    const rows = await dbAll(
      `SELECT id, ticket_id, message AS content, author, role,
              strftime('%Y-%m-%dT%H:%M:%S', timestamp) AS date
       FROM history WHERE ticket_id = ? AND action = "Comment"
       ORDER BY timestamp DESC`,
      [req.params.id]
    );
    res.json(rows);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// GET ticket history
app.get('/api/tickets/:id/history', async (req, res) => {
  try {
    const rows = await dbAll(
      `SELECT id, ticket_id, action, message, author, role,
              strftime('%Y-%m-%dT%H:%M:%S', timestamp) AS date
       FROM history WHERE ticket_id = ?
       ORDER BY timestamp DESC`,
      [req.params.id]
    );
    res.json(rows);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// POST time log
app.post('/api/tickets/:id/time', async (req, res) => {
  const { minutes, comment, author } = req.body;
  if (typeof minutes !== 'number' || minutes <= 0) {
    return res.status(400).json({ error: 'Minutes must be a positive number.' });
  }
  if (!author?.trim()) {
    return res.status(400).json({ error: 'Author is required.' });
  }
  try {
    const ticket = await dbGet('SELECT * FROM tickets WHERE id = ?', [req.params.id]);
    if (!ticket) return res.status(404).json({ error: 'Ticket not found' });

    await dbRun(
      'INSERT INTO time_logs (ticket_id, minutes, comment, author) VALUES (?, ?, ?, ?)',
      [req.params.id, minutes, comment || '', author]
    );

    const { total } = await dbGet(
      'SELECT SUM(minutes) as total FROM time_logs WHERE ticket_id = ?',
      [req.params.id]
    );
    const totalTime = total || 0;

    await dbRun(
      'UPDATE tickets SET total_time = ?, updated_at = ? WHERE id = ?',
      [totalTime, utcNow(), req.params.id]
    );

    logHistory(req.params.id, 'Time Logged', `Added ${minutes} min by ${author}. Comment: ${comment || 'none'}`, author);
    notifyUser('timeLogged', { ticketId: req.params.id, title: ticket.title, minutes, author, comment });

    res.status(201).json({ message: 'Time logged', total_time: totalTime });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// GET time logs
app.get('/api/tickets/:id/time', async (req, res) => {
  try {
    const rows = await dbAll(
      'SELECT * FROM time_logs WHERE ticket_id = ? ORDER BY created_at DESC',
      [req.params.id]
    );
    res.json(rows);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// --- Start ---
app.listen(port, () => {
  console.log(`Server running on http://localhost:${port}`);
  console.log('Webhooks:', {
    admin: ADMIN_WEBHOOK_URL ? 'Configured' : 'Not configured',
    user: USER_WEBHOOK_URL ? 'Configured' : 'Not configured',
  });
});