erp-core-database-v2/ddl/03-core-branches.sql

-- =============================================================
-- ARCHIVO: 03-core-branches.sql
-- DESCRIPCION: Sucursales, jerarquia y asignaciones de usuarios
-- VERSION: 1.0.0
-- PROYECTO: ERP-Core V2
-- FECHA: 2026-01-10
-- =============================================================

-- =====================
-- EXTENSIONES REQUERIDAS
-- =====================
CREATE EXTENSION IF NOT EXISTS cube;
CREATE EXTENSION IF NOT EXISTS earthdistance;

-- =====================
-- SCHEMA: core (si no existe)
-- =====================
CREATE SCHEMA IF NOT EXISTS core;

-- =====================
-- TABLA: branches
-- Sucursales/ubicaciones del negocio
-- =====================
CREATE TABLE IF NOT EXISTS core.branches (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
    parent_id UUID REFERENCES core.branches(id) ON DELETE SET NULL,

    -- Identificacion
    code VARCHAR(20) NOT NULL,
    name VARCHAR(100) NOT NULL,
    short_name VARCHAR(50),

    -- Tipo
    branch_type VARCHAR(30) NOT NULL DEFAULT 'store', -- headquarters, regional, store, warehouse, office, factory

    -- Contacto
    phone VARCHAR(20),
    email VARCHAR(255),
    manager_id UUID REFERENCES auth.users(id),

    -- Direccion
    address_line1 VARCHAR(200),
    address_line2 VARCHAR(200),
    city VARCHAR(100),
    state VARCHAR(100),
    postal_code VARCHAR(20),
    country VARCHAR(3) DEFAULT 'MEX',

    -- Geolocalizacion
    latitude DECIMAL(10, 8),
    longitude DECIMAL(11, 8),
    geofence_radius INTEGER DEFAULT 100, -- Radio en metros para validacion de ubicacion
    geofence_enabled BOOLEAN DEFAULT TRUE,

    -- Configuracion
    timezone VARCHAR(50) DEFAULT 'America/Mexico_City',
    currency VARCHAR(3) DEFAULT 'MXN',
    is_active BOOLEAN DEFAULT TRUE,
    is_main BOOLEAN DEFAULT FALSE, -- Sucursal principal/matriz

    -- Horarios de operacion
    operating_hours JSONB DEFAULT '{}',
    -- Ejemplo: {"monday": {"open": "09:00", "close": "18:00"}, ...}

    -- Configuraciones especificas
    settings JSONB DEFAULT '{}',
    -- Ejemplo: {"allow_pos": true, "allow_warehouse": true, ...}

    -- Jerarquia (path materializado para consultas eficientes)
    hierarchy_path TEXT, -- Ejemplo: /root/regional-norte/sucursal-01
    hierarchy_level INTEGER DEFAULT 0,

    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    created_by UUID REFERENCES auth.users(id),
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_by UUID REFERENCES auth.users(id),
    deleted_at TIMESTAMPTZ,

    UNIQUE(tenant_id, code)
);

-- Indices para branches
CREATE INDEX IF NOT EXISTS idx_branches_tenant ON core.branches(tenant_id);
CREATE INDEX IF NOT EXISTS idx_branches_parent ON core.branches(parent_id);
CREATE INDEX IF NOT EXISTS idx_branches_code ON core.branches(code);
CREATE INDEX IF NOT EXISTS idx_branches_type ON core.branches(branch_type);
CREATE INDEX IF NOT EXISTS idx_branches_active ON core.branches(is_active) WHERE is_active = TRUE;
CREATE INDEX IF NOT EXISTS idx_branches_hierarchy ON core.branches(hierarchy_path);
CREATE INDEX IF NOT EXISTS idx_branches_location ON core.branches USING gist (
    ll_to_earth(latitude, longitude)
) WHERE latitude IS NOT NULL AND longitude IS NOT NULL;

-- =====================
-- TABLA: user_branch_assignments
-- Asignacion de usuarios a sucursales
-- =====================
CREATE TABLE IF NOT EXISTS core.user_branch_assignments (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    branch_id UUID NOT NULL REFERENCES core.branches(id) ON DELETE CASCADE,
    tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,

    -- Tipo de asignacion
    assignment_type VARCHAR(30) NOT NULL DEFAULT 'primary', -- primary, secondary, temporary, floating

    -- Rol en la sucursal
    branch_role VARCHAR(50), -- manager, supervisor, staff

    -- Permisos especificos
    permissions JSONB DEFAULT '[]',

    -- Vigencia (para asignaciones temporales)
    valid_from TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    valid_until TIMESTAMPTZ,

    -- Estado
    is_active BOOLEAN DEFAULT TRUE,

    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    created_by UUID REFERENCES auth.users(id),
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,

    UNIQUE(user_id, branch_id, assignment_type)
);

