-- ============================================================================
-- INFONAVIT Schema DDL - Cumplimiento INFONAVIT y Derechohabientes
-- Modulos: MAI-010 (CRM Derechohabientes), MAI-011 (Integración INFONAVIT)
-- Version: 1.0.0
-- Fecha: 2025-12-08
-- ============================================================================
-- PREREQUISITOS:
--   1. ERP-Core instalado (auth.tenants, auth.users, auth.companies)
--   2. Schema construction instalado (fraccionamientos, lotes, departamentos)
-- ============================================================================

-- Verificar prerequisitos
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_namespace WHERE nspname = 'auth') THEN
        RAISE EXCEPTION 'Schema auth no existe. Ejecutar primero ERP-Core DDL';
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_namespace WHERE nspname = 'construction') THEN
        RAISE EXCEPTION 'Schema construction no existe. Ejecutar primero construction DDL';
    END IF;
END $$;

-- Crear schema
CREATE SCHEMA IF NOT EXISTS infonavit;

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

DO $$ BEGIN
    CREATE TYPE infonavit.derechohabiente_status AS ENUM (
        'prospect', 'pre_qualified', 'qualified', 'assigned', 'in_process', 'owner', 'cancelled'
    );
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

DO $$ BEGIN
    CREATE TYPE infonavit.credit_type AS ENUM (
        'infonavit_tradicional', 'infonavit_total', 'cofinavit', 'mejoravit',
        'fovissste', 'fovissste_infonavit', 'bank_credit', 'cash'
    );
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

DO $$ BEGIN
    CREATE TYPE infonavit.acta_type AS ENUM (
        'inicio_obra', 'verificacion_avance', 'entrega_recepcion', 'conclusion_obra', 'liberacion_vivienda'
    );
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

DO $$ BEGIN
    CREATE TYPE infonavit.acta_status AS ENUM (
        'draft', 'pending', 'signed', 'submitted', 'approved', 'rejected', 'cancelled'
    );
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

DO $$ BEGIN
    CREATE TYPE infonavit.report_type AS ENUM (
        'avance_fisico', 'avance_financiero', 'inventario_viviendas', 'asignaciones', 'escrituraciones', 'cartera_vencida'
    );
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

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

-- Tabla: registro_infonavit (registro del constructor ante INFONAVIT)
CREATE TABLE IF NOT EXISTS 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,
    registro_number VARCHAR(50) NOT NULL,
    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 '{}',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by UUID REFERENCES auth.users(id),
    updated_at TIMESTAMPTZ,
    updated_by UUID REFERENCES auth.users(id),
    deleted_at TIMESTAMPTZ,
    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 IF NOT EXISTS 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',
    rejection_reason TEXT,
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by UUID REFERENCES auth.users(id),
    updated_at TIMESTAMPTZ,
    updated_by UUID REFERENCES auth.users(id),
    deleted_at TIMESTAMPTZ,
    deleted_by UUID REFERENCES auth.users(id),
    CONSTRAINT uq_oferta_vivienda_tenant UNIQUE (tenant_id, oferta_number)
);

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

-- Tabla: derechohabientes (compradores con crédito INFONAVIT)
CREATE TABLE IF NOT EXISTS 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,
    nss VARCHAR(15) NOT NULL,
    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',
    email VARCHAR(255),
    phone VARCHAR(20),
    mobile VARCHAR(20),
    address TEXT,
    city VARCHAR(100),
    state VARCHAR(100),
    zip_code VARCHAR(10),
    employer_name VARCHAR(255),
    employer_rfc VARCHAR(13),
    employment_start_date DATE,
    salary DECIMAL(12,2),
    cotization_weeks INTEGER,
    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),
    status infonavit.derechohabiente_status NOT NULL DEFAULT 'prospect',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by UUID REFERENCES auth.users(id),
    updated_at TIMESTAMPTZ,
    updated_by UUID REFERENCES auth.users(id),
    deleted_at TIMESTAMPTZ,
    deleted_by UUID REFERENCES auth.users(id),
    CONSTRAINT uq_derechohabientes_nss_tenant UNIQUE (tenant_id, nss)
);

