const express = require('express');
const path = require('path');
const { Pool } = require('pg');

const app = express();
app.use(express.json());

// ── Simple API key auth for write operations ──────────────────────────────
const API_KEY = process.env.SHIPS_API_KEY || '';
function requireAuth(req, res, next) {
  if (!API_KEY) return next(); // No key configured = open (dev mode)
  const token = (req.headers.authorization || '').replace('Bearer ', '');
  if (token === API_KEY) return next();
  res.status(401).json({ error: 'unauthorized' });
}

// ── Member name validation ────────────────────────────────────────────────
function cleanMember(raw) {
  if (!raw || typeof raw !== 'string') return 'omair';
  // Strip anything that isn't alphanumeric, dash, underscore, or dot
  const cleaned = raw.toLowerCase().trim().replace(/[^a-z0-9._-]/g, '');
  return cleaned || 'omair';
}

// ── Ships/Oops API (migrated from calvana) ────────────────────────────────
const poolConfig = {
  host: process.env.SHIPS_DB_HOST || 'localhost',
  port: parseInt(process.env.SHIPS_DB_PORT || '5432'),
  user: process.env.SHIPS_DB_USER || 'dokploy',
  password: process.env.SHIPS_DB_PASSWORD || '',
  database: process.env.SHIPS_DB_NAME || 'calvana',
  min: 0,
  max: 10,
};

let pool = null;

const getPool = () => {
  if (!pool) {
    pool = new Pool(poolConfig);
  }
  return pool;
};

// Health check
app.get('/api/health', async (req, res) => {
  try {
    await getPool().query('SELECT 1');
    res.json({ status: 'ok', db: 'connected' });
  } catch (e) {
    res.status(500).json({ status: 'error', db: e.message });
  }
});

// GET all ships (filterable by ?member=)
app.get('/api/ships', async (req, res) => {
  try {
    const member = req.query.member;
    let query = 'SELECT * FROM ships';
    const params = [];
    if (member && member !== 'all') {
      query += ' WHERE LOWER(member) = LOWER($1)';
      params.push(member);
    }
    query += ' ORDER BY created_at DESC';
    const { rows } = await getPool().query(query, params);
    res.json(rows);
  } catch (e) {
    res.status(500).json({ error: e.message });
  }
});

// GET ships/showcase — top shipped items for homepage widget
app.get('/api/ships/showcase', async (req, res) => {
  try {
    const member = req.query.member;
    let query = "SELECT * FROM ships WHERE status = 'shipped'";
    const params = [];
    if (member && member !== 'all') {
      query += ' AND LOWER(member) = LOWER($1)';
      params.push(member);
    }
    query += ' ORDER BY created_at DESC LIMIT 10';
    const { rows } = await getPool().query(query, params);
    res.json(rows);
  } catch (e) {
    res.status(500).json({ error: e.message });
  }
});

// GET stats — totals for homepage counters (filterable by ?member=)
app.get('/api/stats', async (req, res) => {
  try {
    const p = getPool();
    const member = req.query.member;
    const memberFilter = member && member !== 'all';
    const params = memberFilter ? [member] : [];
    const [shipsTotal, shipsShipped, oopsTotal, members] = await Promise.all([
      p.query(`SELECT COUNT(*) FROM ships${memberFilter ? ' WHERE LOWER(member) = LOWER($1)' : ''}`, params),
      p.query(`SELECT COUNT(*) FROM ships WHERE status = 'shipped'${memberFilter ? ' AND LOWER(member) = LOWER($1)' : ''}`, params),
      p.query(`SELECT COUNT(*) FROM oops${memberFilter ? ' WHERE LOWER(member) = LOWER($1)' : ''}`, params),
      p.query("SELECT LOWER(COALESCE(member, 'omair')) as member, COUNT(*) as count FROM ships GROUP BY LOWER(COALESCE(member, 'omair')) ORDER BY count DESC"),
    ]);
    res.json({
      total: parseInt(shipsTotal.rows[0].count),
      shipped: parseInt(shipsShipped.rows[0].count),
      oops: parseInt(oopsTotal.rows[0].count),
      members: members.rows,
    });
  } catch (e) {
    res.status(500).json({ error: e.message });
  }
});

// POST ship
app.post('/api/ships', requireAuth, async (req, res) => {
  const { title, status, metric, details, member } = req.body;
  if (!title) return res.status(400).json({ error: 'title required' });
  try {
    const { rows } = await getPool().query(
      'INSERT INTO ships (title, status, metric, details, member) VALUES ($1, $2, $3, $4, $5) RETURNING *',
      [title, status || 'planned', metric || null, details || null, cleanMember(member)]
    );
    res.status(201).json(rows[0]);
  } catch (e) {
    res.status(500).json({ error: e.message });
  }
});

