water-station-tracker/server.js

const express = require('express');
const sqlite3 = require('sqlite3').verbose();
const session = require('express-session');
const SQLiteStore = require('connect-sqlite3')(session);
const passport = require('passport');
const GoogleStrategy = require('passport-google-oauth20').Strategy;
const FacebookStrategy = require('passport-facebook').Strategy;
const LocalStrategy = require('passport-local').Strategy;
const bcrypt = require('bcryptjs');
const bodyParser = require('body-parser');
const cors = require('cors');
const path = require('path');
require('dotenv').config();

const app = express();
const HOST = process.env.HOST || "0.0.0.0";
const PORT = process.env.PORT || 3000;
const FORCE_HTTPS = process.env.FORCE_HTTPS === 'true';

// Database setup
const db = new sqlite3.Database('./water_stations.db');

// Initialize database tables
db.serialize(() => {
  db.run(`CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT UNIQUE,
    email TEXT UNIQUE,
    password_hash TEXT,
    google_id TEXT,
    facebook_id TEXT,
    display_name TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
  )`);

  db.run(`CREATE TABLE IF NOT EXISTS cities (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL UNIQUE,
    display_name TEXT NOT NULL,
    created_by INTEGER,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES users (id)
  )`);

  db.run(`CREATE TABLE IF NOT EXISTS water_stations (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    latitude REAL NOT NULL,
    longitude REAL NOT NULL,
    description TEXT,
    city_id INTEGER NOT NULL,
    created_by INTEGER,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (city_id) REFERENCES cities (id),
    FOREIGN KEY (created_by) REFERENCES users (id)
  )`);

  db.run(`CREATE TABLE IF NOT EXISTS station_updates (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    station_id INTEGER,
    description TEXT,
    last_refill_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    estimated_empty_time DATETIME,
    updated_by INTEGER,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (station_id) REFERENCES water_stations (id),
    FOREIGN KEY (updated_by) REFERENCES users (id)
  )`);

  // Create default city if none exists
  db.get('SELECT COUNT(*) as count FROM cities', (err, row) => {
    if (!err && row.count === 0) {
      db.run('INSERT INTO cities (name, display_name, created_by) VALUES (?, ?, ?)', 
        ['salem', 'Salem', null]);
    }
  });

  // Add city_id column to existing water_stations if it doesn't exist
  db.all("PRAGMA table_info(water_stations)", (err, columns) => {
    if (!err) {
      const hasCityId = columns.some(col => col.name === 'city_id');
      if (!hasCityId) {
        db.run('ALTER TABLE water_stations ADD COLUMN city_id INTEGER DEFAULT 1');
      }
    }
  });
});

// Middleware
app.use(cors());
app.use(bodyParser.json());
app.use(bodyParser.urlencoded({ extended: true }));
app.use(express.static(path.join(__dirname, 'public')));

// Trust proxy headers (required for Apache/nginx reverse proxy setups)
app.set('trust proxy', true);

// HTTPS enforcement middleware
if (FORCE_HTTPS) {
  app.use((req, res, next) => {
    // Check if the original request was HTTP
    // req.secure will be true if the original request was HTTPS (when trust proxy is enabled)
    // x-forwarded-proto header is set by the proxy
    const isHttps = req.secure || req.header('x-forwarded-proto') === 'https';
    
    if (!isHttps) {
      // Only redirect if the original client request was HTTP
      const host = req.header('x-forwarded-host') || req.header('host');
      return res.redirect(301, `https://${host}${req.url}`);
    }
    
    next();
  });
}

app.use(session({
  store: new SQLiteStore({
    db: 'water_stations.db',
    table: 'sessions'
  }),
  secret: process.env.SESSION_SECRET || 'your-secret-key',
  resave: false,
  saveUninitialized: false,
  cookie: { 
    secure: FORCE_HTTPS, // Use secure cookies when HTTPS is forced
    maxAge: 7 * 24 * 60 * 60 * 1000 // 7 days
  }
}));

app.use(passport.initialize());
app.use(passport.session());

// Helper function to get base URL
function getBaseUrl(req) {
  // Determine protocol: use HTTPS if forced, or if original request was HTTPS
  const isHttps = FORCE_HTTPS || req.secure || req.header('x-forwarded-proto') === 'https';
  const protocol = isHttps ? 'https' : 'http';
  
  // Use x-forwarded-host if available (set by proxy), otherwise use host header
  const host = req.header('x-forwarded-host') || req.header('host');
  
  return `${protocol}://${host}`;
}

