CREATE TYPE estado_usuario AS ENUM ('activo', 'inactivo');
CREATE TYPE estado_membresia AS ENUM ('activa', 'inactiva', 'temporal');
CREATE TABLE personas (
id_persona SERIAL PRIMARY KEY,
nombre VARCHAR(100) NOT NULL,
apellido VARCHAR(100) NOT NULL,
documento VARCHAR(50) UNIQUE NOT NULL,
telefono VARCHAR(20),
email VARCHAR(100) UNIQUE,
direccion VARCHAR(255)
);
CREATE TABLE usuarios (
id_usuario SERIAL PRIMARY KEY,
id_persona INT UNIQUE NOT NULL REFERENCES personas(id_persona) ON DELETE
CASCADE,
usuario VARCHAR(50) UNIQUE NOT NULL,
contraseña VARCHAR(255) NOT NULL,
estado estado_usuario NOT NULL DEFAULT 'activo'
);
CREATE TABLE roles (
id_rol SERIAL PRIMARY KEY,
nombre_rol VARCHAR(50) UNIQUE NOT NULL
);
CREATE TABLE persona_rol (
id_persona INT REFERENCES personas(id_persona) ON DELETE CASCADE,
id_rol INT REFERENCES roles(id_rol) ON DELETE CASCADE,
PRIMARY KEY (id_persona, id_rol)
);
CREATE TABLE turnos (
id_turno SERIAL PRIMARY KEY,
hora_inicio TIME NOT NULL,
hora_fin TIME NOT NULL
);
CREATE TABLE registros_turnos (
id_registro_turno SERIAL PRIMARY KEY,
id_usuario INT REFERENCES usuarios(id_usuario) ON DELETE CASCADE,
id_turno INT REFERENCES turnos(id_turno) ON DELETE CASCADE,
fecha_turno DATE NOT NULL
);
CREATE TABLE tipos_vehiculos (
id_tipo_vehiculo SERIAL PRIMARY KEY,
nombre_tipo VARCHAR(50) UNIQUE NOT NULL
);
CREATE TABLE vehiculos (
id_vehiculo SERIAL PRIMARY KEY,
placa VARCHAR(20) UNIQUE NOT NULL,
id_tipo_vehiculo INT REFERENCES tipos_vehiculos(id_tipo_vehiculo) ON DELETE
RESTRICT,
color VARCHAR(30),
modelo VARCHAR(50),
marca VARCHAR(50),
propietario VARCHAR(100) NOT NULL
);
CREATE TABLE tipos_membresias (
id_tipo_membresia SERIAL PRIMARY KEY,
nombre_tipo VARCHAR(50) UNIQUE NOT NULL,
duracion_dias INT CHECK (duracion_dias > 0),
precio DECIMAL(10,2) CHECK (precio >= 0) NOT NULL
);
CREATE TABLE membresias (
id_membresia SERIAL PRIMARY KEY,
id_vehiculo INT REFERENCES vehiculos(id_vehiculo) ON DELETE CASCADE,
id_tipo_membresia INT REFERENCES tipos_membresias(id_tipo_membresia) ON DELETE
RESTRICT,
fecha_inicio DATE NOT NULL,
fecha_fin DATE NOT NULL,
estado estado_membresia NOT NULL DEFAULT 'activa'
);
CREATE TABLE parqueaderos (
id_parqueadero SERIAL PRIMARY KEY,
nombre VARCHAR(100) NOT NULL,
direccion VARCHAR(255) NOT NULL,
ciudad VARCHAR(100) UNIQUE NOT NULL
);
CREATE TABLE cupos (
id_cupo SERIAL PRIMARY KEY,
id_parqueadero INT REFERENCES parqueaderos(id_parqueadero) ON DELETE CASCADE,
numero_cupo VARCHAR(20) UNIQUE NOT NULL,
disponible BOOLEAN NOT NULL DEFAULT TRUE
);
CREATE TABLE localizacion (
id_localizacion SERIAL PRIMARY KEY,
id_cupo INT UNIQUE REFERENCES cupos(id_cupo) ON DELETE CASCADE,
coordenadas VARCHAR(100) NOT NULL
);
CREATE TABLE historial_parqueo (
id_historial SERIAL PRIMARY KEY,
id_vehiculo INT REFERENCES vehiculos(id_vehiculo) ON DELETE CASCADE,
id_cupo INT REFERENCES cupos(id_cupo) ON DELETE SET NULL,
id_usuario_acceso INT REFERENCES usuarios(id_usuario) ON DELETE SET NULL,
id_usuario_cobro INT REFERENCES usuarios(id_usuario) ON DELETE SET NULL,
hora_entrada TIMESTAMP NOT NULL,
hora_salida TIMESTAMP,
cobrado DECIMAL(10,2) CHECK (cobrado >= 0) NOT NULL
);
CREATE TABLE metodos_pago (
id_metodo_pago SERIAL PRIMARY KEY,
nombre_metodo VARCHAR(50) UNIQUE NOT NULL
);
CREATE TABLE pagos (
id_pago SERIAL PRIMARY KEY,
id_historial INT REFERENCES historial_parqueo(id_historial) ON DELETE SET NULL,
id_membresia INT REFERENCES membresias(id_membresia) ON DELETE SET NULL,
id_metodo_pago INT REFERENCES metodos_pago(id_metodo_pago) ON DELETE RESTRICT,
monto DECIMAL(10,2) CHECK (monto >= 0) NOT NULL,
fecha_pago TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE facturas (
id_factura SERIAL PRIMARY KEY,
id_pago INT UNIQUE REFERENCES pagos(id_pago) ON DELETE CASCADE,
numero_factura VARCHAR(50) UNIQUE NOT NULL,
fecha_emision TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
detalles TEXT
);