-- ============================================================================
-- INVENTORY EXTENSIONS - FASE 8 ERP-Core
-- ERP Clínicas (Base Genérica)
-- ============================================================================
-- Fecha: 2026-01-04
-- Versión: 1.0
-- ============================================================================

-- Schema
CREATE SCHEMA IF NOT EXISTS inventory;

-- ============================================================================
-- TABLAS
-- ============================================================================

-- Tipos de paquete
CREATE TABLE IF NOT EXISTS inventory.package_types (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,
    name VARCHAR(100) NOT NULL,
    height NUMERIC(10,2),
    width NUMERIC(10,2),
    length NUMERIC(10,2),
    base_weight NUMERIC(10,2),
    max_weight NUMERIC(10,2),
    sequence INTEGER DEFAULT 10,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

COMMENT ON TABLE inventory.package_types IS 'Tipos de paquete - FASE 8';

-- Paquetes
CREATE TABLE IF NOT EXISTS inventory.packages (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,
    package_type_id UUID REFERENCES inventory.package_types(id),
    name VARCHAR(100),
    product_id UUID,
    -- Extensiones clínica (medicamentos)
    lote VARCHAR(50),
    fecha_fabricacion DATE,
    fecha_caducidad DATE,
    laboratorio VARCHAR(100),
    registro_sanitario VARCHAR(50),
    -- Control
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

COMMENT ON TABLE inventory.packages IS 'Paquetes/lotes de productos - FASE 8';
COMMENT ON COLUMN inventory.packages.lote IS 'Número de lote del fabricante';
COMMENT ON COLUMN inventory.packages.registro_sanitario IS 'Registro sanitario COFEPRIS';

-- Categorías de almacenamiento
CREATE TABLE IF NOT EXISTS inventory.storage_categories (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,
    name VARCHAR(100) NOT NULL,
    max_weight NUMERIC(10,2),
    allow_new_product VARCHAR(20) DEFAULT 'mixed',
    -- Extensiones clínica
    requiere_refrigeracion BOOLEAN DEFAULT false,
    temperatura_min NUMERIC(5,2),
    temperatura_max NUMERIC(5,2),
    es_controlado BOOLEAN DEFAULT false,
    requiere_receta BOOLEAN DEFAULT false,
    -- Control
    created_at TIMESTAMPTZ DEFAULT NOW()
);

COMMENT ON TABLE inventory.storage_categories IS 'Categorías de almacenamiento - FASE 8';
COMMENT ON COLUMN inventory.storage_categories.es_controlado IS 'Medicamento controlado (requiere receta especial)';
COMMENT ON COLUMN inventory.storage_categories.requiere_refrigeracion IS 'Requiere cadena de frío';

-- Reglas de ubicación
CREATE TABLE IF NOT EXISTS inventory.putaway_rules (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,
    name VARCHAR(100),
    product_id UUID,
    category_id UUID REFERENCES inventory.storage_categories(id),
    warehouse_id UUID,
    location_in_id UUID,
    location_out_id UUID,
    sequence INTEGER DEFAULT 10,
    active BOOLEAN DEFAULT true,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

COMMENT ON TABLE inventory.putaway_rules IS 'Reglas de ubicación automática - FASE 8';

-- Estrategias de remoción
CREATE TABLE IF NOT EXISTS inventory.removal_strategies (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    code VARCHAR(20) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

COMMENT ON TABLE inventory.removal_strategies IS 'Estrategias de remoción (FIFO, FEFO, etc.) - FASE 8';

-- ============================================================================
-- CAMPOS ADICIONALES A PRODUCTS (si existe)
-- ============================================================================

DO $$
BEGIN
    IF EXISTS (SELECT 1 FROM information_schema.tables
               WHERE table_schema = 'inventory' AND table_name = 'products') THEN

        -- tracking
        IF NOT EXISTS (SELECT 1 FROM information_schema.columns
                       WHERE table_schema = 'inventory' AND table_name = 'products'
                       AND column_name = 'tracking') THEN
            ALTER TABLE inventory.products ADD COLUMN tracking VARCHAR(20) DEFAULT 'none';
        END IF;

        -- removal_strategy_id
        IF NOT EXISTS (SELECT 1 FROM information_schema.columns
                       WHERE table_schema = 'inventory' AND table_name = 'products'
                       AND column_name = 'removal_strategy_id') THEN
            ALTER TABLE inventory.products ADD COLUMN removal_strategy_id UUID
                REFERENCES inventory.removal_strategies(id);
        END IF;

        -- sale_ok
        IF NOT EXISTS (SELECT 1 FROM information_schema.columns
                       WHERE table_schema = 'inventory' AND table_name = 'products'
                       AND column_name = 'sale_ok') THEN
            ALTER TABLE inventory.products ADD COLUMN sale_ok BOOLEAN DEFAULT true;
        END IF;

        -- purchase_ok
        IF NOT EXISTS (SELECT 1 FROM information_schema.columns
                       WHERE table_schema = 'inventory' AND table_name = 'products'
                       AND column_name = 'purchase_ok') THEN
            ALTER TABLE inventory.products ADD COLUMN purchase_ok BOOLEAN DEFAULT true;
        END IF;

    END IF;
END $$;

-- ============================================================================
-- ÍNDICES
-- ============================================================================

CREATE INDEX IF NOT EXISTS idx_package_types_tenant ON inventory.package_types(tenant_id);

CREATE INDEX IF NOT EXISTS idx_packages_tenant ON inventory.packages(tenant_id);
CREATE INDEX IF NOT EXISTS idx_packages_type ON inventory.packages(package_type_id);
CREATE INDEX IF NOT EXISTS idx_packages_lote ON inventory.packages(tenant_id, lote);
CREATE INDEX IF NOT EXISTS idx_packages_caducidad ON inventory.packages(tenant_id, fecha_caducidad);

CREATE INDEX IF NOT EXISTS idx_storage_categories_tenant ON inventory.storage_categories(tenant_id);
CREATE INDEX IF NOT EXISTS idx_storage_categories_controlado
    ON inventory.storage_categories(tenant_id, es_controlado) WHERE es_controlado = true;

CREATE INDEX IF NOT EXISTS idx_putaway_rules_tenant ON inventory.putaway_rules(tenant_id);
CREATE INDEX IF NOT EXISTS idx_putaway_rules_category ON inventory.putaway_rules(category_id);

-- ============================================================================
-- RLS
-- ============================================================================

ALTER TABLE inventory.package_types ENABLE ROW LEVEL SECURITY;
ALTER TABLE inventory.packages ENABLE ROW LEVEL SECURITY;
ALTER TABLE inventory.storage_categories ENABLE ROW LEVEL SECURITY;
ALTER TABLE inventory.putaway_rules ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS tenant_isolation_package_types ON inventory.package_types;
CREATE POLICY tenant_isolation_package_types ON inventory.package_types
    USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

DROP POLICY IF EXISTS tenant_isolation_packages ON inventory.packages;
CREATE POLICY tenant_isolation_packages ON inventory.packages
    USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

DROP POLICY IF EXISTS tenant_isolation_storage_categories ON inventory.storage_categories;
CREATE POLICY tenant_isolation_storage_categories ON inventory.storage_categories
    USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

DROP POLICY IF EXISTS tenant_isolation_putaway_rules ON inventory.putaway_rules;
CREATE POLICY tenant_isolation_putaway_rules ON inventory.putaway_rules
    USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

-- ============================================================================
-- FIN INVENTORY EXTENSIONS
-- ============================================================================