workspace-v1/projects/erp-retail/orchestration/planes/fase-3-implementacion/PLAN-IMPL-DATABASE.md

PLAN DE IMPLEMENTACION - DATABASE

Fecha: 2025-12-18 Fase: 3 - Plan de Implementaciones Capa: Base de Datos (PostgreSQL 15+)

---

1. RESUMEN EJECUTIVO

1.1 Alcance

• Tablas heredadas de core: 144
• Tablas nuevas retail: 26
• Schemas afectados: auth, core, financial, inventory, sales, retail (nuevo)
• Migraciones requeridas: 15

1.2 Prerrequisitos Bloqueantes

ID	Modulo Core	Estado	Accion Requerida
GAP-BLK-001	MGN-001 Auth	40%	Completar al 100%
GAP-BLK-002	MGN-005 Catalogs	0%	Implementar
GAP-BLK-003	MGN-010 Financial	70%	Migrar a TypeORM
GAP-BLK-004	MGN-011 Inventory	60%	Completar
GAP-BLK-005	MGN-013 Sales	50%	Completar

---

2. ORDEN DE IMPLEMENTACION

2.1 Secuencia de Dependencias

SPRINT 1 (Prerrequisitos Core)
    │
    ├── 1.1 auth.tenants (verificar/completar)
    ├── 1.2 auth.users (verificar/completar)
    ├── 1.3 auth.roles (verificar/completar)
    └── 1.4 auth.user_roles (verificar/completar)
           │
           v
SPRINT 2 (Catalogos Core)
    │
    ├── 2.1 core.countries
    ├── 2.2 core.currencies
    ├── 2.3 core.uom_categories
    ├── 2.4 core.uom
    └── 2.5 core.sequences
           │
           v
SPRINT 3 (Financial Core)
    │
    ├── 3.1 financial.tax_groups
    ├── 3.2 financial.taxes
    ├── 3.3 financial.accounts
    ├── 3.4 financial.journals
    └── 3.5 financial.payment_methods
           │
           v
SPRINT 4 (Inventory Core)
    │
    ├── 4.1 inventory.product_categories
    ├── 4.2 inventory.products
    ├── 4.3 inventory.warehouses
    └── 4.4 inventory.stock_quants
           │
           v
SPRINT 5 (Partners/Sales Core)
    │
    ├── 5.1 core.partners
    ├── 5.2 sales.pricelists
    └── 5.3 sales.pricelist_items
           │
           v
SPRINT 6 (Retail Schema - Fundamentos)
    │
    ├── 6.1 retail.branches
    ├── 6.2 retail.cash_registers
    └── 6.3 retail.branch_users
           │
           v
SPRINT 7 (Retail - POS)
    │
    ├── 7.1 retail.pos_sessions
    ├── 7.2 retail.pos_orders
    ├── 7.3 retail.pos_order_lines
    └── 7.4 retail.pos_payments
           │
           v
SPRINT 8 (Retail - Caja)
    │
    ├── 8.1 retail.cash_movements
    ├── 8.2 retail.cash_closings
    └── 8.3 retail.cash_counts
           │
           v
SPRINT 9 (Retail - Inventario)
    │
    ├── 9.1 retail.branch_stock (vista/extension)
    ├── 9.2 retail.stock_transfers
    └── 9.3 retail.stock_adjustments
           │
           v
SPRINT 10 (Retail - Clientes/Lealtad)
    │
    ├── 10.1 retail.loyalty_programs
    ├── 10.2 retail.membership_levels
    ├── 10.3 retail.loyalty_transactions
    └── 10.4 retail.customer_memberships
           │
           v
SPRINT 11 (Retail - Precios/Promociones)
    │
    ├── 11.1 retail.promotions
    ├── 11.2 retail.promotion_products
    ├── 11.3 retail.coupons
    └── 11.4 retail.coupon_redemptions
           │
           v
SPRINT 12 (Retail - Compras)
    │
    ├── 12.1 retail.purchase_suggestions
    ├── 12.2 retail.supplier_orders
    └── 12.3 retail.goods_receipts
           │
           v
SPRINT 13 (Retail - Facturacion)
    │
    ├── 13.1 retail.cfdi_config
    └── 13.2 retail.cfdis
           │
           v
SPRINT 14 (Retail - E-commerce)
    │
    ├── 14.1 retail.carts
    ├── 14.2 retail.cart_items
    ├── 14.3 retail.ecommerce_orders
    ├── 14.4 retail.ecommerce_order_lines
    └── 14.5 retail.shipping_rates
           │
           v
SPRINT 15 (Vistas Materializadas y Optimizacion)
    │
    ├── 15.1 retail.mv_daily_sales
    ├── 15.2 retail.mv_product_sales
    └── 15.3 Indices adicionales

---

3. DDL DETALLADO POR SPRINT

3.1 SPRINT 6 - Retail Schema Fundamentos

-- =====================================================
-- RETAIL SCHEMA - FUNDAMENTOS
-- Sprint: 6
-- Dependencias: auth.*, core.*, inventory.warehouses
-- =====================================================

-- Crear schema
CREATE SCHEMA IF NOT EXISTS retail;

-- 6.1 Sucursales
CREATE TABLE retail.branches (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id),
  code VARCHAR(10) NOT NULL,
  name VARCHAR(100) NOT NULL,
  address TEXT,
  phone VARCHAR(20),
  email VARCHAR(100),
  warehouse_id UUID REFERENCES inventory.warehouses(id),
  is_active BOOLEAN DEFAULT TRUE,
  is_main BOOLEAN DEFAULT FALSE,
  config JSONB DEFAULT '{}',
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ,
  UNIQUE(tenant_id, code)
);

-- RLS Policy
ALTER TABLE retail.branches ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON retail.branches
  USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

-- 6.2 Cajas Registradoras
CREATE TABLE retail.cash_registers (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id),
  branch_id UUID NOT NULL REFERENCES retail.branches(id),
  code VARCHAR(10) NOT NULL,
  name VARCHAR(50) NOT NULL,
  is_active BOOLEAN DEFAULT TRUE,
  default_payment_method VARCHAR(20),
  config JSONB DEFAULT '{}',
  created_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(tenant_id, code)
);