-- Tabla: asignacion_vivienda (asignación de vivienda a derechohabiente)
CREATE TABLE IF NOT EXISTS 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),
    assignment_date DATE NOT NULL,
    assignment_number VARCHAR(50),
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    sale_price DECIMAL(14,2) NOT NULL,
    credit_amount DECIMAL(14,2),
    down_payment DECIMAL(14,2),
    subsidy_amount DECIMAL(14,2),
    notary_name VARCHAR(255),
    notary_number VARCHAR(50),
    deed_date DATE,
    deed_number VARCHAR(50),
    public_registry_number VARCHAR(50),
    public_registry_date DATE,
    scheduled_delivery_date DATE,
    actual_delivery_date DATE,
    delivery_act_id UUID,
    notes TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by UUID REFERENCES auth.users(id),
    updated_at TIMESTAMPTZ,
    updated_by UUID REFERENCES auth.users(id),
    deleted_at TIMESTAMPTZ,
    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 IF NOT EXISTS 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',
    infonavit_representative VARCHAR(255),
    constructor_representative VARCHAR(255),
    perito_name VARCHAR(255),
    perito_cedula VARCHAR(50),
    description TEXT,
    observations TEXT,
    agreements TEXT,
    physical_advance_percentage DECIMAL(5,2),
    financial_advance_percentage DECIMAL(5,2),
    signed_at TIMESTAMPTZ,
    submitted_to_infonavit_at TIMESTAMPTZ,
    infonavit_response_at TIMESTAMPTZ,
    infonavit_folio VARCHAR(50),
    document_url VARCHAR(500),
    signed_document_url VARCHAR(500),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by UUID REFERENCES auth.users(id),
    updated_at TIMESTAMPTZ,
    updated_by UUID REFERENCES auth.users(id),
    deleted_at TIMESTAMPTZ,
    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 IF NOT EXISTS 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,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by UUID REFERENCES auth.users(id)
);

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

-- Tabla: reportes_infonavit (reportes enviados a INFONAVIT)
CREATE TABLE IF NOT EXISTS 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',
    infonavit_folio VARCHAR(50),
    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),
    document_url VARCHAR(500),
    acknowledgment_url VARCHAR(500),
    notes TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by UUID REFERENCES auth.users(id),
    updated_at TIMESTAMPTZ,
    updated_by UUID REFERENCES auth.users(id),
    deleted_at TIMESTAMPTZ,
    deleted_by UUID REFERENCES auth.users(id),
    CONSTRAINT uq_reportes_number_tenant UNIQUE (tenant_id, report_number)
);

-- Tabla: historico_puntos (histórico de puntos INFONAVIT)
CREATE TABLE IF NOT EXISTS 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),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by UUID REFERENCES auth.users(id)
);

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

CREATE INDEX IF NOT EXISTS idx_registro_infonavit_tenant_id ON infonavit.registro_infonavit(tenant_id);
CREATE INDEX IF NOT EXISTS idx_registro_infonavit_company_id ON infonavit.registro_infonavit(company_id);

CREATE INDEX IF NOT EXISTS idx_oferta_vivienda_tenant_id ON infonavit.oferta_vivienda(tenant_id);
CREATE INDEX IF NOT EXISTS idx_oferta_vivienda_registro_id ON infonavit.oferta_vivienda(registro_id);
CREATE INDEX IF NOT EXISTS idx_oferta_vivienda_fraccionamiento_id ON infonavit.oferta_vivienda(fraccionamiento_id);
CREATE INDEX IF NOT EXISTS idx_oferta_vivienda_status ON infonavit.oferta_vivienda(status);

CREATE INDEX IF NOT EXISTS idx_derechohabientes_tenant_id ON infonavit.derechohabientes(tenant_id);
CREATE INDEX IF NOT EXISTS idx_derechohabientes_nss ON infonavit.derechohabientes(nss);
CREATE INDEX IF NOT EXISTS idx_derechohabientes_curp ON infonavit.derechohabientes(curp);
CREATE INDEX IF NOT EXISTS idx_derechohabientes_status ON infonavit.derechohabientes(status);
CREATE INDEX IF NOT EXISTS idx_derechohabientes_credit_type ON infonavit.derechohabientes(credit_type);

CREATE INDEX IF NOT EXISTS idx_asignacion_tenant_id ON infonavit.asignacion_vivienda(tenant_id);
CREATE INDEX IF NOT EXISTS idx_asignacion_derechohabiente_id ON infonavit.asignacion_vivienda(derechohabiente_id);
CREATE INDEX IF NOT EXISTS idx_asignacion_lote_id ON infonavit.asignacion_vivienda(lote_id);
CREATE INDEX IF NOT EXISTS idx_asignacion_status ON infonavit.asignacion_vivienda(status);

CREATE INDEX IF NOT EXISTS idx_actas_tenant_id ON infonavit.actas(tenant_id);
CREATE INDEX IF NOT EXISTS idx_actas_fraccionamiento_id ON infonavit.actas(fraccionamiento_id);
CREATE INDEX IF NOT EXISTS idx_actas_type ON infonavit.actas(acta_type);
CREATE INDEX IF NOT EXISTS idx_actas_status ON infonavit.actas(status);

