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

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

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

const db = new Database('/app/data/tourism.db');

db.exec(`
  CREATE TABLE IF NOT EXISTS tourists (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    passport_number TEXT NOT NULL,
    nationality TEXT,
    travel_dates TEXT,
    booking_ref TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
  )
`);

db.exec(`
  CREATE TABLE IF NOT EXISTS bookings (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    tourist_id INTEGER,
    destination TEXT,
    guide_id TEXT,
    status TEXT DEFAULT 'pending',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (tourist_id) REFERENCES tourists(id)
  )
`);

const sampleTourists = [
  ['Kenji Tanaka', 'JP12345678', 'Japanese', '2026-04-15 to 2026-04-22', 'BTC-2026-0142'],
  ['Sarah Mitchell', 'US98765432', 'American', '2026-04-20 to 2026-04-28', 'BTC-2026-0143'],
  ['Hans Weber', 'DE55667788', 'German', '2026-05-01 to 2026-05-10', 'BTC-2026-0144'],
  ['Yuki Sato', 'JP11223344', 'Japanese', '2026-04-18 to 2026-04-25', 'BTC-2026-0145'],
  ['Emma Thompson', 'GB44556677', 'British', '2026-05-05 to 2026-05-12', 'BTC-2026-0146'],
  ['Pierre Dubois', 'FR99887766', 'French', '2026-04-25 to 2026-05-02', 'BTC-2026-0147'],
  ['Li Wei', 'CN33445566', 'Chinese', '2026-05-10 to 2026-05-18', 'BTC-2026-0148'],
  ['Maria Garcia', 'ES77889900', 'Spanish', '2026-05-15 to 2026-05-22', 'BTC-2026-0149'],
];

const existingCount = db.prepare('SELECT COUNT(*) as count FROM tourists').get();
if (existingCount.count === 0) {
  const insert = db.prepare('INSERT INTO tourists (name, passport_number, nationality, travel_dates, booking_ref) VALUES (?, ?, ?, ?, ?)');
  for (const tourist of sampleTourists) {
    insert.run(...tourist);
  }
}

app.get('/', (req, res) => {
  res.render('index', { title: 'Bhutan Tourism Council - Tourism Services Portal' });
});

// VULNERABLE: SQL injection in search endpoint
// The search parameter is concatenated directly into the SQL query
app.get('/search', (req, res) => {
  const query = req.query.q || '';
  try {
    const sql = `SELECT name, nationality, travel_dates, booking_ref FROM tourists WHERE name LIKE '%${query}%' OR booking_ref LIKE '%${query}%'`;
    const results = db.prepare(sql).all();
    res.render('search', { results, query, title: 'Search Results' });
  } catch (err) {
    res.status(500).render('error', { error: err.message, title: 'Error' });
  }
});

// Internal API endpoint -- accessible without authentication from other services
// This API is called by the Guide Management System to look up tourist data
app.get('/api/tourist-data', (req, res) => {
  const tourists = db.prepare('SELECT id, name, passport_number, nationality, travel_dates, booking_ref FROM tourists').all();
  res.json({ tourists });
});

app.get('/api/tourist-data/:id', (req, res) => {
  const tourist = db.prepare('SELECT * FROM tourists WHERE id = ?').get(req.params.id);
  if (tourist) {
    res.json(tourist);
  } else {
    res.status(404).json({ error: 'Tourist not found' });
  }
});

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