ALTER TABLE retail.cash_registers ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON retail.cash_registers
  USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

-- 6.3 Usuarios por Sucursal
CREATE TABLE retail.branch_users (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id),
  branch_id UUID NOT NULL REFERENCES retail.branches(id),
  user_id UUID NOT NULL REFERENCES auth.users(id),
  role VARCHAR(20) NOT NULL, -- manager, cashier, inventory
  is_primary BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(tenant_id, branch_id, user_id)
);

ALTER TABLE retail.branch_users ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON retail.branch_users
  USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

-- Indices
CREATE INDEX idx_branches_tenant ON retail.branches(tenant_id);
CREATE INDEX idx_cash_registers_branch ON retail.cash_registers(branch_id);
CREATE INDEX idx_branch_users_user ON retail.branch_users(user_id);

3.2 SPRINT 7 - Retail POS

-- =====================================================
-- RETAIL SCHEMA - POS
-- Sprint: 7
-- Dependencias: retail.branches, retail.cash_registers
-- =====================================================

-- 7.1 Sesiones POS
CREATE TABLE retail.pos_sessions (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id),
  branch_id UUID NOT NULL REFERENCES retail.branches(id),
  register_id UUID NOT NULL REFERENCES retail.cash_registers(id),
  user_id UUID NOT NULL REFERENCES auth.users(id),
  status VARCHAR(20) NOT NULL DEFAULT 'opening',
  -- opening, open, closing, closed
  opening_balance DECIMAL(12,2) NOT NULL DEFAULT 0,
  closing_balance DECIMAL(12,2),
  opened_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  closed_at TIMESTAMPTZ,
  notes TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ
);

ALTER TABLE retail.pos_sessions ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON retail.pos_sessions
  USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

-- 7.2 Ordenes POS
CREATE TABLE retail.pos_orders (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id),
  session_id UUID NOT NULL REFERENCES retail.pos_sessions(id),
  order_number VARCHAR(20) NOT NULL,
  customer_id UUID REFERENCES core.partners(id),
  status VARCHAR(20) NOT NULL DEFAULT 'draft',
  -- draft, done, refunded, cancelled
  order_date TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  subtotal DECIMAL(12,2) NOT NULL DEFAULT 0,
  discount_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  tax_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  total DECIMAL(12,2) NOT NULL DEFAULT 0,
  notes TEXT,
  -- Campos offline
  offline_id VARCHAR(50),
  synced_at TIMESTAMPTZ,
  -- Facturacion
  is_invoiced BOOLEAN DEFAULT FALSE,
  invoice_id UUID,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ,
  UNIQUE(tenant_id, order_number)
);

ALTER TABLE retail.pos_orders ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON retail.pos_orders
  USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

-- 7.3 Lineas de Orden POS
CREATE TABLE retail.pos_order_lines (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id),
  order_id UUID NOT NULL REFERENCES retail.pos_orders(id) ON DELETE CASCADE,
  product_id UUID NOT NULL REFERENCES inventory.products(id),
  product_name VARCHAR(255) NOT NULL,
  quantity DECIMAL(12,4) NOT NULL,
  unit_price DECIMAL(12,2) NOT NULL,
  discount_percent DECIMAL(5,2) DEFAULT 0,
  discount_amount DECIMAL(12,2) DEFAULT 0,
  tax_amount DECIMAL(12,2) DEFAULT 0,
  total DECIMAL(12,2) NOT NULL,
  notes TEXT
);

ALTER TABLE retail.pos_order_lines ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON retail.pos_order_lines
  USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

-- 7.4 Pagos POS
CREATE TABLE retail.pos_payments (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id),
  order_id UUID NOT NULL REFERENCES retail.pos_orders(id) ON DELETE CASCADE,
  payment_method VARCHAR(20) NOT NULL,
  -- cash, card, transfer, credit, mixed
  amount DECIMAL(12,2) NOT NULL,
  received_amount DECIMAL(12,2),
  change_amount DECIMAL(12,2) DEFAULT 0,
  reference VARCHAR(100),
  card_last_four VARCHAR(4),
  authorization_code VARCHAR(20),
  created_at TIMESTAMPTZ DEFAULT NOW()
);

ALTER TABLE retail.pos_payments ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON retail.pos_payments
  USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

-- Indices
CREATE INDEX idx_pos_sessions_branch ON retail.pos_sessions(branch_id);
CREATE INDEX idx_pos_sessions_user ON retail.pos_sessions(user_id);
CREATE INDEX idx_pos_sessions_status ON retail.pos_sessions(status);
CREATE INDEX idx_pos_orders_session ON retail.pos_orders(session_id);
CREATE INDEX idx_pos_orders_customer ON retail.pos_orders(customer_id);
CREATE INDEX idx_pos_orders_date ON retail.pos_orders(order_date);
CREATE INDEX idx_pos_orders_offline ON retail.pos_orders(offline_id) WHERE offline_id IS NOT NULL;
CREATE INDEX idx_pos_order_lines_product ON retail.pos_order_lines(product_id);
CREATE INDEX idx_pos_payments_order ON retail.pos_payments(order_id);

3.3 SPRINT 8 - Retail Caja

-- =====================================================
-- RETAIL SCHEMA - CAJA
-- Sprint: 8
-- Dependencias: retail.pos_sessions
-- =====================================================

-- 8.1 Movimientos de Caja
CREATE TABLE retail.cash_movements (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id),
  session_id UUID NOT NULL REFERENCES retail.pos_sessions(id),
  movement_type VARCHAR(10) NOT NULL, -- in, out
  amount DECIMAL(12,2) NOT NULL,
  reason VARCHAR(100) NOT NULL,
  notes TEXT,
  authorized_by UUID REFERENCES auth.users(id),
  created_at TIMESTAMPTZ DEFAULT NOW()
);

ALTER TABLE retail.cash_movements ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON retail.cash_movements
  USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

