-- =====================================================
-- SCHEMA: inventory (EXTENSION)
-- PROPOSITO: Extensiones de inventario para construccion
-- MODULOS: MAI-004 (Compras e Inventarios)
-- FECHA: 2025-11-24
-- TIPO: Extension del ERP Generico (MGN-005)
-- =====================================================

-- NOTA: Este archivo contiene SOLO las extensiones especificas
-- de construccion. Las tablas base estan en el ERP Generico.

-- =====================================================
-- TYPES (ENUMs) ADICIONALES
-- =====================================================

CREATE TYPE inventory.warehouse_type_construction AS ENUM (
  'central',         -- Almacen central
  'obra',            -- Almacen en obra
  'temporal',        -- Almacen temporal
  'transito'         -- En transito
);

CREATE TYPE inventory.requisition_status AS ENUM (
  'draft',           -- Borrador
  'submitted',       -- Enviada
  'approved',        -- Aprobada
  'partially_served', -- Parcialmente surtida
  'served',          -- Surtida
  'cancelled'        -- Cancelada
);

-- =====================================================
-- TABLES - EXTENSIONES CONSTRUCCION
-- =====================================================

-- Tabla: almacenes_proyecto (almacen por proyecto/obra)
-- Extiende: inventory.warehouses
CREATE TABLE inventory.almacenes_proyecto (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
  warehouse_id UUID NOT NULL, -- FK a inventory.warehouses (ERP Generico)
  fraccionamiento_id UUID NOT NULL REFERENCES construction.fraccionamientos(id),
  warehouse_type inventory.warehouse_type_construction NOT NULL DEFAULT 'obra',
  location_description TEXT,
  location GEOMETRY(POINT, 4326), -- PostGIS
  responsible_id UUID REFERENCES auth.users(id),
  is_active BOOLEAN NOT NULL DEFAULT TRUE,

  -- Auditoria
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by UUID REFERENCES auth.users(id),
  updated_at TIMESTAMP,
  updated_by UUID REFERENCES auth.users(id),
  deleted_at TIMESTAMP,
  deleted_by UUID REFERENCES auth.users(id),

  CONSTRAINT uq_almacenes_proyecto_warehouse UNIQUE (warehouse_id)
);

-- Tabla: requisiciones_obra (requisiciones desde obra)
CREATE TABLE inventory.requisiciones_obra (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
  fraccionamiento_id UUID NOT NULL REFERENCES construction.fraccionamientos(id),
  requisition_number VARCHAR(30) NOT NULL,
  requisition_date DATE NOT NULL,
  required_date DATE NOT NULL, -- Fecha requerida
  status inventory.requisition_status NOT NULL DEFAULT 'draft',
  priority VARCHAR(20) DEFAULT 'medium', -- low, medium, high, urgent

  -- Solicitante
  requested_by UUID NOT NULL REFERENCES auth.users(id),
  destination_warehouse_id UUID, -- FK a inventory.warehouses

  -- Aprobacion
  approved_by UUID REFERENCES auth.users(id),
  approved_at TIMESTAMP,
  rejection_reason TEXT,

  -- Relacion con OC
  purchase_order_id UUID, -- FK a purchase.purchase_orders

  notes TEXT,

  -- Auditoria
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by UUID REFERENCES auth.users(id),
  updated_at TIMESTAMP,
  updated_by UUID REFERENCES auth.users(id),
  deleted_at TIMESTAMP,
  deleted_by UUID REFERENCES auth.users(id),

  CONSTRAINT uq_requisiciones_obra_number UNIQUE (tenant_id, requisition_number)
);

-- Tabla: requisicion_lineas (lineas de requisicion)
CREATE TABLE inventory.requisicion_lineas (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
  requisicion_id UUID NOT NULL REFERENCES inventory.requisiciones_obra(id) ON DELETE CASCADE,
  product_id UUID NOT NULL, -- FK a inventory.products (ERP Generico)
  concepto_id UUID REFERENCES construction.conceptos(id),
  lote_id UUID REFERENCES construction.lotes(id),
  quantity_requested DECIMAL(12,4) NOT NULL,
  quantity_approved DECIMAL(12,4),
  quantity_served DECIMAL(12,4) DEFAULT 0,
  unit_id UUID, -- FK a core.uom
  notes TEXT,

  -- Auditoria
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by UUID REFERENCES auth.users(id),
  updated_at TIMESTAMP,
  updated_by UUID REFERENCES auth.users(id),
  deleted_at TIMESTAMP,
  deleted_by UUID REFERENCES auth.users(id)
);

