const express = require('express');
const Database = require('better-sqlite3');
const path = require('path');

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

app.set('view engine', 'ejs');
app.set('views', path.join(__dirname, 'views'));
app.use(express.urlencoded({ extended: true }));
app.use(express.json());

// Database setup
const dbPath = process.env.DB_PATH || './data/exports.db';
const db = new Database(dbPath);

db.exec(`
  CREATE TABLE IF NOT EXISTS buyers (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    company TEXT NOT NULL,
    country TEXT NOT NULL,
    contract_price_per_kg REAL NOT NULL,
    payment_terms TEXT,
    last_order_date TEXT
  );

  CREATE TABLE IF NOT EXISTS shipments (
    id INTEGER PRIMARY KEY,
    buyer_id INTEGER,
    lot_number TEXT NOT NULL,
    weight_kg REAL NOT NULL,
    quality_score REAL,
    ship_date TEXT,
    status TEXT DEFAULT 'pending',
    tracking_number TEXT,
    FOREIGN KEY (buyer_id) REFERENCES buyers(id)
  );

  CREATE TABLE IF NOT EXISTS quality_reports (
    id INTEGER PRIMARY KEY,
    shipment_id INTEGER,
    cupping_score REAL,
    altitude_m INTEGER,
    processing_method TEXT,
    notes TEXT,
    FOREIGN KEY (shipment_id) REFERENCES shipments(id)
  );
`);

// Seed data if empty
const count = db.prepare('SELECT COUNT(*) as c FROM buyers').get();
if (count.c === 0) {
  const insertBuyer = db.prepare(
    'INSERT INTO buyers (name, company, country, contract_price_per_kg, payment_terms, last_order_date) VALUES (?, ?, ?, ?, ?, ?)'
  );
  insertBuyer.run('Sarah Chen', 'Pacific Roasters', 'USA', 28.50, 'Net 30', '2024-01-15');
  insertBuyer.run('Kenji Yamamoto', 'Tokyo Coffee House', 'Japan', 32.00, 'Net 45', '2024-02-01');
  insertBuyer.run('Elena Bergmann', 'Nordic Specialty', 'Germany', 26.75, 'Net 30', '2024-01-20');
  insertBuyer.run('Marco Rossi', 'Caffeteria Milano', 'Italy', 24.00, 'Net 60', '2023-12-10');

  const insertShipment = db.prepare(
    'INSERT INTO shipments (buyer_id, lot_number, weight_kg, quality_score, ship_date, status, tracking_number) VALUES (?, ?, ?, ?, ?, ?, ?)'
  );
  insertShipment.run(1, 'NT-2024-001', 500, 87.5, '2024-01-20', 'delivered', 'VE-TR-78234');
  insertShipment.run(2, 'NT-2024-002', 300, 89.0, '2024-02-05', 'in_transit', 'VE-TR-78301');
  insertShipment.run(3, 'NT-2024-003', 450, 86.0, '2024-02-10', 'pending', null);
  insertShipment.run(1, 'NT-2024-004', 600, 88.5, '2024-02-15', 'pending', null);
}

// VULNERABLE: SQL injection in search endpoint
app.get('/search', (req, res) => {
  const query = req.query.q || '';
  // Intentionally vulnerable -- string concatenation instead of parameterized query
  const sql = `SELECT s.*, b.name as buyer_name, b.company, b.contract_price_per_kg
               FROM shipments s
               JOIN buyers b ON s.buyer_id = b.id
               WHERE s.lot_number LIKE '%${query}%'
               OR b.name LIKE '%${query}%'
               OR b.company LIKE '%${query}%'`;
  try {
    const results = db.prepare(sql).all();
    res.json(results);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// API endpoint that exposes buyer pricing without proper scope
app.get('/api/shipments', (req, res) => {
  const shipments = db.prepare(`
    SELECT s.*, b.name as buyer_name, b.company, b.country,
           b.contract_price_per_kg, b.payment_terms
    FROM shipments s
    JOIN buyers b ON s.buyer_id = b.id
    ORDER BY s.ship_date DESC
  `).all();
  res.json(shipments);
});

// Shipping API integration -- passes pricing in URL parameters
const SHIPPING_API = process.env.SHIPPING_API_URL || 'http://shipping-api:4000';

app.post('/api/shipments/:id/track', async (req, res) => {
  const shipment = db.prepare(`
    SELECT s.*, b.contract_price_per_kg, b.company
    FROM shipments s JOIN buyers b ON s.buyer_id = b.id
    WHERE s.id = ?
  `).get(req.params.id);

  if (!shipment) return res.status(404).json({ error: 'Shipment not found' });

  // VULNERABLE: Buyer pricing sent in URL query parameters
  const trackingUrl = `${SHIPPING_API}/track?lot=${shipment.lot_number}&weight=${shipment.weight_kg}&price=${shipment.contract_price_per_kg}&buyer=${encodeURIComponent(shipment.company)}`;

  try {
    const response = await fetch(trackingUrl);
    const data = await response.json();
    res.json(data);
  } catch (err) {
    res.status(502).json({ error: 'Shipping API unavailable' });
  }
});

app.get('/', (req, res) => {
  const shipments = db.prepare(`
    SELECT s.*, b.name as buyer_name, b.company
    FROM shipments s JOIN buyers b ON s.buyer_id = b.id
    ORDER BY s.ship_date DESC
  `).all();
  res.render('index', { shipments });
});

app.get('/login', (req, res) => {
  res.render('login');
});

app.listen(PORT, '0.0.0.0', () => {
  console.log(`Export tracker running on port ${PORT}`);
});