-- =====================================================
-- SCHEMA: infonavit
-- PROPOSITO: Cumplimiento INFONAVIT, derechohabientes, actas
-- MODULOS: MAI-010 (CRM Derechohabientes), MAI-011 (INFONAVIT)
-- FECHA: 2025-11-24
-- ESPECIFICO: 100% Mexico - INFONAVIT
-- =====================================================

-- Crear schema
CREATE SCHEMA IF NOT EXISTS infonavit;

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

CREATE TYPE infonavit.derechohabiente_status AS ENUM (
  'prospect',        -- Prospecto
  'pre_qualified',   -- Precalificado
  'qualified',       -- Calificado (credito aprobado)
  'assigned',        -- Asignado a vivienda
  'in_process',      -- En proceso de escrituracion
  'owner',           -- Propietario (escriturado)
  'cancelled'        -- Cancelado
);

CREATE TYPE infonavit.credit_type AS ENUM (
  'infonavit_tradicional', -- Credito INFONAVIT tradicional
  'infonavit_total',       -- INFONAVIT Total
  'cofinavit',             -- Cofinavit (INFONAVIT + banco)
  'mejoravit',             -- Mejoravit
  'fovissste',             -- FOVISSSTE
  'fovissste_infonavit',   -- FOVISSSTE + INFONAVIT
  'bank_credit',           -- Credito bancario
  'cash'                   -- Contado
);

CREATE TYPE infonavit.acta_type AS ENUM (
  'inicio_obra',           -- Acta de inicio de obra
  'verificacion_avance',   -- Acta de verificacion de avance
  'entrega_recepcion',     -- Acta de entrega-recepcion
  'conclusion_obra',       -- Acta de conclusion de obra
  'liberacion_vivienda'    -- Liberacion de vivienda
);

CREATE TYPE infonavit.acta_status AS ENUM (
  'draft',           -- Borrador
  'pending',         -- Pendiente firma
  'signed',          -- Firmada
  'submitted',       -- Enviada a INFONAVIT
  'approved',        -- Aprobada
  'rejected',        -- Rechazada
  'cancelled'        -- Cancelada
);

CREATE TYPE infonavit.report_type AS ENUM (
  'avance_fisico',         -- Reporte de avance fisico
  'avance_financiero',     -- Reporte de avance financiero
  'inventario_viviendas',  -- Inventario de viviendas
  'asignaciones',          -- Reporte de asignaciones
  'escrituraciones',       -- Reporte de escrituraciones
  'cartera_vencida'        -- Cartera vencida
);

-- =====================================================
-- TABLES - REGISTRO INFONAVIT
-- =====================================================

-- Tabla: registro_infonavit (registro del constructor ante INFONAVIT)
CREATE TABLE infonavit.registro_infonavit (
  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),
  registro_number VARCHAR(50) NOT NULL, -- Numero de registro INFONAVIT
  registro_date DATE NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'active',
  vigencia_start DATE,
  vigencia_end DATE,
  responsable_tecnico VARCHAR(255),
  cedula_profesional VARCHAR(50),
  metadata JSONB DEFAULT '{}',

  -- 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_registro_infonavit_tenant UNIQUE (tenant_id, registro_number)
);

-- Tabla: oferta_vivienda (oferta de viviendas ante INFONAVIT)
CREATE TABLE infonavit.oferta_vivienda (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
  registro_id UUID NOT NULL REFERENCES infonavit.registro_infonavit(id),
  fraccionamiento_id UUID NOT NULL REFERENCES construction.fraccionamientos(id),
  oferta_number VARCHAR(50) NOT NULL,
  submission_date DATE NOT NULL,
  approval_date DATE,
  total_units INTEGER NOT NULL DEFAULT 0,
  approved_units INTEGER DEFAULT 0,
  price_range_min DECIMAL(14,2),
  price_range_max DECIMAL(14,2),
  status VARCHAR(20) NOT NULL DEFAULT 'pending', -- pending, approved, rejected
  rejection_reason TEXT,
  metadata JSONB DEFAULT '{}',

  -- 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_oferta_vivienda_tenant UNIQUE (tenant_id, oferta_number)
);

-- =====================================================
-- TABLES - DERECHOHABIENTES
-- =====================================================

