Index: server.js =================================================================== --- server.js (revision d8deee62c54f2f99103fcf6857652e90d8a97637) +++ server.js (revision d8deee62c54f2f99103fcf6857652e90d8a97637) @@ -0,0 +1,1071 @@ +๏ปฟ// ============================================================= +// Wedding Planner โ€” Complete Backend (Node.js + Express) +// Database: PostgreSQL via server +// Auth: Session-based (login / register) +// Email: Gmail via Nodemailer +// ============================================================= + +// Load environment variables +require('dotenv').config(); + +const express = require('express'); +const { Pool } = require('pg'); +const cors = require('cors'); + +const app = express(); + +app.use(cors({ + origin: "http://localhost:3000", + credentials: true +})); + +app.use(express.json()); +const bcrypt = require('bcryptjs'); +const session = require('express-session'); +const nodemailer = require('nodemailer'); +const crypto = require('crypto'); +const path = require('path'); +const PORT = process.env.PORT || 3000; + +// ============================================================= +// MIDDLEWARE +// ============================================================= +app.use(cors({ origin: 'http://localhost:3000', credentials: true })); +app.use(express.json()); +app.use(express.urlencoded({ extended: true })); + +// Serve all HTML files from the same folder as this server.js +app.use(express.static(path.join(__dirname))); + +// Session setup +app.use(session({ + secret: 'wedding_planner_secret_key_2025', + resave: false, + saveUninitialized: false, + cookie: { + secure: false, // set true only if using HTTPS + httpOnly: true, + maxAge: 1000 * 60 * 60 * 8 // 8 hours + } +})); + +// ============================================================= +// DATABASE CONNECTION +// +// ============================================================= +const pool = new Pool({ + user: process.env.DB_USER, + host: process.env.DB_HOST, + database: process.env.DB_NAME, + password: process.env.DB_PASSWORD, + port: parseInt(process.env.DB_PORT, 10), + ssl: { rejectUnauthorized: false } +}); + +console.log('๐Ÿ“ก Attempting to connect to PostgreSQL...'); +console.log(` Host: ${process.env.DB_HOST}:${process.env.DB_PORT}`); +console.log(` Database: ${process.env.DB_NAME}`); + +pool.connect() + .then(client => { + console.log('โœ… Connected to PostgreSQL database!'); + client.release(); + }) + .catch(err => { + console.error('โŒ DB connection error:', err.message); + console.error(' Make sure SSH tunnel is running on port 9999'); + console.error(' SSH: ssh -L 9999:db_server:5432 t_wedding_planner2025@194.149.135.130'); + console.error(' Full error:', err.stack); + }); + +// Helper: always use the project schema +const Q = (text, params) => pool.query(`SET search_path TO project; ${text}`, params); + +// ============================================================= +// EMAIL TRANSPORTER +// ============================================================= +const transporter = nodemailer.createTransport({ + service: 'gmail', + auth: { + user: process.env.EMAIL_USER, + pass: process.env.EMAIL_PASS + } +}); + +// ============================================================= +// AUTH MIDDLEWARE โ€” protect routes that require login +// ============================================================= +function requireAuth(req, res, next) { + if (req.session && req.session.user) return next(); + return res.status(401).json({ error: 'Not authenticated. Please log in.' }); +} + +// ============================================================= +// AUTH ROUTES +// ============================================================= + +// POST /api/auth/register +app.post('/api/auth/register', async (req, res) => { + const { first_name, last_name, email, password, phone_number, gender, birthday } = req.body; + if (!first_name || !last_name || !email || !password) + return res.status(400).json({ error: 'First name, last name, email and password are required.' }); + + try { + // Check email not already taken + const exists = await pool.query( + 'SELECT user_id FROM project."user" WHERE email = $1', [email] + ); + if (exists.rows.length > 0) + return res.status(409).json({ error: 'Email already registered.' }); + + const hash = await bcrypt.hash(password, 10); + + const result = await pool.query( + `INSERT INTO project."user"(first_name, last_name, email, password_hash, phone_number, gender, birthday) + VALUES ($1,$2,$3,$4,$5,$6,$7) RETURNING user_id, first_name, last_name, email, gender`, + [first_name, last_name, email, hash, phone_number || null, gender || null, birthday || null] + ); + + const user = result.rows[0]; + req.session.user = user; + res.status(201).json({ message: 'Registered successfully.', user }); + } catch (err) { + console.error('Register error:', err.message); + res.status(500).json({ error: 'Server error during registration.' }); + } +}); + +// POST /api/auth/login +app.post('/api/auth/login', async (req, res) => { + const { email, password } = req.body; + if (!email || !password) + return res.status(400).json({ error: 'Email and password are required.' }); + + try { + const result = await pool.query( + 'SELECT * FROM project."user" WHERE email = $1', [email] + ); + if (result.rows.length === 0) + return res.status(401).json({ error: 'Invalid email or password.' }); + + const user = result.rows[0]; + const match = await bcrypt.compare(password, user.password_hash); + if (!match) + return res.status(401).json({ error: 'Invalid email or password.' }); + + req.session.user = { + user_id: user.user_id, + first_name: user.first_name, + last_name: user.last_name, + email: user.email, + gender: user.gender + }; + res.json({ message: 'Logged in.', user: req.session.user }); + } catch (err) { + console.error('Login error:', err.message); + res.status(500).json({ error: 'Server error during login.' }); + } +}); + +// POST /api/auth/logout +app.post('/api/auth/logout', (req, res) => { + req.session.destroy(() => res.json({ message: 'Logged out.' })); +}); + +// GET /api/auth/me โ€” check current session +app.get('/api/auth/me', (req, res) => { + if (req.session && req.session.user) + return res.json({ user: req.session.user }); + res.status(401).json({ error: 'Not logged in.' }); +}); + +// ============================================================= +// USER / PROFILE +// ============================================================= + +// GET /api/users/:id +app.get('/api/users/:id', requireAuth, async (req, res) => { + try { + const result = await pool.query( + 'SELECT user_id, first_name, last_name, email, phone_number, gender, birthday FROM project."user" WHERE user_id = $1', + [req.params.id] + ); + if (result.rows.length === 0) return res.status(404).json({ error: 'User not found.' }); + res.json(result.rows[0]); + } catch (err) { + console.error(err.message); + res.status(500).json({ error: 'Server error.' }); + } +}); + +// PUT /api/users/:id โ€” update profile +app.put('/api/users/:id', requireAuth, async (req, res) => { + const { first_name, last_name, email, phone_number, gender, birthday } = req.body; + try { + const result = await pool.query( + `UPDATE project."user" + SET first_name=$1, last_name=$2, email=$3, phone_number=$4, gender=$5, birthday=$6 + WHERE user_id=$7 RETURNING user_id, first_name, last_name, email, phone_number, gender, birthday`, + [first_name, last_name, email, phone_number, gender, birthday || null, req.params.id] + ); + if (result.rows.length === 0) return res.status(404).json({ error: 'User not found.' }); + // Update session too + req.session.user = { ...req.session.user, ...result.rows[0] }; + res.json(result.rows[0]); + } catch (err) { + console.error(err.message); + res.status(500).json({ error: 'Server error.' }); + } +}); + +// ============================================================= +// WEDDINGS (UC0003) +// ============================================================= + +// GET /api/weddings โ€” get wedding(s) for logged-in user +app.get('/api/weddings', requireAuth, async (req, res) => { + try { + const result = await pool.query( + 'SELECT * FROM project.wedding WHERE user_id = $1 ORDER BY date', + [req.session.user.user_id] + ); + res.json(result.rows); + } catch (err) { + console.error(err.message); + res.status(500).json({ error: 'Server error.' }); + } +}); + +// GET /api/weddings/:id +app.get('/api/weddings/:id', requireAuth, async (req, res) => { + try { + const result = await pool.query( + 'SELECT * FROM project.wedding WHERE wedding_id = $1 AND user_id = $2', + [req.params.id, req.session.user.user_id] + ); + if (result.rows.length === 0) return res.status(404).json({ error: 'Wedding not found.' }); + res.json(result.rows[0]); + } catch (err) { + console.error(err.message); + res.status(500).json({ error: 'Server error.' }); + } +}); + +// POST /api/weddings โ€” create wedding +app.post('/api/weddings', requireAuth, async (req, res) => { + const { date, budget, notes } = req.body; + if (!date) return res.status(400).json({ error: 'Wedding date is required.' }); + try { + const result = await pool.query( + 'INSERT INTO project.wedding("date", budget, notes, user_id) VALUES ($1,$2,$3,$4) RETURNING *', + [date, budget || null, notes || null, req.session.user.user_id] + ); + res.status(201).json(result.rows[0]); + } catch (err) { + console.error(err.message); + res.status(500).json({ error: 'Server error.' }); + } +}); + +// PUT /api/weddings/:id +app.put('/api/weddings/:id', requireAuth, async (req, res) => { + const { date, budget, notes } = req.body; + try { + const result = await pool.query( + `UPDATE project.wedding SET "date"=$1, budget=$2, notes=$3 + WHERE wedding_id=$4 AND user_id=$5 RETURNING *`, + [date, budget || null, notes || null, req.params.id, req.session.user.user_id] + ); + if (result.rows.length === 0) return res.status(404).json({ error: 'Wedding not found.' }); + res.json(result.rows[0]); + } catch (err) { + console.error(err.message); + res.status(500).json({ error: 'Server error.' }); + } +}); + +// DELETE /api/weddings/:id +app.delete('/api/weddings/:id', requireAuth, async (req, res) => { + try { + await pool.query( + 'DELETE FROM project.wedding WHERE wedding_id=$1 AND user_id=$2', + [req.params.id, req.session.user.user_id] + ); + res.json({ message: 'Wedding deleted.' }); + } catch (err) { + console.error(err.message); + res.status(500).json({ error: 'Server error.' }); + } +}); + +// ============================================================= +// EVENTS (UC0005) +// ============================================================= + +// GET /api/weddings/:wid/events +app.get('/api/weddings/:wid/events', requireAuth, async (req, res) => { + try { + const result = await pool.query( + 'SELECT * FROM project.event WHERE wedding_id=$1 ORDER BY "date", start_time', + [req.params.wid] + ); + res.json(result.rows); + } catch (err) { + console.error(err.message); + res.status(500).json({ error: 'Server error.' }); + } +}); + +// POST /api/weddings/:wid/events +app.post('/api/weddings/:wid/events', requireAuth, async (req, res) => { + const { event_type, date, start_time, end_time, status } = req.body; + if (!event_type || !date || !start_time || !end_time) + return res.status(400).json({ error: 'event_type, date, start_time, end_time are required.' }); + try { + const result = await pool.query( + `INSERT INTO project.event(event_type,"date",start_time,end_time,status,wedding_id) + VALUES ($1,$2,$3,$4,$5,$6) RETURNING *`, + [event_type, date, start_time, end_time, status || 'scheduled', req.params.wid] + ); + res.status(201).json(result.rows[0]); + } catch (err) { + console.error(err.message); + res.status(500).json({ error: 'Server error.' }); + } +}); + +// PUT /api/events/:id +app.put('/api/events/:id', requireAuth, async (req, res) => { + const { event_type, date, start_time, end_time, status } = req.body; + try { + const result = await pool.query( + `UPDATE project.event SET event_type=$1,"date"=$2,start_time=$3,end_time=$4,status=$5 + WHERE event_id=$6 RETURNING *`, + [event_type, date, start_time, end_time, status, req.params.id] + ); + if (result.rows.length === 0) return res.status(404).json({ error: 'Event not found.' }); + res.json(result.rows[0]); + } catch (err) { + console.error(err.message); + res.status(500).json({ error: 'Server error.' }); + } +}); + +// DELETE /api/events/:id +app.delete('/api/events/:id', requireAuth, async (req, res) => { + try { + await pool.query('DELETE FROM project.event WHERE event_id=$1', [req.params.id]); + res.json({ message: 'Event deleted.' }); + } catch (err) { + console.error(err.message); + res.status(500).json({ error: 'Server error.' }); + } +}); + +// ============================================================= +// GUESTS (UC0004) +// ============================================================= + +// GET /api/weddings/:wid/guests +app.get('/api/weddings/:wid/guests', requireAuth, async (req, res) => { + try { + const result = await pool.query( + 'SELECT guest_id, first_name, last_name, email, role, wedding_id FROM project.guest WHERE wedding_id=$1 ORDER BY last_name, first_name', + [req.params.wid] + ); + res.json(result.rows); + } catch (err) { + console.error(err.message); + res.status(500).json({ error: 'Server error.' }); + } +}); + +// POST /api/weddings/:wid/guests โ€” add guest + send email invite +app.post('/api/weddings/:wid/guests', requireAuth, async (req, res) => { + const { first_name, last_name, email, role } = req.body; + if (!first_name || !last_name) + return res.status(400).json({ error: 'First name and last name are required.' }); + + try { + // Insert guest + const result = await pool.query( + 'INSERT INTO project.guest(first_name,last_name,email,wedding_id) VALUES ($1,$2,$3,$4) RETURNING *', + [first_name, last_name, email || null, req.params.wid] + ); + const guest = result.rows[0]; + + // Fetch wedding info for the email + const weddingResult = await pool.query( + `SELECT w."date", u.first_name AS owner_first, u.last_name AS owner_last + FROM project.wedding w + JOIN project."user" u ON w.user_id = u.user_id + WHERE w.wedding_id = $1`, + [req.params.wid] + ); + const wedding = weddingResult.rows[0]; + + // Fetch all events for this wedding so guest can RSVP + const eventsResult = await pool.query( + 'SELECT * FROM project.event WHERE wedding_id=$1 ORDER BY "date", start_time', + [req.params.wid] + ); + const events = eventsResult.rows; + + // Create initial RSVP records with status "invited" for each event + for (const event of events) { + try { + await pool.query( + `INSERT INTO project.event_rsvp(status, response_date, guest_id, event_id) + VALUES ('invited', CURRENT_DATE, $1, $2) + ON CONFLICT (guest_id, event_id) DO NOTHING`, + [guest.guest_id, event.event_id] + ); + } catch (rsvpErr) { + console.warn(`โš ๏ธ Could not create RSVP record for guest ${guest.guest_id}, event ${event.event_id}:`, rsvpErr.message); + } + } + + // Send email invitation if email provided + if (email && events.length > 0) { + // Generate a secure token per event for the RSVP links + const eventLinks = events.map(ev => { + const token = crypto + .createHmac('sha256', 'wedding_rsvp_secret') + .update(`${guest.guest_id}-${ev.event_id}`) + .digest('hex'); + const base = `http://localhost:${PORT}`; + return ` + + + ${ev.event_type} โ€” ${ev.date} ${ev.start_time}โ€“${ev.end_time} + + + + โœ… Accept + + + โŒ Decline + + + `; + }).join(''); + + const mailOptions = { + from: `"Wedding Planner" <${process.env.EMAIL_USER}>`, + to: email, + subject: `You're Invited! ๐Ÿ’ ${wedding.owner_first} & ${wedding.owner_last}'s Wedding`, + html: ` +
+
+
๐Ÿ’
+