-- Indices para user_branch_assignments
CREATE INDEX IF NOT EXISTS idx_user_branch_user ON core.user_branch_assignments(user_id);
CREATE INDEX IF NOT EXISTS idx_user_branch_branch ON core.user_branch_assignments(branch_id);
CREATE INDEX IF NOT EXISTS idx_user_branch_tenant ON core.user_branch_assignments(tenant_id);
CREATE INDEX IF NOT EXISTS idx_user_branch_active ON core.user_branch_assignments(is_active) WHERE is_active = TRUE;

-- =====================
-- TABLA: branch_schedules
-- Horarios de trabajo por sucursal
-- =====================
CREATE TABLE IF NOT EXISTS core.branch_schedules (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    branch_id UUID NOT NULL REFERENCES core.branches(id) ON DELETE CASCADE,

    -- Identificacion
    name VARCHAR(100) NOT NULL,
    description TEXT,

    -- Tipo
    schedule_type VARCHAR(30) NOT NULL DEFAULT 'regular', -- regular, holiday, special

    -- Dia de la semana (0=domingo, 1=lunes, ..., 6=sabado) o fecha especifica
    day_of_week INTEGER, -- NULL para fechas especificas
    specific_date DATE, -- Para dias festivos o especiales

    -- Horarios
    open_time TIME NOT NULL,
    close_time TIME NOT NULL,

    -- Turnos (si aplica)
    shifts JSONB DEFAULT '[]',
    -- Ejemplo: [{"name": "Matutino", "start": "08:00", "end": "14:00"}, ...]

    -- Estado
    is_active BOOLEAN DEFAULT TRUE,

    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- Indices para branch_schedules
CREATE INDEX IF NOT EXISTS idx_branch_schedules_branch ON core.branch_schedules(branch_id);
CREATE INDEX IF NOT EXISTS idx_branch_schedules_day ON core.branch_schedules(day_of_week);
CREATE INDEX IF NOT EXISTS idx_branch_schedules_date ON core.branch_schedules(specific_date);

-- =====================
-- TABLA: branch_inventory_settings
-- Configuracion de inventario por sucursal
-- =====================
CREATE TABLE IF NOT EXISTS core.branch_inventory_settings (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    branch_id UUID NOT NULL REFERENCES core.branches(id) ON DELETE CASCADE,

    -- Almacen asociado
    warehouse_id UUID, -- Referencia a inventory.warehouses

    -- Configuracion de stock
    default_stock_min INTEGER DEFAULT 0,
    default_stock_max INTEGER DEFAULT 1000,
    auto_reorder_enabled BOOLEAN DEFAULT FALSE,

    -- Configuracion de precios
    price_list_id UUID, -- Referencia a sales.price_lists
    allow_price_override BOOLEAN DEFAULT FALSE,
    max_discount_percent DECIMAL(5,2) DEFAULT 0,

    -- Configuracion de impuestos
    tax_config JSONB DEFAULT '{}',

    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,

    UNIQUE(branch_id)
);

-- Indices para branch_inventory_settings
CREATE INDEX IF NOT EXISTS idx_branch_inventory_branch ON core.branch_inventory_settings(branch_id);

-- =====================
-- TABLA: branch_payment_terminals
-- Terminales de pago asociadas a sucursal
-- =====================
CREATE TABLE IF NOT EXISTS core.branch_payment_terminals (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    branch_id UUID NOT NULL REFERENCES core.branches(id) ON DELETE CASCADE,

    -- Terminal
    terminal_provider VARCHAR(30) NOT NULL, -- clip, mercadopago, stripe
    terminal_id VARCHAR(100) NOT NULL,
    terminal_name VARCHAR(100),

    -- Credenciales (encriptadas)
    credentials JSONB NOT NULL DEFAULT '{}',

    -- Configuracion
    is_primary BOOLEAN DEFAULT FALSE,
    is_active BOOLEAN DEFAULT TRUE,

    -- Limites
    daily_limit DECIMAL(12,2),
    transaction_limit DECIMAL(12,2),

    -- Ultima actividad
    last_transaction_at TIMESTAMPTZ,
    last_health_check_at TIMESTAMPTZ,
    health_status VARCHAR(20) DEFAULT 'unknown', -- healthy, degraded, offline, unknown

    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,

    UNIQUE(branch_id, terminal_provider, terminal_id)
);

-- Indices para branch_payment_terminals
CREATE INDEX IF NOT EXISTS idx_branch_terminals_branch ON core.branch_payment_terminals(branch_id);
CREATE INDEX IF NOT EXISTS idx_branch_terminals_provider ON core.branch_payment_terminals(terminal_provider);
CREATE INDEX IF NOT EXISTS idx_branch_terminals_active ON core.branch_payment_terminals(is_active) WHERE is_active = TRUE;

-- =====================
-- RLS POLICIES
-- =====================
ALTER TABLE core.branches ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation_branches ON core.branches
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

ALTER TABLE core.user_branch_assignments ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation_branch_assignments ON core.user_branch_assignments
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

ALTER TABLE core.branch_schedules ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation_branch_schedules ON core.branch_schedules
    USING (branch_id IN (
        SELECT id FROM core.branches
        WHERE tenant_id = current_setting('app.current_tenant_id', true)::uuid
    ));

ALTER TABLE core.branch_inventory_settings ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation_branch_inventory ON core.branch_inventory_settings
    USING (branch_id IN (
        SELECT id FROM core.branches
        WHERE tenant_id = current_setting('app.current_tenant_id', true)::uuid
    ));

ALTER TABLE core.branch_payment_terminals ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation_branch_terminals ON core.branch_payment_terminals
    USING (branch_id IN (
        SELECT id FROM core.branches
        WHERE tenant_id = current_setting('app.current_tenant_id', true)::uuid
    ));

-- =====================
-- FUNCIONES
-- =====================

-- Funcion para actualizar hierarchy_path
CREATE OR REPLACE FUNCTION core.update_branch_hierarchy()
RETURNS TRIGGER AS $$
DECLARE
    parent_path TEXT;
BEGIN
    IF NEW.parent_id IS NULL THEN
        NEW.hierarchy_path := '/' || NEW.code;
        NEW.hierarchy_level := 0;
    ELSE
        SELECT hierarchy_path, hierarchy_level + 1
        INTO parent_path, NEW.hierarchy_level
        FROM core.branches
        WHERE id = NEW.parent_id;

        NEW.hierarchy_path := parent_path || '/' || NEW.code;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Trigger para actualizar hierarchy_path automaticamente
CREATE TRIGGER trg_update_branch_hierarchy
    BEFORE INSERT OR UPDATE OF parent_id, code ON core.branches
    FOR EACH ROW
    EXECUTE FUNCTION core.update_branch_hierarchy();

-- Funcion para obtener todas las sucursales hijas
CREATE OR REPLACE FUNCTION core.get_branch_children(parent_branch_id UUID)
RETURNS SETOF core.branches AS $$
BEGIN
    RETURN QUERY
    WITH RECURSIVE branch_tree AS (
        SELECT * FROM core.branches WHERE id = parent_branch_id
        UNION ALL
        SELECT b.* FROM core.branches b
        JOIN branch_tree bt ON b.parent_id = bt.id
    )
    SELECT * FROM branch_tree WHERE id != parent_branch_id;
END;
$$ LANGUAGE plpgsql;

-- Funcion para validar si usuario esta en rango de geofence
CREATE OR REPLACE FUNCTION core.is_within_geofence(
    branch_id UUID,
    user_lat DECIMAL(10, 8),
    user_lon DECIMAL(11, 8)
)
RETURNS BOOLEAN AS $$
DECLARE
    branch_record RECORD;
    distance_meters FLOAT;
BEGIN
    SELECT latitude, longitude, geofence_radius, geofence_enabled
    INTO branch_record
    FROM core.branches
    WHERE id = branch_id;

    IF NOT branch_record.geofence_enabled THEN
        RETURN TRUE;
    END IF;

    IF branch_record.latitude IS NULL OR branch_record.longitude IS NULL THEN
        RETURN TRUE;
    END IF;

    -- Calcular distancia usando formula Haversine (aproximada)
    distance_meters := 6371000 * acos(
        cos(radians(user_lat)) * cos(radians(branch_record.latitude)) *
        cos(radians(branch_record.longitude) - radians(user_lon)) +
        sin(radians(user_lat)) * sin(radians(branch_record.latitude))
    );

    RETURN distance_meters <= branch_record.geofence_radius;
END;
$$ LANGUAGE plpgsql;

-- =====================
-- COMENTARIOS DE TABLAS
-- =====================
COMMENT ON TABLE core.branches IS 'Sucursales/ubicaciones del negocio con soporte para jerarquia';
COMMENT ON TABLE core.user_branch_assignments IS 'Asignacion de usuarios a sucursales';
COMMENT ON TABLE core.branch_schedules IS 'Horarios de operacion por sucursal';
COMMENT ON TABLE core.branch_inventory_settings IS 'Configuracion de inventario especifica por sucursal';
COMMENT ON TABLE core.branch_payment_terminals IS 'Terminales de pago asociadas a cada sucursal';