-- Tabla: derechohabientes (compradores con credito INFONAVIT)
CREATE TABLE infonavit.derechohabientes (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
  partner_id UUID, -- FK a core.partners

  -- Datos personales
  nss VARCHAR(15) NOT NULL, -- Numero de Seguro Social
  curp VARCHAR(18),
  rfc VARCHAR(13),
  full_name VARCHAR(255) NOT NULL,
  first_name VARCHAR(100),
  last_name VARCHAR(100),
  second_last_name VARCHAR(100),
  birth_date DATE,
  gender VARCHAR(10),
  marital_status VARCHAR(20),
  nationality VARCHAR(50) DEFAULT 'Mexicana',

  -- Contacto
  email VARCHAR(255),
  phone VARCHAR(20),
  mobile VARCHAR(20),
  address TEXT,
  city VARCHAR(100),
  state VARCHAR(100),
  zip_code VARCHAR(10),

  -- Datos laborales
  employer_name VARCHAR(255),
  employer_rfc VARCHAR(13),
  employment_start_date DATE,
  salary DECIMAL(12,2),
  cotization_weeks INTEGER, -- Semanas cotizadas

  -- Credito
  credit_type infonavit.credit_type,
  credit_number VARCHAR(50),
  credit_amount DECIMAL(14,2),
  puntos_infonavit DECIMAL(10,2),
  subcuenta_vivienda DECIMAL(14,2),
  precalificacion_date DATE,
  precalificacion_amount DECIMAL(14,2),

  -- Estado
  status infonavit.derechohabiente_status NOT NULL DEFAULT 'prospect',

  -- 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_derechohabientes_nss_tenant UNIQUE (tenant_id, nss)
);

-- Tabla: asignacion_vivienda (asignacion de vivienda a derechohabiente)
CREATE TABLE infonavit.asignacion_vivienda (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
  derechohabiente_id UUID NOT NULL REFERENCES infonavit.derechohabientes(id),
  lote_id UUID REFERENCES construction.lotes(id),
  departamento_id UUID REFERENCES construction.departamentos(id),
  oferta_id UUID REFERENCES infonavit.oferta_vivienda(id),

  -- Asignacion
  assignment_date DATE NOT NULL,
  assignment_number VARCHAR(50),
  status VARCHAR(20) NOT NULL DEFAULT 'pending', -- pending, confirmed, cancelled

  -- Precios
  sale_price DECIMAL(14,2) NOT NULL,
  credit_amount DECIMAL(14,2),
  down_payment DECIMAL(14,2),
  subsidy_amount DECIMAL(14,2), -- Subsidio (si aplica)

  -- Escrituracion
  notary_name VARCHAR(255),
  notary_number VARCHAR(50),
  deed_date DATE, -- Fecha de escritura
  deed_number VARCHAR(50),
  public_registry_number VARCHAR(50),
  public_registry_date DATE,

  -- Entrega
  scheduled_delivery_date DATE,
  actual_delivery_date DATE,
  delivery_act_id UUID, -- FK a acta de entrega

  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 chk_asignacion_lote_or_depto CHECK (
    (lote_id IS NOT NULL AND departamento_id IS NULL) OR
    (lote_id IS NULL AND departamento_id IS NOT NULL)
  )
);

-- =====================================================
-- TABLES - ACTAS
-- =====================================================

-- Tabla: actas (actas INFONAVIT)
CREATE TABLE infonavit.actas (
  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),
  acta_type infonavit.acta_type NOT NULL,
  acta_number VARCHAR(50) NOT NULL,
  acta_date DATE NOT NULL,
  status infonavit.acta_status NOT NULL DEFAULT 'draft',

  -- Participantes
  infonavit_representative VARCHAR(255),
  constructor_representative VARCHAR(255),
  perito_name VARCHAR(255),
  perito_cedula VARCHAR(50),

  -- Contenido
  description TEXT,
  observations TEXT,
  agreements TEXT,

  -- Avance reportado (para actas de verificacion)
  physical_advance_percentage DECIMAL(5,2),
  financial_advance_percentage DECIMAL(5,2),

  -- Firmas
  signed_at TIMESTAMP,
  submitted_to_infonavit_at TIMESTAMP,
  infonavit_response_at TIMESTAMP,
  infonavit_folio VARCHAR(50),

  -- Documentos
  document_url VARCHAR(500),
  signed_document_url VARCHAR(500),

  -- 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_actas_number_tenant UNIQUE (tenant_id, acta_number)
);