-- Tabla: consumos_obra (consumos de materiales por obra/lote)
CREATE TABLE inventory.consumos_obra (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
  stock_move_id UUID, -- FK a inventory.stock_moves (ERP Generico)
  fraccionamiento_id UUID NOT NULL REFERENCES construction.fraccionamientos(id),
  lote_id UUID REFERENCES construction.lotes(id),
  departamento_id UUID REFERENCES construction.departamentos(id),
  concepto_id UUID REFERENCES construction.conceptos(id),
  product_id UUID NOT NULL, -- FK a inventory.products
  quantity DECIMAL(12,4) NOT NULL,
  unit_cost DECIMAL(12,4),
  total_cost DECIMAL(14,2) GENERATED ALWAYS AS (quantity * unit_cost) STORED,
  consumption_date DATE NOT NULL,
  registered_by UUID NOT NULL REFERENCES auth.users(id),
  notes TEXT,

  -- Auditoria
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by UUID REFERENCES auth.users(id),
  updated_at TIMESTAMP,
  updated_by UUID REFERENCES auth.users(id),
  deleted_at TIMESTAMP,
  deleted_by UUID REFERENCES auth.users(id)
);

-- =====================================================
-- INDICES
-- =====================================================

-- Almacenes Proyecto
CREATE INDEX idx_almacenes_proyecto_tenant_id ON inventory.almacenes_proyecto(tenant_id);
CREATE INDEX idx_almacenes_proyecto_warehouse_id ON inventory.almacenes_proyecto(warehouse_id);
CREATE INDEX idx_almacenes_proyecto_fraccionamiento_id ON inventory.almacenes_proyecto(fraccionamiento_id);

-- Requisiciones Obra
CREATE INDEX idx_requisiciones_obra_tenant_id ON inventory.requisiciones_obra(tenant_id);
CREATE INDEX idx_requisiciones_obra_fraccionamiento_id ON inventory.requisiciones_obra(fraccionamiento_id);
CREATE INDEX idx_requisiciones_obra_status ON inventory.requisiciones_obra(status);
CREATE INDEX idx_requisiciones_obra_date ON inventory.requisiciones_obra(requisition_date);
CREATE INDEX idx_requisiciones_obra_required_date ON inventory.requisiciones_obra(required_date);

-- Requisicion Lineas
CREATE INDEX idx_requisicion_lineas_tenant_id ON inventory.requisicion_lineas(tenant_id);
CREATE INDEX idx_requisicion_lineas_requisicion_id ON inventory.requisicion_lineas(requisicion_id);
CREATE INDEX idx_requisicion_lineas_product_id ON inventory.requisicion_lineas(product_id);

-- Consumos Obra
CREATE INDEX idx_consumos_obra_tenant_id ON inventory.consumos_obra(tenant_id);
CREATE INDEX idx_consumos_obra_fraccionamiento_id ON inventory.consumos_obra(fraccionamiento_id);
CREATE INDEX idx_consumos_obra_lote_id ON inventory.consumos_obra(lote_id);
CREATE INDEX idx_consumos_obra_concepto_id ON inventory.consumos_obra(concepto_id);
CREATE INDEX idx_consumos_obra_product_id ON inventory.consumos_obra(product_id);
CREATE INDEX idx_consumos_obra_date ON inventory.consumos_obra(consumption_date);

-- =====================================================
-- ROW LEVEL SECURITY (RLS)
-- =====================================================

ALTER TABLE inventory.almacenes_proyecto ENABLE ROW LEVEL SECURITY;
ALTER TABLE inventory.requisiciones_obra ENABLE ROW LEVEL SECURITY;
ALTER TABLE inventory.requisicion_lineas ENABLE ROW LEVEL SECURITY;
ALTER TABLE inventory.consumos_obra ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation_almacenes_proyecto ON inventory.almacenes_proyecto
  USING (tenant_id = get_current_tenant_id());

CREATE POLICY tenant_isolation_requisiciones_obra ON inventory.requisiciones_obra
  USING (tenant_id = get_current_tenant_id());

CREATE POLICY tenant_isolation_requisicion_lineas ON inventory.requisicion_lineas
  USING (tenant_id = get_current_tenant_id());

CREATE POLICY tenant_isolation_consumos_obra ON inventory.consumos_obra
  USING (tenant_id = get_current_tenant_id());

-- =====================================================
-- COMENTARIOS
-- =====================================================

COMMENT ON TABLE inventory.almacenes_proyecto IS 'Extension: almacenes por proyecto de construccion';
COMMENT ON TABLE inventory.requisiciones_obra IS 'Extension: requisiciones de material desde obra';
COMMENT ON TABLE inventory.requisicion_lineas IS 'Extension: lineas de requisicion de obra';
COMMENT ON TABLE inventory.consumos_obra IS 'Extension: consumos de materiales por obra/lote';

-- =====================================================
-- FIN DE EXTENSIONES INVENTORY
-- =====================================================