-- 8.2 Cortes de Caja
CREATE TABLE retail.cash_closings (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id),
  session_id UUID NOT NULL REFERENCES retail.pos_sessions(id) UNIQUE,
  closing_date TIMESTAMPTZ NOT NULL DEFAULT NOW(),

  -- Montos esperados (calculados)
  expected_cash DECIMAL(12,2) NOT NULL,
  expected_card DECIMAL(12,2) NOT NULL DEFAULT 0,
  expected_transfer DECIMAL(12,2) NOT NULL DEFAULT 0,

  -- Montos declarados
  declared_cash DECIMAL(12,2) NOT NULL,
  declared_card DECIMAL(12,2) NOT NULL DEFAULT 0,
  declared_transfer DECIMAL(12,2) NOT NULL DEFAULT 0,

  -- Diferencias (columnas generadas)
  cash_difference DECIMAL(12,2) GENERATED ALWAYS AS (declared_cash - expected_cash) STORED,
  card_difference DECIMAL(12,2) GENERATED ALWAYS AS (declared_card - expected_card) STORED,
  transfer_difference DECIMAL(12,2) GENERATED ALWAYS AS (declared_transfer - expected_transfer) STORED,

  -- Detalle de denominaciones
  denomination_detail JSONB,
  notes TEXT,

  -- Auditoria
  closed_by UUID NOT NULL REFERENCES auth.users(id),
  approved_by UUID REFERENCES auth.users(id),
  is_approved BOOLEAN DEFAULT FALSE,

  created_at TIMESTAMPTZ DEFAULT NOW()
);

ALTER TABLE retail.cash_closings ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON retail.cash_closings
  USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

-- 8.3 Arqueos Parciales
CREATE TABLE retail.cash_counts (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id),
  session_id UUID NOT NULL REFERENCES retail.pos_sessions(id),
  count_date TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  expected_amount DECIMAL(12,2) NOT NULL,
  counted_amount DECIMAL(12,2) NOT NULL,
  difference DECIMAL(12,2) GENERATED ALWAYS AS (counted_amount - expected_amount) STORED,
  denomination_detail JSONB,
  counted_by UUID NOT NULL REFERENCES auth.users(id),
  notes TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

ALTER TABLE retail.cash_counts ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON retail.cash_counts
  USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

-- Indices
CREATE INDEX idx_cash_movements_session ON retail.cash_movements(session_id);
CREATE INDEX idx_cash_closings_date ON retail.cash_closings(closing_date);
CREATE INDEX idx_cash_counts_session ON retail.cash_counts(session_id);

3.4 SPRINT 9 - Retail Inventario

-- =====================================================
-- RETAIL SCHEMA - INVENTARIO MULTI-SUCURSAL
-- Sprint: 9
-- Dependencias: retail.branches, inventory.*
-- =====================================================

-- 9.1 Vista de Stock por Sucursal (extiende inventory.stock_quants)
CREATE OR REPLACE VIEW retail.branch_stock AS
SELECT
  sq.id,
  sq.tenant_id,
  sq.product_id,
  sq.warehouse_id,
  b.id as branch_id,
  b.code as branch_code,
  b.name as branch_name,
  sq.quantity,
  sq.reserved_quantity,
  (sq.quantity - sq.reserved_quantity) as available_quantity,
  p.default_code as product_code,
  p.name as product_name,
  p.sale_price,
  sq.updated_at
FROM inventory.stock_quants sq
JOIN retail.branches b ON b.warehouse_id = sq.warehouse_id
JOIN inventory.products p ON p.id = sq.product_id
WHERE sq.tenant_id = current_setting('app.current_tenant_id', true)::UUID;

-- 9.2 Transferencias entre Sucursales
CREATE TABLE retail.stock_transfers (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id),
  transfer_number VARCHAR(20) NOT NULL,
  source_branch_id UUID NOT NULL REFERENCES retail.branches(id),
  dest_branch_id UUID NOT NULL REFERENCES retail.branches(id),
  status VARCHAR(20) NOT NULL DEFAULT 'draft',
  -- draft, confirmed, in_transit, received, cancelled
  requested_by UUID NOT NULL REFERENCES auth.users(id),
  confirmed_by UUID REFERENCES auth.users(id),
  received_by UUID REFERENCES auth.users(id),
  requested_date TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  confirmed_date TIMESTAMPTZ,
  received_date TIMESTAMPTZ,
  notes TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ,
  UNIQUE(tenant_id, transfer_number)
);

ALTER TABLE retail.stock_transfers ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON retail.stock_transfers
  USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

-- 9.2.1 Lineas de Transferencia
CREATE TABLE retail.stock_transfer_lines (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id),
  transfer_id UUID NOT NULL REFERENCES retail.stock_transfers(id) ON DELETE CASCADE,
  product_id UUID NOT NULL REFERENCES inventory.products(id),
  requested_qty DECIMAL(12,4) NOT NULL,
  sent_qty DECIMAL(12,4),
  received_qty DECIMAL(12,4),
  notes TEXT
);

ALTER TABLE retail.stock_transfer_lines ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON retail.stock_transfer_lines
  USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

-- 9.3 Ajustes de Inventario
CREATE TABLE retail.stock_adjustments (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id),
  adjustment_number VARCHAR(20) NOT NULL,
  branch_id UUID NOT NULL REFERENCES retail.branches(id),
  adjustment_type VARCHAR(20) NOT NULL,
  -- count, damage, theft, expiry, correction
  status VARCHAR(20) NOT NULL DEFAULT 'draft',
  -- draft, confirmed, cancelled
  adjusted_by UUID NOT NULL REFERENCES auth.users(id),
  approved_by UUID REFERENCES auth.users(id),
  adjustment_date TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  notes TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(tenant_id, adjustment_number)
);

ALTER TABLE retail.stock_adjustments ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON retail.stock_adjustments
  USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