-- Tabla: acta_viviendas (viviendas incluidas en acta)
CREATE TABLE infonavit.acta_viviendas (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
  acta_id UUID NOT NULL REFERENCES infonavit.actas(id) ON DELETE CASCADE,
  lote_id UUID REFERENCES construction.lotes(id),
  departamento_id UUID REFERENCES construction.departamentos(id),
  advance_percentage DECIMAL(5,2),
  observations TEXT,

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

-- =====================================================
-- TABLES - REPORTES INFONAVIT
-- =====================================================

-- Tabla: reportes_infonavit (reportes enviados a INFONAVIT)
CREATE TABLE infonavit.reportes_infonavit (
  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),
  report_type infonavit.report_type NOT NULL,
  report_number VARCHAR(50) NOT NULL,
  period_start DATE NOT NULL,
  period_end DATE NOT NULL,
  submission_date DATE,
  status VARCHAR(20) NOT NULL DEFAULT 'draft', -- draft, submitted, acknowledged
  infonavit_folio VARCHAR(50),

  -- Datos del reporte
  total_units INTEGER,
  units_in_progress INTEGER,
  units_completed INTEGER,
  units_delivered INTEGER,
  physical_advance_percentage DECIMAL(5,2),
  financial_advance_percentage DECIMAL(5,2),

  -- Archivos
  document_url VARCHAR(500),
  acknowledgment_url VARCHAR(500),

  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_reportes_number_tenant UNIQUE (tenant_id, report_number)
);

