source: src/models/queries.js

const db = require('../config/database');

const adminQueries = {
  getBuildingsByAdminId: async (admin_id) => {
    const query = `
      SELECT building_id, name, address, description
      FROM building
      WHERE admin_id = $1
      ORDER BY name
    `;
    const result = await db.getPool().query(query, [admin_id]);
    return result.rows;
  },

  getFloorsByBuildingId: async (buildingId) => {
    const query = `
      SELECT floor_id, floor_number, layout_image
      FROM floor
      WHERE building_id = $1
      ORDER BY floor_number
    `;
    const result = await db.getPool().query(query, [buildingId]);
    return result.rows;
  },

  validateUnitNumber: async (floorId, unitNumber) => {
    const query = `
      SELECT COUNT(*) as count
      FROM unit 
      WHERE floor_id = $1
      AND unit_number = $2
    `;
    const result = await db.getPool().query(query, [floorId, unitNumber]);
    return result.rows[0].count === '0';
  },

  insertUnit: async (unitData, floorId) => {
    const query = `
      INSERT INTO unit (unit_number, room_number, floor_area, status, price, image, floorplan, vector_image, floor_id)
      VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)
      RETURNING unit_id, unit_number, room_number, floor_area, status, price
    `;
    const values = [
      unitData.unit_number,
      unitData.room_number,
      unitData.floor_area,
      unitData.status,
      unitData.price,
      unitData.image,
      unitData.floorplan,
      unitData.vector_image,
      floorId,
    ];
    const result = await db.getPool().query(query, values);
    return result.rows[0];
  },

  getBuildingById: async (buildingId) => {
    const query = `
      SELECT building_id, name, address, description, admin_id
      FROM building
      WHERE building_id = $1
    `;
    const result = await db.getPool().query(query, [buildingId]);
    return result.rows[0];
  },

  getFloorById: async (floorId) => {
    const query = `
      SELECT floor_id, floor_number, layout_image, building_id
      FROM floor
      WHERE floor_id = $1
    `;
    const result = await db.getPool().query(query, [floorId]);
    return result.rows[0];
  },
};

const clientQueries = {
  getAllBuildings: async () => {
    const query = `
      SELECT building_id, name, address, description
      FROM building
      ORDER BY name
    `;
    const result = await db.getPool().query(query);
    return result.rows;
  },

  getBuildingDetailById: async (buildingId) => {
    const query = `
      SELECT 
        b.building_id,
        b.name AS building_name,
        b.address,
        b.description,
        a.full_name AS architect_name,
        f.floor_id,
        f.floor_number,
        f.layout_image
      FROM building b
      LEFT JOIN designs d ON b.building_id = d.building_id
      LEFT JOIN architect a ON d.architect_id = a.architect_id
      JOIN floor f ON b.building_id = f.building_id
      WHERE b.building_id = $1
      ORDER BY f.floor_number
    `;
    const result = await db.getPool().query(query, [buildingId]);
    return result.rows;
  },

  getUnitsByFloorId: async (floorId) => {
    const query = `
      SELECT 
        u.unit_id,
        u.unit_number,
        u.room_number,
        u.floor_area,
        u.price,
        u.status,
        u.image
      FROM unit u
      WHERE u.floor_id = $1
      ORDER BY u.unit_number
    `;
    const result = await db.getPool().query(query, [floorId]);
    return result.rows;
  },

  getUnitById: async (unitId) => {
    const query = `
      SELECT 
        u.unit_id,
        u.unit_number,
        u.room_number,
        u.floor_area,
        u.price,
        u.status,
        u.image,
        u.floorplan,
        u.vector_image,
        f.floor_id,
        f.floor_number,
        b.building_id,
        b.name AS building_name
      FROM unit u
      JOIN floor f ON u.floor_id = f.floor_id
      JOIN building b ON f.building_id = b.building_id
      WHERE u.unit_id = $1
    `;
    const result = await db.getPool().query(query, [unitId]);
    return result.rows[0];
  },
};

const appointmentQueries = {
  getAvailableTimeslots: async () => {
    const query = `
      SELECT 
        a.agent_id,
        a.name AS agent_name,
        a.email,
        t.timeslot_id,
        t.date,
        t.time_start,
        t.time_end
      FROM agent a
      JOIN timeslot t ON a.agent_id = t.agent_id
      WHERE t.status = 'Available'
      AND t.date >= CURRENT_DATE
      ORDER BY a.name, t.date, t.time_start
    `;
    const result = await db.getPool().query(query);
    return result.rows;
  },

  // upsert: update client info on email conflict
  createClient: async (name, email, phone) => {
    const query = `
      INSERT INTO client (name, email, phone)
      VALUES ($1, $2, $3)
      ON CONFLICT (email) DO UPDATE SET name = $1, phone = $3
      RETURNING client_id
    `;
    const result = await db.getPool().query(query, [name, email, phone]);
    return result.rows[0].client_id;
  },

  verifyTimeslot: async (timeslotId) => {
    const query = `
      SELECT status 
      FROM timeslot 
      WHERE timeslot_id = $1
    `;
    const result = await db.getPool().query(query, [timeslotId]);
    return result.rows[0]?.status === 'Available';
  },

  getAgentFromTimeslot: async (timeslotId) => {
    const query = `
      SELECT agent_id
      FROM timeslot
      WHERE timeslot_id = $1
    `;
    const result = await db.getPool().query(query, [timeslotId]);
    return result.rows[0]?.agent_id;
  },

  createAppointment: async (clientId, unitId, timeslotId) => {
    const query = `
      INSERT INTO appointment (status, client_id, unit_id, timeslot_id)
      VALUES ('Scheduled', $1, $2, $3)
      RETURNING appointment_id
    `;
    const result = await db.getPool().query(query, [clientId, unitId, timeslotId]);
    return result.rows[0].appointment_id;
  },

  bookTimeslot: async (timeslotId) => {
    const query = `
      UPDATE timeslot
      SET status = 'Booked'
      WHERE timeslot_id = $1
    `;
    await db.getPool().query(query, [timeslotId]);
  },

  getAppointmentDetails: async (appointmentId) => {
    const query = `
      SELECT 
        ap.appointment_id,
        c.name AS client_name,
        c.email AS client_email,
        c.phone AS client_phone,
        u.unit_number,
        b.name AS building_name,
        f.floor_number,
        t.date,
        t.time_start,
        t.time_end,
        a.name AS agent_name,
        a.email AS agent_email
      FROM appointment ap
      JOIN client c ON ap.client_id = c.client_id
      JOIN unit u ON ap.unit_id = u.unit_id
      JOIN floor f ON u.floor_id = f.floor_id
      JOIN building b ON f.building_id = b.building_id
      JOIN timeslot t ON ap.timeslot_id = t.timeslot_id
      JOIN agent a ON t.agent_id = a.agent_id
      WHERE ap.appointment_id = $1
    `;
    const result = await db.getPool().query(query, [appointmentId]);
    return result.rows[0];
  },
};

module.exports = {
  adminQueries,
  clientQueries,
  appointmentQueries,
};