cesar
2a88b4a71b
- Backend migrado a estructura VPS (src/ subfolder) - Frontend con estructura Vite + React 19 + Tailwind - Configuración PostgreSQL con Pool - API service con interceptores JWT - Ambos servidores funcionando (backend:3001, frontend:5173)
420 lines
14 KiB
PL/PgSQL
420 lines
14 KiB
PL/PgSQL
-- ================================================
|
|
-- OFERTAWEB.CL - ESQUEMA DE BASE DE DATOS
|
|
-- Sistema E-Commerce Multi-Canal
|
|
-- PostgreSQL 12+
|
|
-- ================================================
|
|
|
|
-- ================================================
|
|
-- 1. MÓDULO: USUARIOS Y AUTENTICACIÓN
|
|
-- ================================================
|
|
|
|
CREATE TABLE users (
|
|
id SERIAL PRIMARY KEY,
|
|
email VARCHAR(255) UNIQUE NOT NULL,
|
|
password_hash VARCHAR(255) NOT NULL,
|
|
nombre VARCHAR(100) NOT NULL,
|
|
apellido VARCHAR(100),
|
|
telefono VARCHAR(20),
|
|
role VARCHAR(20) DEFAULT 'cliente' CHECK (role IN ('cliente', 'admin', 'repartidor')),
|
|
is_active BOOLEAN DEFAULT true,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE user_addresses (
|
|
id SERIAL PRIMARY KEY,
|
|
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
direccion TEXT NOT NULL,
|
|
comuna VARCHAR(100) NOT NULL,
|
|
ciudad VARCHAR(100) NOT NULL,
|
|
region VARCHAR(100) NOT NULL,
|
|
codigo_postal VARCHAR(20),
|
|
referencia TEXT,
|
|
is_default BOOLEAN DEFAULT false,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE INDEX idx_user_addresses_user_id ON user_addresses(user_id);
|
|
|
|
-- ================================================
|
|
-- 2. MÓDULO: CATÁLOGO DE PRODUCTOS
|
|
-- ================================================
|
|
|
|
CREATE TABLE categories (
|
|
id SERIAL PRIMARY KEY,
|
|
nombre VARCHAR(100) NOT NULL,
|
|
slug VARCHAR(100) UNIQUE NOT NULL,
|
|
descripcion TEXT,
|
|
parent_id INTEGER REFERENCES categories(id) ON DELETE SET NULL,
|
|
orden INTEGER DEFAULT 0,
|
|
is_active BOOLEAN DEFAULT true,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE products (
|
|
id SERIAL PRIMARY KEY,
|
|
sku VARCHAR(50) UNIQUE NOT NULL,
|
|
nombre VARCHAR(255) NOT NULL,
|
|
descripcion TEXT,
|
|
precio_base DECIMAL(10, 2) NOT NULL,
|
|
peso_gramos INTEGER,
|
|
alto_cm DECIMAL(6, 2),
|
|
ancho_cm DECIMAL(6, 2),
|
|
largo_cm DECIMAL(6, 2),
|
|
category_id INTEGER REFERENCES categories(id) ON DELETE SET NULL,
|
|
is_active BOOLEAN DEFAULT true,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE product_images (
|
|
id SERIAL PRIMARY KEY,
|
|
product_id INTEGER NOT NULL REFERENCES products(id) ON DELETE CASCADE,
|
|
url TEXT NOT NULL,
|
|
alt_text VARCHAR(255),
|
|
orden INTEGER DEFAULT 0,
|
|
is_primary BOOLEAN DEFAULT false,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE INDEX idx_product_images_product_id ON product_images(product_id);
|
|
CREATE INDEX idx_products_category_id ON products(category_id);
|
|
CREATE INDEX idx_products_sku ON products(sku);
|
|
|
|
-- ================================================
|
|
-- 3. MÓDULO: MULTI-CANAL
|
|
-- ================================================
|
|
|
|
CREATE TABLE channels (
|
|
id SERIAL PRIMARY KEY,
|
|
nombre VARCHAR(50) NOT NULL CHECK (nombre IN ('tienda', 'mercadolibre', 'instagram')),
|
|
slug VARCHAR(50) UNIQUE NOT NULL,
|
|
config JSONB DEFAULT '{}',
|
|
is_active BOOLEAN DEFAULT true,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Insertar canales por defecto
|
|
INSERT INTO channels (nombre, slug) VALUES
|
|
('tienda', 'tienda'),
|
|
('mercadolibre', 'mercadolibre'),
|
|
('instagram', 'instagram');
|
|
|
|
CREATE TABLE channel_products (
|
|
id SERIAL PRIMARY KEY,
|
|
product_id INTEGER NOT NULL REFERENCES products(id) ON DELETE CASCADE,
|
|
channel_id INTEGER NOT NULL REFERENCES channels(id) ON DELETE CASCADE,
|
|
external_id VARCHAR(100),
|
|
precio_ajustado DECIMAL(10, 2),
|
|
precio_factor DECIMAL(5, 2) DEFAULT 1.00,
|
|
estado VARCHAR(20) DEFAULT 'publicado' CHECK (estado IN ('publicado', 'pausado', 'agotado')),
|
|
last_sync_at TIMESTAMP,
|
|
sync_errors JSONB,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE(product_id, channel_id)
|
|
);
|
|
|
|
CREATE TABLE channel_sync_logs (
|
|
id SERIAL PRIMARY KEY,
|
|
channel_id INTEGER NOT NULL REFERENCES channels(id) ON DELETE CASCADE,
|
|
product_id INTEGER REFERENCES products(id) ON DELETE SET NULL,
|
|
tipo VARCHAR(20) CHECK (tipo IN ('stock', 'precio', 'publicacion')),
|
|
status VARCHAR(20) CHECK (status IN ('success', 'error')),
|
|
mensaje TEXT,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE INDEX idx_channel_products_product_id ON channel_products(product_id);
|
|
CREATE INDEX idx_channel_products_channel_id ON channel_products(channel_id);
|
|
CREATE INDEX idx_channel_sync_logs_created_at ON channel_sync_logs(created_at);
|
|
|
|
-- ================================================
|
|
-- 4. MÓDULO: INVENTARIO (STOCK UNIFICADO)
|
|
-- ================================================
|
|
|
|
CREATE TABLE inventory (
|
|
id SERIAL PRIMARY KEY,
|
|
product_id INTEGER UNIQUE NOT NULL REFERENCES products(id) ON DELETE CASCADE,
|
|
stock_actual INTEGER DEFAULT 0 CHECK (stock_actual >= 0),
|
|
stock_minimo INTEGER DEFAULT 5,
|
|
stock_reservado INTEGER DEFAULT 0 CHECK (stock_reservado >= 0),
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE inventory_movements (
|
|
id SERIAL PRIMARY KEY,
|
|
product_id INTEGER NOT NULL REFERENCES products(id) ON DELETE CASCADE,
|
|
tipo VARCHAR(20) CHECK (tipo IN ('entrada', 'salida', 'ajuste', 'reserva', 'liberacion')),
|
|
cantidad INTEGER NOT NULL,
|
|
cantidad_anterior INTEGER NOT NULL,
|
|
cantidad_nueva INTEGER NOT NULL,
|
|
referencia VARCHAR(100),
|
|
notas TEXT,
|
|
created_by INTEGER REFERENCES users(id) ON DELETE SET NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE INDEX idx_inventory_product_id ON inventory(product_id);
|
|
CREATE INDEX idx_inventory_movements_product_id ON inventory_movements(product_id);
|
|
CREATE INDEX idx_inventory_movements_created_at ON inventory_movements(created_at);
|
|
|
|
-- ================================================
|
|
-- 5. MÓDULO: CARRITO DE COMPRAS
|
|
-- ================================================
|
|
|
|
CREATE TABLE carts (
|
|
id SERIAL PRIMARY KEY,
|
|
user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
|
|
session_id VARCHAR(100),
|
|
expires_at TIMESTAMP NOT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE cart_items (
|
|
id SERIAL PRIMARY KEY,
|
|
cart_id INTEGER NOT NULL REFERENCES carts(id) ON DELETE CASCADE,
|
|
product_id INTEGER NOT NULL REFERENCES products(id) ON DELETE CASCADE,
|
|
cantidad INTEGER NOT NULL CHECK (cantidad > 0),
|
|
precio_unitario DECIMAL(10, 2) NOT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE INDEX idx_carts_user_id ON carts(user_id);
|
|
CREATE INDEX idx_carts_session_id ON carts(session_id);
|
|
CREATE INDEX idx_cart_items_cart_id ON cart_items(cart_id);
|
|
|
|
-- ================================================
|
|
-- 6. MÓDULO: ÓRDENES DE COMPRA
|
|
-- ================================================
|
|
|
|
CREATE TABLE orders (
|
|
id SERIAL PRIMARY KEY,
|
|
order_number VARCHAR(50) UNIQUE NOT NULL,
|
|
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
|
|
channel_id INTEGER NOT NULL REFERENCES channels(id) ON DELETE RESTRICT,
|
|
estado VARCHAR(30) DEFAULT 'pendiente_pago'
|
|
CHECK (estado IN ('pendiente_pago', 'pagada', 'preparando', 'enviada', 'entregada', 'cancelada')),
|
|
subtotal DECIMAL(10, 2) NOT NULL,
|
|
descuento DECIMAL(10, 2) DEFAULT 0,
|
|
costo_envio DECIMAL(10, 2) DEFAULT 0,
|
|
total DECIMAL(10, 2) NOT NULL,
|
|
notas_cliente TEXT,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE order_items (
|
|
id SERIAL PRIMARY KEY,
|
|
order_id INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
|
|
product_id INTEGER NOT NULL REFERENCES products(id) ON DELETE RESTRICT,
|
|
cantidad INTEGER NOT NULL CHECK (cantidad > 0),
|
|
precio_unitario DECIMAL(10, 2) NOT NULL,
|
|
subtotal DECIMAL(10, 2) NOT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE INDEX idx_orders_user_id ON orders(user_id);
|
|
CREATE INDEX idx_orders_order_number ON orders(order_number);
|
|
CREATE INDEX idx_orders_estado ON orders(estado);
|
|
CREATE INDEX idx_order_items_order_id ON order_items(order_id);
|
|
|
|
-- ================================================
|
|
-- 7. MÓDULO: PAGOS
|
|
-- ================================================
|
|
|
|
CREATE TABLE payments (
|
|
id SERIAL PRIMARY KEY,
|
|
order_id INTEGER UNIQUE NOT NULL REFERENCES orders(id) ON DELETE RESTRICT,
|
|
metodo VARCHAR(30) CHECK (metodo IN ('webpay', 'transferencia', 'mercadopago', 'efectivo')),
|
|
estado VARCHAR(20) DEFAULT 'pendiente'
|
|
CHECK (estado IN ('pendiente', 'aprobado', 'rechazado', 'reembolsado')),
|
|
monto DECIMAL(10, 2) NOT NULL,
|
|
transaction_id VARCHAR(100),
|
|
response_data JSONB,
|
|
paid_at TIMESTAMP,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE payment_transactions (
|
|
id SERIAL PRIMARY KEY,
|
|
payment_id INTEGER NOT NULL REFERENCES payments(id) ON DELETE CASCADE,
|
|
tipo VARCHAR(20) CHECK (tipo IN ('authorization', 'capture', 'refund')),
|
|
status VARCHAR(20),
|
|
request_data JSONB,
|
|
response_data JSONB,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE INDEX idx_payments_order_id ON payments(order_id);
|
|
CREATE INDEX idx_payment_transactions_payment_id ON payment_transactions(payment_id);
|
|
|
|
-- ================================================
|
|
-- 8. MÓDULO: DESPACHO Y ENVÍOS
|
|
-- ================================================
|
|
|
|
CREATE TABLE shipments (
|
|
id SERIAL PRIMARY KEY,
|
|
order_id INTEGER UNIQUE NOT NULL REFERENCES orders(id) ON DELETE RESTRICT,
|
|
tracking_number VARCHAR(100),
|
|
courier VARCHAR(30) CHECK (courier IN ('bluexpress', 'chilexpress', 'starken', 'retiro')),
|
|
estado VARCHAR(30) DEFAULT 'pendiente'
|
|
CHECK (estado IN ('pendiente', 'preparando', 'en_transito', 'en_reparto', 'entregado', 'fallido')),
|
|
direccion_id INTEGER REFERENCES user_addresses(id) ON DELETE RESTRICT,
|
|
costo_calculado DECIMAL(10, 2),
|
|
peso_total INTEGER,
|
|
dimensiones JSONB,
|
|
fecha_estimada_entrega DATE,
|
|
fecha_entrega_real TIMESTAMP,
|
|
repartidor_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
|
|
notas_despacho TEXT,
|
|
comprobante_url TEXT,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE shipment_tracking (
|
|
id SERIAL PRIMARY KEY,
|
|
shipment_id INTEGER NOT NULL REFERENCES shipments(id) ON DELETE CASCADE,
|
|
estado VARCHAR(30) NOT NULL,
|
|
ubicacion VARCHAR(255),
|
|
descripcion TEXT,
|
|
created_by INTEGER REFERENCES users(id) ON DELETE SET NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE shipping_quotes (
|
|
id SERIAL PRIMARY KEY,
|
|
order_id INTEGER REFERENCES orders(id) ON DELETE CASCADE,
|
|
courier VARCHAR(30) NOT NULL,
|
|
servicio VARCHAR(50),
|
|
origen_comuna VARCHAR(100),
|
|
destino_comuna VARCHAR(100),
|
|
peso_gramos INTEGER NOT NULL,
|
|
costo DECIMAL(10, 2) NOT NULL,
|
|
dias_estimados INTEGER,
|
|
response_data JSONB,
|
|
expires_at TIMESTAMP NOT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE INDEX idx_shipments_order_id ON shipments(order_id);
|
|
CREATE INDEX idx_shipments_estado ON shipments(estado);
|
|
CREATE INDEX idx_shipment_tracking_shipment_id ON shipment_tracking(shipment_id);
|
|
|
|
-- ================================================
|
|
-- 9. MÓDULO: CONFIGURACIÓN DEL SISTEMA
|
|
-- ================================================
|
|
|
|
CREATE TABLE settings (
|
|
id SERIAL PRIMARY KEY,
|
|
key VARCHAR(100) UNIQUE NOT NULL,
|
|
value TEXT,
|
|
tipo VARCHAR(20) DEFAULT 'string' CHECK (tipo IN ('string', 'number', 'boolean', 'json')),
|
|
descripcion TEXT,
|
|
is_secret BOOLEAN DEFAULT false,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- ================================================
|
|
-- FUNCIONES Y TRIGGERS
|
|
-- ================================================
|
|
|
|
-- Trigger para actualizar updated_at automáticamente
|
|
CREATE OR REPLACE FUNCTION update_updated_at_column()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
NEW.updated_at = CURRENT_TIMESTAMP;
|
|
RETURN NEW;
|
|
END;
|
|
$$ language 'plpgsql';
|
|
|
|
-- Aplicar trigger a las tablas relevantes
|
|
CREATE TRIGGER update_users_updated_at BEFORE UPDATE ON users
|
|
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
CREATE TRIGGER update_products_updated_at BEFORE UPDATE ON products
|
|
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
CREATE TRIGGER update_orders_updated_at BEFORE UPDATE ON orders
|
|
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
CREATE TRIGGER update_inventory_updated_at BEFORE UPDATE ON inventory
|
|
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
-- Función para generar número de orden
|
|
CREATE OR REPLACE FUNCTION generate_order_number()
|
|
RETURNS TEXT AS $$
|
|
DECLARE
|
|
new_number TEXT;
|
|
date_part TEXT;
|
|
sequence_part TEXT;
|
|
count_today INTEGER;
|
|
BEGIN
|
|
date_part := TO_CHAR(CURRENT_DATE, 'YYYYMMDD');
|
|
|
|
SELECT COUNT(*) INTO count_today
|
|
FROM orders
|
|
WHERE order_number LIKE 'ORD-' || date_part || '-%';
|
|
|
|
sequence_part := LPAD((count_today + 1)::TEXT, 4, '0');
|
|
new_number := 'ORD-' || date_part || '-' || sequence_part;
|
|
|
|
RETURN new_number;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- ================================================
|
|
-- VISTAS ÚTILES
|
|
-- ================================================
|
|
|
|
-- Vista de productos con stock disponible
|
|
CREATE VIEW v_products_with_stock AS
|
|
SELECT
|
|
p.id,
|
|
p.sku,
|
|
p.nombre,
|
|
p.precio_base,
|
|
p.is_active,
|
|
i.stock_actual,
|
|
i.stock_reservado,
|
|
(i.stock_actual - i.stock_reservado) as stock_disponible,
|
|
c.nombre as categoria
|
|
FROM products p
|
|
LEFT JOIN inventory i ON p.id = i.product_id
|
|
LEFT JOIN categories c ON p.category_id = c.id;
|
|
|
|
-- Vista de órdenes con información completa
|
|
CREATE VIEW v_orders_complete AS
|
|
SELECT
|
|
o.id,
|
|
o.order_number,
|
|
o.estado,
|
|
o.total,
|
|
o.created_at,
|
|
u.nombre || ' ' || COALESCE(u.apellido, '') as cliente,
|
|
u.email,
|
|
ch.nombre as canal,
|
|
p.estado as estado_pago,
|
|
s.tracking_number,
|
|
s.estado as estado_envio
|
|
FROM orders o
|
|
JOIN users u ON o.user_id = u.id
|
|
JOIN channels ch ON o.channel_id = ch.id
|
|
LEFT JOIN payments p ON o.id = p.order_id
|
|
LEFT JOIN shipments s ON o.id = s.order_id;
|
|
|
|
-- ================================================
|
|
-- DATOS DE PRUEBA (OPCIONAL)
|
|
-- ================================================
|
|
|
|
-- Usuario admin por defecto (password: admin123)
|
|
INSERT INTO users (email, password_hash, nombre, apellido, role) VALUES
|
|
('admin@ofertaweb.cl', '$2a$10$X6vE7F.QqP8F5F5F5F5F5eK9Z1Z1Z1Z1Z1Z1Z1Z1Z1Z1Z1Z1Z1Z1Z', 'Admin', 'Sistema', 'admin');
|
|
|
|
-- Categorías de ejemplo
|
|
INSERT INTO categories (nombre, slug, descripcion, orden) VALUES
|
|
('Electrónica', 'electronica', 'Productos electrónicos y tecnología', 1),
|
|
('Ropa', 'ropa', 'Vestimenta y accesorios', 2),
|
|
('Hogar', 'hogar', 'Artículos para el hogar', 3);
|
|
|
|
COMMIT;
|