CREATE INDEX IF NOT EXISTS idx_acta_viviendas_tenant_id ON infonavit.acta_viviendas(tenant_id);
CREATE INDEX IF NOT EXISTS idx_acta_viviendas_acta_id ON infonavit.acta_viviendas(acta_id);

CREATE INDEX IF NOT EXISTS idx_reportes_tenant_id ON infonavit.reportes_infonavit(tenant_id);
CREATE INDEX IF NOT EXISTS idx_reportes_fraccionamiento_id ON infonavit.reportes_infonavit(fraccionamiento_id);
CREATE INDEX IF NOT EXISTS idx_reportes_type ON infonavit.reportes_infonavit(report_type);

CREATE INDEX IF NOT EXISTS idx_historico_puntos_tenant_id ON infonavit.historico_puntos(tenant_id);
CREATE INDEX IF NOT EXISTS idx_historico_puntos_derechohabiente_id ON infonavit.historico_puntos(derechohabiente_id);

-- ============================================================================
-- ROW LEVEL SECURITY (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;

DO $$ BEGIN
    DROP POLICY IF EXISTS tenant_isolation_registro_infonavit ON infonavit.registro_infonavit;
    CREATE POLICY tenant_isolation_registro_infonavit ON infonavit.registro_infonavit
        FOR ALL USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);
EXCEPTION WHEN undefined_object THEN NULL; END $$;

DO $$ BEGIN
    DROP POLICY IF EXISTS tenant_isolation_oferta_vivienda ON infonavit.oferta_vivienda;
    CREATE POLICY tenant_isolation_oferta_vivienda ON infonavit.oferta_vivienda
        FOR ALL USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);
EXCEPTION WHEN undefined_object THEN NULL; END $$;

DO $$ BEGIN
    DROP POLICY IF EXISTS tenant_isolation_derechohabientes ON infonavit.derechohabientes;
    CREATE POLICY tenant_isolation_derechohabientes ON infonavit.derechohabientes
        FOR ALL USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);
EXCEPTION WHEN undefined_object THEN NULL; END $$;

DO $$ BEGIN
    DROP POLICY IF EXISTS tenant_isolation_asignacion_vivienda ON infonavit.asignacion_vivienda;
    CREATE POLICY tenant_isolation_asignacion_vivienda ON infonavit.asignacion_vivienda
        FOR ALL USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);
EXCEPTION WHEN undefined_object THEN NULL; END $$;

DO $$ BEGIN
    DROP POLICY IF EXISTS tenant_isolation_actas ON infonavit.actas;
    CREATE POLICY tenant_isolation_actas ON infonavit.actas
        FOR ALL USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);
EXCEPTION WHEN undefined_object THEN NULL; END $$;

DO $$ BEGIN
    DROP POLICY IF EXISTS tenant_isolation_acta_viviendas ON infonavit.acta_viviendas;
    CREATE POLICY tenant_isolation_acta_viviendas ON infonavit.acta_viviendas
        FOR ALL USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);
EXCEPTION WHEN undefined_object THEN NULL; END $$;

DO $$ BEGIN
    DROP POLICY IF EXISTS tenant_isolation_reportes_infonavit ON infonavit.reportes_infonavit;
    CREATE POLICY tenant_isolation_reportes_infonavit ON infonavit.reportes_infonavit
        FOR ALL USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);
EXCEPTION WHEN undefined_object THEN NULL; END $$;

DO $$ BEGIN
    DROP POLICY IF EXISTS tenant_isolation_historico_puntos ON infonavit.historico_puntos;
    CREATE POLICY tenant_isolation_historico_puntos ON infonavit.historico_puntos
        FOR ALL USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);
EXCEPTION WHEN undefined_object THEN NULL; END $$;

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

COMMENT ON SCHEMA infonavit IS 'Schema de cumplimiento INFONAVIT y gestión 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 'Asignación de vivienda a derechohabiente';
COMMENT ON TABLE infonavit.actas IS 'Actas oficiales INFONAVIT';
COMMENT ON TABLE infonavit.acta_viviendas IS 'Viviendas incluidas en cada acta';
COMMENT ON TABLE infonavit.reportes_infonavit IS 'Reportes periódicos enviados a INFONAVIT';
COMMENT ON TABLE infonavit.historico_puntos IS 'Histórico de consulta de puntos INFONAVIT';

-- ============================================================================
-- FIN DEL SCHEMA INFONAVIT
-- Total tablas: 8
-- ============================================================================