-- 9.3.1 Lineas de Ajuste
CREATE TABLE retail.stock_adjustment_lines (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id),
  adjustment_id UUID NOT NULL REFERENCES retail.stock_adjustments(id) ON DELETE CASCADE,
  product_id UUID NOT NULL REFERENCES inventory.products(id),
  theoretical_qty DECIMAL(12,4) NOT NULL,
  actual_qty DECIMAL(12,4) NOT NULL,
  difference DECIMAL(12,4) GENERATED ALWAYS AS (actual_qty - theoretical_qty) STORED,
  reason VARCHAR(100),
  notes TEXT
);

ALTER TABLE retail.stock_adjustment_lines ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON retail.stock_adjustment_lines
  USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

-- Indices
CREATE INDEX idx_stock_transfers_source ON retail.stock_transfers(source_branch_id);
CREATE INDEX idx_stock_transfers_dest ON retail.stock_transfers(dest_branch_id);
CREATE INDEX idx_stock_transfers_status ON retail.stock_transfers(status);
CREATE INDEX idx_stock_adjustments_branch ON retail.stock_adjustments(branch_id);

3.5 SPRINT 10 - Retail Clientes y Lealtad

-- =====================================================
-- RETAIL SCHEMA - CLIENTES Y LEALTAD
-- Sprint: 10
-- Dependencias: core.partners
-- =====================================================

-- 10.1 Programas de Lealtad
CREATE TABLE retail.loyalty_programs (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id),
  code VARCHAR(20) NOT NULL,
  name VARCHAR(100) NOT NULL,
  description TEXT,
  points_per_currency DECIMAL(10,4) NOT NULL DEFAULT 1,
  -- 1 punto por cada $1
  currency_per_point DECIMAL(10,4) NOT NULL DEFAULT 0.01,
  -- $0.01 por punto al canjear
  min_points_redeem INT NOT NULL DEFAULT 100,
  max_discount_percent DECIMAL(5,2) DEFAULT 100,
  is_active BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ,
  UNIQUE(tenant_id, code)
);

ALTER TABLE retail.loyalty_programs ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON retail.loyalty_programs
  USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

-- 10.2 Niveles de Membresia
CREATE TABLE retail.membership_levels (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id),
  program_id UUID NOT NULL REFERENCES retail.loyalty_programs(id),
  code VARCHAR(20) NOT NULL,
  name VARCHAR(50) NOT NULL,
  min_points INT NOT NULL DEFAULT 0,
  discount_percent DECIMAL(5,2) DEFAULT 0,
  points_multiplier DECIMAL(5,2) DEFAULT 1,
  benefits JSONB,
  color VARCHAR(7), -- hex color
  sort_order INT DEFAULT 0,
  UNIQUE(tenant_id, program_id, code)
);

ALTER TABLE retail.membership_levels ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON retail.membership_levels
  USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

-- 10.3 Transacciones de Puntos
CREATE TABLE retail.loyalty_transactions (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id),
  customer_id UUID NOT NULL REFERENCES core.partners(id),
  program_id UUID NOT NULL REFERENCES retail.loyalty_programs(id),
  order_id UUID REFERENCES retail.pos_orders(id),
  transaction_type VARCHAR(20) NOT NULL,
  -- earn, redeem, expire, adjust, bonus
  points INT NOT NULL,
  balance_after INT NOT NULL,
  description TEXT,
  expires_at DATE,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

ALTER TABLE retail.loyalty_transactions ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON retail.loyalty_transactions
  USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

-- 10.4 Membresias de Clientes
CREATE TABLE retail.customer_memberships (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id),
  customer_id UUID NOT NULL REFERENCES core.partners(id),
  program_id UUID NOT NULL REFERENCES retail.loyalty_programs(id),
  level_id UUID REFERENCES retail.membership_levels(id),
  membership_number VARCHAR(20) NOT NULL,
  current_points INT NOT NULL DEFAULT 0,
  lifetime_points INT NOT NULL DEFAULT 0,
  enrolled_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  last_activity_at TIMESTAMPTZ,
  status VARCHAR(20) DEFAULT 'active',
  -- active, inactive, suspended
  UNIQUE(tenant_id, membership_number),
  UNIQUE(tenant_id, customer_id, program_id)
);

ALTER TABLE retail.customer_memberships ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON retail.customer_memberships
  USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

-- Indices
CREATE INDEX idx_loyalty_programs_active ON retail.loyalty_programs(is_active);
CREATE INDEX idx_membership_levels_program ON retail.membership_levels(program_id);
CREATE INDEX idx_loyalty_trans_customer ON retail.loyalty_transactions(customer_id);
CREATE INDEX idx_loyalty_trans_date ON retail.loyalty_transactions(created_at);
CREATE INDEX idx_customer_memberships_customer ON retail.customer_memberships(customer_id);

3.6 SPRINT 11 - Retail Precios y Promociones

-- =====================================================
-- RETAIL SCHEMA - PRECIOS Y PROMOCIONES
-- Sprint: 11
-- Dependencias: retail.branches, inventory.products
-- =====================================================

-- 11.1 Promociones
CREATE TABLE retail.promotions (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id),
  code VARCHAR(20) NOT NULL,
  name VARCHAR(100) NOT NULL,
  description TEXT,
  promotion_type VARCHAR(30) NOT NULL,
  -- percentage, fixed_amount, buy_x_get_y, bundle
  discount_value DECIMAL(10,2) NOT NULL,
  start_date DATE NOT NULL,
  end_date DATE NOT NULL,
  applies_to_all BOOLEAN DEFAULT FALSE,
  min_quantity INT,
  min_amount DECIMAL(12,2),
  branch_ids UUID[], -- null = todas
  is_active BOOLEAN DEFAULT TRUE,
  max_uses INT,
  current_uses INT DEFAULT 0,
  -- Para NxM
  buy_quantity INT,
  get_quantity INT,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ,
  UNIQUE(tenant_id, code),
  CONSTRAINT valid_dates CHECK (end_date >= start_date)
);

ALTER TABLE retail.promotions ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON retail.promotions
  USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

