source: server.js@ d8deee6

// =============================================================
//  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 `
                    <tr>
                      <td style="padding:8px 0; font-size:15px; color:#2c1f1f;">
                        <strong>${ev.event_type}</strong> — ${ev.date} ${ev.start_time}–${ev.end_time}
                      </td>
                      <td style="padding:8px 0; text-align:right;">
                        <a href="${base}/rsvp.html?guest=${guest.guest_id}&event=${ev.event_id}&token=${token}&action=accepted"
                           style="background:#3a9e6b;color:#fff;padding:6px 14px;border-radius:6px;text-decoration:none;margin-right:6px;font-size:13px;">
                          ✅ Accept
                        </a>
                        <a href="${base}/rsvp.html?guest=${guest.guest_id}&event=${ev.event_id}&token=${token}&action=declined"
                           style="background:#c94545;color:#fff;padding:6px 14px;border-radius:6px;text-decoration:none;font-size:13px;">
                          ❌ Decline
                        </a>
                      </td>
                    </tr>`;
            }).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: `
                    <div style="font-family:'DM Sans',Arial,sans-serif;max-width:600px;margin:auto;background:#fdf8f3;border-radius:12px;overflow:hidden;border:1px solid #ecddd0;">
                      <div style="background:linear-gradient(135deg,#d4606a,#e8949c);padding:32px;text-align:center;">
                        <div style="font-size:36px;margin-bottom:8px;">💍</div>
                        <h1 style="color:#fff;font-size:26px;margin:0;">You're Invited!</h1>
                        <p style="color:rgba(255,255,255,0.9);margin:8px 0 0;">Wedding of ${wedding.owner_first} &amp; ${wedding.owner_last}</p>
                      </div>
                      <div style="padding:32px;">
                        <p style="font-size:16px;color:#2c1f1f;">Dear <strong>${first_name} ${last_name}</strong>,</p>
                        <p style="font-size:15px;color:#6b4f4f;line-height:1.6;">
                          We are delighted to invite you to celebrate the wedding of
                          <strong>${wedding.owner_first} &amp; ${wedding.owner_last}</strong>
                          on <strong>${wedding.date}</strong>.
                        </p>
                        <p style="font-size:15px;color:#2c1f1f;font-weight:600;margin-top:24px;">Please RSVP for each event:</p>
                        <table style="width:100%;border-collapse:collapse;">
                          ${eventLinks}
                        </table>
                        <p style="font-size:13px;color:#a08080;margin-top:24px;">
                          We look forward to celebrating with you. If you have any questions, please contact us directly.
                        </p>
                      </div>
                      <div style="background:#f2e8db;padding:16px;text-align:center;">
                        <p style="font-size:12px;color:#a08080;margin:0;">Wedding Planner App · Sent with love 💌</p>
                      </div>
                    </div>`
            };

            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`);
});