724 lines
28 KiB
SQL
724 lines
28 KiB
SQL
-- ============================================================================
|
|
-- TABLAS RETAIL/POS - ERP Retail
|
|
-- ============================================================================
|
|
-- Módulos: RT-001 (POS), RT-002 (Inventario), RT-003 (Productos), RT-004 (Clientes)
|
|
-- Versión: 1.0.0
|
|
-- Fecha: 2025-12-09
|
|
-- ============================================================================
|
|
-- PREREQUISITOS:
|
|
-- 1. ERP-Core instalado (auth, core, inventory, sales, financial)
|
|
-- 2. Schema retail creado
|
|
-- ============================================================================
|
|
|
|
-- ============================================================================
|
|
-- TYPES (ENUMs)
|
|
-- ============================================================================
|
|
|
|
DO $$ BEGIN
|
|
CREATE TYPE retail.pos_session_status AS ENUM (
|
|
'opening', 'open', 'closing', 'closed'
|
|
);
|
|
EXCEPTION WHEN duplicate_object THEN NULL; END $$;
|
|
|
|
DO $$ BEGIN
|
|
CREATE TYPE retail.pos_order_status AS ENUM (
|
|
'draft', 'paid', 'done', 'cancelled', 'refunded'
|
|
);
|
|
EXCEPTION WHEN duplicate_object THEN NULL; END $$;
|
|
|
|
DO $$ BEGIN
|
|
CREATE TYPE retail.payment_method AS ENUM (
|
|
'cash', 'card', 'transfer', 'credit', 'mixed'
|
|
);
|
|
EXCEPTION WHEN duplicate_object THEN NULL; END $$;
|
|
|
|
DO $$ BEGIN
|
|
CREATE TYPE retail.cash_movement_type AS ENUM (
|
|
'in', 'out'
|
|
);
|
|
EXCEPTION WHEN duplicate_object THEN NULL; END $$;
|
|
|
|
DO $$ BEGIN
|
|
CREATE TYPE retail.transfer_status AS ENUM (
|
|
'draft', 'pending', 'in_transit', 'received', 'cancelled'
|
|
);
|
|
EXCEPTION WHEN duplicate_object THEN NULL; END $$;
|
|
|
|
DO $$ BEGIN
|
|
CREATE TYPE retail.promotion_type AS ENUM (
|
|
'percentage', 'fixed_amount', 'buy_x_get_y', 'bundle'
|
|
);
|
|
EXCEPTION WHEN duplicate_object THEN NULL; END $$;
|
|
|
|
-- ============================================================================
|
|
-- SUCURSALES Y CONFIGURACIÓN
|
|
-- ============================================================================
|
|
|
|
-- Tabla: branches (Sucursales)
|
|
CREATE TABLE IF NOT EXISTS retail.branches (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
|
|
company_id UUID REFERENCES auth.companies(id),
|
|
|
|
-- Identificación
|
|
code VARCHAR(20) NOT NULL,
|
|
name VARCHAR(100) NOT NULL,
|
|
|
|
-- Ubicación
|
|
address VARCHAR(255),
|
|
city VARCHAR(100),
|
|
state VARCHAR(100),
|
|
zip_code VARCHAR(10),
|
|
country VARCHAR(100) DEFAULT 'México',
|
|
latitude DECIMAL(10,8),
|
|
longitude DECIMAL(11,8),
|
|
|
|
-- Contacto
|
|
phone VARCHAR(20),
|
|
email VARCHAR(255),
|
|
manager_id UUID REFERENCES auth.users(id),
|
|
|
|
-- Configuración
|
|
warehouse_id UUID, -- FK a inventory.warehouses (ERP Core)
|
|
default_pricelist_id UUID,
|
|
timezone VARCHAR(50) DEFAULT 'America/Mexico_City',
|
|
|
|
-- Control
|
|
is_active BOOLEAN NOT NULL DEFAULT TRUE,
|
|
opening_date DATE,
|
|
|
|
-- Auditoría
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
created_by UUID REFERENCES auth.users(id),
|
|
updated_at TIMESTAMPTZ,
|
|
updated_by UUID REFERENCES auth.users(id),
|
|
deleted_at TIMESTAMPTZ,
|
|
deleted_by UUID REFERENCES auth.users(id),
|
|
|
|
CONSTRAINT uq_branches_code UNIQUE (tenant_id, code)
|
|
);
|
|
|
|
-- Tabla: cash_registers (Cajas registradoras)
|
|
CREATE TABLE IF NOT EXISTS retail.cash_registers (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
|
|
branch_id UUID NOT NULL REFERENCES retail.branches(id),
|
|
|
|
-- Identificación
|
|
code VARCHAR(20) NOT NULL,
|
|
name VARCHAR(100) NOT NULL,
|
|
|
|
-- Configuración
|
|
is_active BOOLEAN NOT NULL DEFAULT TRUE,
|
|
default_payment_method retail.payment_method DEFAULT 'cash',
|
|
|
|
-- Auditoría
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
created_by UUID REFERENCES auth.users(id),
|
|
updated_at TIMESTAMPTZ,
|
|
updated_by UUID REFERENCES auth.users(id),
|
|
|
|
CONSTRAINT uq_cash_registers_code UNIQUE (tenant_id, branch_id, code)
|
|
);
|
|
|
|
-- ============================================================================
|
|
-- PUNTO DE VENTA (RT-001)
|
|
-- ============================================================================
|
|
|
|
-- Tabla: pos_sessions (Sesiones de POS)
|
|
CREATE TABLE IF NOT EXISTS retail.pos_sessions (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
|
|
branch_id UUID NOT NULL REFERENCES retail.branches(id),
|
|
cash_register_id UUID NOT NULL REFERENCES retail.cash_registers(id),
|
|
|
|
-- Usuario
|
|
user_id UUID NOT NULL REFERENCES auth.users(id),
|
|
|
|
-- Estado
|
|
status retail.pos_session_status NOT NULL DEFAULT 'opening',
|
|
|
|
-- Apertura
|
|
opening_date TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
opening_balance DECIMAL(14,2) NOT NULL DEFAULT 0,
|
|
|
|
-- Cierre
|
|
closing_date TIMESTAMPTZ,
|
|
closing_balance DECIMAL(14,2),
|
|
closing_notes TEXT,
|
|
|
|
-- Totales calculados
|
|
total_sales DECIMAL(14,2) DEFAULT 0,
|
|
total_refunds DECIMAL(14,2) DEFAULT 0,
|
|
total_cash_in DECIMAL(14,2) DEFAULT 0,
|
|
total_cash_out DECIMAL(14,2) DEFAULT 0,
|
|
total_card DECIMAL(14,2) DEFAULT 0,
|
|
total_transfer DECIMAL(14,2) DEFAULT 0,
|
|
|
|
-- Diferencia
|
|
expected_balance DECIMAL(14,2),
|
|
difference DECIMAL(14,2),
|
|
|
|
-- Auditoría
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
created_by UUID REFERENCES auth.users(id),
|
|
updated_at TIMESTAMPTZ,
|
|
updated_by UUID REFERENCES auth.users(id)
|
|
);
|
|
|
|
-- Tabla: pos_orders (Órdenes/Ventas de POS)
|
|
CREATE TABLE IF NOT EXISTS retail.pos_orders (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
|
|
session_id UUID NOT NULL REFERENCES retail.pos_sessions(id),
|
|
branch_id UUID NOT NULL REFERENCES retail.branches(id),
|
|
|
|
-- Número de ticket
|
|
order_number VARCHAR(30) NOT NULL,
|
|
order_date TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
|
|
-- Cliente (opcional)
|
|
customer_id UUID, -- FK a core.partners (ERP Core)
|
|
customer_name VARCHAR(200),
|
|
|
|
-- Estado
|
|
status retail.pos_order_status NOT NULL DEFAULT 'draft',
|
|
|
|
-- Totales
|
|
subtotal DECIMAL(14,2) NOT NULL DEFAULT 0,
|
|
discount_amount DECIMAL(14,2) DEFAULT 0,
|
|
tax_amount DECIMAL(14,2) DEFAULT 0,
|
|
total DECIMAL(14,2) NOT NULL DEFAULT 0,
|
|
|
|
-- Pago
|
|
payment_method retail.payment_method,
|
|
amount_paid DECIMAL(14,2) DEFAULT 0,
|
|
change_amount DECIMAL(14,2) DEFAULT 0,
|
|
|
|
-- Facturación
|
|
requires_invoice BOOLEAN DEFAULT FALSE,
|
|
invoice_id UUID, -- FK a financial.invoices (ERP Core)
|
|
|
|
-- Notas
|
|
notes TEXT,
|
|
|
|
-- Auditoría
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
created_by UUID REFERENCES auth.users(id),
|
|
updated_at TIMESTAMPTZ,
|
|
updated_by UUID REFERENCES auth.users(id),
|
|
|
|
CONSTRAINT uq_pos_orders_number UNIQUE (tenant_id, order_number)
|
|
);
|
|
|
|
-- Tabla: pos_order_lines (Líneas de venta)
|
|
CREATE TABLE IF NOT EXISTS retail.pos_order_lines (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
|
|
order_id UUID NOT NULL REFERENCES retail.pos_orders(id) ON DELETE CASCADE,
|
|
|
|
-- Producto
|
|
product_id UUID NOT NULL, -- FK a inventory.products (ERP Core)
|
|
product_name VARCHAR(255) NOT NULL,
|
|
barcode VARCHAR(50),
|
|
|
|
-- Cantidades
|
|
quantity DECIMAL(12,4) NOT NULL,
|
|
unit_price DECIMAL(12,4) NOT NULL,
|
|
|
|
-- Descuentos
|
|
discount_percent DECIMAL(5,2) DEFAULT 0,
|
|
discount_amount DECIMAL(12,2) DEFAULT 0,
|
|
|
|
-- Totales
|
|
subtotal DECIMAL(14,2) GENERATED ALWAYS AS (quantity * unit_price) STORED,
|
|
tax_amount DECIMAL(12,2) DEFAULT 0,
|
|
total DECIMAL(14,2) NOT NULL,
|
|
|
|
-- Orden
|
|
sequence INTEGER DEFAULT 1,
|
|
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
created_by UUID REFERENCES auth.users(id)
|
|
);
|
|
|
|
-- Tabla: pos_payments (Pagos de orden - para pagos mixtos)
|
|
CREATE TABLE IF NOT EXISTS retail.pos_payments (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
|
|
order_id UUID NOT NULL REFERENCES retail.pos_orders(id) ON DELETE CASCADE,
|
|
|
|
payment_method retail.payment_method NOT NULL,
|
|
amount DECIMAL(14,2) NOT NULL,
|
|
|
|
-- Referencia (para tarjeta/transferencia)
|
|
reference VARCHAR(100),
|
|
card_last_four VARCHAR(4),
|
|
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
created_by UUID REFERENCES auth.users(id)
|
|
);
|
|
|
|
-- Tabla: cash_movements (Movimientos de efectivo)
|
|
CREATE TABLE IF NOT EXISTS retail.cash_movements (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
|
|
session_id UUID NOT NULL REFERENCES retail.pos_sessions(id),
|
|
|
|
-- Tipo y monto
|
|
movement_type retail.cash_movement_type NOT NULL,
|
|
amount DECIMAL(14,2) NOT NULL,
|
|
|
|
-- Razón
|
|
reason VARCHAR(255) NOT NULL,
|
|
notes TEXT,
|
|
|
|
-- Autorización
|
|
authorized_by UUID REFERENCES auth.users(id),
|
|
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
created_by UUID REFERENCES auth.users(id)
|
|
);
|
|
|
|
-- ============================================================================
|
|
-- INVENTARIO MULTI-SUCURSAL (RT-002)
|
|
-- ============================================================================
|
|
|
|
-- Tabla: branch_stock (Stock por sucursal)
|
|
CREATE TABLE IF NOT EXISTS retail.branch_stock (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
|
|
branch_id UUID NOT NULL REFERENCES retail.branches(id),
|
|
product_id UUID NOT NULL, -- FK a inventory.products (ERP Core)
|
|
|
|
-- Cantidades
|
|
quantity_on_hand DECIMAL(12,4) NOT NULL DEFAULT 0,
|
|
quantity_reserved DECIMAL(12,4) DEFAULT 0,
|
|
quantity_available DECIMAL(12,4) GENERATED ALWAYS AS (quantity_on_hand - COALESCE(quantity_reserved, 0)) STORED,
|
|
|
|
-- Límites
|
|
reorder_point DECIMAL(12,4),
|
|
max_stock DECIMAL(12,4),
|
|
|
|
-- Control
|
|
last_count_date DATE,
|
|
last_count_qty DECIMAL(12,4),
|
|
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ,
|
|
|
|
CONSTRAINT uq_branch_stock UNIQUE (branch_id, product_id)
|
|
);
|
|
|
|
-- Tabla: stock_transfers (Transferencias entre sucursales)
|
|
CREATE TABLE IF NOT EXISTS retail.stock_transfers (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
|
|
|
|
-- Número
|
|
transfer_number VARCHAR(30) NOT NULL,
|
|
|
|
-- Origen y destino
|
|
source_branch_id UUID NOT NULL REFERENCES retail.branches(id),
|
|
destination_branch_id UUID NOT NULL REFERENCES retail.branches(id),
|
|
|
|
-- Estado
|
|
status retail.transfer_status NOT NULL DEFAULT 'draft',
|
|
|
|
-- Fechas
|
|
request_date TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
ship_date TIMESTAMPTZ,
|
|
receive_date TIMESTAMPTZ,
|
|
|
|
-- Responsables
|
|
requested_by UUID NOT NULL REFERENCES auth.users(id),
|
|
shipped_by UUID REFERENCES auth.users(id),
|
|
received_by UUID REFERENCES auth.users(id),
|
|
|
|
-- Notas
|
|
notes TEXT,
|
|
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
created_by UUID REFERENCES auth.users(id),
|
|
updated_at TIMESTAMPTZ,
|
|
updated_by UUID REFERENCES auth.users(id),
|
|
|
|
CONSTRAINT uq_stock_transfers_number UNIQUE (tenant_id, transfer_number),
|
|
CONSTRAINT chk_different_branches CHECK (source_branch_id != destination_branch_id)
|
|
);
|
|
|
|
-- Tabla: stock_transfer_lines (Líneas de transferencia)
|
|
CREATE TABLE IF NOT EXISTS retail.stock_transfer_lines (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
|
|
transfer_id UUID NOT NULL REFERENCES retail.stock_transfers(id) ON DELETE CASCADE,
|
|
|
|
product_id UUID NOT NULL, -- FK a inventory.products (ERP Core)
|
|
quantity_requested DECIMAL(12,4) NOT NULL,
|
|
quantity_shipped DECIMAL(12,4),
|
|
quantity_received DECIMAL(12,4),
|
|
|
|
notes TEXT,
|
|
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
created_by UUID REFERENCES auth.users(id)
|
|
);
|
|
|
|
-- ============================================================================
|
|
-- PRODUCTOS RETAIL (RT-003)
|
|
-- ============================================================================
|
|
|
|
-- Tabla: product_barcodes (Códigos de barras múltiples)
|
|
CREATE TABLE IF NOT EXISTS retail.product_barcodes (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
|
|
product_id UUID NOT NULL, -- FK a inventory.products (ERP Core)
|
|
|
|
barcode VARCHAR(50) NOT NULL,
|
|
barcode_type VARCHAR(20) DEFAULT 'EAN13', -- EAN13, EAN8, UPC, CODE128, etc.
|
|
is_primary BOOLEAN DEFAULT FALSE,
|
|
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
created_by UUID REFERENCES auth.users(id),
|
|
|
|
CONSTRAINT uq_product_barcodes UNIQUE (tenant_id, barcode)
|
|
);
|
|
|
|
-- Tabla: promotions (Promociones)
|
|
CREATE TABLE IF NOT EXISTS retail.promotions (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
|
|
|
|
code VARCHAR(30) NOT NULL,
|
|
name VARCHAR(100) NOT NULL,
|
|
description TEXT,
|
|
|
|
-- Tipo de promoción
|
|
promotion_type retail.promotion_type NOT NULL,
|
|
discount_value DECIMAL(10,2), -- Porcentaje o monto fijo
|
|
|
|
-- Vigencia
|
|
start_date TIMESTAMPTZ NOT NULL,
|
|
end_date TIMESTAMPTZ NOT NULL,
|
|
|
|
-- Aplicación
|
|
applies_to_all BOOLEAN DEFAULT FALSE,
|
|
min_quantity DECIMAL(12,4),
|
|
min_amount DECIMAL(14,2),
|
|
|
|
-- Sucursales (NULL = todas)
|
|
branch_ids UUID[],
|
|
|
|
-- Control
|
|
is_active BOOLEAN NOT NULL DEFAULT TRUE,
|
|
max_uses INTEGER,
|
|
current_uses INTEGER DEFAULT 0,
|
|
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
created_by UUID REFERENCES auth.users(id),
|
|
updated_at TIMESTAMPTZ,
|
|
updated_by UUID REFERENCES auth.users(id),
|
|
|
|
CONSTRAINT uq_promotions_code UNIQUE (tenant_id, code),
|
|
CONSTRAINT chk_promotion_dates CHECK (end_date > start_date)
|
|
);
|
|
|
|
-- Tabla: promotion_products (Productos en promoción)
|
|
CREATE TABLE IF NOT EXISTS retail.promotion_products (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
|
|
promotion_id UUID NOT NULL REFERENCES retail.promotions(id) ON DELETE CASCADE,
|
|
product_id UUID NOT NULL, -- FK a inventory.products (ERP Core)
|
|
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
-- ============================================================================
|
|
-- CLIENTES Y FIDELIZACIÓN (RT-004)
|
|
-- ============================================================================
|
|
|
|
-- Tabla: loyalty_programs (Programas de fidelización)
|
|
CREATE TABLE IF NOT EXISTS retail.loyalty_programs (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
|
|
|
|
code VARCHAR(20) NOT NULL,
|
|
name VARCHAR(100) NOT NULL,
|
|
description TEXT,
|
|
|
|
-- Configuración de puntos
|
|
points_per_currency DECIMAL(10,4) DEFAULT 1, -- Puntos por peso gastado
|
|
currency_per_point DECIMAL(10,4) DEFAULT 0.01, -- Valor del punto en pesos
|
|
min_points_redeem INTEGER DEFAULT 100,
|
|
|
|
-- Vigencia
|
|
points_expiry_days INTEGER, -- NULL = no expiran
|
|
|
|
is_active BOOLEAN NOT NULL DEFAULT TRUE,
|
|
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
created_by UUID REFERENCES auth.users(id),
|
|
updated_at TIMESTAMPTZ,
|
|
updated_by UUID REFERENCES auth.users(id),
|
|
|
|
CONSTRAINT uq_loyalty_programs_code UNIQUE (tenant_id, code)
|
|
);
|
|
|
|
-- Tabla: loyalty_cards (Tarjetas de fidelización)
|
|
CREATE TABLE IF NOT EXISTS retail.loyalty_cards (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
|
|
program_id UUID NOT NULL REFERENCES retail.loyalty_programs(id),
|
|
customer_id UUID NOT NULL, -- FK a core.partners (ERP Core)
|
|
|
|
card_number VARCHAR(30) NOT NULL,
|
|
issue_date DATE NOT NULL DEFAULT CURRENT_DATE,
|
|
|
|
-- Balance
|
|
points_balance INTEGER NOT NULL DEFAULT 0,
|
|
points_earned INTEGER NOT NULL DEFAULT 0,
|
|
points_redeemed INTEGER NOT NULL DEFAULT 0,
|
|
points_expired INTEGER NOT NULL DEFAULT 0,
|
|
|
|
is_active BOOLEAN NOT NULL DEFAULT TRUE,
|
|
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
created_by UUID REFERENCES auth.users(id),
|
|
updated_at TIMESTAMPTZ,
|
|
updated_by UUID REFERENCES auth.users(id),
|
|
|
|
CONSTRAINT uq_loyalty_cards_number UNIQUE (tenant_id, card_number)
|
|
);
|
|
|
|
-- Tabla: loyalty_transactions (Transacciones de puntos)
|
|
CREATE TABLE IF NOT EXISTS retail.loyalty_transactions (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
|
|
card_id UUID NOT NULL REFERENCES retail.loyalty_cards(id),
|
|
|
|
-- Tipo
|
|
transaction_type VARCHAR(20) NOT NULL, -- earn, redeem, expire, adjust
|
|
points INTEGER NOT NULL,
|
|
|
|
-- Referencia
|
|
order_id UUID REFERENCES retail.pos_orders(id),
|
|
description TEXT,
|
|
|
|
-- Balance después de la transacción
|
|
balance_after INTEGER NOT NULL,
|
|
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
created_by UUID REFERENCES auth.users(id)
|
|
);
|
|
|
|
-- ============================================================================
|
|
-- ÍNDICES
|
|
-- ============================================================================
|
|
|
|
-- Branches
|
|
CREATE INDEX IF NOT EXISTS idx_branches_tenant ON retail.branches(tenant_id);
|
|
CREATE INDEX IF NOT EXISTS idx_branches_company ON retail.branches(company_id);
|
|
|
|
-- Cash registers
|
|
CREATE INDEX IF NOT EXISTS idx_cash_registers_tenant ON retail.cash_registers(tenant_id);
|
|
CREATE INDEX IF NOT EXISTS idx_cash_registers_branch ON retail.cash_registers(branch_id);
|
|
|
|
-- POS sessions
|
|
CREATE INDEX IF NOT EXISTS idx_pos_sessions_tenant ON retail.pos_sessions(tenant_id);
|
|
CREATE INDEX IF NOT EXISTS idx_pos_sessions_branch ON retail.pos_sessions(branch_id);
|
|
CREATE INDEX IF NOT EXISTS idx_pos_sessions_user ON retail.pos_sessions(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_pos_sessions_status ON retail.pos_sessions(status);
|
|
CREATE INDEX IF NOT EXISTS idx_pos_sessions_date ON retail.pos_sessions(opening_date);
|
|
|
|
-- POS orders
|
|
CREATE INDEX IF NOT EXISTS idx_pos_orders_tenant ON retail.pos_orders(tenant_id);
|
|
CREATE INDEX IF NOT EXISTS idx_pos_orders_session ON retail.pos_orders(session_id);
|
|
CREATE INDEX IF NOT EXISTS idx_pos_orders_branch ON retail.pos_orders(branch_id);
|
|
CREATE INDEX IF NOT EXISTS idx_pos_orders_customer ON retail.pos_orders(customer_id);
|
|
CREATE INDEX IF NOT EXISTS idx_pos_orders_date ON retail.pos_orders(order_date);
|
|
CREATE INDEX IF NOT EXISTS idx_pos_orders_status ON retail.pos_orders(status);
|
|
|
|
-- POS order lines
|
|
CREATE INDEX IF NOT EXISTS idx_pos_order_lines_tenant ON retail.pos_order_lines(tenant_id);
|
|
CREATE INDEX IF NOT EXISTS idx_pos_order_lines_order ON retail.pos_order_lines(order_id);
|
|
CREATE INDEX IF NOT EXISTS idx_pos_order_lines_product ON retail.pos_order_lines(product_id);
|
|
|
|
-- POS payments
|
|
CREATE INDEX IF NOT EXISTS idx_pos_payments_tenant ON retail.pos_payments(tenant_id);
|
|
CREATE INDEX IF NOT EXISTS idx_pos_payments_order ON retail.pos_payments(order_id);
|
|
|
|
-- Cash movements
|
|
CREATE INDEX IF NOT EXISTS idx_cash_movements_tenant ON retail.cash_movements(tenant_id);
|
|
CREATE INDEX IF NOT EXISTS idx_cash_movements_session ON retail.cash_movements(session_id);
|
|
|
|
-- Branch stock
|
|
CREATE INDEX IF NOT EXISTS idx_branch_stock_tenant ON retail.branch_stock(tenant_id);
|
|
CREATE INDEX IF NOT EXISTS idx_branch_stock_branch ON retail.branch_stock(branch_id);
|
|
CREATE INDEX IF NOT EXISTS idx_branch_stock_product ON retail.branch_stock(product_id);
|
|
|
|
-- Stock transfers
|
|
CREATE INDEX IF NOT EXISTS idx_stock_transfers_tenant ON retail.stock_transfers(tenant_id);
|
|
CREATE INDEX IF NOT EXISTS idx_stock_transfers_source ON retail.stock_transfers(source_branch_id);
|
|
CREATE INDEX IF NOT EXISTS idx_stock_transfers_dest ON retail.stock_transfers(destination_branch_id);
|
|
CREATE INDEX IF NOT EXISTS idx_stock_transfers_status ON retail.stock_transfers(status);
|
|
|
|
-- Product barcodes
|
|
CREATE INDEX IF NOT EXISTS idx_product_barcodes_tenant ON retail.product_barcodes(tenant_id);
|
|
CREATE INDEX IF NOT EXISTS idx_product_barcodes_barcode ON retail.product_barcodes(barcode);
|
|
CREATE INDEX IF NOT EXISTS idx_product_barcodes_product ON retail.product_barcodes(product_id);
|
|
|
|
-- Promotions
|
|
CREATE INDEX IF NOT EXISTS idx_promotions_tenant ON retail.promotions(tenant_id);
|
|
CREATE INDEX IF NOT EXISTS idx_promotions_dates ON retail.promotions(start_date, end_date);
|
|
CREATE INDEX IF NOT EXISTS idx_promotions_active ON retail.promotions(is_active);
|
|
|
|
-- Loyalty
|
|
CREATE INDEX IF NOT EXISTS idx_loyalty_cards_tenant ON retail.loyalty_cards(tenant_id);
|
|
CREATE INDEX IF NOT EXISTS idx_loyalty_cards_customer ON retail.loyalty_cards(customer_id);
|
|
CREATE INDEX IF NOT EXISTS idx_loyalty_transactions_tenant ON retail.loyalty_transactions(tenant_id);
|
|
CREATE INDEX IF NOT EXISTS idx_loyalty_transactions_card ON retail.loyalty_transactions(card_id);
|
|
|
|
-- ============================================================================
|
|
-- ROW LEVEL SECURITY
|
|
-- ============================================================================
|
|
|
|
ALTER TABLE retail.branches ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE retail.cash_registers ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE retail.pos_sessions ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE retail.pos_orders ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE retail.pos_order_lines ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE retail.pos_payments ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE retail.cash_movements ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE retail.branch_stock ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE retail.stock_transfers ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE retail.stock_transfer_lines ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE retail.product_barcodes ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE retail.promotions ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE retail.promotion_products ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE retail.loyalty_programs ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE retail.loyalty_cards ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE retail.loyalty_transactions ENABLE ROW LEVEL SECURITY;
|
|
|
|
-- Políticas de aislamiento por tenant
|
|
DO $$ BEGIN
|
|
DROP POLICY IF EXISTS tenant_isolation_branches ON retail.branches;
|
|
CREATE POLICY tenant_isolation_branches ON retail.branches
|
|
FOR ALL USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);
|
|
EXCEPTION WHEN undefined_object THEN NULL; END $$;
|
|
|
|
DO $$ BEGIN
|
|
DROP POLICY IF EXISTS tenant_isolation_cash_registers ON retail.cash_registers;
|
|
CREATE POLICY tenant_isolation_cash_registers ON retail.cash_registers
|
|
FOR ALL USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);
|
|
EXCEPTION WHEN undefined_object THEN NULL; END $$;
|
|
|
|
DO $$ BEGIN
|
|
DROP POLICY IF EXISTS tenant_isolation_pos_sessions ON retail.pos_sessions;
|
|
CREATE POLICY tenant_isolation_pos_sessions ON retail.pos_sessions
|
|
FOR ALL USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);
|
|
EXCEPTION WHEN undefined_object THEN NULL; END $$;
|
|
|
|
DO $$ BEGIN
|
|
DROP POLICY IF EXISTS tenant_isolation_pos_orders ON retail.pos_orders;
|
|
CREATE POLICY tenant_isolation_pos_orders ON retail.pos_orders
|
|
FOR ALL USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);
|
|
EXCEPTION WHEN undefined_object THEN NULL; END $$;
|
|
|
|
DO $$ BEGIN
|
|
DROP POLICY IF EXISTS tenant_isolation_pos_order_lines ON retail.pos_order_lines;
|
|
CREATE POLICY tenant_isolation_pos_order_lines ON retail.pos_order_lines
|
|
FOR ALL USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);
|
|
EXCEPTION WHEN undefined_object THEN NULL; END $$;
|
|
|
|
DO $$ BEGIN
|
|
DROP POLICY IF EXISTS tenant_isolation_pos_payments ON retail.pos_payments;
|
|
CREATE POLICY tenant_isolation_pos_payments ON retail.pos_payments
|
|
FOR ALL USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);
|
|
EXCEPTION WHEN undefined_object THEN NULL; END $$;
|
|
|
|
DO $$ BEGIN
|
|
DROP POLICY IF EXISTS tenant_isolation_cash_movements ON retail.cash_movements;
|
|
CREATE POLICY tenant_isolation_cash_movements ON retail.cash_movements
|
|
FOR ALL USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);
|
|
EXCEPTION WHEN undefined_object THEN NULL; END $$;
|
|
|
|
DO $$ BEGIN
|
|
DROP POLICY IF EXISTS tenant_isolation_branch_stock ON retail.branch_stock;
|
|
CREATE POLICY tenant_isolation_branch_stock ON retail.branch_stock
|
|
FOR ALL USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);
|
|
EXCEPTION WHEN undefined_object THEN NULL; END $$;
|
|
|
|
DO $$ BEGIN
|
|
DROP POLICY IF EXISTS tenant_isolation_stock_transfers ON retail.stock_transfers;
|
|
CREATE POLICY tenant_isolation_stock_transfers ON retail.stock_transfers
|
|
FOR ALL USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);
|
|
EXCEPTION WHEN undefined_object THEN NULL; END $$;
|
|
|
|
DO $$ BEGIN
|
|
DROP POLICY IF EXISTS tenant_isolation_stock_transfer_lines ON retail.stock_transfer_lines;
|
|
CREATE POLICY tenant_isolation_stock_transfer_lines ON retail.stock_transfer_lines
|
|
FOR ALL USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);
|
|
EXCEPTION WHEN undefined_object THEN NULL; END $$;
|
|
|
|
DO $$ BEGIN
|
|
DROP POLICY IF EXISTS tenant_isolation_product_barcodes ON retail.product_barcodes;
|
|
CREATE POLICY tenant_isolation_product_barcodes ON retail.product_barcodes
|
|
FOR ALL USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);
|
|
EXCEPTION WHEN undefined_object THEN NULL; END $$;
|
|
|
|
DO $$ BEGIN
|
|
DROP POLICY IF EXISTS tenant_isolation_promotions ON retail.promotions;
|
|
CREATE POLICY tenant_isolation_promotions ON retail.promotions
|
|
FOR ALL USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);
|
|
EXCEPTION WHEN undefined_object THEN NULL; END $$;
|
|
|
|
DO $$ BEGIN
|
|
DROP POLICY IF EXISTS tenant_isolation_promotion_products ON retail.promotion_products;
|
|
CREATE POLICY tenant_isolation_promotion_products ON retail.promotion_products
|
|
FOR ALL USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);
|
|
EXCEPTION WHEN undefined_object THEN NULL; END $$;
|
|
|
|
DO $$ BEGIN
|
|
DROP POLICY IF EXISTS tenant_isolation_loyalty_programs ON retail.loyalty_programs;
|
|
CREATE POLICY tenant_isolation_loyalty_programs ON retail.loyalty_programs
|
|
FOR ALL USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);
|
|
EXCEPTION WHEN undefined_object THEN NULL; END $$;
|
|
|
|
DO $$ BEGIN
|
|
DROP POLICY IF EXISTS tenant_isolation_loyalty_cards ON retail.loyalty_cards;
|
|
CREATE POLICY tenant_isolation_loyalty_cards ON retail.loyalty_cards
|
|
FOR ALL USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);
|
|
EXCEPTION WHEN undefined_object THEN NULL; END $$;
|
|
|
|
DO $$ BEGIN
|
|
DROP POLICY IF EXISTS tenant_isolation_loyalty_transactions ON retail.loyalty_transactions;
|
|
CREATE POLICY tenant_isolation_loyalty_transactions ON retail.loyalty_transactions
|
|
FOR ALL USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);
|
|
EXCEPTION WHEN undefined_object THEN NULL; END $$;
|
|
|
|
-- ============================================================================
|
|
-- COMENTARIOS
|
|
-- ============================================================================
|
|
|
|
COMMENT ON TABLE retail.branches IS 'Sucursales de la empresa';
|
|
COMMENT ON TABLE retail.cash_registers IS 'Cajas registradoras por sucursal';
|
|
COMMENT ON TABLE retail.pos_sessions IS 'Sesiones de punto de venta';
|
|
COMMENT ON TABLE retail.pos_orders IS 'Órdenes/Ventas de punto de venta';
|
|
COMMENT ON TABLE retail.pos_order_lines IS 'Líneas de venta';
|
|
COMMENT ON TABLE retail.pos_payments IS 'Pagos de orden (para pagos mixtos)';
|
|
COMMENT ON TABLE retail.cash_movements IS 'Entradas/salidas de efectivo';
|
|
COMMENT ON TABLE retail.branch_stock IS 'Stock por sucursal';
|
|
COMMENT ON TABLE retail.stock_transfers IS 'Transferencias entre sucursales';
|
|
COMMENT ON TABLE retail.stock_transfer_lines IS 'Líneas de transferencia';
|
|
COMMENT ON TABLE retail.product_barcodes IS 'Códigos de barras múltiples por producto';
|
|
COMMENT ON TABLE retail.promotions IS 'Promociones y descuentos';
|
|
COMMENT ON TABLE retail.promotion_products IS 'Productos en promoción';
|
|
COMMENT ON TABLE retail.loyalty_programs IS 'Programas de fidelización';
|
|
COMMENT ON TABLE retail.loyalty_cards IS 'Tarjetas de fidelización';
|
|
COMMENT ON TABLE retail.loyalty_transactions IS 'Transacciones de puntos';
|
|
|
|
-- ============================================================================
|
|
-- FIN TABLAS RETAIL
|
|
-- Total: 16 tablas, 6 ENUMs
|
|
-- ============================================================================
|