// Passport configuration
passport.use(new LocalStrategy(
  { usernameField: 'username' },
  async (username, password, done) => {
    db.get('SELECT * FROM users WHERE username = ?', [username], async (err, user) => {
      if (err) return done(err);
      if (!user) return done(null, false);
      
      const isValid = await bcrypt.compare(password, user.password_hash);
      if (!isValid) return done(null, false);
      
      return done(null, user);
    });
  }
));

// Initialize OAuth strategies with dynamic callback URLs
function initializeOAuthStrategies(baseUrl = '') {
  // Clear existing strategies
  passport.unuse('google');
  passport.unuse('facebook');
  
  // Google OAuth Strategy
  passport.use(new GoogleStrategy({
    clientID: process.env.GOOGLE_CLIENT_ID,
    clientSecret: process.env.GOOGLE_CLIENT_SECRET,
    callbackURL: `${baseUrl}/auth/google/callback`
  }, (accessToken, refreshToken, profile, done) => {
    db.get('SELECT * FROM users WHERE google_id = ?', [profile.id], (err, user) => {
      if (err) return done(err);
      
      if (user) {
        return done(null, user);
      } else {
        db.run('INSERT INTO users (google_id, display_name, email) VALUES (?, ?, ?)',
          [profile.id, profile.displayName, profile.emails[0].value],
          function(err) {
            if (err) return done(err);
            
            db.get('SELECT * FROM users WHERE id = ?', [this.lastID], (err, user) => {
              return done(err, user);
            });
          }
        );
      }
    });
  }));

  // Facebook OAuth Strategy
  passport.use(new FacebookStrategy({
    clientID: process.env.FACEBOOK_CLIENT_ID,
    clientSecret: process.env.FACEBOOK_CLIENT_SECRET,
    callbackURL: `${baseUrl}/auth/facebook/callback`,
    profileFields: ['id', 'name', 'email'],
    enableProof: true
  }, (accessToken, refreshToken, profile, done) => {
    db.get('SELECT * FROM users WHERE facebook_id = ?', [profile.id], (err, user) => {
      if (err) return done(err);
      
      if (user) {
        return done(null, user);
      } else {
        const email = profile.emails && profile.emails[0] ? profile.emails[0].value : null;
        const displayName = profile.name ? `${profile.name.givenName} ${profile.name.familyName}` : profile.displayName;
        db.run('INSERT INTO users (facebook_id, display_name, email) VALUES (?, ?, ?)',
          [profile.id, displayName, email],
          function(err) {
            if (err) return done(err);
            
            db.get('SELECT * FROM users WHERE id = ?', [this.lastID], (err, user) => {
              return done(err, user);
            });
          }
        );
      }
    });
  }));
}

// Initialize OAuth strategies with empty base URL (will be updated per request)
initializeOAuthStrategies();

passport.serializeUser((user, done) => {
  done(null, user.id);
});

passport.deserializeUser((id, done) => {
  db.get('SELECT * FROM users WHERE id = ?', [id], (err, user) => {
    done(err, user);
  });
});

// Routes
app.get('/', (req, res) => {
  res.redirect('/city/salem');
});

app.get('/city-select', (req, res) => {
  res.sendFile(path.join(__dirname, 'public', 'city-select.html'));
});

// Authentication routes
app.get('/auth/google', (req, res, next) => {
  if (req.query.redirect) {
    req.session.redirectUrl = req.query.redirect;
  }
  
  // Reinitialize strategies with current request's base URL
  const baseUrl = getBaseUrl(req);
  initializeOAuthStrategies(baseUrl);
  
  passport.authenticate('google', { scope: ['profile', 'email'] })(req, res, next);
});

app.get('/auth/google/callback',
  passport.authenticate('google', { failureRedirect: '/login' }),
  (req, res) => {
    const redirectUrl = req.session.redirectUrl || '/city/salem/dashboard';
    delete req.session.redirectUrl;
    res.redirect(redirectUrl);
  }
);

app.get('/auth/facebook', (req, res, next) => {
  if (req.query.redirect) {
    req.session.redirectUrl = req.query.redirect;
  }
  
  // Reinitialize strategies with current request's base URL
  const baseUrl = getBaseUrl(req);
  initializeOAuthStrategies(baseUrl);
  
  passport.authenticate('facebook', { scope: ['public_profile', 'email'] })(req, res, next);
});