-- 11.2 Productos en Promocion
CREATE TABLE retail.promotion_products (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id),
  promotion_id UUID NOT NULL REFERENCES retail.promotions(id) ON DELETE CASCADE,
  product_id UUID REFERENCES inventory.products(id),
  category_id UUID REFERENCES inventory.product_categories(id),
  -- Al menos uno debe tener valor
  CONSTRAINT product_or_category CHECK (product_id IS NOT NULL OR category_id IS NOT NULL)
);

ALTER TABLE retail.promotion_products ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON retail.promotion_products
  USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

-- 11.3 Cupones
CREATE TABLE retail.coupons (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id),
  code VARCHAR(20) NOT NULL,
  coupon_type VARCHAR(20) NOT NULL,
  -- percentage, fixed_amount
  discount_value DECIMAL(10,2) NOT NULL,
  min_purchase DECIMAL(12,2),
  max_discount DECIMAL(12,2),
  valid_from DATE NOT NULL,
  valid_until DATE NOT NULL,
  max_uses INT DEFAULT 1,
  times_used INT DEFAULT 0,
  is_active BOOLEAN DEFAULT TRUE,
  customer_id UUID REFERENCES core.partners(id), -- null = cualquier cliente
  created_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(tenant_id, code),
  CONSTRAINT valid_dates CHECK (valid_until >= valid_from)
);

ALTER TABLE retail.coupons ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON retail.coupons
  USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

-- 11.4 Canjes de Cupones
CREATE TABLE retail.coupon_redemptions (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id),
  coupon_id UUID NOT NULL REFERENCES retail.coupons(id),
  order_id UUID NOT NULL REFERENCES retail.pos_orders(id),
  discount_applied DECIMAL(12,2) NOT NULL,
  redeemed_at TIMESTAMPTZ DEFAULT NOW()
);

ALTER TABLE retail.coupon_redemptions ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON retail.coupon_redemptions
  USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

-- Indices
CREATE INDEX idx_promotions_active ON retail.promotions(is_active, start_date, end_date);
CREATE INDEX idx_promotions_branches ON retail.promotions USING GIN(branch_ids);
CREATE INDEX idx_promotion_products_promotion ON retail.promotion_products(promotion_id);
CREATE INDEX idx_promotion_products_product ON retail.promotion_products(product_id);
CREATE INDEX idx_coupons_code ON retail.coupons(code);
CREATE INDEX idx_coupons_valid ON retail.coupons(valid_from, valid_until, is_active);

3.7 SPRINT 12 - Retail Compras

-- =====================================================
-- RETAIL SCHEMA - COMPRAS
-- Sprint: 12
-- Dependencias: retail.branches, inventory.products, core.partners
-- =====================================================

-- 12.1 Sugerencias de Compra
CREATE TABLE retail.purchase_suggestions (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id),
  branch_id UUID NOT NULL REFERENCES retail.branches(id),
  product_id UUID NOT NULL REFERENCES inventory.products(id),
  supplier_id UUID REFERENCES core.partners(id),
  current_stock DECIMAL(12,4) NOT NULL,
  min_stock DECIMAL(12,4) NOT NULL,
  max_stock DECIMAL(12,4) NOT NULL,
  suggested_qty DECIMAL(12,4) NOT NULL,
  avg_daily_sales DECIMAL(12,4),
  days_of_stock DECIMAL(8,2),
  priority VARCHAR(10) NOT NULL DEFAULT 'medium',
  -- critical, high, medium, low
  status VARCHAR(20) NOT NULL DEFAULT 'pending',
  -- pending, ordered, ignored
  generated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE(tenant_id, branch_id, product_id, generated_at::DATE)
);

ALTER TABLE retail.purchase_suggestions ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON retail.purchase_suggestions
  USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

-- 12.2 Ordenes a Proveedores
CREATE TABLE retail.supplier_orders (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id),
  order_number VARCHAR(20) NOT NULL,
  supplier_id UUID NOT NULL REFERENCES core.partners(id),
  branch_id UUID NOT NULL REFERENCES retail.branches(id),
  status VARCHAR(20) NOT NULL DEFAULT 'draft',
  -- draft, sent, confirmed, partial, received, cancelled
  order_date TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  expected_date DATE,
  subtotal DECIMAL(12,2) NOT NULL DEFAULT 0,
  tax_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  total DECIMAL(12,2) NOT NULL DEFAULT 0,
  notes TEXT,
  created_by UUID NOT NULL REFERENCES auth.users(id),
  approved_by UUID REFERENCES auth.users(id),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ,
  UNIQUE(tenant_id, order_number)
);

ALTER TABLE retail.supplier_orders ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON retail.supplier_orders
  USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

-- 12.2.1 Lineas de Orden a Proveedor
CREATE TABLE retail.supplier_order_lines (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id),
  order_id UUID NOT NULL REFERENCES retail.supplier_orders(id) ON DELETE CASCADE,
  product_id UUID NOT NULL REFERENCES inventory.products(id),
  quantity DECIMAL(12,4) NOT NULL,
  unit_price DECIMAL(12,2) NOT NULL,
  tax_percent DECIMAL(5,2) DEFAULT 0,
  total DECIMAL(12,2) NOT NULL,
  received_qty DECIMAL(12,4) DEFAULT 0
);

ALTER TABLE retail.supplier_order_lines ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON retail.supplier_order_lines
  USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

-- 12.3 Recepciones de Mercancia
CREATE TABLE retail.goods_receipts (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id),
  receipt_number VARCHAR(20) NOT NULL,
  supplier_order_id UUID REFERENCES retail.supplier_orders(id),
  branch_id UUID NOT NULL REFERENCES retail.branches(id),
  supplier_id UUID NOT NULL REFERENCES core.partners(id),
  status VARCHAR(20) NOT NULL DEFAULT 'draft',
  -- draft, confirmed, cancelled
  receipt_date TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  supplier_invoice VARCHAR(50),
  notes TEXT,
  received_by UUID NOT NULL REFERENCES auth.users(id),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(tenant_id, receipt_number)
);

ALTER TABLE retail.goods_receipts ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON retail.goods_receipts
  USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

