const { sequelize } = require('../../models');

exports.transactions = async (req, res, next) => {
	try {
		const { memberId } = req;
		const sql = `
        SELECT
            reference_no,
            'Regular Ticket' AS item_name,
            purchased_at AS createdAt,
            'Paid' AS payment_status,
            tickets.total 
        FROM
        tickets
        WHERE
            purchased_by = ?

        UNION ALL

        SELECT
            event_registrations.reference_no,
        events.title AS item_name,
            event_registrations.createdAt,
            event_registrations.payment_status,
            event_registrations.total 
        FROM
            event_registrations
        LEFT JOIN events ON events.id = event_registrations.event_id
        WHERE
            event_registrations.member_id = ?

        UNION ALL

        SELECT
            hotel_bookings.reference_no,
            hotels.name AS item_name,
            hotel_bookings.createdAt,
            hotel_bookings.payment_status,
            hotel_bookings.total
        FROM
            hotel_bookings
        LEFT JOIN hotels ON hotels.id = hotel_bookings.hotel_id
        WHERE
            hotel_bookings.member_id = ?

        UNION ALL

        SELECT
            activity_bookings.reference_no,
            activities.title AS item_name,
            activity_bookings.createdAt,
            activity_bookings.payment_status,
            activity_bookings.total
        FROM
            activity_bookings
        LEFT JOIN activities ON activities.id = activity_bookings.activity_id
        WHERE
            activity_bookings.member_id = ?

        UNION ALL

        SELECT
            pp.reference_no,
            GROUP_CONCAT(p.name SEPARATOR ', ') AS item_name,
            pp.createdAt,
            pp.payment_status,
            pp.total
        FROM
            product_purchases AS pp
        LEFT JOIN product_purchase_items AS ppi ON pp.id = ppi.purchase_id
        LEFT JOIN products AS p ON p.id = ppi.product_id
        WHERE
            pp.member_id = ?
        GROUP BY
            pp.id, pp.reference_no, pp.createdAt, pp.payment_status

        ORDER BY
            createdAt DESC;
    `;

		const [rows] = await sequelize.query(sql, {
			replacements: [memberId, memberId, memberId, memberId, memberId],
		});

		return res.status(200).json({
			status: 'success',
			data: rows,
		});
	} catch (error) {
		next(error);
	}
};
