// src/db-client.ts - Cliente PostgreSQL para el MCP
import pg from 'pg';
const { Pool } = pg;
export interface ReservaRecord {
id: number;
bookingid: string;
arrival: string | null;
departure: string | null;
propertyid: string | null;
roomid: number | null;
guestfirstname: string | null;
guestlastname: string | null;
guestemail: string | null;
guestphone: string | null;
guestmobile: string | null;
status: string | null;
channel: string | null;
numadult: string | null;
numchild: string | null;
price: string | null;
deposit: string | null;
notes: string | null;
}
export class DbClient {
private pool: pg.Pool | null = null;
private _isConnected = false;
// Room IDs de Pa'Cartagena (7 apartamentos)
private readonly PACARTAGENA_ROOM_IDS = [378317, 378321, 378316, 378110, 506591, 378318, 378320];
// Room ID de Terceros/Tours
private readonly TERCEROS_TOURS_ROOM_ID = 584854;
async initialize(connectionString: string): Promise<void> {
this.pool = new Pool({
connectionString,
ssl: connectionString.includes('railway') || connectionString.includes('ssl=true')
? { rejectUnauthorized: false }
: undefined,
max: 5,
idleTimeoutMillis: 30000,
connectionTimeoutMillis: 10000
});
// Test connection
const client = await this.pool.connect();
await client.query('SELECT NOW()');
client.release();
this._isConnected = true;
}
isReady(): boolean {
return this._isConnected && this.pool !== null;
}
// Metodo publico para verificar conexion (MCP 2025-11-25)
isConnected(): boolean {
return this.isReady();
}
// ═══════════════════════════════════════════════════════════════
// RESERVAS FUTURAS (Pa'Cartagena)
// ═══════════════════════════════════════════════════════════════
async getReservasFuturas(): Promise<ReservaRecord[]> {
if (!this.pool) throw new Error('DB no conectada');
const query = `
SELECT * FROM reservas
WHERE departure IS NOT NULL
AND departure != ''
AND departure ~ '^\\d{4}-\\d{2}-\\d{2}'
AND departure::date >= (CURRENT_TIMESTAMP AT TIME ZONE 'America/Bogota')::date
AND LOWER(COALESCE(status, '')) NOT LIKE '%cancel%'
AND roomid = ANY($1)
ORDER BY arrival ASC
`;
const result = await this.pool.query(query, [this.PACARTAGENA_ROOM_IDS]);
return result.rows;
}
// ═══════════════════════════════════════════════════════════════
// RESERVAS TOURS/TERCEROS
// ═══════════════════════════════════════════════════════════════
async getReservasTercerosTours(): Promise<ReservaRecord[]> {
if (!this.pool) throw new Error('DB no conectada');
const query = `
SELECT * FROM reservas
WHERE departure IS NOT NULL
AND departure != ''
AND departure ~ '^\\d{4}-\\d{2}-\\d{2}'
AND departure::date >= (CURRENT_TIMESTAMP AT TIME ZONE 'America/Bogota')::date
AND LOWER(COALESCE(status, '')) NOT LIKE '%cancel%'
AND roomid = $1
ORDER BY arrival ASC
`;
const result = await this.pool.query(query, [this.TERCEROS_TOURS_ROOM_ID]);
return result.rows;
}
// ═══════════════════════════════════════════════════════════════
// BÚSQUEDA Y ESTADÍSTICAS
// ═══════════════════════════════════════════════════════════════
async getReservaByBookingId(bookingId: string): Promise<ReservaRecord | null> {
if (!this.pool) throw new Error('DB no conectada');
const result = await this.pool.query(
'SELECT * FROM reservas WHERE bookingid = $1',
[bookingId]
);
return result.rows[0] || null;
}
async countReservas(): Promise<{ total: number; futuras: number; tours: number }> {
if (!this.pool) throw new Error('DB no conectada');
const totalResult = await this.pool.query('SELECT COUNT(*) FROM reservas');
const futurasResult = await this.pool.query(`
SELECT COUNT(*) FROM reservas
WHERE departure IS NOT NULL
AND departure != ''
AND departure ~ '^\\d{4}-\\d{2}-\\d{2}'
AND departure::date >= (CURRENT_TIMESTAMP AT TIME ZONE 'America/Bogota')::date
AND LOWER(COALESCE(status, '')) NOT LIKE '%cancel%'
AND roomid = ANY($1)
`, [this.PACARTAGENA_ROOM_IDS]);
const toursResult = await this.pool.query(`
SELECT COUNT(*) FROM reservas
WHERE departure IS NOT NULL
AND departure != ''
AND departure ~ '^\\d{4}-\\d{2}-\\d{2}'
AND departure::date >= (CURRENT_TIMESTAMP AT TIME ZONE 'America/Bogota')::date
AND LOWER(COALESCE(status, '')) NOT LIKE '%cancel%'
AND roomid = $1
`, [this.TERCEROS_TOURS_ROOM_ID]);
return {
total: parseInt(totalResult.rows[0].count, 10),
futuras: parseInt(futurasResult.rows[0].count, 10),
tours: parseInt(toursResult.rows[0].count, 10),
};
}
async getReservasPorFecha(desde: string, hasta: string): Promise<ReservaRecord[]> {
if (!this.pool) throw new Error('DB no conectada');
const query = `
SELECT * FROM reservas
WHERE arrival IS NOT NULL
AND arrival != ''
AND arrival ~ '^\\d{4}-\\d{2}-\\d{2}'
AND arrival::date >= $1::date
AND arrival::date <= $2::date
AND LOWER(COALESCE(status, '')) NOT LIKE '%cancel%'
ORDER BY arrival ASC
`;
const result = await this.pool.query(query, [desde, hasta]);
return result.rows;
}
async getReservasPorCanal(canal: string): Promise<ReservaRecord[]> {
if (!this.pool) throw new Error('DB no conectada');
const query = `
SELECT * FROM reservas
WHERE LOWER(channel) = LOWER($1)
AND departure IS NOT NULL
AND departure != ''
AND departure ~ '^\\d{4}-\\d{2}-\\d{2}'
AND departure::date >= (CURRENT_TIMESTAMP AT TIME ZONE 'America/Bogota')::date
AND LOWER(COALESCE(status, '')) NOT LIKE '%cancel%'
ORDER BY arrival ASC
`;
const result = await this.pool.query(query, [canal]);
return result.rows;
}
async getEstadisticasCanales(): Promise<{ canal: string; cantidad: number }[]> {
if (!this.pool) throw new Error('DB no conectada');
const query = `
SELECT
COALESCE(channel, 'Sin canal') as canal,
COUNT(*) as cantidad
FROM reservas
WHERE departure IS NOT NULL
AND departure != ''
AND departure ~ '^\\d{4}-\\d{2}-\\d{2}'
AND departure::date >= (CURRENT_TIMESTAMP AT TIME ZONE 'America/Bogota')::date
AND LOWER(COALESCE(status, '')) NOT LIKE '%cancel%'
GROUP BY channel
ORDER BY cantidad DESC
`;
const result = await this.pool.query(query);
return result.rows.map(r => ({
canal: r.canal,
cantidad: parseInt(r.cantidad, 10),
}));
}
}
export const dbClient = new DbClient();