-- 12.3.1 Lineas de Recepcion
CREATE TABLE retail.goods_receipt_lines (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id),
  receipt_id UUID NOT NULL REFERENCES retail.goods_receipts(id) ON DELETE CASCADE,
  product_id UUID NOT NULL REFERENCES inventory.products(id),
  expected_qty DECIMAL(12,4),
  received_qty DECIMAL(12,4) NOT NULL,
  unit_cost DECIMAL(12,2),
  notes TEXT
);

ALTER TABLE retail.goods_receipt_lines ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON retail.goods_receipt_lines
  USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

-- Indices
CREATE INDEX idx_purchase_suggestions_branch ON retail.purchase_suggestions(branch_id);
CREATE INDEX idx_purchase_suggestions_status ON retail.purchase_suggestions(status);
CREATE INDEX idx_purchase_suggestions_priority ON retail.purchase_suggestions(priority);
CREATE INDEX idx_supplier_orders_supplier ON retail.supplier_orders(supplier_id);
CREATE INDEX idx_supplier_orders_status ON retail.supplier_orders(status);
CREATE INDEX idx_goods_receipts_order ON retail.goods_receipts(supplier_order_id);
CREATE INDEX idx_goods_receipts_branch ON retail.goods_receipts(branch_id);

3.8 SPRINT 13 - Retail Facturacion CFDI

-- =====================================================
-- RETAIL SCHEMA - FACTURACION CFDI 4.0
-- Sprint: 13
-- Dependencias: retail.pos_orders, core.partners
-- =====================================================

-- 13.1 Configuracion CFDI por Tenant
CREATE TABLE retail.cfdi_config (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id) UNIQUE,
  emisor_rfc VARCHAR(13) NOT NULL,
  emisor_nombre VARCHAR(255) NOT NULL,
  emisor_regimen VARCHAR(3) NOT NULL,
  emisor_cp VARCHAR(5) NOT NULL,
  pac_provider VARCHAR(20) NOT NULL DEFAULT 'finkok',
  pac_user VARCHAR(100),
  pac_password_encrypted TEXT,
  cer_path TEXT,
  key_path TEXT,
  key_password_encrypted TEXT,
  serie_factura VARCHAR(10) DEFAULT 'A',
  serie_nota_credito VARCHAR(10) DEFAULT 'NC',
  folio_actual INT DEFAULT 1,
  autofactura_enabled BOOLEAN DEFAULT TRUE,
  autofactura_dias INT DEFAULT 30,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ
);

ALTER TABLE retail.cfdi_config ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON retail.cfdi_config
  USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

-- 13.2 CFDIs Emitidos
CREATE TABLE retail.cfdis (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id),

  -- Relacion origen
  source_type VARCHAR(20) NOT NULL, -- pos_order, ecommerce_order
  source_id UUID NOT NULL,

  -- Datos comprobante
  serie VARCHAR(10),
  folio VARCHAR(20),
  uuid VARCHAR(36), -- UUID del SAT
  fecha_emision TIMESTAMPTZ NOT NULL,
  tipo_comprobante CHAR(1) NOT NULL, -- I, E, T, N, P
  forma_pago VARCHAR(2),
  metodo_pago VARCHAR(3),

  -- Receptor
  receptor_rfc VARCHAR(13) NOT NULL,
  receptor_nombre VARCHAR(255) NOT NULL,
  receptor_regimen VARCHAR(3),
  receptor_cp VARCHAR(5),
  uso_cfdi VARCHAR(4),

  -- Totales
  subtotal DECIMAL(12,2) NOT NULL,
  descuento DECIMAL(12,2) DEFAULT 0,
  total_impuestos DECIMAL(12,2) NOT NULL,
  total DECIMAL(12,2) NOT NULL,

  -- Estado
  status VARCHAR(20) NOT NULL DEFAULT 'vigente',
  -- vigente, cancelado, pendiente
  cancel_date TIMESTAMPTZ,
  cancel_reason VARCHAR(2),

  -- Archivos
  xml_content TEXT,
  xml_path TEXT,
  pdf_path TEXT,

  -- Timbre
  fecha_timbrado TIMESTAMPTZ,
  rfc_pac VARCHAR(13),
  sello_cfd TEXT,
  sello_sat TEXT,
  no_certificado_sat VARCHAR(20),

  -- CFDI relacionado (para notas de credito)
  cfdi_relacionado_uuid VARCHAR(36),
  tipo_relacion VARCHAR(2),

  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ,

  UNIQUE(tenant_id, uuid)
);

ALTER TABLE retail.cfdis ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON retail.cfdis
  USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

-- Indices
CREATE INDEX idx_cfdis_source ON retail.cfdis(source_type, source_id);
CREATE INDEX idx_cfdis_uuid ON retail.cfdis(uuid);
CREATE INDEX idx_cfdis_fecha ON retail.cfdis(fecha_emision);
CREATE INDEX idx_cfdis_receptor ON retail.cfdis(receptor_rfc);
CREATE INDEX idx_cfdis_status ON retail.cfdis(status);

3.9 SPRINT 14 - Retail E-commerce

-- =====================================================
-- RETAIL SCHEMA - E-COMMERCE
-- Sprint: 14
-- Dependencias: retail.branches, inventory.products, core.partners
-- =====================================================

-- 14.1 Carritos
CREATE TABLE retail.carts (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id),
  customer_id UUID REFERENCES core.partners(id),
  session_id VARCHAR(100), -- Para guests
  subtotal DECIMAL(12,2) DEFAULT 0,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ,
  expires_at TIMESTAMPTZ,
  CONSTRAINT customer_or_session CHECK (customer_id IS NOT NULL OR session_id IS NOT NULL)
);

ALTER TABLE retail.carts ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON retail.carts
  USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

-- 14.2 Items del Carrito
CREATE TABLE retail.cart_items (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id),
  cart_id UUID NOT NULL REFERENCES retail.carts(id) ON DELETE CASCADE,
  product_id UUID NOT NULL REFERENCES inventory.products(id),
  quantity DECIMAL(12,4) NOT NULL,
  unit_price DECIMAL(12,2) NOT NULL,
  total DECIMAL(12,2) NOT NULL,
  added_at TIMESTAMPTZ DEFAULT NOW()
);