-- Tabla: historico_puntos (historico de puntos INFONAVIT)
CREATE TABLE infonavit.historico_puntos (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
  derechohabiente_id UUID NOT NULL REFERENCES infonavit.derechohabientes(id),
  query_date DATE NOT NULL,
  puntos DECIMAL(10,2),
  subcuenta_vivienda DECIMAL(14,2),
  cotization_weeks INTEGER,
  credit_capacity DECIMAL(14,2),
  source VARCHAR(50), -- API, manual, file_import

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

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

-- Registro INFONAVIT
CREATE INDEX idx_registro_infonavit_tenant_id ON infonavit.registro_infonavit(tenant_id);
CREATE INDEX idx_registro_infonavit_company_id ON infonavit.registro_infonavit(company_id);

-- Oferta Vivienda
CREATE INDEX idx_oferta_vivienda_tenant_id ON infonavit.oferta_vivienda(tenant_id);
CREATE INDEX idx_oferta_vivienda_registro_id ON infonavit.oferta_vivienda(registro_id);
CREATE INDEX idx_oferta_vivienda_fraccionamiento_id ON infonavit.oferta_vivienda(fraccionamiento_id);
CREATE INDEX idx_oferta_vivienda_status ON infonavit.oferta_vivienda(status);

-- Derechohabientes
CREATE INDEX idx_derechohabientes_tenant_id ON infonavit.derechohabientes(tenant_id);
CREATE INDEX idx_derechohabientes_nss ON infonavit.derechohabientes(nss);
CREATE INDEX idx_derechohabientes_curp ON infonavit.derechohabientes(curp);
CREATE INDEX idx_derechohabientes_status ON infonavit.derechohabientes(status);
CREATE INDEX idx_derechohabientes_credit_type ON infonavit.derechohabientes(credit_type);
CREATE INDEX idx_derechohabientes_full_name ON infonavit.derechohabientes(full_name);

-- Asignacion Vivienda
CREATE INDEX idx_asignacion_tenant_id ON infonavit.asignacion_vivienda(tenant_id);
CREATE INDEX idx_asignacion_derechohabiente_id ON infonavit.asignacion_vivienda(derechohabiente_id);
CREATE INDEX idx_asignacion_lote_id ON infonavit.asignacion_vivienda(lote_id);
CREATE INDEX idx_asignacion_departamento_id ON infonavit.asignacion_vivienda(departamento_id);
CREATE INDEX idx_asignacion_status ON infonavit.asignacion_vivienda(status);

-- Actas
CREATE INDEX idx_actas_tenant_id ON infonavit.actas(tenant_id);
CREATE INDEX idx_actas_fraccionamiento_id ON infonavit.actas(fraccionamiento_id);
CREATE INDEX idx_actas_type ON infonavit.actas(acta_type);
CREATE INDEX idx_actas_status ON infonavit.actas(status);
CREATE INDEX idx_actas_date ON infonavit.actas(acta_date);

-- Acta Viviendas
CREATE INDEX idx_acta_viviendas_tenant_id ON infonavit.acta_viviendas(tenant_id);
CREATE INDEX idx_acta_viviendas_acta_id ON infonavit.acta_viviendas(acta_id);

-- Reportes
CREATE INDEX idx_reportes_tenant_id ON infonavit.reportes_infonavit(tenant_id);
CREATE INDEX idx_reportes_fraccionamiento_id ON infonavit.reportes_infonavit(fraccionamiento_id);
CREATE INDEX idx_reportes_type ON infonavit.reportes_infonavit(report_type);
CREATE INDEX idx_reportes_status ON infonavit.reportes_infonavit(status);

-- Historico Puntos
CREATE INDEX idx_historico_puntos_tenant_id ON infonavit.historico_puntos(tenant_id);
CREATE INDEX idx_historico_puntos_derechohabiente_id ON infonavit.historico_puntos(derechohabiente_id);
CREATE INDEX idx_historico_puntos_query_date ON infonavit.historico_puntos(query_date);

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

-- Habilitar RLS
ALTER TABLE infonavit.registro_infonavit ENABLE ROW LEVEL SECURITY;
ALTER TABLE infonavit.oferta_vivienda ENABLE ROW LEVEL SECURITY;
ALTER TABLE infonavit.derechohabientes ENABLE ROW LEVEL SECURITY;
ALTER TABLE infonavit.asignacion_vivienda ENABLE ROW LEVEL SECURITY;
ALTER TABLE infonavit.actas ENABLE ROW LEVEL SECURITY;
ALTER TABLE infonavit.acta_viviendas ENABLE ROW LEVEL SECURITY;
ALTER TABLE infonavit.reportes_infonavit ENABLE ROW LEVEL SECURITY;
ALTER TABLE infonavit.historico_puntos ENABLE ROW LEVEL SECURITY;

-- Policies
CREATE POLICY tenant_isolation_registro_infonavit ON infonavit.registro_infonavit
  USING (tenant_id = get_current_tenant_id());

CREATE POLICY tenant_isolation_oferta_vivienda ON infonavit.oferta_vivienda
  USING (tenant_id = get_current_tenant_id());

CREATE POLICY tenant_isolation_derechohabientes ON infonavit.derechohabientes
  USING (tenant_id = get_current_tenant_id());

CREATE POLICY tenant_isolation_asignacion_vivienda ON infonavit.asignacion_vivienda
  USING (tenant_id = get_current_tenant_id());

CREATE POLICY tenant_isolation_actas ON infonavit.actas
  USING (tenant_id = get_current_tenant_id());

CREATE POLICY tenant_isolation_acta_viviendas ON infonavit.acta_viviendas
  USING (tenant_id = get_current_tenant_id());

CREATE POLICY tenant_isolation_reportes_infonavit ON infonavit.reportes_infonavit
  USING (tenant_id = get_current_tenant_id());

CREATE POLICY tenant_isolation_historico_puntos ON infonavit.historico_puntos
  USING (tenant_id = get_current_tenant_id());

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

COMMENT ON SCHEMA infonavit IS 'Schema de cumplimiento INFONAVIT y gestion de derechohabientes';
COMMENT ON TABLE infonavit.registro_infonavit IS 'Registro del constructor ante INFONAVIT';
COMMENT ON TABLE infonavit.oferta_vivienda IS 'Oferta de viviendas registrada ante INFONAVIT';
COMMENT ON TABLE infonavit.derechohabientes IS 'Derechohabientes INFONAVIT/compradores';
COMMENT ON TABLE infonavit.asignacion_vivienda IS 'Asignacion de vivienda a derechohabiente';
COMMENT ON TABLE infonavit.actas IS 'Actas oficiales INFONAVIT (inicio, verificacion, entrega)';
COMMENT ON TABLE infonavit.acta_viviendas IS 'Viviendas incluidas en cada acta';
COMMENT ON TABLE infonavit.reportes_infonavit IS 'Reportes periodicos enviados a INFONAVIT';
COMMENT ON TABLE infonavit.historico_puntos IS 'Historico de consulta de puntos INFONAVIT';

-- =====================================================
-- FIN DEL SCHEMA INFONAVIT
-- =====================================================