erp-core/database/ddl/11-crm.sql

-- =====================================================
-- SCHEMA: crm
-- PROPOSITO: Customer Relationship Management
-- MODULOS: MGN-CRM (CRM)
-- FECHA: 2025-11-24
-- =====================================================

-- Crear schema
CREATE SCHEMA IF NOT EXISTS crm;

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

CREATE TYPE crm.lead_status AS ENUM (
  'new',
  'contacted',
  'qualified',
  'converted',
  'lost'
);

CREATE TYPE crm.opportunity_status AS ENUM (
  'open',
  'won',
  'lost'
);

CREATE TYPE crm.activity_type AS ENUM (
  'call',
  'email',
  'meeting',
  'task',
  'note'
);

CREATE TYPE crm.lead_source AS ENUM (
  'website',
  'phone',
  'email',
  'referral',
  'social_media',
  'advertising',
  'event',
  'other'
);

-- =====================================================
-- TABLES
-- =====================================================

-- Tabla: lead_stages (Etapas del pipeline de leads)
CREATE TABLE crm.lead_stages (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,

  name VARCHAR(100) NOT NULL,
  sequence INTEGER NOT NULL DEFAULT 10,
  is_won BOOLEAN DEFAULT FALSE,
  probability DECIMAL(5, 2) DEFAULT 0,
  requirements TEXT,

  active BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,

  UNIQUE(tenant_id, name)
);

-- Tabla: opportunity_stages (Etapas del pipeline de oportunidades)
CREATE TABLE crm.opportunity_stages (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,

  name VARCHAR(100) NOT NULL,
  sequence INTEGER NOT NULL DEFAULT 10,
  is_won BOOLEAN DEFAULT FALSE,
  probability DECIMAL(5, 2) DEFAULT 0,
  requirements TEXT,

  active BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,

  UNIQUE(tenant_id, name)
);

-- Tabla: lost_reasons (Razones de perdida)
CREATE TABLE crm.lost_reasons (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,

  name VARCHAR(100) NOT NULL,
  description TEXT,

  active BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,

  UNIQUE(tenant_id, name)
);

