erp-construccion/docs/04-modelado/database-design/schemas/estimates-schema-ddl.sql

-- =====================================================
-- SCHEMA: estimates
-- PROPOSITO: Estimaciones, anticipos, retenciones, generadores
-- MODULOS: MAI-008 (Estimaciones y Facturacion)
-- FECHA: 2025-11-24
-- ESPECIFICO: 100% construccion
-- =====================================================

-- Crear schema
CREATE SCHEMA IF NOT EXISTS estimates;

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

CREATE TYPE estimates.estimate_status AS ENUM (
  'draft',           -- Borrador
  'submitted',       -- Enviada para revision
  'reviewed',        -- Revisada
  'approved',        -- Aprobada
  'invoiced',        -- Facturada
  'paid',            -- Pagada
  'rejected',        -- Rechazada
  'cancelled'        -- Cancelada
);

CREATE TYPE estimates.advance_type AS ENUM (
  'initial',         -- Anticipo inicial
  'progress',        -- Anticipo por avance
  'materials'        -- Anticipo para materiales
);

CREATE TYPE estimates.retention_type AS ENUM (
  'guarantee',       -- Retencion de garantia (fondo)
  'tax',             -- Retencion fiscal
  'penalty',         -- Penalizacion
  'other'            -- Otra retencion
);

CREATE TYPE estimates.generator_status AS ENUM (
  'draft',           -- Borrador
  'in_progress',     -- En captura
  'completed',       -- Completado
  'approved'         -- Aprobado
);

-- =====================================================
-- TABLES - ESTIMACIONES
-- =====================================================

-- Tabla: estimaciones (estimaciones de obra)
CREATE TABLE estimates.estimaciones (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
  contrato_id UUID NOT NULL REFERENCES construction.contratos(id),
  fraccionamiento_id UUID NOT NULL REFERENCES construction.fraccionamientos(id),
  estimate_number VARCHAR(30) NOT NULL,
  period_start DATE NOT NULL,
  period_end DATE NOT NULL,
  sequence_number INTEGER NOT NULL, -- Numero de estimacion (1, 2, 3...)
  status estimates.estimate_status NOT NULL DEFAULT 'draft',

  -- Montos
  subtotal DECIMAL(16,2) DEFAULT 0,
  advance_amount DECIMAL(16,2) DEFAULT 0, -- Amortizacion de anticipo
  retention_amount DECIMAL(16,2) DEFAULT 0, -- Retencion
  tax_amount DECIMAL(16,2) DEFAULT 0, -- IVA
  total_amount DECIMAL(16,2) DEFAULT 0, -- Neto a pagar

  -- Aprobaciones
  submitted_at TIMESTAMP,
  submitted_by UUID REFERENCES auth.users(id),
  reviewed_at TIMESTAMP,
  reviewed_by UUID REFERENCES auth.users(id),
  approved_at TIMESTAMP,
  approved_by UUID REFERENCES auth.users(id),

  -- Facturacion
  invoice_id UUID, -- FK a facturas (financial.invoices)
  invoiced_at TIMESTAMP,
  paid_at TIMESTAMP,

  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_estimaciones_number_tenant UNIQUE (tenant_id, estimate_number),
  CONSTRAINT uq_estimaciones_sequence_contrato UNIQUE (contrato_id, sequence_number),
  CONSTRAINT chk_estimaciones_period CHECK (period_end >= period_start)
);

-- Tabla: estimacion_conceptos (lineas de estimacion)
CREATE TABLE estimates.estimacion_conceptos (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
  estimacion_id UUID NOT NULL REFERENCES estimates.estimaciones(id) ON DELETE CASCADE,
  concepto_id UUID NOT NULL REFERENCES construction.conceptos(id),
  contrato_partida_id UUID REFERENCES construction.contrato_partidas(id),

  -- Cantidades
  quantity_contract DECIMAL(12,4) DEFAULT 0, -- Cantidad contratada
  quantity_previous DECIMAL(12,4) DEFAULT 0, -- Acumulado anterior
  quantity_current DECIMAL(12,4) DEFAULT 0, -- Esta estimacion
  quantity_accumulated DECIMAL(12,4) GENERATED ALWAYS AS (quantity_previous + quantity_current) STORED,

  -- Precios
  unit_price DECIMAL(12,4) NOT NULL DEFAULT 0,
  amount_current DECIMAL(14,2) GENERATED ALWAYS AS (quantity_current * unit_price) STORED,

  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_est_conceptos_estimacion_concepto UNIQUE (estimacion_id, concepto_id)
);