// PATCH ship
app.patch('/api/ships/:id', requireAuth, async (req, res) => {
  const { id } = req.params;
  const { title, status, metric, details, member } = req.body;
  try {
    const sets = [];
    const vals = [];
    let i = 1;
    if (title !== undefined)   { sets.push(`title=$${i++}`);   vals.push(title); }
    if (status !== undefined)  { sets.push(`status=$${i++}`);  vals.push(status); }
    if (metric !== undefined)  { sets.push(`metric=$${i++}`);  vals.push(metric); }
    if (details !== undefined) { sets.push(`details=$${i++}`); vals.push(details); }
    if (member !== undefined)  { sets.push(`member=$${i++}`);  vals.push(cleanMember(member)); }
    if (sets.length === 0) return res.status(400).json({ error: 'nothing to update' });
    sets.push(`updated_at=NOW()`);
    vals.push(id);
    const { rows } = await getPool().query(
      `UPDATE ships SET ${sets.join(', ')} WHERE id=$${i} RETURNING *`,
      vals
    );
    if (rows.length === 0) return res.status(404).json({ error: 'not found' });
    res.json(rows[0]);
  } catch (e) {
    res.status(500).json({ error: e.message });
  }
});

// DELETE ship
app.delete('/api/ships/:id', requireAuth, async (req, res) => {
  const { id } = req.params;
  try {
    const { rows } = await getPool().query(
      'DELETE FROM ships WHERE id=$1 RETURNING *', [id]
    );
    if (rows.length === 0) return res.status(404).json({ error: 'not found' });
    res.json({ deleted: true, ship: rows[0] });
  } catch (e) {
    res.status(500).json({ error: e.message });
  }
});

// GET oops (filterable by ?member=)
app.get('/api/oops', async (req, res) => {
  try {
    const member = req.query.member;
    let query = 'SELECT * FROM oops';
    const params = [];
    if (member && member !== 'all') {
      query += ' WHERE LOWER(member) = LOWER($1)';
      params.push(member);
    }
    query += ' ORDER BY created_at DESC';
    const { rows } = await getPool().query(query, params);
    res.json(rows);
  } catch (e) {
    res.status(500).json({ error: e.message });
  }
});

// POST oops
app.post('/api/oops', requireAuth, async (req, res) => {
  const { description, fix_time, commit_link, member } = req.body;
  if (!description) return res.status(400).json({ error: 'description required' });
  try {
    const { rows } = await getPool().query(
      'INSERT INTO oops (description, fix_time, commit_link, member) VALUES ($1, $2, $3, $4) RETURNING *',
      [description, fix_time || null, commit_link || null, cleanMember(member)]
    );
    res.status(201).json(rows[0]);
  } catch (e) {
    res.status(500).json({ error: e.message });
  }
});

// DELETE oops
app.delete('/api/oops/:id', requireAuth, async (req, res) => {
  const { id } = req.params;
  try {
    const { rows } = await getPool().query(
      'DELETE FROM oops WHERE id=$1 RETURNING *', [id]
    );
    if (rows.length === 0) return res.status(404).json({ error: 'not found' });
    res.json({ deleted: true, oops: rows[0] });
  } catch (e) {
    res.status(500).json({ error: e.message });
  }
});

// GET team — list all team members with ship counts
app.get('/api/team', async (req, res) => {
  try {
    const { rows } = await getPool().query(
      "SELECT LOWER(COALESCE(member, 'omair')) as member, COUNT(*)::int as ships, COUNT(*) FILTER (WHERE status = 'shipped')::int as shipped FROM ships GROUP BY LOWER(COALESCE(member, 'omair')) ORDER BY ships DESC"
    );
    res.json(rows);
  } catch (e) {
    res.status(500).json({ error: e.message });
  }
});

// Redirect /work/ to first case study (matches old nginx behaviour)
app.get('/work/', (req, res) => res.redirect(301, '/work/charityright/'));

// Serve static files from html/ directory
app.use(express.static(path.join(__dirname, 'html')));

// API 404 — don't let SPA fallback swallow bad API routes
app.all('/api/*', (req, res) => {
  res.status(404).json({ error: 'not found' });
});

// SPA fallback: send index.html for unmatched routes
app.get('*', (req, res) => {
  res.sendFile(path.join(__dirname, 'html', 'index.html'));
});

const PORT = process.env.PORT || 3000;
const server = app.listen(PORT, '0.0.0.0', () => {
  console.log(`QuikCue Site server listening on :${PORT}`);
});

// Graceful shutdown
process.on('SIGTERM', () => {
  console.log('SIGTERM received, shutting down...');
  server.close(() => {
    if (pool) pool.end();
    process.exit(0);
  });
});