ALTER TABLE retail.cart_items ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON retail.cart_items
  USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

-- 14.3 Pedidos E-commerce
CREATE TABLE retail.ecommerce_orders (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id),
  order_number VARCHAR(20) NOT NULL,
  customer_id UUID NOT NULL REFERENCES core.partners(id),
  status VARCHAR(20) NOT NULL DEFAULT 'pending',
  -- pending, paid, preparing, shipped, ready_pickup, delivered, cancelled
  order_date TIMESTAMPTZ NOT NULL DEFAULT NOW(),

  -- Totales
  subtotal DECIMAL(12,2) NOT NULL,
  discount_amount DECIMAL(12,2) DEFAULT 0,
  shipping_cost DECIMAL(12,2) NOT NULL DEFAULT 0,
  tax_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  total DECIMAL(12,2) NOT NULL,

  -- Pago
  payment_status VARCHAR(20), -- pending, paid, failed, refunded
  payment_method VARCHAR(50),
  payment_reference VARCHAR(100),

  -- Entrega
  delivery_method VARCHAR(20) NOT NULL, -- shipping, pickup
  pickup_branch_id UUID REFERENCES retail.branches(id),
  shipping_address JSONB,
  tracking_number VARCHAR(50),

  -- Facturacion
  is_invoiced BOOLEAN DEFAULT FALSE,
  invoice_id UUID,

  notes TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ,

  UNIQUE(tenant_id, order_number)
);

ALTER TABLE retail.ecommerce_orders ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON retail.ecommerce_orders
  USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

-- 14.4 Lineas de Pedido E-commerce
CREATE TABLE retail.ecommerce_order_lines (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id),
  order_id UUID NOT NULL REFERENCES retail.ecommerce_orders(id) ON DELETE CASCADE,
  product_id UUID NOT NULL REFERENCES inventory.products(id),
  product_name VARCHAR(255) NOT NULL,
  quantity DECIMAL(12,4) NOT NULL,
  unit_price DECIMAL(12,2) NOT NULL,
  total DECIMAL(12,2) NOT NULL
);

ALTER TABLE retail.ecommerce_order_lines ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON retail.ecommerce_order_lines
  USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

-- 14.5 Tarifas de Envio
CREATE TABLE retail.shipping_rates (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id),
  name VARCHAR(100) NOT NULL,
  carrier VARCHAR(50) NOT NULL,
  base_rate DECIMAL(12,2) NOT NULL,
  free_shipping_minimum DECIMAL(12,2),
  estimated_days INT,
  is_active BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

ALTER TABLE retail.shipping_rates ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON retail.shipping_rates
  USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

-- Indices
CREATE INDEX idx_carts_customer ON retail.carts(customer_id);
CREATE INDEX idx_carts_session ON retail.carts(session_id);
CREATE INDEX idx_carts_expires ON retail.carts(expires_at);
CREATE INDEX idx_cart_items_cart ON retail.cart_items(cart_id);
CREATE INDEX idx_ecommerce_orders_customer ON retail.ecommerce_orders(customer_id);
CREATE INDEX idx_ecommerce_orders_status ON retail.ecommerce_orders(status);
CREATE INDEX idx_ecommerce_orders_date ON retail.ecommerce_orders(order_date);

3.10 SPRINT 15 - Vistas Materializadas

-- =====================================================
-- RETAIL SCHEMA - VISTAS MATERIALIZADAS
-- Sprint: 15
-- Para performance en reportes
-- =====================================================

-- 15.1 Ventas Diarias
CREATE MATERIALIZED VIEW retail.mv_daily_sales AS
SELECT
  DATE(po.order_date) as sale_date,
  po.tenant_id,
  ps.branch_id,
  ps.user_id as cashier_id,
  COUNT(*) as transaction_count,
  SUM(po.total) as total_sales,
  SUM(po.discount_amount) as total_discounts,
  AVG(po.total) as avg_ticket,
  SUM(CASE WHEN po.status = 'refunded' THEN po.total ELSE 0 END) as refunds
FROM retail.pos_orders po
JOIN retail.pos_sessions ps ON po.session_id = ps.id
WHERE po.status IN ('done', 'refunded')
GROUP BY DATE(po.order_date), po.tenant_id, ps.branch_id, ps.user_id;

CREATE UNIQUE INDEX idx_mv_daily_sales
ON retail.mv_daily_sales(sale_date, tenant_id, branch_id, cashier_id);

-- 15.2 Ventas por Producto
CREATE MATERIALIZED VIEW retail.mv_product_sales AS
SELECT
  pol.product_id,
  pol.tenant_id,
  ps.branch_id,
  DATE_TRUNC('month', po.order_date) as sale_month,
  SUM(pol.quantity) as qty_sold,
  SUM(pol.total) as revenue,
  COUNT(DISTINCT po.id) as order_count
FROM retail.pos_order_lines pol
JOIN retail.pos_orders po ON pol.order_id = po.id
JOIN retail.pos_sessions ps ON po.session_id = ps.id
WHERE po.status = 'done'
GROUP BY pol.product_id, pol.tenant_id, ps.branch_id, DATE_TRUNC('month', po.order_date);

CREATE UNIQUE INDEX idx_mv_product_sales
ON retail.mv_product_sales(product_id, tenant_id, branch_id, sale_month);

-- 15.3 Stock Actual por Sucursal
CREATE MATERIALIZED VIEW retail.mv_branch_stock_summary AS
SELECT
  b.tenant_id,
  b.id as branch_id,
  b.code as branch_code,
  b.name as branch_name,
  COUNT(DISTINCT sq.product_id) as product_count,
  SUM(sq.quantity) as total_stock,
  SUM(sq.quantity * p.cost_price) as stock_value,
  COUNT(CASE WHEN sq.quantity <= p.min_stock THEN 1 END) as low_stock_count,
  COUNT(CASE WHEN sq.quantity = 0 THEN 1 END) as out_of_stock_count