app.get('/auth/facebook/callback',
  passport.authenticate('facebook', { failureRedirect: '/login' }),
  (req, res) => {
    const redirectUrl = req.session.redirectUrl || '/city/salem/dashboard';
    delete req.session.redirectUrl;
    res.redirect(redirectUrl);
  }
);

app.post('/auth/login', passport.authenticate('local'), (req, res) => {
  res.json({ success: true, user: req.user });
});

app.post('/auth/register', async (req, res) => {
  const { username, email, password } = req.body;
  
  try {
    const hashedPassword = await bcrypt.hash(password, 10);
    
    db.run('INSERT INTO users (username, email, password_hash) VALUES (?, ?, ?)',
      [username, email, hashedPassword],
      function(err) {
        if (err) {
          return res.status(400).json({ error: 'Username or email already exists' });
        }
        
        db.get('SELECT * FROM users WHERE id = ?', [this.lastID], (err, user) => {
          if (err) return res.status(500).json({ error: 'Database error' });
          
          req.login(user, (err) => {
            if (err) return res.status(500).json({ error: 'Login error' });
            res.json({ success: true, user: user });
          });
        });
      }
    );
  } catch (error) {
    res.status(500).json({ error: 'Server error' });
  }
});

app.post('/auth/logout', (req, res) => {
  req.logout(() => {
    res.json({ success: true });
  });
});

// API routes
app.get('/api/user', (req, res) => {
  res.json({ user: req.user || null });
});

app.get('/api/cities', (req, res) => {
  db.all('SELECT * FROM cities ORDER BY display_name', [], (err, rows) => {
    if (err) return res.status(500).json({ error: 'Database error' });
    res.json(rows);
  });
});

app.post('/api/cities', (req, res) => {
  if (!req.user) {
    return res.status(401).json({ error: 'Authentication required' });
  }
  
  const { name, display_name } = req.body;
  
  if (!name || !display_name) {
    return res.status(400).json({ error: 'Name and display name are required' });
  }
  
  const normalizedName = name.toLowerCase().replace(/\s+/g, '-');
  
  db.run('INSERT INTO cities (name, display_name, created_by) VALUES (?, ?, ?)',
    [normalizedName, display_name, req.user.id],
    function(err) {
      if (err) {
        if (err.code === 'SQLITE_CONSTRAINT') {
          return res.status(400).json({ error: 'City already exists' });
        }
        return res.status(500).json({ error: 'Database error' });
      }
      res.json({ id: this.lastID, success: true });
    }
  );
});

app.get('/api/cities/:cityName/stations', (req, res) => {
  const cityName = req.params.cityName;
  
  const query = `
    SELECT 
      ws.*,
      COALESCE(u.display_name, u.username) as created_by_name,
      su.description as latest_description,
      su.last_refill_time,
      su.estimated_empty_time,
      su.updated_at as last_updated,
      COALESCE(u2.display_name, u2.username) as updated_by_name,
      c.display_name as city_name
    FROM water_stations ws
    JOIN cities c ON ws.city_id = c.id
    LEFT JOIN users u ON ws.created_by = u.id
    LEFT JOIN (
      SELECT station_id, description, last_refill_time, estimated_empty_time, updated_by, updated_at,
             ROW_NUMBER() OVER (PARTITION BY station_id ORDER BY updated_at DESC) as rn
      FROM station_updates
    ) su ON ws.id = su.station_id AND su.rn = 1
    LEFT JOIN users u2 ON su.updated_by = u2.id
    WHERE c.name = ?
  `;
  
  db.all(query, [cityName], (err, rows) => {
    if (err) return res.status(500).json({ error: 'Database error' });
    res.json(rows);
  });
});

app.post('/api/cities/:cityName/stations', (req, res) => {
  if (!req.user) {
    return res.status(401).json({ error: 'Authentication required' });
  }
  
  const cityName = req.params.cityName;
  const { name, latitude, longitude, description } = req.body;
  
  // First, get the city ID
  db.get('SELECT id FROM cities WHERE name = ?', [cityName], (err, city) => {
    if (err) return res.status(500).json({ error: 'Database error' });
    if (!city) return res.status(404).json({ error: 'City not found' });
    
    db.run('INSERT INTO water_stations (name, latitude, longitude, description, city_id, created_by) VALUES (?, ?, ?, ?, ?, ?)',
      [name, latitude, longitude, description, city.id, req.user.id],
      function(err) {
        if (err) return res.status(500).json({ error: 'Database error' });
        res.json({ id: this.lastID, success: true });
      }
    );
  });
});