-- Tabla: generadores (soporte de cantidades)
CREATE TABLE estimates.generadores (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
  estimacion_concepto_id UUID NOT NULL REFERENCES estimates.estimacion_conceptos(id) ON DELETE CASCADE,
  generator_number VARCHAR(30) NOT NULL,
  description TEXT,
  status estimates.generator_status NOT NULL DEFAULT 'draft',

  -- Ubicacion
  lote_id UUID REFERENCES construction.lotes(id),
  departamento_id UUID REFERENCES construction.departamentos(id),
  location_description VARCHAR(255), -- Descripcion libre de ubicacion

  -- Calculo
  quantity DECIMAL(12,4) NOT NULL DEFAULT 0,
  formula TEXT, -- Formula de calculo (ej: "3.5 x 2.0 x 0.15")

  -- Evidencia
  photo_url VARCHAR(500),
  sketch_url VARCHAR(500),

  captured_by UUID NOT NULL REFERENCES auth.users(id),
  captured_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  approved_by UUID REFERENCES auth.users(id),
  approved_at TIMESTAMP,

  -- 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)
);

-- =====================================================
-- TABLES - ANTICIPOS
-- =====================================================

-- Tabla: anticipos (anticipos otorgados)
CREATE TABLE estimates.anticipos (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
  contrato_id UUID NOT NULL REFERENCES construction.contratos(id),
  advance_type estimates.advance_type NOT NULL DEFAULT 'initial',
  advance_number VARCHAR(30) NOT NULL,
  advance_date DATE NOT NULL,

  -- Montos
  gross_amount DECIMAL(16,2) NOT NULL, -- Monto bruto
  tax_amount DECIMAL(16,2) DEFAULT 0, -- IVA
  net_amount DECIMAL(16,2) NOT NULL, -- Monto neto

  -- Amortizacion
  amortization_percentage DECIMAL(5,2) DEFAULT 0, -- % a amortizar por estimacion
  amortized_amount DECIMAL(16,2) DEFAULT 0, -- Total amortizado
  pending_amount DECIMAL(16,2) GENERATED ALWAYS AS (net_amount - amortized_amount) STORED,
  is_fully_amortized BOOLEAN DEFAULT FALSE,

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

  -- Pago
  paid_at TIMESTAMP,
  payment_reference VARCHAR(100),

  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_anticipos_number_tenant UNIQUE (tenant_id, advance_number)
);

-- Tabla: amortizaciones (amortizaciones de anticipos)
CREATE TABLE estimates.amortizaciones (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
  anticipo_id UUID NOT NULL REFERENCES estimates.anticipos(id),
  estimacion_id UUID NOT NULL REFERENCES estimates.estimaciones(id),
  amount DECIMAL(16,2) NOT NULL,
  amortization_date DATE NOT NULL,
  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_amortizaciones_anticipo_estimacion UNIQUE (anticipo_id, estimacion_id)
);

-- =====================================================
-- TABLES - RETENCIONES
-- =====================================================