FROM retail.branches b
JOIN inventory.stock_quants sq ON sq.warehouse_id = b.warehouse_id
JOIN inventory.products p ON p.id = sq.product_id
GROUP BY b.tenant_id, b.id, b.code, b.name;

CREATE UNIQUE INDEX idx_mv_branch_stock_summary
ON retail.mv_branch_stock_summary(tenant_id, branch_id);

-- Funcion para refrescar vistas
CREATE OR REPLACE FUNCTION retail.refresh_materialized_views()
RETURNS void AS $$
BEGIN
  REFRESH MATERIALIZED VIEW CONCURRENTLY retail.mv_daily_sales;
  REFRESH MATERIALIZED VIEW CONCURRENTLY retail.mv_product_sales;
  REFRESH MATERIALIZED VIEW CONCURRENTLY retail.mv_branch_stock_summary;
END;
$$ LANGUAGE plpgsql;

-- Comentario: Programar cron job para ejecutar cada hora
-- SELECT cron.schedule('refresh-retail-views', '0 * * * *', 'SELECT retail.refresh_materialized_views()');

---

4. MIGRACIONES TYPEORM

4.1 Estructura de Archivos

src/migrations/
├── 1734500000000-CreateRetailSchema.ts
├── 1734500000001-CreateBranchTables.ts
├── 1734500000002-CreatePOSTables.ts
├── 1734500000003-CreateCashTables.ts
├── 1734500000004-CreateInventoryTables.ts
├── 1734500000005-CreateLoyaltyTables.ts
├── 1734500000006-CreatePromotionTables.ts
├── 1734500000007-CreatePurchaseTables.ts
├── 1734500000008-CreateCFDITables.ts
├── 1734500000009-CreateEcommerceTables.ts
├── 1734500000010-CreateMaterializedViews.ts
├── 1734500000011-CreateIndices.ts
└── 1734500000012-SeedCatalogs.ts

4.2 Ejemplo de Migracion

// 1734500000002-CreatePOSTables.ts
import { MigrationInterface, QueryRunner } from 'typeorm';

export class CreatePOSTables1734500000002 implements MigrationInterface {
  name = 'CreatePOSTables1734500000002';

  public async up(queryRunner: QueryRunner): Promise<void> {
    // pos_sessions
    await queryRunner.query(`
      CREATE TABLE retail.pos_sessions (
        id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
        tenant_id UUID NOT NULL REFERENCES auth.tenants(id),
        branch_id UUID NOT NULL REFERENCES retail.branches(id),
        register_id UUID NOT NULL REFERENCES retail.cash_registers(id),
        user_id UUID NOT NULL REFERENCES auth.users(id),
        status VARCHAR(20) NOT NULL DEFAULT 'opening',
        opening_balance DECIMAL(12,2) NOT NULL DEFAULT 0,
        closing_balance DECIMAL(12,2),
        opened_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
        closed_at TIMESTAMPTZ,
        notes TEXT,
        created_at TIMESTAMPTZ DEFAULT NOW(),
        updated_at TIMESTAMPTZ
      )
    `);

    // RLS
    await queryRunner.query(`
      ALTER TABLE retail.pos_sessions ENABLE ROW LEVEL SECURITY
    `);

    await queryRunner.query(`
      CREATE POLICY tenant_isolation ON retail.pos_sessions
        USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID)
    `);

    // Similar para pos_orders, pos_order_lines, pos_payments...
  }

  public async down(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(`DROP TABLE IF EXISTS retail.pos_payments`);
    await queryRunner.query(`DROP TABLE IF EXISTS retail.pos_order_lines`);
    await queryRunner.query(`DROP TABLE IF EXISTS retail.pos_orders`);
    await queryRunner.query(`DROP TABLE IF EXISTS retail.pos_sessions`);
  }
}

---

5. SCRIPTS DE SEED

5.1 Catalogos SAT (para CFDI)

-- Catalogos SAT
INSERT INTO retail.sat_forma_pago (clave, descripcion) VALUES
('01', 'Efectivo'),
('02', 'Cheque nominativo'),
('03', 'Transferencia electrónica de fondos'),
('04', 'Tarjeta de crédito'),
('28', 'Tarjeta de débito'),
('99', 'Por definir');

INSERT INTO retail.sat_metodo_pago (clave, descripcion) VALUES
('PUE', 'Pago en una sola exhibición'),
('PPD', 'Pago en parcialidades o diferido');

INSERT INTO retail.sat_uso_cfdi (clave, descripcion, persona_fisica, persona_moral) VALUES
('G01', 'Adquisición de mercancías', true, true),
('G03', 'Gastos en general', true, true),
('P01', 'Por definir', true, true);

INSERT INTO retail.sat_regimen_fiscal (clave, descripcion, persona_fisica, persona_moral) VALUES
('601', 'General de Ley Personas Morales', false, true),
('612', 'Personas Físicas con Actividades Empresariales y Profesionales', true, false),
('621', 'Incorporación Fiscal', true, false),
('626', 'Régimen Simplificado de Confianza', true, true);

---

6. CHECKLIST DE IMPLEMENTACION

Sprint 1-5: Core Prerequisites

• Verificar schema auth completo
• Completar MGN-001 Auth al 100%
• Implementar MGN-005 Catalogs
• Migrar MGN-010 Financial a TypeORM
• Completar MGN-011 Inventory
• Completar MGN-013 Sales

Sprint 6-15: Retail Schema

• Crear schema retail
• Sprint 6: Branches y cajas
• Sprint 7: POS
• Sprint 8: Caja
• Sprint 9: Inventario multi-sucursal
• Sprint 10: Lealtad
• Sprint 11: Promociones
• Sprint 12: Compras
• Sprint 13: CFDI
• Sprint 14: E-commerce
• Sprint 15: Vistas materializadas

Post-implementacion

• Ejecutar todas las migraciones
• Verificar RLS en todas las tablas
• Cargar seeds de catalogos
• Validar indices de performance
• Configurar cron de refresh de vistas

---

Estado: PLAN COMPLETO Fecha estimada: 15 sprints (7.5 semanas con sprints de 3 dias)