PostgreSQL Database Schema and Connection Pool Setup

import { Pool } from 'pg';
import dotenv from 'dotenv';

dotenv.config();

export const pool = new Pool({
  host:     process.env.DB_HOST,
  port:     Number(process.env.DB_PORT),
  database: process.env.DB_NAME,
  user:     process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  max:               10,     // maximum simultaneous connections
  idleTimeoutMillis: 30000,  // close idle connections after 30 s
});

-- Sports courts available for booking
CREATE TABLE IF NOT EXISTS canchas (
  id          SERIAL PRIMARY KEY,
  nombre      VARCHAR(100)   NOT NULL,
  descripcion TEXT,
  precio_hora DECIMAL(10, 2) NOT NULL,
  activa      BOOLEAN        DEFAULT true,
  created_at  TIMESTAMPTZ    DEFAULT NOW()
);

-- Bookings placed against a specific court
CREATE TABLE IF NOT EXISTS reservas (
  id             SERIAL PRIMARY KEY,
  cancha_id      INTEGER        NOT NULL REFERENCES canchas(id),
  fecha          DATE           NOT NULL,
  hora_inicio    TIME           NOT NULL,
  hora_fin       TIME           NOT NULL,
  nombre_cliente VARCHAR(255)   NOT NULL,
  telefono       VARCHAR(20)    NOT NULL,
  estado         VARCHAR(20)    DEFAULT 'pendiente',
  origen         VARCHAR(20)    DEFAULT 'interfaz',
  created_at     TIMESTAMPTZ    DEFAULT NOW(),
  CONSTRAINT estado_valido CHECK (estado IN ('pendiente','confirmada','cancelada')),
  CONSTRAINT origen_valido CHECK (origen IN ('whatsapp','interfaz')),
  CONSTRAINT hora_valida   CHECK (hora_fin > hora_inicio)
);

-- Registered users (admin / staff)
CREATE TABLE IF NOT EXISTS users (
  id            SERIAL PRIMARY KEY,
  nombre        VARCHAR(100)  NOT NULL,
  email         VARCHAR(255)  UNIQUE NOT NULL,
  password      VARCHAR(255)  NOT NULL,
  refresh_token TEXT,
  created_at    TIMESTAMPTZ   DEFAULT NOW()
);