-- Tabla: retenciones (retenciones aplicadas)
CREATE TABLE estimates.retenciones (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
  estimacion_id UUID NOT NULL REFERENCES estimates.estimaciones(id),
  retention_type estimates.retention_type NOT NULL,
  description VARCHAR(255) NOT NULL,
  percentage DECIMAL(5,2), -- Porcentaje de retencion
  amount DECIMAL(16,2) NOT NULL, -- Monto retenido
  release_date DATE, -- Fecha de liberacion programada
  released_at TIMESTAMP, -- Fecha de liberacion real
  released_amount DECIMAL(16,2), -- Monto liberado
  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: fondo_garantia (acumulado de fondo de garantia)
CREATE TABLE estimates.fondo_garantia (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
  contrato_id UUID NOT NULL REFERENCES construction.contratos(id),
  accumulated_amount DECIMAL(16,2) DEFAULT 0, -- Total acumulado
  released_amount DECIMAL(16,2) DEFAULT 0, -- Total liberado
  pending_amount DECIMAL(16,2) GENERATED ALWAYS AS (accumulated_amount - released_amount) STORED,

  -- Liberacion
  release_date DATE, -- Fecha programada de liberacion
  released_at TIMESTAMP,
  released_by UUID REFERENCES auth.users(id),

  -- 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_fondo_garantia_contrato UNIQUE (contrato_id)
);

-- =====================================================
-- TABLES - WORKFLOW
-- =====================================================

-- Tabla: estimacion_workflow (historial de workflow)
CREATE TABLE estimates.estimacion_workflow (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
  estimacion_id UUID NOT NULL REFERENCES estimates.estimaciones(id) ON DELETE CASCADE,
  from_status estimates.estimate_status,
  to_status estimates.estimate_status NOT NULL,
  action VARCHAR(50) NOT NULL, -- submit, review, approve, reject, etc.
  comments TEXT,
  performed_by UUID NOT NULL REFERENCES auth.users(id),
  performed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

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

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

-- Estimaciones
CREATE INDEX idx_estimaciones_tenant_id ON estimates.estimaciones(tenant_id);
CREATE INDEX idx_estimaciones_contrato_id ON estimates.estimaciones(contrato_id);
CREATE INDEX idx_estimaciones_fraccionamiento_id ON estimates.estimaciones(fraccionamiento_id);
CREATE INDEX idx_estimaciones_status ON estimates.estimaciones(status);
CREATE INDEX idx_estimaciones_period ON estimates.estimaciones(period_start, period_end);
CREATE INDEX idx_estimaciones_active ON estimates.estimaciones(tenant_id) WHERE deleted_at IS NULL;

-- Estimacion Conceptos
CREATE INDEX idx_est_conceptos_tenant_id ON estimates.estimacion_conceptos(tenant_id);
CREATE INDEX idx_est_conceptos_estimacion_id ON estimates.estimacion_conceptos(estimacion_id);
CREATE INDEX idx_est_conceptos_concepto_id ON estimates.estimacion_conceptos(concepto_id);

-- Generadores
CREATE INDEX idx_generadores_tenant_id ON estimates.generadores(tenant_id);
CREATE INDEX idx_generadores_est_concepto_id ON estimates.generadores(estimacion_concepto_id);
CREATE INDEX idx_generadores_lote_id ON estimates.generadores(lote_id);
CREATE INDEX idx_generadores_status ON estimates.generadores(status);

-- Anticipos
CREATE INDEX idx_anticipos_tenant_id ON estimates.anticipos(tenant_id);
CREATE INDEX idx_anticipos_contrato_id ON estimates.anticipos(contrato_id);
CREATE INDEX idx_anticipos_type ON estimates.anticipos(advance_type);
CREATE INDEX idx_anticipos_pending ON estimates.anticipos(tenant_id) WHERE is_fully_amortized = FALSE;

-- Amortizaciones
CREATE INDEX idx_amortizaciones_tenant_id ON estimates.amortizaciones(tenant_id);
CREATE INDEX idx_amortizaciones_anticipo_id ON estimates.amortizaciones(anticipo_id);
CREATE INDEX idx_amortizaciones_estimacion_id ON estimates.amortizaciones(estimacion_id);

-- Retenciones
CREATE INDEX idx_retenciones_tenant_id ON estimates.retenciones(tenant_id);
CREATE INDEX idx_retenciones_estimacion_id ON estimates.retenciones(estimacion_id);
CREATE INDEX idx_retenciones_type ON estimates.retenciones(retention_type);
CREATE INDEX idx_retenciones_pending ON estimates.retenciones(tenant_id) WHERE released_at IS NULL;

-- Fondo Garantia
CREATE INDEX idx_fondo_garantia_tenant_id ON estimates.fondo_garantia(tenant_id);
CREATE INDEX idx_fondo_garantia_contrato_id ON estimates.fondo_garantia(contrato_id);

-- Workflow
CREATE INDEX idx_est_workflow_tenant_id ON estimates.estimacion_workflow(tenant_id);
CREATE INDEX idx_est_workflow_estimacion_id ON estimates.estimacion_workflow(estimacion_id);
CREATE INDEX idx_est_workflow_performed_at ON estimates.estimacion_workflow(performed_at);

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

-- Habilitar RLS
ALTER TABLE estimates.estimaciones ENABLE ROW LEVEL SECURITY;
ALTER TABLE estimates.estimacion_conceptos ENABLE ROW LEVEL SECURITY;
ALTER TABLE estimates.generadores ENABLE ROW LEVEL SECURITY;
ALTER TABLE estimates.anticipos ENABLE ROW LEVEL SECURITY;
ALTER TABLE estimates.amortizaciones ENABLE ROW LEVEL SECURITY;
ALTER TABLE estimates.retenciones ENABLE ROW LEVEL SECURITY;
ALTER TABLE estimates.fondo_garantia ENABLE ROW LEVEL SECURITY;
ALTER TABLE estimates.estimacion_workflow ENABLE ROW LEVEL SECURITY;

-- Policies
CREATE POLICY tenant_isolation_estimaciones ON estimates.estimaciones
  USING (tenant_id = get_current_tenant_id());

CREATE POLICY tenant_isolation_est_conceptos ON estimates.estimacion_conceptos
  USING (tenant_id = get_current_tenant_id());

CREATE POLICY tenant_isolation_generadores ON estimates.generadores
  USING (tenant_id = get_current_tenant_id());

CREATE POLICY tenant_isolation_anticipos ON estimates.anticipos
  USING (tenant_id = get_current_tenant_id());

CREATE POLICY tenant_isolation_amortizaciones ON estimates.amortizaciones
  USING (tenant_id = get_current_tenant_id());

CREATE POLICY tenant_isolation_retenciones ON estimates.retenciones
  USING (tenant_id = get_current_tenant_id());

CREATE POLICY tenant_isolation_fondo_garantia ON estimates.fondo_garantia
  USING (tenant_id = get_current_tenant_id());

CREATE POLICY tenant_isolation_est_workflow ON estimates.estimacion_workflow
  USING (tenant_id = get_current_tenant_id());

-- =====================================================
-- FUNCTIONS
-- =====================================================

-- Funcion: calcular totales de estimacion
CREATE OR REPLACE FUNCTION estimates.calculate_estimate_totals(p_estimacion_id UUID)
RETURNS VOID AS $$
DECLARE
  v_subtotal DECIMAL(16,2);
  v_advance DECIMAL(16,2);
  v_retention DECIMAL(16,2);
  v_tax_rate DECIMAL(5,2) := 0.16; -- IVA Mexico
  v_tax DECIMAL(16,2);
  v_total DECIMAL(16,2);
BEGIN
  -- Calcular subtotal
  SELECT COALESCE(SUM(amount_current), 0) INTO v_subtotal
  FROM estimates.estimacion_conceptos
  WHERE estimacion_id = p_estimacion_id AND deleted_at IS NULL;

  -- Calcular amortizacion de anticipos
  SELECT COALESCE(SUM(amount), 0) INTO v_advance
  FROM estimates.amortizaciones
  WHERE estimacion_id = p_estimacion_id AND deleted_at IS NULL;

  -- Calcular retenciones
  SELECT COALESCE(SUM(amount), 0) INTO v_retention
  FROM estimates.retenciones
  WHERE estimacion_id = p_estimacion_id AND deleted_at IS NULL;

  -- Calcular IVA sobre subtotal
  v_tax := v_subtotal * v_tax_rate;

  -- Calcular total neto
  v_total := v_subtotal + v_tax - v_advance - v_retention;

  -- Actualizar estimacion
  UPDATE estimates.estimaciones
  SET
    subtotal = v_subtotal,
    advance_amount = v_advance,
    retention_amount = v_retention,
    tax_amount = v_tax,
    total_amount = v_total,
    updated_at = CURRENT_TIMESTAMP
  WHERE id = p_estimacion_id;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION estimates.calculate_estimate_totals IS 'Recalcula los totales de una estimacion';

-- Funcion: actualizar amortizacion de anticipo
CREATE OR REPLACE FUNCTION estimates.update_advance_amortization()
RETURNS TRIGGER AS $$
BEGIN
  IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
    UPDATE estimates.anticipos
    SET
      amortized_amount = (
        SELECT COALESCE(SUM(amount), 0)
        FROM estimates.amortizaciones
        WHERE anticipo_id = NEW.anticipo_id AND deleted_at IS NULL
      ),
      is_fully_amortized = (
        SELECT COALESCE(SUM(amount), 0) >= net_amount
        FROM estimates.amortizaciones
        WHERE anticipo_id = NEW.anticipo_id AND deleted_at IS NULL
      ),
      updated_at = CURRENT_TIMESTAMP
    WHERE id = NEW.anticipo_id;
  END IF;

  IF TG_OP = 'DELETE' THEN
    UPDATE estimates.anticipos
    SET
      amortized_amount = (
        SELECT COALESCE(SUM(amount), 0)
        FROM estimates.amortizaciones
        WHERE anticipo_id = OLD.anticipo_id AND deleted_at IS NULL
      ),
      is_fully_amortized = FALSE,
      updated_at = CURRENT_TIMESTAMP
    WHERE id = OLD.anticipo_id;
  END IF;

  RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_update_advance_amortization
  AFTER INSERT OR UPDATE OR DELETE ON estimates.amortizaciones
  FOR EACH ROW
  EXECUTE FUNCTION estimates.update_advance_amortization();

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

COMMENT ON SCHEMA estimates IS 'Schema de estimaciones, anticipos y retenciones de obra';
COMMENT ON TABLE estimates.estimaciones IS 'Estimaciones de obra periodicas';
COMMENT ON TABLE estimates.estimacion_conceptos IS 'Lineas de concepto por estimacion';
COMMENT ON TABLE estimates.generadores IS 'Generadores de cantidades para estimaciones';
COMMENT ON TABLE estimates.anticipos IS 'Anticipos otorgados a subcontratistas';
COMMENT ON TABLE estimates.amortizaciones IS 'Amortizaciones de anticipos por estimacion';
COMMENT ON TABLE estimates.retenciones IS 'Retenciones aplicadas a estimaciones';
COMMENT ON TABLE estimates.fondo_garantia IS 'Fondo de garantia acumulado por contrato';
COMMENT ON TABLE estimates.estimacion_workflow IS 'Historial de workflow de estimaciones';

-- =====================================================
-- FIN DEL SCHEMA ESTIMATES
-- =====================================================