-- Tabla: leads (Prospectos/Leads)
CREATE TABLE crm.leads (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
  company_id UUID NOT NULL REFERENCES auth.companies(id) ON DELETE CASCADE,

  -- Numeracion
  name VARCHAR(255) NOT NULL,
  ref VARCHAR(100),

  -- Contacto
  contact_name VARCHAR(255),
  email VARCHAR(255),
  phone VARCHAR(50),
  mobile VARCHAR(50),
  website VARCHAR(255),

  -- Empresa del prospecto
  company_name VARCHAR(255),
  job_position VARCHAR(100),
  industry VARCHAR(100),
  employee_count VARCHAR(50),
  annual_revenue DECIMAL(15, 2),

  -- Direccion
  street VARCHAR(255),
  city VARCHAR(100),
  state VARCHAR(100),
  zip VARCHAR(20),
  country VARCHAR(100),

  -- Pipeline
  stage_id UUID REFERENCES crm.lead_stages(id),
  status crm.lead_status NOT NULL DEFAULT 'new',

  -- Asignacion
  user_id UUID REFERENCES auth.users(id),
  sales_team_id UUID REFERENCES sales.sales_teams(id),

  -- Origen
  source crm.lead_source,
  campaign_id UUID, -- Para futuro modulo marketing
  medium VARCHAR(100),

  -- Valoracion
  priority INTEGER DEFAULT 0 CHECK (priority >= 0 AND priority <= 3),
  probability DECIMAL(5, 2) DEFAULT 0,
  expected_revenue DECIMAL(15, 2),

  -- Fechas
  date_open TIMESTAMP WITH TIME ZONE,
  date_closed TIMESTAMP WITH TIME ZONE,
  date_deadline DATE,
  date_last_activity TIMESTAMP WITH TIME ZONE,

  -- Conversion
  partner_id UUID REFERENCES core.partners(id),
  opportunity_id UUID, -- Se llena al convertir

  -- Perdida
  lost_reason_id UUID REFERENCES crm.lost_reasons(id),
  lost_notes TEXT,

  -- Notas
  description TEXT,
  notes TEXT,
  tags VARCHAR(255)[],

  -- Auditoria
  created_by UUID REFERENCES auth.users(id),
  updated_by UUID REFERENCES auth.users(id),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Tabla: opportunities (Oportunidades de venta)
CREATE TABLE crm.opportunities (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
  company_id UUID NOT NULL REFERENCES auth.companies(id) ON DELETE CASCADE,

  -- Numeracion
  name VARCHAR(255) NOT NULL,
  ref VARCHAR(100),

  -- Cliente
  partner_id UUID NOT NULL REFERENCES core.partners(id),
  contact_name VARCHAR(255),
  email VARCHAR(255),
  phone VARCHAR(50),

  -- Pipeline
  stage_id UUID REFERENCES crm.opportunity_stages(id),
  status crm.opportunity_status NOT NULL DEFAULT 'open',

  -- Asignacion
  user_id UUID REFERENCES auth.users(id),
  sales_team_id UUID REFERENCES sales.sales_teams(id),

  -- Valoracion
  priority INTEGER DEFAULT 0 CHECK (priority >= 0 AND priority <= 3),
  probability DECIMAL(5, 2) DEFAULT 0,
  expected_revenue DECIMAL(15, 2),
  recurring_revenue DECIMAL(15, 2),
  recurring_plan VARCHAR(50),

  -- Fechas
  date_deadline DATE,
  date_closed TIMESTAMP WITH TIME ZONE,
  date_last_activity TIMESTAMP WITH TIME ZONE,

  -- Origen (si viene de lead)
  lead_id UUID REFERENCES crm.leads(id),
  source crm.lead_source,
  campaign_id UUID,
  medium VARCHAR(100),

  -- Cierre
  lost_reason_id UUID REFERENCES crm.lost_reasons(id),
  lost_notes TEXT,

  -- Relaciones
  quotation_id UUID REFERENCES sales.quotations(id),
  order_id UUID REFERENCES sales.sales_orders(id),

  -- Notas
  description TEXT,
  notes TEXT,
  tags VARCHAR(255)[],

  -- Auditoria
  created_by UUID REFERENCES auth.users(id),
  updated_by UUID REFERENCES auth.users(id),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Actualizar referencia circular en leads
ALTER TABLE crm.leads ADD CONSTRAINT fk_leads_opportunity
  FOREIGN KEY (opportunity_id) REFERENCES crm.opportunities(id);

-- Tabla: crm_activities (Actividades CRM)
CREATE TABLE crm.activities (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,

  -- Referencia polimorfica
  res_model VARCHAR(100) NOT NULL,
  res_id UUID NOT NULL,

  -- Actividad
  activity_type crm.activity_type NOT NULL,
  summary VARCHAR(255),
  description TEXT,

  -- Fechas
  date_deadline DATE,
  date_done TIMESTAMP WITH TIME ZONE,

  -- Asignacion
  user_id UUID REFERENCES auth.users(id),
  assigned_to UUID REFERENCES auth.users(id),

  -- Estado
  done BOOLEAN DEFAULT FALSE,

  -- Auditoria
  created_by UUID REFERENCES auth.users(id),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- =====================================================
-- INDEXES
-- =====================================================

CREATE INDEX idx_lead_stages_tenant ON crm.lead_stages(tenant_id);
CREATE INDEX idx_opportunity_stages_tenant ON crm.opportunity_stages(tenant_id);
CREATE INDEX idx_lost_reasons_tenant ON crm.lost_reasons(tenant_id);

CREATE INDEX idx_leads_tenant ON crm.leads(tenant_id);
CREATE INDEX idx_leads_company ON crm.leads(company_id);
CREATE INDEX idx_leads_status ON crm.leads(status);
CREATE INDEX idx_leads_stage ON crm.leads(stage_id);
CREATE INDEX idx_leads_user ON crm.leads(user_id);
CREATE INDEX idx_leads_partner ON crm.leads(partner_id);
CREATE INDEX idx_leads_email ON crm.leads(email);

CREATE INDEX idx_opportunities_tenant ON crm.opportunities(tenant_id);
CREATE INDEX idx_opportunities_company ON crm.opportunities(company_id);
CREATE INDEX idx_opportunities_status ON crm.opportunities(status);
CREATE INDEX idx_opportunities_stage ON crm.opportunities(stage_id);
CREATE INDEX idx_opportunities_user ON crm.opportunities(user_id);
CREATE INDEX idx_opportunities_partner ON crm.opportunities(partner_id);

CREATE INDEX idx_crm_activities_tenant ON crm.activities(tenant_id);
CREATE INDEX idx_crm_activities_model ON crm.activities(res_model, res_id);
CREATE INDEX idx_crm_activities_user ON crm.activities(assigned_to);
CREATE INDEX idx_crm_activities_deadline ON crm.activities(date_deadline);

-- =====================================================
-- TRIGGERS
-- =====================================================

CREATE TRIGGER update_lead_stages_timestamp
  BEFORE UPDATE ON crm.lead_stages
  FOR EACH ROW EXECUTE FUNCTION core.update_timestamp();

CREATE TRIGGER update_opportunity_stages_timestamp
  BEFORE UPDATE ON crm.opportunity_stages
  FOR EACH ROW EXECUTE FUNCTION core.update_timestamp();

CREATE TRIGGER update_leads_timestamp
  BEFORE UPDATE ON crm.leads
  FOR EACH ROW EXECUTE FUNCTION core.update_timestamp();

CREATE TRIGGER update_opportunities_timestamp
  BEFORE UPDATE ON crm.opportunities
  FOR EACH ROW EXECUTE FUNCTION core.update_timestamp();

CREATE TRIGGER update_crm_activities_timestamp
  BEFORE UPDATE ON crm.activities
  FOR EACH ROW EXECUTE FUNCTION core.update_timestamp();

-- =====================================================
-- ROW LEVEL SECURITY
-- =====================================================

-- Habilitar RLS
ALTER TABLE crm.lead_stages ENABLE ROW LEVEL SECURITY;
ALTER TABLE crm.opportunity_stages ENABLE ROW LEVEL SECURITY;
ALTER TABLE crm.lost_reasons ENABLE ROW LEVEL SECURITY;
ALTER TABLE crm.leads ENABLE ROW LEVEL SECURITY;
ALTER TABLE crm.opportunities ENABLE ROW LEVEL SECURITY;
ALTER TABLE crm.activities ENABLE ROW LEVEL SECURITY;

-- Políticas de aislamiento por tenant
CREATE POLICY tenant_isolation_lead_stages ON crm.lead_stages
  USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_opportunity_stages ON crm.opportunity_stages
  USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_lost_reasons ON crm.lost_reasons
  USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_leads ON crm.leads
  USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_opportunities ON crm.opportunities
  USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_crm_activities ON crm.activities
  USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

-- =====================================================
-- COR-014: Predictive Lead Scoring (PLS)
-- Sistema de scoring predictivo para leads/oportunidades
-- =====================================================

-- Tabla: lead_scoring_rules (Reglas de scoring)
CREATE TABLE crm.lead_scoring_rules (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,

  name VARCHAR(100) NOT NULL,
  description TEXT,

  -- Tipo de regla
  rule_type VARCHAR(50) NOT NULL,  -- field_value, activity, demographic, behavioral

  -- Condicion (JSON)
  -- Ejemplo: {"field": "industry", "operator": "equals", "value": "technology"}
  -- Ejemplo: {"field": "annual_revenue", "operator": "greater_than", "value": 1000000}
  condition JSONB NOT NULL,

  -- Puntuacion
  score_value INTEGER NOT NULL,  -- Puede ser negativo para penalizaciones

  -- Peso para ML (0-1)
  weight DECIMAL(3, 2) DEFAULT 1.0,

  -- Control
  active BOOLEAN DEFAULT TRUE,
  sequence INTEGER DEFAULT 10,

  -- Auditoria
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  created_by UUID REFERENCES auth.users(id),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,

  CONSTRAINT uq_lead_scoring_rules_name_tenant UNIQUE (tenant_id, name),
  CONSTRAINT chk_lead_scoring_rules_weight CHECK (weight >= 0 AND weight <= 1)
);

-- Tabla: lead_scoring_history (Historial de scoring)
CREATE TABLE crm.lead_scoring_history (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,

  -- Referencia al lead/oportunidad
  lead_id UUID REFERENCES crm.leads(id) ON DELETE CASCADE,
  opportunity_id UUID REFERENCES crm.opportunities(id) ON DELETE CASCADE,

  -- Scores
  score_before INTEGER,
  score_after INTEGER NOT NULL,
  score_delta INTEGER GENERATED ALWAYS AS (score_after - COALESCE(score_before, 0)) STORED,

  -- Regla aplicada (opcional)
  rule_id UUID REFERENCES crm.lead_scoring_rules(id),

  -- Razon del cambio
  reason VARCHAR(255),

  -- Auditoria
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  created_by UUID REFERENCES auth.users(id),

  CONSTRAINT chk_lead_scoring_history_ref CHECK (
    (lead_id IS NOT NULL AND opportunity_id IS NULL) OR
    (lead_id IS NULL AND opportunity_id IS NOT NULL)
  )
);

-- Agregar campos de scoring a leads
ALTER TABLE crm.leads
ADD COLUMN IF NOT EXISTS automated_score INTEGER DEFAULT 0,
ADD COLUMN IF NOT EXISTS manual_score_adjustment INTEGER DEFAULT 0,
ADD COLUMN IF NOT EXISTS total_score INTEGER GENERATED ALWAYS AS (automated_score + manual_score_adjustment) STORED,
ADD COLUMN IF NOT EXISTS score_calculated_at TIMESTAMP WITH TIME ZONE,
ADD COLUMN IF NOT EXISTS score_tier VARCHAR(20);  -- hot, warm, cold

-- Agregar campos de scoring a opportunities
ALTER TABLE crm.opportunities
ADD COLUMN IF NOT EXISTS automated_score INTEGER DEFAULT 0,
ADD COLUMN IF NOT EXISTS manual_score_adjustment INTEGER DEFAULT 0,
ADD COLUMN IF NOT EXISTS total_score INTEGER GENERATED ALWAYS AS (automated_score + manual_score_adjustment) STORED,
ADD COLUMN IF NOT EXISTS score_calculated_at TIMESTAMP WITH TIME ZONE,
ADD COLUMN IF NOT EXISTS score_tier VARCHAR(20);  -- hot, warm, cold

-- Indices para scoring
CREATE INDEX idx_lead_scoring_rules_tenant ON crm.lead_scoring_rules(tenant_id);
CREATE INDEX idx_lead_scoring_rules_active ON crm.lead_scoring_rules(active) WHERE active = TRUE;
CREATE INDEX idx_lead_scoring_history_lead ON crm.lead_scoring_history(lead_id);
CREATE INDEX idx_lead_scoring_history_opportunity ON crm.lead_scoring_history(opportunity_id);
CREATE INDEX idx_leads_total_score ON crm.leads(total_score DESC);
CREATE INDEX idx_leads_score_tier ON crm.leads(score_tier);
CREATE INDEX idx_opportunities_total_score ON crm.opportunities(total_score DESC);

-- RLS para scoring tables
ALTER TABLE crm.lead_scoring_rules ENABLE ROW LEVEL SECURITY;
ALTER TABLE crm.lead_scoring_history ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation_lead_scoring_rules ON crm.lead_scoring_rules
  USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_lead_scoring_history ON crm.lead_scoring_history
  USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

-- Funcion: calculate_lead_score
CREATE OR REPLACE FUNCTION crm.calculate_lead_score(p_lead_id UUID)
RETURNS INTEGER AS $$
DECLARE
  v_lead RECORD;
  v_rule RECORD;
  v_total_score INTEGER := 0;
  v_condition JSONB;
  v_field_value TEXT;
  v_matches BOOLEAN;
BEGIN
  -- Obtener lead
  SELECT * INTO v_lead FROM crm.leads WHERE id = p_lead_id;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'Lead % not found', p_lead_id;
  END IF;

  -- Evaluar cada regla activa
  FOR v_rule IN
    SELECT * FROM crm.lead_scoring_rules
    WHERE tenant_id = v_lead.tenant_id AND active = TRUE
    ORDER BY sequence
  LOOP
    v_condition := v_rule.condition;
    v_matches := FALSE;

    -- Evaluar condicion basada en tipo de regla
    IF v_rule.rule_type = 'field_value' THEN
      -- Obtener valor del campo dinamicamente
      EXECUTE format('SELECT ($1).%I::TEXT', v_condition->>'field')
        INTO v_field_value USING v_lead;

      -- Evaluar operador
      CASE v_condition->>'operator'
        WHEN 'equals' THEN
          v_matches := v_field_value = (v_condition->>'value');
        WHEN 'not_equals' THEN
          v_matches := v_field_value != (v_condition->>'value');
        WHEN 'contains' THEN
          v_matches := v_field_value ILIKE '%' || (v_condition->>'value') || '%';
        WHEN 'greater_than' THEN
          v_matches := v_field_value::NUMERIC > (v_condition->>'value')::NUMERIC;
        WHEN 'less_than' THEN
          v_matches := v_field_value::NUMERIC < (v_condition->>'value')::NUMERIC;
        ELSE
          v_matches := FALSE;
      END CASE;
    END IF;

    IF v_matches THEN
      v_total_score := v_total_score + v_rule.score_value;
    END IF;
  END LOOP;

  -- Actualizar lead con score
  UPDATE crm.leads
  SET automated_score = v_total_score,
      score_calculated_at = CURRENT_TIMESTAMP,
      score_tier = CASE
        WHEN v_total_score >= 80 THEN 'hot'
        WHEN v_total_score >= 40 THEN 'warm'
        ELSE 'cold'
      END
  WHERE id = p_lead_id;

  -- Registrar en historial
  INSERT INTO crm.lead_scoring_history (tenant_id, lead_id, score_before, score_after, reason)
  VALUES (v_lead.tenant_id, p_lead_id, v_lead.automated_score, v_total_score, 'Auto-calculated');

  RETURN v_total_score;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION crm.calculate_lead_score IS
'COR-014: Calcula el score de un lead basado en reglas de scoring activas';

-- =====================================================
-- COR-019: Auto-Assignment Rules
-- Reglas de asignacion automatica de leads
-- =====================================================

-- Tabla: lead_assignment_rules (Reglas de asignacion)
CREATE TABLE crm.lead_assignment_rules (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,

  name VARCHAR(100) NOT NULL,
  description TEXT,

  -- Condiciones (JSON array)
  -- Ejemplo: [{"field": "source", "operator": "equals", "value": "website"}]
  conditions JSONB NOT NULL DEFAULT '[]',

  -- Asignacion
  assignment_type VARCHAR(20) NOT NULL,  -- user, team, round_robin
  user_id UUID REFERENCES auth.users(id),
  sales_team_id UUID REFERENCES sales.sales_teams(id),

  -- Round-robin tracking
  last_assigned_user_id UUID REFERENCES auth.users(id),
  round_robin_users UUID[] DEFAULT '{}',

  -- Prioridad
  sequence INTEGER DEFAULT 10,

  -- Control
  active BOOLEAN DEFAULT TRUE,

  -- Auditoria
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  created_by UUID REFERENCES auth.users(id),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,

  CONSTRAINT uq_lead_assignment_rules_name_tenant UNIQUE (tenant_id, name),
  CONSTRAINT chk_assignment_type CHECK (assignment_type IN ('user', 'team', 'round_robin'))
);

-- Indices para assignment rules
CREATE INDEX idx_lead_assignment_rules_tenant ON crm.lead_assignment_rules(tenant_id);
CREATE INDEX idx_lead_assignment_rules_active ON crm.lead_assignment_rules(active) WHERE active = TRUE;
CREATE INDEX idx_lead_assignment_rules_sequence ON crm.lead_assignment_rules(sequence);

-- RLS
ALTER TABLE crm.lead_assignment_rules ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation_lead_assignment_rules ON crm.lead_assignment_rules
  USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

-- Funcion: auto_assign_lead
CREATE OR REPLACE FUNCTION crm.auto_assign_lead(p_lead_id UUID)
RETURNS UUID AS $$
DECLARE
  v_lead RECORD;
  v_rule RECORD;
  v_assigned_user_id UUID;
  v_matches BOOLEAN;
  v_condition JSONB;
  v_all_conditions_match BOOLEAN;
  v_next_user_idx INTEGER;
BEGIN
  -- Obtener lead
  SELECT * INTO v_lead FROM crm.leads WHERE id = p_lead_id;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'Lead % not found', p_lead_id;
  END IF;

  -- Si ya tiene usuario asignado, retornar
  IF v_lead.user_id IS NOT NULL THEN
    RETURN v_lead.user_id;
  END IF;

  -- Evaluar reglas en orden de prioridad
  FOR v_rule IN
    SELECT * FROM crm.lead_assignment_rules
    WHERE tenant_id = v_lead.tenant_id AND active = TRUE
    ORDER BY sequence
  LOOP
    v_all_conditions_match := TRUE;

    -- Evaluar todas las condiciones
    FOR v_condition IN SELECT * FROM jsonb_array_elements(v_rule.conditions)
    LOOP
      -- Simplificado: solo verificar igualdad
      EXECUTE format('SELECT ($1).%I::TEXT = $2', v_condition->>'field')
        INTO v_matches
        USING v_lead, v_condition->>'value';

      IF NOT v_matches THEN
        v_all_conditions_match := FALSE;
        EXIT;
      END IF;
    END LOOP;

    IF v_all_conditions_match THEN
      -- Determinar usuario a asignar
      CASE v_rule.assignment_type
        WHEN 'user' THEN
          v_assigned_user_id := v_rule.user_id;
        WHEN 'team' THEN
          -- Asignar al lider del equipo
          SELECT team_leader_id INTO v_assigned_user_id
          FROM sales.sales_teams WHERE id = v_rule.sales_team_id;
        WHEN 'round_robin' THEN
          -- Obtener siguiente usuario en round-robin
          IF array_length(v_rule.round_robin_users, 1) > 0 THEN
            v_next_user_idx := 1;
            IF v_rule.last_assigned_user_id IS NOT NULL THEN
              FOR i IN 1..array_length(v_rule.round_robin_users, 1) LOOP
                IF v_rule.round_robin_users[i] = v_rule.last_assigned_user_id THEN
                  v_next_user_idx := CASE WHEN i >= array_length(v_rule.round_robin_users, 1) THEN 1 ELSE i + 1 END;
                  EXIT;
                END IF;
              END LOOP;
            END IF;
            v_assigned_user_id := v_rule.round_robin_users[v_next_user_idx];

            -- Actualizar ultimo asignado
            UPDATE crm.lead_assignment_rules
            SET last_assigned_user_id = v_assigned_user_id
            WHERE id = v_rule.id;
          END IF;
      END CASE;

      -- Asignar lead
      IF v_assigned_user_id IS NOT NULL THEN
        UPDATE crm.leads
        SET user_id = v_assigned_user_id,
            sales_team_id = COALESCE(v_rule.sales_team_id, v_lead.sales_team_id)
        WHERE id = p_lead_id;

        RETURN v_assigned_user_id;
      END IF;
    END IF;
  END LOOP;

  RETURN NULL;  -- No se encontro regla aplicable
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION crm.auto_assign_lead IS
'COR-019: Asigna automaticamente un lead basado en reglas de asignacion';

-- =====================================================
-- COMMENTS
-- =====================================================

COMMENT ON TABLE crm.lead_stages IS 'Etapas del pipeline de leads';
COMMENT ON TABLE crm.opportunity_stages IS 'Etapas del pipeline de oportunidades';
COMMENT ON TABLE crm.lost_reasons IS 'Razones de perdida de leads/oportunidades';
COMMENT ON TABLE crm.leads IS 'Prospectos/leads de ventas';
COMMENT ON TABLE crm.opportunities IS 'Oportunidades de venta';
COMMENT ON TABLE crm.activities IS 'Actividades CRM (llamadas, reuniones, etc.)';
COMMENT ON TABLE crm.lead_scoring_rules IS 'COR-014: Reglas de scoring predictivo para leads';
COMMENT ON TABLE crm.lead_scoring_history IS 'COR-014: Historial de cambios de score';
COMMENT ON TABLE crm.lead_assignment_rules IS 'COR-019: Reglas de asignacion automatica de leads';

-- =====================================================
-- COR-030: Merge Leads Function
-- Equivalente a la funcionalidad de merge en Odoo CRM
-- =====================================================

-- Agregar columna para tracking de leads fusionados
ALTER TABLE crm.leads ADD COLUMN IF NOT EXISTS merged_into_id UUID REFERENCES crm.leads(id);

-- Funcion: merge_leads
CREATE OR REPLACE FUNCTION crm.merge_leads(
  p_lead_ids UUID[],
  p_target_lead_id UUID
)
RETURNS UUID AS $$
DECLARE
  v_lead_id UUID;
  v_target RECORD;
BEGIN
  -- Validar target existe
  SELECT * INTO v_target FROM crm.leads WHERE id = p_target_lead_id;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'Target lead % not found', p_target_lead_id;
  END IF;

  -- Fusionar leads
  FOREACH v_lead_id IN ARRAY p_lead_ids LOOP
    IF v_lead_id != p_target_lead_id THEN
      -- Mover actividades al target
      UPDATE crm.activities
      SET lead_id = p_target_lead_id
      WHERE lead_id = v_lead_id;

      -- Acumular expected revenue
      UPDATE crm.leads t
      SET expected_revenue = t.expected_revenue + COALESCE(
        (SELECT expected_revenue FROM crm.leads WHERE id = v_lead_id), 0
      )
      WHERE t.id = p_target_lead_id;

      -- Marcar como fusionado (soft delete)
      UPDATE crm.leads
      SET is_deleted = TRUE,
          merged_into_id = p_target_lead_id,
          updated_at = NOW()
      WHERE id = v_lead_id;
    END IF;
  END LOOP;

  RETURN p_target_lead_id;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION crm.merge_leads IS 'COR-030: Merge multiple leads into one target lead';

-- =====================================================
-- COR-051: Convert Lead to Opportunity
-- Equivalente a convert_opportunity de Odoo
-- =====================================================

CREATE OR REPLACE FUNCTION crm.convert_lead_to_opportunity(
  p_lead_id UUID,
  p_partner_id UUID DEFAULT NULL,
  p_create_partner BOOLEAN DEFAULT TRUE
)
RETURNS UUID AS $$
DECLARE
  v_lead RECORD;
  v_opportunity_id UUID;
  v_partner_id UUID;
BEGIN
  -- Obtener lead
  SELECT * INTO v_lead FROM crm.leads WHERE id = p_lead_id;

  IF NOT FOUND THEN
    RAISE EXCEPTION 'Lead % not found', p_lead_id;
  END IF;

  IF v_lead.status = 'converted' THEN
    RAISE EXCEPTION 'Lead % is already converted', p_lead_id;
  END IF;

  -- Determinar partner
  IF p_partner_id IS NOT NULL THEN
    v_partner_id := p_partner_id;
  ELSIF v_lead.partner_id IS NOT NULL THEN
    v_partner_id := v_lead.partner_id;
  ELSIF p_create_partner THEN
    -- Crear partner desde datos del lead
    INSERT INTO core.partners (
      tenant_id, company_id, name, email, phone, mobile,
      website, street, city, state, zip, country,
      is_customer, is_company
    ) VALUES (
      v_lead.tenant_id, v_lead.company_id,
      COALESCE(v_lead.company_name, v_lead.contact_name, v_lead.name),
      v_lead.email, v_lead.phone, v_lead.mobile,
      v_lead.website, v_lead.street, v_lead.city,
      v_lead.state, v_lead.zip, v_lead.country,
      TRUE, v_lead.company_name IS NOT NULL
    ) RETURNING id INTO v_partner_id;
  ELSE
    RAISE EXCEPTION 'No partner specified and create_partner is false';
  END IF;

  -- Crear oportunidad
  INSERT INTO crm.opportunities (
    tenant_id, company_id, name, ref,
    partner_id, contact_name, email, phone,
    stage_id, status,
    user_id, sales_team_id,
    priority, probability, expected_revenue,
    date_deadline,
    lead_id, source, campaign_id, medium,
    description, notes, tags,
    created_by
  ) VALUES (
    v_lead.tenant_id, v_lead.company_id, v_lead.name, v_lead.ref,
    v_partner_id, v_lead.contact_name, v_lead.email, v_lead.phone,
    (SELECT id FROM crm.opportunity_stages WHERE tenant_id = v_lead.tenant_id ORDER BY sequence LIMIT 1),
    'open',
    v_lead.user_id, v_lead.sales_team_id,
    v_lead.priority, v_lead.probability, v_lead.expected_revenue,
    v_lead.date_deadline,
    p_lead_id, v_lead.source, v_lead.campaign_id, v_lead.medium,
    v_lead.description, v_lead.notes, v_lead.tags,
    v_lead.created_by
  ) RETURNING id INTO v_opportunity_id;

  -- Actualizar lead
  UPDATE crm.leads
  SET status = 'converted',
      partner_id = v_partner_id,
      opportunity_id = v_opportunity_id,
      date_closed = NOW(),
      updated_at = NOW()
  WHERE id = p_lead_id;

  -- Mover actividades
  UPDATE crm.activities
  SET res_model = 'crm.opportunities',
      res_id = v_opportunity_id
  WHERE res_model = 'crm.leads' AND res_id = p_lead_id;

  RETURN v_opportunity_id;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION crm.convert_lead_to_opportunity IS 'COR-051: Convert lead to opportunity with optional partner creation';

-- =====================================================
-- COR-052: Lead/Opportunity Additional Fields
-- Campos adicionales para alinear con Odoo
-- =====================================================

-- Agregar campos a leads
ALTER TABLE crm.leads
  ADD COLUMN IF NOT EXISTS is_deleted BOOLEAN DEFAULT FALSE,
  ADD COLUMN IF NOT EXISTS color INTEGER DEFAULT 0,
  ADD COLUMN IF NOT EXISTS referred VARCHAR(255),  -- Referido por
  ADD COLUMN IF NOT EXISTS type VARCHAR(20) DEFAULT 'lead',  -- lead, opportunity
  ADD COLUMN IF NOT EXISTS day_open INTEGER,  -- Dias desde apertura
  ADD COLUMN IF NOT EXISTS day_close INTEGER,  -- Dias para cierre
  ADD COLUMN IF NOT EXISTS planned_revenue DECIMAL(20,6),
  ADD COLUMN IF NOT EXISTS date_conversion TIMESTAMP WITH TIME ZONE,  -- Fecha de conversion
  ADD COLUMN IF NOT EXISTS date_action DATE,  -- Proxima accion
  ADD COLUMN IF NOT EXISTS title_action VARCHAR(255);  -- Titulo proxima accion

-- Agregar campos a opportunities
ALTER TABLE crm.opportunities
  ADD COLUMN IF NOT EXISTS color INTEGER DEFAULT 0,
  ADD COLUMN IF NOT EXISTS is_won BOOLEAN DEFAULT FALSE,
  ADD COLUMN IF NOT EXISTS referred VARCHAR(255),
  ADD COLUMN IF NOT EXISTS day_open INTEGER,
  ADD COLUMN IF NOT EXISTS day_close INTEGER,
  ADD COLUMN IF NOT EXISTS date_action DATE,
  ADD COLUMN IF NOT EXISTS title_action VARCHAR(255),
  ADD COLUMN IF NOT EXISTS prorated_revenue DECIMAL(20,6),  -- Revenue * probability
  ADD COLUMN IF NOT EXISTS company_currency_id UUID REFERENCES core.currencies(id);

CREATE INDEX idx_leads_is_deleted ON crm.leads(is_deleted) WHERE is_deleted = FALSE;
CREATE INDEX idx_leads_type ON crm.leads(type);
CREATE INDEX idx_opportunities_is_won ON crm.opportunities(is_won);

-- =====================================================
-- COR-053: Mark Lead/Opportunity as Lost
-- Funcion para marcar como perdido
-- =====================================================

CREATE OR REPLACE FUNCTION crm.action_set_lost(
  p_model VARCHAR,
  p_id UUID,
  p_lost_reason_id UUID,
  p_lost_notes TEXT DEFAULT NULL
)
RETURNS VOID AS $$
BEGIN
  IF p_model = 'lead' THEN
    UPDATE crm.leads
    SET status = 'lost',
        lost_reason_id = p_lost_reason_id,
        lost_notes = p_lost_notes,
        date_closed = NOW(),
        updated_at = NOW()
    WHERE id = p_id;
  ELSIF p_model = 'opportunity' THEN
    UPDATE crm.opportunities
    SET status = 'lost',
        lost_reason_id = p_lost_reason_id,
        lost_notes = p_lost_notes,
        date_closed = NOW(),
        is_won = FALSE,
        updated_at = NOW()
    WHERE id = p_id;
  ELSE
    RAISE EXCEPTION 'Invalid model: %', p_model;
  END IF;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION crm.action_set_lost IS 'COR-053: Mark lead or opportunity as lost';

-- =====================================================
-- COR-054: Mark Opportunity as Won
-- Funcion para marcar como ganado
-- =====================================================

CREATE OR REPLACE FUNCTION crm.action_set_won(p_opportunity_id UUID)
RETURNS VOID AS $$
DECLARE
  v_opportunity RECORD;
  v_won_stage_id UUID;
BEGIN
  SELECT * INTO v_opportunity FROM crm.opportunities WHERE id = p_opportunity_id;

  IF NOT FOUND THEN
    RAISE EXCEPTION 'Opportunity % not found', p_opportunity_id;
  END IF;

  -- Obtener etapa de ganado
  SELECT id INTO v_won_stage_id
  FROM crm.opportunity_stages
  WHERE tenant_id = v_opportunity.tenant_id AND is_won = TRUE
  ORDER BY sequence DESC
  LIMIT 1;

  UPDATE crm.opportunities
  SET status = 'won',
      is_won = TRUE,
      stage_id = COALESCE(v_won_stage_id, stage_id),
      probability = 100,
      date_closed = NOW(),
      updated_at = NOW()
  WHERE id = p_opportunity_id;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION crm.action_set_won IS 'COR-054: Mark opportunity as won';

-- =====================================================
-- COR-055: CRM Tags
-- Etiquetas para leads y oportunidades
-- =====================================================

CREATE TABLE crm.tags (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
  name VARCHAR(100) NOT NULL,
  color INTEGER DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE(tenant_id, name)
);

CREATE TABLE crm.lead_tag_rel (
  lead_id UUID NOT NULL REFERENCES crm.leads(id) ON DELETE CASCADE,
  tag_id UUID NOT NULL REFERENCES crm.tags(id) ON DELETE CASCADE,
  PRIMARY KEY (lead_id, tag_id)
);

CREATE TABLE crm.opportunity_tag_rel (
  opportunity_id UUID NOT NULL REFERENCES crm.opportunities(id) ON DELETE CASCADE,
  tag_id UUID NOT NULL REFERENCES crm.tags(id) ON DELETE CASCADE,
  PRIMARY KEY (opportunity_id, tag_id)
);

CREATE INDEX idx_crm_tags_tenant ON crm.tags(tenant_id);
CREATE INDEX idx_lead_tag_rel_lead ON crm.lead_tag_rel(lead_id);
CREATE INDEX idx_opportunity_tag_rel_opportunity ON crm.opportunity_tag_rel(opportunity_id);

-- RLS
ALTER TABLE crm.tags ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation_crm_tags ON crm.tags
  USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

COMMENT ON TABLE crm.tags IS 'COR-055: CRM tags for leads and opportunities';

-- =====================================================
-- FIN DEL SCHEMA CRM
-- =====================================================