-- ============================================================================
-- 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
-- ============================================================================