app.get('/api/stations', (req, res) => {
  const query = `
    SELECT 
      ws.*,
      COALESCE(u.display_name, u.username) as created_by_name,
      su.description as latest_description,
      su.last_refill_time,
      su.estimated_empty_time,
      su.updated_at as last_updated,
      COALESCE(u2.display_name, u2.username) as updated_by_name
    FROM water_stations ws
    LEFT JOIN users u ON ws.created_by = u.id
    LEFT JOIN (
      SELECT station_id, description, last_refill_time, estimated_empty_time, updated_by, updated_at,
             ROW_NUMBER() OVER (PARTITION BY station_id ORDER BY updated_at DESC) as rn
      FROM station_updates
    ) su ON ws.id = su.station_id AND su.rn = 1
    LEFT JOIN users u2 ON su.updated_by = u2.id
  `;
  
  db.all(query, [], (err, rows) => {
    if (err) return res.status(500).json({ error: 'Database error' });
    res.json(rows);
  });
});

app.post('/api/stations', (req, res) => {
  if (!req.user) {
    return res.status(401).json({ error: 'Authentication required' });
  }
  
  const { name, latitude, longitude, description, city_id } = req.body;
  
  db.run('INSERT INTO water_stations (name, latitude, longitude, description, city_id, created_by) VALUES (?, ?, ?, ?, ?, ?)',
    [name, latitude, longitude, description, city_id || 1, req.user.id],
    function(err) {
      if (err) return res.status(500).json({ error: 'Database error' });
      res.json({ id: this.lastID, success: true });
    }
  );
});

app.post('/api/stations/:id/update', (req, res) => {
  if (!req.user) {
    return res.status(401).json({ error: 'Authentication required' });
  }
  
  const { description, estimatedHours, statusAsOf } = req.body;
  const stationId = req.params.id;
  
  // Use provided status date/time or current time
  // statusAsOf now comes as UTC ISO string from client
  const refillTime = statusAsOf ? new Date(statusAsOf) : new Date();
  
  const estimatedEmptyTime = new Date(refillTime);
  estimatedEmptyTime.setHours(estimatedEmptyTime.getHours() + parseInt(estimatedHours));
  
  console.log("Updated by",req.user);
  db.run('INSERT INTO station_updates (station_id, description, last_refill_time, estimated_empty_time, updated_by) VALUES (?, ?, ?, ?, ?)',
    [stationId, description, refillTime.toISOString(), estimatedEmptyTime.toISOString(), req.user.id],
    function(err) {
      if (err) return res.status(500).json({ error: 'Database error' });
      res.json({ success: true });
    }
  );
});

app.put('/api/stations/:id', (req, res) => {
  if (!req.user) {
    return res.status(401).json({ error: 'Authentication required' });
  }
  
  const { name, description } = req.body;
  const stationId = req.params.id;
  
  db.run('UPDATE water_stations SET name = ?, description = ? WHERE id = ?',
    [name, description, stationId],
    function(err) {
      if (err) return res.status(500).json({ error: 'Database error' });
      res.json({ success: true });
    }
  );
});

app.delete('/api/stations/:id', (req, res) => {
  if (!req.user) {
    return res.status(401).json({ error: 'Authentication required' });
  }
  
  const stationId = req.params.id;
  
  // Delete station updates first (foreign key constraint)
  db.run('DELETE FROM station_updates WHERE station_id = ?', [stationId], function(err) {
    if (err) return res.status(500).json({ error: 'Database error' });
    
    // Then delete the station
    db.run('DELETE FROM water_stations WHERE id = ?', [stationId], function(err) {
      if (err) return res.status(500).json({ error: 'Database error' });
      if (this.changes === 0) {
        return res.status(404).json({ error: 'Station not found' });
      }
      res.json({ success: true });
    });
  });
});

app.get('/dashboard', (req, res) => {
  if (!req.user) {
    return res.redirect('/login');
  }
  res.redirect('/city/salem/dashboard');
});

app.get('/city/:cityName', (req, res) => {
  res.sendFile(path.join(__dirname, 'public', 'index.html'));
});

app.get('/city/:cityName/dashboard', (req, res) => {
  if (!req.user) {
    return res.redirect('/login');
  }
  res.sendFile(path.join(__dirname, 'public', 'dashboard.html'));
});

app.get('/login', (req, res) => {
  if (req.query.redirect) {
    req.session.redirectUrl = req.query.redirect;
  }
  res.sendFile(path.join(__dirname, 'public', 'login.html'));
});

app.listen(PORT, HOST, () => {
  console.log(`Server running on http://${HOST}:${PORT}`);
});