You're Invited!

+

Wedding of ${wedding.owner_first} & ${wedding.owner_last}

+
+
+

Dear ${first_name} ${last_name},

+

+ We are delighted to invite you to celebrate the wedding of + ${wedding.owner_first} & ${wedding.owner_last} + on ${wedding.date}. +

+

Please RSVP for each event:

+ + ${eventLinks} +
+

+ We look forward to celebrating with you. If you have any questions, please contact us directly. +

+
+
+

Wedding Planner App ยท Sent with love ๐Ÿ’Œ

+
+
` + }; + + try { + await transporter.sendMail(mailOptions); + console.log(`๐Ÿ“ง Invite sent to ${email}`); + } catch (mailErr) { + // Don't fail the guest insert if email fails โ€” just log it + console.warn('โš ๏ธ Email send failed (guest still added):', mailErr.message); + } + } + + res.status(201).json({ guest, emailSent: !!(email && events.length > 0) }); + } catch (err) { + console.error(err.message); + res.status(500).json({ error: 'Server error.' }); + } +}); + +// PUT /api/guests/:id +app.put('/api/guests/:id', requireAuth, async (req, res) => { + const { first_name, last_name, email, role } = req.body; + try { + const result = await pool.query( + 'UPDATE project.guest SET first_name=$1,last_name=$2,email=$3,role=$4 WHERE guest_id=$5 RETURNING *', + [first_name, last_name, email || null, role || 'Guest', req.params.id] + ); + if (result.rows.length === 0) return res.status(404).json({ error: 'Guest not found.' }); + res.json(result.rows[0]); + } catch (err) { + console.error(err.message); + res.status(500).json({ error: 'Server error.' }); + } +}); + +// DELETE /api/guests/:id +app.delete('/api/guests/:id', requireAuth, async (req, res) => { + try { + await pool.query('DELETE FROM project.guest WHERE guest_id=$1', [req.params.id]); + res.json({ message: 'Guest deleted.' }); + } catch (err) { + console.error(err.message); + res.status(500).json({ error: 'Server error.' }); + } +}); + +// ============================================================= +// RSVP (UC0009) +// ============================================================= + +// GET /api/rsvp?guest_id=&event_id= โ€” check existing RSVP +app.get('/api/rsvp', async (req, res) => { + const { guest_id, event_id } = req.query; + try { + const result = await pool.query( + 'SELECT * FROM project.event_rsvp WHERE guest_id=$1 AND event_id=$2', + [guest_id, event_id] + ); + res.json(result.rows[0] || null); + } catch (err) { + console.error(err.message); + res.status(500).json({ error: 'Server error.' }); + } +}); + +// POST /api/rsvp โ€” guest submits RSVP (called from rsvp.html, no auth needed) +app.post('/api/rsvp', async (req, res) => { + const { guest_id, event_id, status, token } = req.body; + if (!guest_id || !event_id || !status) + return res.status(400).json({ error: 'guest_id, event_id and status are required.' }); + + // Verify token (protects against random submissions) + const expected = crypto + .createHmac('sha256', 'wedding_rsvp_secret') + .update(`${guest_id}-${event_id}`) + .digest('hex'); + if (token !== expected) + return res.status(403).json({ error: 'Invalid or expired RSVP link.' }); + + const validStatuses = ['accepted', 'declined', 'pending', 'invited']; + if (!validStatuses.includes(status)) + return res.status(400).json({ error: 'Status must be accepted, declined, pending, or invited.' }); + + try { + // Upsert: update if exists, insert if not + const result = await pool.query( + `INSERT INTO project.event_rsvp(status, response_date, guest_id, event_id) + VALUES ($1, CURRENT_DATE, $2, $3) + ON CONFLICT (guest_id, event_id) + DO UPDATE SET status=$1, response_date=CURRENT_DATE + RETURNING *`, + [status, guest_id, event_id] + ); + + // Fetch guest + event info for the confirmation response + const guestInfo = await pool.query( + `SELECT g.first_name, g.last_name, e.event_type, e.date, e.start_time, e.end_time + FROM project.guest g, project.event e + WHERE g.guest_id=$1 AND e.event_id=$2`, + [guest_id, event_id] + ); + + res.json({ + rsvp: result.rows[0], + guest: guestInfo.rows[0] || null + }); + } catch (err) { + console.error(err.message); + res.status(500).json({ error: 'Server error.' }); + } +}); + +// GET /api/weddings/:wid/rsvp โ€” all RSVPs for a wedding (for admin view) +app.get('/api/weddings/:wid/rsvp', requireAuth, async (req, res) => { + try { + const result = await pool.query( + `SELECT er.response_id, er.status, er.response_date, + g.guest_id, g.first_name, g.last_name, g.email, + e.event_id, e.event_type, e.date AS event_date + FROM project.event_rsvp er + JOIN project.guest g ON er.guest_id = g.guest_id + JOIN project.event e ON er.event_id = e.event_id + WHERE e.wedding_id = $1 + ORDER BY e.date, g.last_name`, + [req.params.wid] + ); + res.json(result.rows); + } catch (err) { + console.error(err.message); + res.status(500).json({ error: 'Server error.' }); + } +}); + +// ============================================================= +// ATTENDANCE / SEATING (UC0011) +// ============================================================= + +// GET /api/weddings/:wid/attendance โ€” full seating list for a wedding +app.get('/api/weddings/:wid/attendance', requireAuth, async (req, res) => { + try { + const result = await pool.query( + `SELECT a.attendance_id, a.status, a.table_number, a.role, + g.guest_id, g.first_name, g.last_name, + e.event_id, e.event_type + FROM project.attendance a + JOIN project.guest g ON a.guest_id = g.guest_id + JOIN project.event e ON a.event_id = e.event_id + WHERE e.wedding_id = $1 + ORDER BY a.table_number NULLS LAST, g.last_name`, + [req.params.wid] + ); + res.json(result.rows); + } catch (err) { + console.error(err.message); + res.status(500).json({ error: 'Server error.' }); + } +}); + +// GET /api/events/:eid/attendance โ€” seating for a specific event +app.get('/api/events/:eid/attendance', requireAuth, async (req, res) => { + try { + const result = await pool.query( + `SELECT a.attendance_id, a.status, a.table_number, a.role, + g.guest_id, g.first_name, g.last_name + FROM project.attendance a + JOIN project.guest g ON a.guest_id = g.guest_id + WHERE a.event_id = $1 + ORDER BY a.table_number NULLS LAST, g.last_name`, + [req.params.eid] + ); + res.json(result.rows); + } catch (err) { + console.error(err.message); + res.status(500).json({ error: 'Server error.' }); + } +}); + +// POST /api/attendance โ€” assign guest to event with table + role +app.post('/api/attendance', requireAuth, async (req, res) => { + const { table_number, role, guest_id, event_id } = req.body; + if (!guest_id || !event_id || !role) + return res.status(400).json({ error: 'guest_id, event_id and role are required.' }); + try { + // Fetch the guest's RSVP status for this event + const rsvpResult = await pool.query( + 'SELECT status FROM project.event_rsvp WHERE guest_id=$1 AND event_id=$2', + [guest_id, event_id] + ); + const rsvpStatus = rsvpResult.rows[0]?.status || 'pending'; + + const result = await pool.query( + `INSERT INTO project.attendance(status, table_number, role, guest_id, event_id) + VALUES ($1,$2,$3,$4,$5) + ON CONFLICT (guest_id, event_id) + DO UPDATE SET status=$1, table_number=$2, role=$3 + RETURNING *`, + [rsvpStatus, table_number || null, role, guest_id, event_id] + ); + res.status(201).json(result.rows[0]); + } catch (err) { + console.error(err.message); + res.status(500).json({ error: 'Server error.' }); + } +}); + +// PUT /api/attendance/:id โ€” update seating assignment +app.put('/api/attendance/:id', requireAuth, async (req, res) => { + const { table_number, role } = req.body; + try { + // Fetch the attendance record to get guest_id and event_id + const attendanceRecord = await pool.query( + 'SELECT guest_id, event_id FROM project.attendance WHERE attendance_id=$1', + [req.params.id] + ); + if (attendanceRecord.rows.length === 0) + return res.status(404).json({ error: 'Record not found.' }); + + const { guest_id, event_id } = attendanceRecord.rows[0]; + + // Fetch the guest's RSVP status for this event + const rsvpResult = await pool.query( + 'SELECT status FROM project.event_rsvp WHERE guest_id=$1 AND event_id=$2', + [guest_id, event_id] + ); + const rsvpStatus = rsvpResult.rows[0]?.status || 'pending'; + + const result = await pool.query( + `UPDATE project.attendance SET status=$1, table_number=$2, role=$3 + WHERE attendance_id=$4 RETURNING *`, + [rsvpStatus, table_number || null, role, req.params.id] + ); + if (result.rows.length === 0) return res.status(404).json({ error: 'Record not found.' }); + res.json(result.rows[0]); + } catch (err) { + console.error(err.message); + res.status(500).json({ error: 'Server error.' }); + } +}); + +// DELETE /api/attendance/:id +app.delete('/api/attendance/:id', requireAuth, async (req, res) => { + try { + await pool.query('DELETE FROM project.attendance WHERE attendance_id=$1', [req.params.id]); + res.json({ message: 'Attendance record deleted.' }); + } catch (err) { + console.error(err.message); + res.status(500).json({ error: 'Server error.' }); + } +}); + +// ============================================================= +// VENUES (UC0006) +// ============================================================= + +// GET /api/venues โ€” all venues with type name +app.get('/api/venues', requireAuth, async (req, res) => { + try { + const result = await pool.query( + `SELECT v.*, vt.type_name + FROM project.venue v + JOIN project.venue_type vt ON v.type_id = vt.type_id + ORDER BY v.city, v.name` + ); + res.json(result.rows); + } catch (err) { + console.error(err.message); + res.status(500).json({ error: 'Server error.' }); + } +}); + +// GET /api/weddings/:wid/venue-bookings +app.get('/api/weddings/:wid/venue-bookings', requireAuth, async (req, res) => { + try { + const result = await pool.query( + `SELECT vb.*, v.name AS venue_name, v.location, v.city + FROM project.venue_booking vb + JOIN project.venue v ON vb.venue_id = v.venue_id + WHERE vb.wedding_id = $1 + ORDER BY vb.date`, + [req.params.wid] + ); + res.json(result.rows); + } catch (err) { + console.error(err.message); + res.status(500).json({ error: 'Server error.' }); + } +}); + +// POST /api/weddings/:wid/venue-bookings +app.post('/api/weddings/:wid/venue-bookings', requireAuth, async (req, res) => { + const { date, start_time, end_time, status, price, venue_id } = req.body; + if (!date || !start_time || !end_time || !venue_id) + return res.status(400).json({ error: 'date, start_time, end_time and venue_id are required.' }); + + // Check availability: no overlapping booking for same venue on same date + try { + const conflict = await pool.query( + `SELECT booking_id FROM project.venue_booking + WHERE venue_id=$1 AND "date"=$2 + AND NOT (end_time <= $3 OR start_time >= $4)`, + [venue_id, date, start_time, end_time] + ); + if (conflict.rows.length > 0) + return res.status(409).json({ error: 'Venue is already booked during this time slot.' }); + + const result = await pool.query( + `INSERT INTO project.venue_booking("date",start_time,end_time,status,price,venue_id,wedding_id) + VALUES ($1,$2,$3,$4,$5,$6,$7) RETURNING *`, + [date, start_time, end_time, status || 'confirmed', price || 0, venue_id, req.params.wid] + ); + res.status(201).json(result.rows[0]); + } catch (err) { + console.error(err.message); + res.status(500).json({ error: 'Server error.' }); + } +}); + +// DELETE /api/venue-bookings/:id +app.delete('/api/venue-bookings/:id', requireAuth, async (req, res) => { + try { + await pool.query('DELETE FROM project.venue_booking WHERE booking_id=$1', [req.params.id]); + res.json({ message: 'Venue booking cancelled.' }); + } catch (err) { + console.error(err.message); + res.status(500).json({ error: 'Server error.' }); + } +}); + +// ============================================================= +// BANDS (UC0007) +// ============================================================= + +// GET /api/bands +app.get('/api/bands', requireAuth, async (req, res) => { + try { + const result = await pool.query('SELECT * FROM project.band ORDER BY band_name'); + res.json(result.rows); + } catch (err) { + console.error(err.message); + res.status(500).json({ error: 'Server error.' }); + } +}); + +// GET /api/weddings/:wid/band-bookings +app.get('/api/weddings/:wid/band-bookings', requireAuth, async (req, res) => { + try { + const result = await pool.query( + `SELECT bb.*, b.band_name, b.genre + FROM project.band_booking bb + JOIN project.band b ON bb.band_id = b.band_id + WHERE bb.wedding_id=$1 ORDER BY bb.date`, + [req.params.wid] + ); + res.json(result.rows); + } catch (err) { + console.error(err.message); + res.status(500).json({ error: 'Server error.' }); + } +}); + +// POST /api/weddings/:wid/band-bookings +app.post('/api/weddings/:wid/band-bookings', requireAuth, async (req, res) => { + const { date, start_time, end_time, status, band_id } = req.body; + if (!date || !start_time || !end_time || !band_id) + return res.status(400).json({ error: 'date, start_time, end_time and band_id are required.' }); + try { + const result = await pool.query( + `INSERT INTO project.band_booking("date",start_time,end_time,status,band_id,wedding_id) + VALUES ($1,$2,$3,$4,$5,$6) RETURNING *`, + [date, start_time, end_time, status || 'confirmed', band_id, req.params.wid] + ); + res.status(201).json(result.rows[0]); + } catch (err) { + console.error(err.message); + res.status(500).json({ error: 'Server error.' }); + } +}); + +// DELETE /api/band-bookings/:id +app.delete('/api/band-bookings/:id', requireAuth, async (req, res) => { + try { + await pool.query('DELETE FROM project.band_booking WHERE booking_id=$1', [req.params.id]); + res.json({ message: 'Band booking removed.' }); + } catch (err) { + console.error(err.message); + res.status(500).json({ error: 'Server error.' }); + } +}); + +// ============================================================= +// PHOTOGRAPHERS (UC0008) +// ============================================================= + +// GET /api/photographers +app.get('/api/photographers', requireAuth, async (req, res) => { + try { + const result = await pool.query('SELECT * FROM project.photographer ORDER BY name'); + res.json(result.rows); + } catch (err) { + console.error(err.message); + res.status(500).json({ error: 'Server error.' }); + } +}); + +// GET /api/weddings/:wid/photographer-bookings +app.get('/api/weddings/:wid/photographer-bookings', requireAuth, async (req, res) => { + try { + const result = await pool.query( + `SELECT pb.*, p.name AS photographer_name, p.email AS photographer_email + FROM project.photographer_booking pb + JOIN project.photographer p ON pb.photographer_id = p.photographer_id + WHERE pb.wedding_id=$1 ORDER BY pb.date`, + [req.params.wid] + ); + res.json(result.rows); + } catch (err) { + console.error(err.message); + res.status(500).json({ error: 'Server error.' }); + } +}); + +// POST /api/weddings/:wid/photographer-bookings +app.post('/api/weddings/:wid/photographer-bookings', requireAuth, async (req, res) => { + const { date, start_time, end_time, status, photographer_id } = req.body; + if (!date || !start_time || !end_time || !photographer_id) + return res.status(400).json({ error: 'date, start_time, end_time and photographer_id are required.' }); + try { + const result = await pool.query( + `INSERT INTO project.photographer_booking("date",start_time,end_time,status,photographer_id,wedding_id) + VALUES ($1,$2,$3,$4,$5,$6) RETURNING *`, + [date, start_time, end_time, status || 'confirmed', photographer_id, req.params.wid] + ); + res.status(201).json(result.rows[0]); + } catch (err) { + console.error(err.message); + res.status(500).json({ error: 'Server error.' }); + } +}); + +// DELETE /api/photographer-bookings/:id +app.delete('/api/photographer-bookings/:id', requireAuth, async (req, res) => { + try { + await pool.query('DELETE FROM project.photographer_booking WHERE booking_id=$1', [req.params.id]); + res.json({ message: 'Photographer booking removed.' }); + } catch (err) { + console.error(err.message); + res.status(500).json({ error: 'Server error.' }); + } +}); + +// ============================================================= +// CHURCHES + PRIESTS +// ============================================================= + +// GET /api/churches โ€” all churches with their linked priest +app.get('/api/churches', requireAuth, async (req, res) => { + try { + const result = await pool.query( + `SELECT c.*, p.name AS priest_name, p.contact AS priest_contact + FROM project.church c + LEFT JOIN project.priest p ON p.church_id = c.church_id + ORDER BY c.name` + ); + res.json(result.rows); + } catch (err) { + console.error(err.message); + res.status(500).json({ error: 'Server error.' }); + } +}); + +// GET /api/weddings/:wid/church-bookings +app.get('/api/weddings/:wid/church-bookings', requireAuth, async (req, res) => { + try { + const result = await pool.query( + `SELECT cb.*, c.name AS church_name, c.location, c.contact, + p.name AS priest_name, p.contact AS priest_contact + FROM project.church_booking cb + JOIN project.church c ON cb.church_id = c.church_id + LEFT JOIN project.priest p ON p.church_id = c.church_id + WHERE cb.wedding_id=$1 ORDER BY cb.date`, + [req.params.wid] + ); + res.json(result.rows); + } catch (err) { + console.error(err.message); + res.status(500).json({ error: 'Server error.' }); + } +}); + +// POST /api/weddings/:wid/church-bookings +app.post('/api/weddings/:wid/church-bookings', requireAuth, async (req, res) => { + const { date, start_time, end_time, status, church_id } = req.body; + if (!date || !start_time || !end_time || !church_id) + return res.status(400).json({ error: 'date, start_time, end_time and church_id are required.' }); + try { + const result = await pool.query( + `INSERT INTO project.church_booking("date",start_time,end_time,status,church_id,wedding_id) + VALUES ($1,$2,$3,$4,$5,$6) RETURNING *`, + [date, start_time, end_time, status || 'confirmed', church_id, req.params.wid] + ); + res.status(201).json(result.rows[0]); + } catch (err) { + console.error(err.message); + res.status(500).json({ error: 'Server error.' }); + } +}); + +// DELETE /api/church-bookings/:id +app.delete('/api/church-bookings/:id', requireAuth, async (req, res) => { + try { + await pool.query('DELETE FROM project.church_booking WHERE booking_id=$1', [req.params.id]); + res.json({ message: 'Church booking removed.' }); + } catch (err) { + console.error(err.message); + res.status(500).json({ error: 'Server error.' }); + } +}); + +// ============================================================= +// REGISTRARS +// ============================================================= + +// GET /api/registrars +app.get('/api/registrars', requireAuth, async (req, res) => { + try { + const result = await pool.query('SELECT * FROM project.registrar ORDER BY name'); + res.json(result.rows); + } catch (err) { + console.error(err.message); + res.status(500).json({ error: 'Server error.' }); + } +}); + +// GET /api/weddings/:wid/registrar-bookings +app.get('/api/weddings/:wid/registrar-bookings', requireAuth, async (req, res) => { + try { + const result = await pool.query( + `SELECT rb.*, r.name AS registrar_name, r.location + FROM project.registrar_booking rb + JOIN project.registrar r ON rb.registrar_id = r.registrar_id + WHERE rb.wedding_id=$1 ORDER BY rb.date`, + [req.params.wid] + ); + res.json(result.rows); + } catch (err) { + console.error(err.message); + res.status(500).json({ error: 'Server error.' }); + } +}); + +// POST /api/weddings/:wid/registrar-bookings +app.post('/api/weddings/:wid/registrar-bookings', requireAuth, async (req, res) => { + const { date, start_time, end_time, status, registrar_id } = req.body; + if (!date || !start_time || !end_time || !registrar_id) + return res.status(400).json({ error: 'date, start_time, end_time and registrar_id are required.' }); + try { + const result = await pool.query( + `INSERT INTO project.registrar_booking("date",start_time,end_time,status,registrar_id,wedding_id) + VALUES ($1,$2,$3,$4,$5,$6) RETURNING *`, + [date, start_time, end_time, status || 'confirmed', registrar_id, req.params.wid] + ); + res.status(201).json(result.rows[0]); + } catch (err) { + console.error(err.message); + res.status(500).json({ error: 'Server error.' }); + } +}); + +// DELETE /api/registrar-bookings/:id +app.delete('/api/registrar-bookings/:id', requireAuth, async (req, res) => { + try { + await pool.query('DELETE FROM project.registrar_booking WHERE booking_id=$1', [req.params.id]); + res.json({ message: 'Registrar booking removed.' }); + } catch (err) { + console.error(err.message); + res.status(500).json({ error: 'Server error.' }); + } +}); + +// ============================================================= +// START SERVER +// ============================================================= +app.listen(PORT, () => { + console.log(`\n๐ŸŒธ Wedding Planner server running at http://localhost:${PORT}`); + console.log(` Dashboard โ†’ http://localhost:${PORT}/Wedding_Planner.html`); + console.log(` Login โ†’ http://localhost:${PORT}/login.html`); + console.log(` RSVP page โ†’ http://localhost:${PORT}/rsvp.html\n`); +});