workspace-v1/projects/erp-construccion/docs/02-definicion-modulos/MAI-002-proyectos-estructura/especificaciones/ET-PROJ-001-database.md

DDL-SPEC: Schema project_management

Identificacion

Campo	Valor
Schema	project_management
Modulo	MAI-002
Version	1.0
Estado	En Diseno
Autor	Requirements-Analyst
Fecha	2025-12-06

---

Descripcion General

El schema project_management contiene todas las tablas para la gestion completa de proyectos de construccion, incluyendo la jerarquia de 5 niveles (Proyecto → Etapa → Manzana → Lote → Vivienda), prototipos de vivienda con versionado, asignacion de equipo con validacion de workload, calendario de hitos y fechas criticas.

Alcance

• Catalogo de proyectos con 4 tipos y 5 estados del ciclo de vida
• Estructura jerarquica flexible (fraccionamiento, conjunto, torre, mixto)
• Prototipos de vivienda con versionado automatico
• Asignacion de equipo con validacion de limites por rol
• Calendario de hitos con dependencias y alertas automaticas
• Fases constructivas y avances fisicos ponderados
• Documentos y permisos legales por proyecto

RF Cubiertos

RF	Titulo	Tablas
RF-PROJ-001	Catalogo de Proyectos	projects
RF-PROJ-002	Estructura Jerarquica de Obra	stages, blocks, lots, housing_units
RF-PROJ-003	Prototipos de Vivienda	housing_prototypes, prototype_versions
RF-PROJ-004	Asignacion de Equipo y Calendario	project_team_assignments, project_milestones, critical_dates, construction_phases

---

Diagrama Entidad-Relacion

erDiagram
    %% Jerarquia Principal
    projects ||--o{ stages : "tiene"
    projects ||--o{ housing_prototypes : "define"
    projects ||--o{ project_team_assignments : "asigna"
    projects ||--o{ project_milestones : "planifica"
    projects ||--o{ critical_dates : "registra"
    projects ||--o{ project_documents : "almacena"

    stages ||--o{ blocks : "contiene"
    stages ||--o{ lots : "contiene_directos"

    blocks ||--o{ lots : "agrupa"

    lots ||--o{ housing_units : "construye"

    %% Prototipos
    housing_prototypes ||--o{ prototype_versions : "versiones"
    housing_prototypes ||--o{ housing_units : "asignado_a"

    %% Fases Constructivas
    housing_units ||--o{ construction_phases : "progreso"

    %% Relaciones con Core
    projects }o--|| tenants : "pertenece"
    projects }o--|| contacts : "cliente"
    housing_units }o--|| contacts : "derechohabiente"

    projects {
        uuid id PK
        uuid tenant_id FK
        varchar project_code UK
        varchar name
        enum project_type
        enum project_status
        enum client_type
        varchar client_name
        decimal contract_amount
        text address
        date contract_start_date
        date scheduled_end_date
        int total_housing_units
        decimal physical_progress
        boolean is_active
    }

    stages {
        uuid id PK
        uuid tenant_id FK
        uuid project_id FK
        varchar code
        varchar name
        enum stage_status
        int stage_number
        date planned_start_date
        date planned_end_date
        decimal progress
        boolean is_active
    }

    blocks {
        uuid id PK
        uuid tenant_id FK
        uuid stage_id FK
        varchar code
        varchar name
        varchar block_type
        int total_lots
        decimal total_area
        boolean is_active
    }

    lots {
        uuid id PK
        uuid tenant_id FK
        uuid project_id FK
        uuid stage_id FK
        uuid block_id FK
        varchar lot_number
        enum lot_status
        decimal lot_area
        decimal front_meters
        decimal depth_meters
        decimal cadastral_value
        decimal sale_price
        uuid owner_id FK
        date sale_date
        boolean is_active
    }

    housing_units {
        uuid id PK
        uuid tenant_id FK
        uuid project_id FK
        uuid lot_id FK
        uuid prototype_id FK
        varchar unit_code
        enum unit_type
        enum unit_status
        decimal construction_area
        decimal total_cost
        decimal physical_progress
        date construction_start_date
        date estimated_delivery_date
        jsonb prototype_snapshot
        boolean is_active
    }

    housing_prototypes {
        uuid id PK
        uuid tenant_id FK
        uuid project_id FK
        varchar code UK
        varchar name
        enum category
        enum segment
        int current_version
        decimal construction_area
        decimal land_area
        int bedrooms
        int bathrooms
        decimal estimated_cost
        boolean is_active
    }

    prototype_versions {
        uuid id PK
        uuid tenant_id FK
        uuid prototype_id FK
        int version_number
        varchar change_description
        jsonb specifications
        decimal estimated_cost
        timestamptz created_at
        uuid created_by FK
    }

    project_team_assignments {
        uuid id PK
        uuid tenant_id FK
        uuid project_id FK
        uuid user_id FK
        enum role_type
        boolean is_primary
        int workload_percentage
        date assignment_start_date
        date assignment_end_date
        boolean is_active
    }

    project_milestones {
        uuid id PK
        uuid tenant_id FK
        uuid project_id FK
        enum milestone_type
        varchar name
        date planned_date
        date actual_date
        enum status
        jsonb dependencies
        boolean is_active
    }

    critical_dates {
        uuid id PK
        uuid tenant_id FK
        uuid project_id FK
        enum date_type
        varchar name
        date date_value
        int alert_days_before
        boolean alert_sent
        boolean is_active
    }

    construction_phases {
        uuid id PK
        uuid tenant_id FK
        uuid housing_unit_id FK
        enum phase_name
        decimal weight_percentage
        decimal progress_percentage
        date start_date
        date end_date
        enum status
        boolean is_active
    }

    project_documents {
        uuid id PK
        uuid tenant_id FK
        uuid project_id FK
        enum document_type
        varchar name
        varchar file_path
        date issue_date
        date expiration_date
        boolean is_active
    }

---

ENUMs

1. project_type

Tipos de proyectos de construccion.

CREATE TYPE project_management.project_type AS ENUM (
    'fraccionamiento_horizontal',
    'conjunto_habitacional',
    'edificio_vertical',
    'mixto'
);

Descripcion:

• fraccionamiento_horizontal: Proyecto con etapas, manzanas y lotes
• conjunto_habitacional: Proyecto sin manzanas (lotes directos)
• edificio_vertical: Torres con niveles y departamentos
• mixto: Combinacion de estructuras anteriores

---

2. project_status

Estados del ciclo de vida del proyecto.

CREATE TYPE project_management.project_status AS ENUM (
    'licitacion',
    'adjudicado',
    'ejecucion',
    'entregado',
    'cerrado'
);

Descripcion:

• licitacion: Proyecto en proceso de licitacion
• adjudicado: Proyecto ganado pero no iniciado
• ejecucion: Proyecto en construccion activa
• entregado: Proyecto terminado y entregado al cliente
• cerrado: Proyecto cerrado administrativamente

---

3. client_type

Tipo de cliente del proyecto.

CREATE TYPE project_management.client_type AS ENUM (
    'publico',
    'privado',
    'mixto'
);

---

4. contract_type

Tipo de contrato del proyecto.

CREATE TYPE project_management.contract_type AS ENUM (
    'llave_en_mano',
    'precio_alzado',
    'administracion',
    'mixto'
);

---

5. stage_status

Estado de la etapa del proyecto.

CREATE TYPE project_management.stage_status AS ENUM (
    'pending',
    'in_progress',
    'completed',
    'cancelled'
);

---

6. lot_status

Estado del lote/terreno.

CREATE TYPE project_management.lot_status AS ENUM (
    'available',
    'reserved',
    'sold',
    'in_construction',
    'completed',
    'delivered',
    'cancelled'
);

Descripcion:

• available: Lote disponible para venta
• reserved: Lote reservado temporalmente
• sold: Lote vendido
• in_construction: Vivienda en construccion
• completed: Vivienda terminada
• delivered: Vivienda entregada al propietario
• cancelled: Lote cancelado

---

7. unit_type

Tipo de unidad habitacional.

CREATE TYPE project_management.unit_type AS ENUM (
    'casa_unifamiliar',
    'departamento',
    'duplex',
    'triplex',
    'local_comercial',
    'bodega'
);

---

8. unit_status

Estado de la unidad habitacional.

CREATE TYPE project_management.unit_status AS ENUM (
    'planned',
    'in_construction',
    'completed',
    'delivered',
    'cancelled'
);

---

9. prototype_category

Categoria del prototipo de vivienda.

CREATE TYPE project_management.prototype_category AS ENUM (
    'casa_unifamiliar',
    'departamento',
    'duplex',
    'triplex',
    'local_comercial'
);

---

10. prototype_segment

Segmento de mercado del prototipo.

CREATE TYPE project_management.prototype_segment AS ENUM (
    'interes_social',
    'interes_medio',
    'residencial_medio',
    'residencial_alto',
    'premium'
);

---

11. team_role_type

Roles del equipo de proyecto.

CREATE TYPE project_management.team_role_type AS ENUM (
    'director',
    'residente',
    'ingeniero_civil',
    'ingeniero_electrico',
    'ingeniero_hidraulico',
    'supervisor',
    'gerente_compras',
    'coordinador_calidad',
    'coordinador_seguridad'
);

---

12. milestone_type

Tipos de hitos del proyecto.

CREATE TYPE project_management.milestone_type AS ENUM (
    'arranque',
    'preliminares',
    'cimentacion',
    'estructura',
    'albanileria',
    'instalaciones',
    'acabados',
    'exteriores',
    'urbanizacion',
    'entrega_parcial',
    'cierre_administrativo'
);

---

13. critical_date_type

Tipos de fechas criticas.

CREATE TYPE project_management.critical_date_type AS ENUM (
    'contractual',
    'regulatory',
    'financial',
    'milestone',
    'other'
);

---

14. construction_phase_name

Fases constructivas para avance fisico.

CREATE TYPE project_management.construction_phase_name AS ENUM (
    'preliminares',
    'cimentacion',
    'estructura',
    'muros_albanileria',
    'instalaciones_hidraulicas',
    'instalaciones_electricas',
    'instalaciones_sanitarias',
    'acabados_interiores',
    'acabados_exteriores'
);

---

15. construction_phase_status

Estado de la fase constructiva.

CREATE TYPE project_management.construction_phase_status AS ENUM (
    'not_started',
    'in_progress',
    'completed',
    'on_hold'
);

---

16. document_type

Tipos de documentos del proyecto.

CREATE TYPE project_management.document_type AS ENUM (
    'construction_license',
    'environmental_impact',
    'land_use_permit',
    'approved_plan',
    'infonavit_certification',
    'fovissste_certification',
    'contract',
    'other'
);

---

Tablas Principales

1. projects

Catalogo principal de proyectos de construccion.

DDL Completo

-- ============================================================================
-- Schema: project_management
-- Tabla: projects
-- Descripcion: Catalogo principal de proyectos de construccion
-- Modulo: MAI-002
-- ============================================================================

CREATE TABLE IF NOT EXISTS project_management.projects (
    -- Primary Key
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Multi-tenant
    tenant_id UUID NOT NULL REFERENCES core_tenants.tenants(id) ON DELETE CASCADE,

    -- Codigo auto-generado
    project_code VARCHAR(20) NOT NULL UNIQUE,

    -- Informacion basica
    name VARCHAR(200) NOT NULL,
    description TEXT,
    project_type project_management.project_type NOT NULL,
    status project_management.project_status NOT NULL DEFAULT 'licitacion',

    -- Cliente
    client_type project_management.client_type NOT NULL,
    client_id UUID REFERENCES core_catalogs.contacts(id),
    client_name VARCHAR(200) NOT NULL,
    client_rfc VARCHAR(13),
    client_contact_name VARCHAR(100),
    client_contact_email VARCHAR(100),
    client_contact_phone VARCHAR(20),

    -- Contrato
    contract_type project_management.contract_type NOT NULL,
    contract_number VARCHAR(50),
    contract_amount DECIMAL(15,2) NOT NULL,
    contract_currency_id UUID REFERENCES core_catalogs.currencies(id),

    -- Ubicacion
    address TEXT NOT NULL,
    state VARCHAR(100) NOT NULL,
    municipality VARCHAR(100) NOT NULL,
    postal_code VARCHAR(5),
    country_id UUID REFERENCES core_catalogs.countries(id),
    state_id UUID REFERENCES core_catalogs.states(id),
    latitude DECIMAL(10,6),
    longitude DECIMAL(10,6),
    total_area DECIMAL(12,2) NOT NULL, -- m²
    buildable_area DECIMAL(12,2) NOT NULL, -- m²

    -- Fechas
    bidding_date DATE,
    award_date DATE,
    contract_start_date DATE NOT NULL,
    actual_start_date DATE,
    contract_duration INTEGER NOT NULL, -- meses
    scheduled_end_date DATE NOT NULL,
    actual_end_date DATE,
    delivery_date DATE,
    closure_date DATE,

    -- Informacion legal
    construction_license_number VARCHAR(50),
    license_issue_date DATE,
    license_expiration_date DATE,
    environmental_impact_number VARCHAR(50),
    land_use_approved VARCHAR(20),
    approved_plan_number VARCHAR(50),
    infonavit_number VARCHAR(50),
    fovissste_number VARCHAR(50),

    -- Metricas calculadas (triggers)
    total_housing_units INTEGER NOT NULL DEFAULT 0,
    delivered_housing_units INTEGER NOT NULL DEFAULT 0,
    physical_progress DECIMAL(5,2) NOT NULL DEFAULT 0, -- %
    financial_progress DECIMAL(5,2) NOT NULL DEFAULT 0, -- %

    -- Configuracion
    settings JSONB DEFAULT '{}',
    metadata JSONB DEFAULT '{}',

    -- Auditoria
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by UUID REFERENCES core_users.users(id),
    updated_by UUID REFERENCES core_users.users(id),

    -- Soft delete
    is_active BOOLEAN NOT NULL DEFAULT true,
    deleted_at TIMESTAMPTZ,
    deleted_by UUID REFERENCES core_users.users(id),

    -- Constraints
    CONSTRAINT chk_projects_total_area CHECK (total_area > 0),
    CONSTRAINT chk_projects_buildable_area CHECK (buildable_area > 0 AND buildable_area <= total_area),
    CONSTRAINT chk_projects_contract_amount CHECK (contract_amount > 0),
    CONSTRAINT chk_projects_duration CHECK (contract_duration > 0),
    CONSTRAINT chk_projects_progress CHECK (
        physical_progress >= 0 AND physical_progress <= 100 AND
        financial_progress >= 0 AND financial_progress <= 100
    ),
    CONSTRAINT chk_projects_dates CHECK (scheduled_end_date > contract_start_date),
    CONSTRAINT chk_projects_actual_dates CHECK (
        actual_end_date IS NULL OR actual_end_date >= actual_start_date
    )
);

-- Comentarios
COMMENT ON TABLE project_management.projects IS 'Catalogo principal de proyectos de construccion';
COMMENT ON COLUMN project_management.projects.tenant_id IS 'ID de la constructora (multi-tenancy)';
COMMENT ON COLUMN project_management.projects.project_code IS 'Codigo auto-generado: PROJ-2025-001';
COMMENT ON COLUMN project_management.projects.project_type IS 'Tipo: fraccionamiento_horizontal, conjunto_habitacional, edificio_vertical, mixto';
COMMENT ON COLUMN project_management.projects.status IS 'Estado: licitacion, adjudicado, ejecucion, entregado, cerrado';
COMMENT ON COLUMN project_management.projects.total_housing_units IS 'Total de viviendas (calculado por trigger)';
COMMENT ON COLUMN project_management.projects.physical_progress IS 'Avance fisico en porcentaje (calculado por trigger)';

Indices

-- ============================================================================
-- INDICES: projects
-- ============================================================================

-- Indice obligatorio para RLS
CREATE INDEX idx_projects_tenant_id ON project_management.projects(tenant_id);

-- Indice para codigo unico
CREATE UNIQUE INDEX idx_projects_code ON project_management.projects(project_code);

-- Indices para busqueda y filtros
CREATE INDEX idx_projects_status ON project_management.projects(status) WHERE is_active = true;
CREATE INDEX idx_projects_type ON project_management.projects(project_type) WHERE is_active = true;
CREATE INDEX idx_projects_client_id ON project_management.projects(client_id);
CREATE INDEX idx_projects_created_at ON project_management.projects(created_at DESC);

-- Indice para busqueda full-text
CREATE INDEX idx_projects_search ON project_management.projects
    USING gin(to_tsvector('spanish',
        name || ' ' ||
        COALESCE(project_code, '') || ' ' ||
        COALESCE(client_name, '') || ' ' ||
        COALESCE(description, '')
    ));

-- Indice para ubicacion geografica
CREATE INDEX idx_projects_location ON project_management.projects(state, municipality);

-- Indice compuesto para dashboard
CREATE INDEX idx_projects_tenant_status_active
    ON project_management.projects(tenant_id, status, is_active);

---

2. stages

Etapas del proyecto (1er nivel de jerarquia).

DDL Completo

-- ============================================================================
-- Schema: project_management
-- Tabla: stages
-- Descripcion: Etapas del proyecto (1er nivel de jerarquia)
-- Modulo: MAI-002
-- ============================================================================

CREATE TABLE IF NOT EXISTS project_management.stages (
    -- Primary Key
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Multi-tenant
    tenant_id UUID NOT NULL REFERENCES core_tenants.tenants(id) ON DELETE CASCADE,

    -- Relacion con proyecto
    project_id UUID NOT NULL REFERENCES project_management.projects(id) ON DELETE CASCADE,

    -- Informacion basica
    code VARCHAR(20) NOT NULL,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    status project_management.stage_status NOT NULL DEFAULT 'pending',
    stage_number INTEGER NOT NULL,

    -- Fechas planificadas
    planned_start_date DATE NOT NULL,
    planned_end_date DATE NOT NULL,
    actual_start_date DATE,
    actual_end_date DATE,

    -- Metricas
    total_lots INTEGER NOT NULL DEFAULT 0,
    total_housing_units INTEGER NOT NULL DEFAULT 0,
    progress DECIMAL(5,2) NOT NULL DEFAULT 0, -- %

    -- Configuracion
    metadata JSONB DEFAULT '{}',

    -- Auditoria
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by UUID REFERENCES core_users.users(id),
    updated_by UUID REFERENCES core_users.users(id),

    -- Soft delete
    is_active BOOLEAN NOT NULL DEFAULT true,
    deleted_at TIMESTAMPTZ,
    deleted_by UUID REFERENCES core_users.users(id),

    -- Constraints
    CONSTRAINT uq_stages_project_code UNIQUE (project_id, code),
    CONSTRAINT uq_stages_project_number UNIQUE (project_id, stage_number),
    CONSTRAINT chk_stages_dates CHECK (planned_end_date > planned_start_date),
    CONSTRAINT chk_stages_actual_dates CHECK (
        actual_end_date IS NULL OR actual_end_date >= actual_start_date
    ),
    CONSTRAINT chk_stages_progress CHECK (progress >= 0 AND progress <= 100),
    CONSTRAINT chk_stages_stage_number CHECK (stage_number > 0)
);

-- Comentarios
COMMENT ON TABLE project_management.stages IS 'Etapas del proyecto (1er nivel de jerarquia)';
COMMENT ON COLUMN project_management.stages.stage_number IS 'Numero secuencial de la etapa dentro del proyecto';
COMMENT ON COLUMN project_management.stages.progress IS 'Avance fisico de la etapa en porcentaje';

Indices

-- ============================================================================
-- INDICES: stages
-- ============================================================================

CREATE INDEX idx_stages_tenant_id ON project_management.stages(tenant_id);
CREATE INDEX idx_stages_project_id ON project_management.stages(project_id);
CREATE INDEX idx_stages_status ON project_management.stages(status) WHERE is_active = true;
CREATE INDEX idx_stages_project_number ON project_management.stages(project_id, stage_number);

---

3. blocks

Manzanas del proyecto (2do nivel de jerarquia, solo para fraccionamientos).

DDL Completo

-- ============================================================================
-- Schema: project_management
-- Tabla: blocks
-- Descripcion: Manzanas (2do nivel jerarquia, solo fraccionamientos)
-- Modulo: MAI-002
-- ============================================================================

CREATE TABLE IF NOT EXISTS project_management.blocks (
    -- Primary Key
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Multi-tenant
    tenant_id UUID NOT NULL REFERENCES core_tenants.tenants(id) ON DELETE CASCADE,

    -- Relacion con etapa
    stage_id UUID NOT NULL REFERENCES project_management.stages(id) ON DELETE CASCADE,

    -- Informacion basica
    code VARCHAR(20) NOT NULL,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    block_type VARCHAR(50), -- comercial, residencial, mixto

    -- Metricas
    total_lots INTEGER NOT NULL DEFAULT 0,
    total_area DECIMAL(12,2), -- m²

    -- Configuracion
    metadata JSONB DEFAULT '{}',

    -- Auditoria
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by UUID REFERENCES core_users.users(id),
    updated_by UUID REFERENCES core_users.users(id),

    -- Soft delete
    is_active BOOLEAN NOT NULL DEFAULT true,
    deleted_at TIMESTAMPTZ,
    deleted_by UUID REFERENCES core_users.users(id),

    -- Constraints
    CONSTRAINT uq_blocks_stage_code UNIQUE (stage_id, code),
    CONSTRAINT chk_blocks_total_area CHECK (total_area IS NULL OR total_area > 0)
);

-- Comentarios
COMMENT ON TABLE project_management.blocks IS 'Manzanas del proyecto (solo para fraccionamientos horizontales)';
COMMENT ON COLUMN project_management.blocks.block_type IS 'Tipo de manzana: comercial, residencial, mixto';

Indices

-- ============================================================================
-- INDICES: blocks
-- ============================================================================

CREATE INDEX idx_blocks_tenant_id ON project_management.blocks(tenant_id);
CREATE INDEX idx_blocks_stage_id ON project_management.blocks(stage_id);
CREATE INDEX idx_blocks_stage_code ON project_management.blocks(stage_id, code);

---

4. lots

Lotes/terrenos individuales (3er nivel de jerarquia).

DDL Completo

-- ============================================================================
-- Schema: project_management
-- Tabla: lots
-- Descripcion: Lotes/terrenos individuales (3er nivel jerarquia)
-- Modulo: MAI-002
-- ============================================================================

CREATE TABLE IF NOT EXISTS project_management.lots (
    -- Primary Key
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Multi-tenant
    tenant_id UUID NOT NULL REFERENCES core_tenants.tenants(id) ON DELETE CASCADE,

    -- Relacion jerarquica (project_id siempre, block_id opcional)
    project_id UUID NOT NULL REFERENCES project_management.projects(id) ON DELETE CASCADE,
    stage_id UUID NOT NULL REFERENCES project_management.stages(id) ON DELETE CASCADE,
    block_id UUID REFERENCES project_management.blocks(id) ON DELETE CASCADE,

    -- Identificacion del lote
    lot_number VARCHAR(20) NOT NULL,
    cadastral_key VARCHAR(50),

    -- Estado
    status project_management.lot_status NOT NULL DEFAULT 'available',

    -- Dimensiones
    lot_area DECIMAL(12,2) NOT NULL, -- m²
    front_meters DECIMAL(8,2),
    depth_meters DECIMAL(8,2),

    -- Valores
    cadastral_value DECIMAL(15,2),
    base_price DECIMAL(15,2),
    sale_price DECIMAL(15,2),

    -- Venta
    owner_id UUID REFERENCES core_catalogs.contacts(id),
    sale_date DATE,
    sale_notes TEXT,

    -- Configuracion
    metadata JSONB DEFAULT '{}',

    -- Auditoria
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by UUID REFERENCES core_users.users(id),
    updated_by UUID REFERENCES core_users.users(id),

    -- Soft delete
    is_active BOOLEAN NOT NULL DEFAULT true,
    deleted_at TIMESTAMPTZ,
    deleted_by UUID REFERENCES core_users.users(id),

    -- Constraints
    CONSTRAINT uq_lots_project_stage_number UNIQUE (project_id, stage_id, lot_number),
    CONSTRAINT chk_lots_area CHECK (lot_area > 0),
    CONSTRAINT chk_lots_dimensions CHECK (
        (front_meters IS NULL AND depth_meters IS NULL) OR
        (front_meters > 0 AND depth_meters > 0)
    ),
    CONSTRAINT chk_lots_prices CHECK (
        (cadastral_value IS NULL OR cadastral_value > 0) AND
        (base_price IS NULL OR base_price > 0) AND
        (sale_price IS NULL OR sale_price > 0)
    ),
    CONSTRAINT chk_lots_sold_has_owner CHECK (
        status != 'sold' OR owner_id IS NOT NULL
    )
);

-- Comentarios
COMMENT ON TABLE project_management.lots IS 'Lotes/terrenos individuales donde se construiran viviendas';
COMMENT ON COLUMN project_management.lots.status IS 'Estado: available, reserved, sold, in_construction, completed, delivered';
COMMENT ON COLUMN project_management.lots.block_id IS 'FK a blocks (NULL para conjuntos sin manzanas)';
COMMENT ON COLUMN project_management.lots.owner_id IS 'FK a contacts (derechohabiente/comprador)';

Indices

-- ============================================================================
-- INDICES: lots
-- ============================================================================

CREATE INDEX idx_lots_tenant_id ON project_management.lots(tenant_id);
CREATE INDEX idx_lots_project_id ON project_management.lots(project_id);
CREATE INDEX idx_lots_stage_id ON project_management.lots(stage_id);
CREATE INDEX idx_lots_block_id ON project_management.lots(block_id);
CREATE INDEX idx_lots_owner_id ON project_management.lots(owner_id);
CREATE INDEX idx_lots_status ON project_management.lots(status) WHERE is_active = true;
CREATE INDEX idx_lots_project_stage_number ON project_management.lots(project_id, stage_id, lot_number);

---

5. housing_units

Viviendas construidas en los lotes (4to nivel de jerarquia).

DDL Completo

-- ============================================================================
-- Schema: project_management
-- Tabla: housing_units
-- Descripcion: Viviendas construidas en los lotes (4to nivel jerarquia)
-- Modulo: MAI-002
-- ============================================================================

CREATE TABLE IF NOT EXISTS project_management.housing_units (
    -- Primary Key
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Multi-tenant
    tenant_id UUID NOT NULL REFERENCES core_tenants.tenants(id) ON DELETE CASCADE,

    -- Relacion jerarquica
    project_id UUID NOT NULL REFERENCES project_management.projects(id) ON DELETE CASCADE,
    lot_id UUID NOT NULL REFERENCES project_management.lots(id) ON DELETE CASCADE,
    prototype_id UUID REFERENCES project_management.housing_prototypes(id),

    -- Identificacion
    unit_code VARCHAR(50) NOT NULL,
    unit_type project_management.unit_type NOT NULL,
    status project_management.unit_status NOT NULL DEFAULT 'planned',

    -- Caracteristicas (heredadas del prototipo al momento de asignacion)
    construction_area DECIMAL(10,2) NOT NULL, -- m²
    land_area DECIMAL(10,2),
    bedrooms INTEGER,
    bathrooms DECIMAL(3,1),
    parking_spaces INTEGER,
    levels INTEGER,

    -- Costos
    estimated_cost DECIMAL(15,2),
    actual_cost DECIMAL(15,2),
    total_cost DECIMAL(15,2),

    -- Avance
    physical_progress DECIMAL(5,2) NOT NULL DEFAULT 0, -- %

    -- Fechas
    construction_start_date DATE,
    estimated_delivery_date DATE,
    actual_delivery_date DATE,

    -- Snapshot del prototipo (JSONB)
    prototype_snapshot JSONB,

    -- Configuracion
    metadata JSONB DEFAULT '{}',

    -- Auditoria
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by UUID REFERENCES core_users.users(id),
    updated_by UUID REFERENCES core_users.users(id),

    -- Soft delete
    is_active BOOLEAN NOT NULL DEFAULT true,
    deleted_at TIMESTAMPTZ,
    deleted_by UUID REFERENCES core_users.users(id),

    -- Constraints
    CONSTRAINT uq_housing_units_project_code UNIQUE (project_id, unit_code),
    CONSTRAINT chk_housing_units_area CHECK (construction_area > 0),
    CONSTRAINT chk_housing_units_rooms CHECK (
        bedrooms IS NULL OR bedrooms > 0
    ),
    CONSTRAINT chk_housing_units_bathrooms CHECK (
        bathrooms IS NULL OR bathrooms > 0
    ),
    CONSTRAINT chk_housing_units_costs CHECK (
        (estimated_cost IS NULL OR estimated_cost > 0) AND
        (actual_cost IS NULL OR actual_cost >= 0) AND
        (total_cost IS NULL OR total_cost >= 0)
    ),
    CONSTRAINT chk_housing_units_progress CHECK (
        physical_progress >= 0 AND physical_progress <= 100
    ),
    CONSTRAINT chk_housing_units_dates CHECK (
        actual_delivery_date IS NULL OR
        construction_start_date IS NULL OR
        actual_delivery_date >= construction_start_date
    )
);

-- Comentarios
COMMENT ON TABLE project_management.housing_units IS 'Viviendas construidas en los lotes';
COMMENT ON COLUMN project_management.housing_units.prototype_id IS 'FK al prototipo asignado (puede ser NULL si es custom)';
COMMENT ON COLUMN project_management.housing_units.prototype_snapshot IS 'Snapshot JSON del prototipo al momento de asignacion';
COMMENT ON COLUMN project_management.housing_units.physical_progress IS 'Avance fisico calculado desde construction_phases';

Indices

-- ============================================================================
-- INDICES: housing_units
-- ============================================================================

CREATE INDEX idx_housing_units_tenant_id ON project_management.housing_units(tenant_id);
CREATE INDEX idx_housing_units_project_id ON project_management.housing_units(project_id);
CREATE INDEX idx_housing_units_lot_id ON project_management.housing_units(lot_id);
CREATE INDEX idx_housing_units_prototype_id ON project_management.housing_units(prototype_id);
CREATE INDEX idx_housing_units_status ON project_management.housing_units(status) WHERE is_active = true;
CREATE INDEX idx_housing_units_project_code ON project_management.housing_units(project_id, unit_code);

---

6. housing_prototypes

Catalogo de prototipos de vivienda.

DDL Completo

-- ============================================================================
-- Schema: project_management
-- Tabla: housing_prototypes
-- Descripcion: Catalogo de prototipos de vivienda con versionado
-- Modulo: MAI-002
-- ============================================================================

CREATE TABLE IF NOT EXISTS project_management.housing_prototypes (
    -- Primary Key
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Multi-tenant
    tenant_id UUID NOT NULL REFERENCES core_tenants.tenants(id) ON DELETE CASCADE,

    -- Relacion con proyecto (opcional, puede ser catalogo general)
    project_id UUID REFERENCES project_management.projects(id),

    -- Identificacion
    code VARCHAR(50) NOT NULL,
    name VARCHAR(200) NOT NULL,
    description TEXT,

    -- Clasificacion
    category project_management.prototype_category NOT NULL,
    segment project_management.prototype_segment NOT NULL,

    -- Versionado
    current_version INTEGER NOT NULL DEFAULT 1,

    -- Caracteristicas basicas
    construction_area DECIMAL(10,2) NOT NULL, -- m²
    land_area DECIMAL(10,2),
    sellable_area DECIMAL(10,2),
    bedrooms INTEGER NOT NULL,
    bathrooms DECIMAL(3,1) NOT NULL,
    half_bathrooms INTEGER DEFAULT 0,
    parking_spaces INTEGER DEFAULT 0,
    levels INTEGER DEFAULT 1,

    -- Distribucion
    has_kitchen BOOLEAN DEFAULT true,
    has_dining_room BOOLEAN DEFAULT true,
    has_living_room BOOLEAN DEFAULT true,
    has_laundry_area BOOLEAN DEFAULT false,
    has_patio BOOLEAN DEFAULT false,
    has_garden BOOLEAN DEFAULT false,
    has_balcony BOOLEAN DEFAULT false,
    has_roof_garden BOOLEAN DEFAULT false,

    -- Acabados
    floor_type VARCHAR(50),
    wall_finish VARCHAR(50),
    kitchen_finish VARCHAR(50),
    bathroom_finish VARCHAR(50),

    -- Costos estimados
    estimated_cost DECIMAL(15,2),
    cost_per_sqm DECIMAL(10,2),

    -- Archivos
    floor_plan_url TEXT,
    facade_image_url TEXT,
    render_images JSONB DEFAULT '[]',

    -- Especificaciones tecnicas (JSONB)
    specifications JSONB DEFAULT '{}',

    -- Configuracion
    metadata JSONB DEFAULT '{}',

    -- Auditoria
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by UUID REFERENCES core_users.users(id),
    updated_by UUID REFERENCES core_users.users(id),

    -- Soft delete
    is_active BOOLEAN NOT NULL DEFAULT true,
    deleted_at TIMESTAMPTZ,
    deleted_by UUID REFERENCES core_users.users(id),

    -- Constraints
    CONSTRAINT uq_prototypes_tenant_code UNIQUE (tenant_id, code),
    CONSTRAINT chk_prototypes_areas CHECK (
        construction_area > 0 AND
        (land_area IS NULL OR land_area > 0) AND
        (sellable_area IS NULL OR sellable_area > 0)
    ),
    CONSTRAINT chk_prototypes_rooms CHECK (
        bedrooms > 0 AND
        bathrooms > 0 AND
        half_bathrooms >= 0 AND
        parking_spaces >= 0 AND
        levels > 0
    ),
    CONSTRAINT chk_prototypes_costs CHECK (
        (estimated_cost IS NULL OR estimated_cost > 0) AND
        (cost_per_sqm IS NULL OR cost_per_sqm > 0)
    ),
    CONSTRAINT chk_prototypes_version CHECK (current_version > 0)
);

-- Comentarios
COMMENT ON TABLE project_management.housing_prototypes IS 'Catalogo de prototipos de vivienda con versionado automatico';
COMMENT ON COLUMN project_management.housing_prototypes.project_id IS 'FK a proyecto (NULL = prototipo general del catalogo)';
COMMENT ON COLUMN project_management.housing_prototypes.current_version IS 'Version actual del prototipo';
COMMENT ON COLUMN project_management.housing_prototypes.specifications IS 'Especificaciones tecnicas en formato JSON';

Indices

-- ============================================================================
-- INDICES: housing_prototypes
-- ============================================================================

CREATE INDEX idx_prototypes_tenant_id ON project_management.housing_prototypes(tenant_id);
CREATE INDEX idx_prototypes_project_id ON project_management.housing_prototypes(project_id);
CREATE INDEX idx_prototypes_category ON project_management.housing_prototypes(category) WHERE is_active = true;
CREATE INDEX idx_prototypes_segment ON project_management.housing_prototypes(segment) WHERE is_active = true;
CREATE INDEX idx_prototypes_tenant_code ON project_management.housing_prototypes(tenant_id, code);

-- Indice para busqueda full-text
CREATE INDEX idx_prototypes_search ON project_management.housing_prototypes
    USING gin(to_tsvector('spanish',
        name || ' ' ||
        COALESCE(code, '') || ' ' ||
        COALESCE(description, '')
    ));

---

7. prototype_versions

Historial de versiones de prototipos.

DDL Completo

-- ============================================================================
-- Schema: project_management
-- Tabla: prototype_versions
-- Descripcion: Historial de versiones de prototipos
-- Modulo: MAI-002
-- ============================================================================

CREATE TABLE IF NOT EXISTS project_management.prototype_versions (
    -- Primary Key
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Multi-tenant
    tenant_id UUID NOT NULL REFERENCES core_tenants.tenants(id) ON DELETE CASCADE,

    -- Relacion con prototipo
    prototype_id UUID NOT NULL REFERENCES project_management.housing_prototypes(id) ON DELETE CASCADE,

    -- Version
    version_number INTEGER NOT NULL,
    change_description TEXT NOT NULL,

    -- Snapshot completo de las especificaciones
    specifications JSONB NOT NULL,

    -- Costos en esta version
    estimated_cost DECIMAL(15,2),

    -- Auditoria
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by UUID REFERENCES core_users.users(id),

    -- Constraints
    CONSTRAINT uq_prototype_versions_prototype_version
        UNIQUE (prototype_id, version_number),
    CONSTRAINT chk_prototype_versions_number CHECK (version_number > 0)
);

-- Comentarios
COMMENT ON TABLE project_management.prototype_versions IS 'Historial de versiones de prototipos de vivienda';
COMMENT ON COLUMN project_management.prototype_versions.specifications IS 'Snapshot completo del prototipo en esta version';

Indices

-- ============================================================================
-- INDICES: prototype_versions
-- ============================================================================

CREATE INDEX idx_prototype_versions_tenant_id ON project_management.prototype_versions(tenant_id);
CREATE INDEX idx_prototype_versions_prototype_id ON project_management.prototype_versions(prototype_id);
CREATE INDEX idx_prototype_versions_created_at ON project_management.prototype_versions(created_at DESC);

---

8. project_team_assignments

Asignacion de equipo de trabajo al proyecto.

DDL Completo

-- ============================================================================
-- Schema: project_management
-- Tabla: project_team_assignments
-- Descripcion: Asignacion de equipo de trabajo al proyecto
-- Modulo: MAI-002
-- ============================================================================

CREATE TABLE IF NOT EXISTS project_management.project_team_assignments (
    -- Primary Key
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Multi-tenant
    tenant_id UUID NOT NULL REFERENCES core_tenants.tenants(id) ON DELETE CASCADE,

    -- Relaciones
    project_id UUID NOT NULL REFERENCES project_management.projects(id) ON DELETE CASCADE,
    user_id UUID NOT NULL REFERENCES core_users.users(id) ON DELETE CASCADE,

    -- Rol en el proyecto
    role_type project_management.team_role_type NOT NULL,
    is_primary BOOLEAN NOT NULL DEFAULT false,

    -- Carga de trabajo
    workload_percentage INTEGER NOT NULL DEFAULT 100,

    -- Vigencia
    assignment_start_date DATE NOT NULL,
    assignment_end_date DATE,

    -- Notas
    notes TEXT,

    -- Auditoria
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by UUID REFERENCES core_users.users(id),
    updated_by UUID REFERENCES core_users.users(id),

    -- Soft delete
    is_active BOOLEAN NOT NULL DEFAULT true,
    deleted_at TIMESTAMPTZ,
    deleted_by UUID REFERENCES core_users.users(id),

    -- Constraints
    CONSTRAINT uq_team_assignments_project_user_role
        UNIQUE (project_id, user_id, role_type),
    CONSTRAINT chk_team_assignments_workload
        CHECK (workload_percentage > 0 AND workload_percentage <= 100),
    CONSTRAINT chk_team_assignments_dates
        CHECK (assignment_end_date IS NULL OR assignment_end_date > assignment_start_date)
);

-- Comentarios
COMMENT ON TABLE project_management.project_team_assignments IS 'Asignacion de equipo de trabajo a proyectos con validacion de workload';
COMMENT ON COLUMN project_management.project_team_assignments.is_primary IS 'Indica si es el responsable principal (ej: Director principal)';
COMMENT ON COLUMN project_management.project_team_assignments.workload_percentage IS 'Porcentaje de tiempo dedicado al proyecto (1-100)';

Indices

-- ============================================================================
-- INDICES: project_team_assignments
-- ============================================================================

CREATE INDEX idx_team_assignments_tenant_id ON project_management.project_team_assignments(tenant_id);
CREATE INDEX idx_team_assignments_project_id ON project_management.project_team_assignments(project_id);
CREATE INDEX idx_team_assignments_user_id ON project_management.project_team_assignments(user_id);
CREATE INDEX idx_team_assignments_role ON project_management.project_team_assignments(role_type);
CREATE INDEX idx_team_assignments_active ON project_management.project_team_assignments(is_active, assignment_end_date);

---

9. project_milestones

Hitos del proyecto con dependencias.

DDL Completo

-- ============================================================================
-- Schema: project_management
-- Tabla: project_milestones
-- Descripcion: Hitos del proyecto con dependencias y estado
-- Modulo: MAI-002
-- ============================================================================

CREATE TABLE IF NOT EXISTS project_management.project_milestones (
    -- Primary Key
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Multi-tenant
    tenant_id UUID NOT NULL REFERENCES core_tenants.tenants(id) ON DELETE CASCADE,

    -- Relacion con proyecto
    project_id UUID NOT NULL REFERENCES project_management.projects(id) ON DELETE CASCADE,

    -- Tipo de hito
    milestone_type project_management.milestone_type NOT NULL,

    -- Informacion
    name VARCHAR(200) NOT NULL,
    description TEXT,

    -- Fechas
    planned_date DATE NOT NULL,
    actual_date DATE,

    -- Estado
    status VARCHAR(20) NOT NULL DEFAULT 'pending', -- pending, in_progress, completed, delayed

    -- Dependencias (array de IDs)
    dependencies JSONB DEFAULT '[]',

    -- Responsable
    responsible_user_id UUID REFERENCES core_users.users(id),

    -- Auditoria
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by UUID REFERENCES core_users.users(id),
    updated_by UUID REFERENCES core_users.users(id),

    -- Soft delete
    is_active BOOLEAN NOT NULL DEFAULT true,
    deleted_at TIMESTAMPTZ,
    deleted_by UUID REFERENCES core_users.users(id),

    -- Constraints
    CONSTRAINT chk_milestones_status
        CHECK (status IN ('pending', 'in_progress', 'completed', 'delayed', 'cancelled'))
);

-- Comentarios
COMMENT ON TABLE project_management.project_milestones IS 'Hitos del proyecto con dependencias y seguimiento';
COMMENT ON COLUMN project_management.project_milestones.dependencies IS 'Array JSON de IDs de hitos prerequisitos';

Indices

-- ============================================================================
-- INDICES: project_milestones
-- ============================================================================

CREATE INDEX idx_milestones_tenant_id ON project_management.project_milestones(tenant_id);
CREATE INDEX idx_milestones_project_id ON project_management.project_milestones(project_id);
CREATE INDEX idx_milestones_type ON project_management.project_milestones(milestone_type);
CREATE INDEX idx_milestones_status ON project_management.project_milestones(status) WHERE is_active = true;
CREATE INDEX idx_milestones_planned_date ON project_management.project_milestones(planned_date);
CREATE INDEX idx_milestones_responsible ON project_management.project_milestones(responsible_user_id);

---

10. critical_dates

Fechas criticas con alertas automaticas.

DDL Completo

-- ============================================================================
-- Schema: project_management
-- Tabla: critical_dates
-- Descripcion: Fechas criticas del proyecto con alertas automaticas
-- Modulo: MAI-002
-- ============================================================================

CREATE TABLE IF NOT EXISTS project_management.critical_dates (
    -- Primary Key
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Multi-tenant
    tenant_id UUID NOT NULL REFERENCES core_tenants.tenants(id) ON DELETE CASCADE,

    -- Relacion con proyecto
    project_id UUID NOT NULL REFERENCES project_management.projects(id) ON DELETE CASCADE,

    -- Tipo de fecha
    date_type project_management.critical_date_type NOT NULL,

    -- Informacion
    name VARCHAR(200) NOT NULL,
    description TEXT,

    -- Fecha
    date_value DATE NOT NULL,

    -- Alertas
    alert_days_before INTEGER NOT NULL DEFAULT 7,
    alert_sent BOOLEAN NOT NULL DEFAULT false,
    alert_sent_at TIMESTAMPTZ,

    -- Auditoria
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by UUID REFERENCES core_users.users(id),
    updated_by UUID REFERENCES core_users.users(id),

    -- Soft delete
    is_active BOOLEAN NOT NULL DEFAULT true,
    deleted_at TIMESTAMPTZ,
    deleted_by UUID REFERENCES core_users.users(id),

    -- Constraints
    CONSTRAINT chk_critical_dates_alert_days
        CHECK (alert_days_before >= 0 AND alert_days_before <= 365)
);

-- Comentarios
COMMENT ON TABLE project_management.critical_dates IS 'Fechas criticas con sistema de alertas automaticas';
COMMENT ON COLUMN project_management.critical_dates.alert_days_before IS 'Dias antes de la fecha para enviar alerta';
COMMENT ON COLUMN project_management.critical_dates.alert_sent IS 'Indica si ya se envio la alerta';

Indices

-- ============================================================================
-- INDICES: critical_dates
-- ============================================================================

CREATE INDEX idx_critical_dates_tenant_id ON project_management.critical_dates(tenant_id);
CREATE INDEX idx_critical_dates_project_id ON project_management.critical_dates(project_id);
CREATE INDEX idx_critical_dates_type ON project_management.critical_dates(date_type);
CREATE INDEX idx_critical_dates_date ON project_management.critical_dates(date_value);
CREATE INDEX idx_critical_dates_alerts ON project_management.critical_dates(alert_sent, date_value)
    WHERE is_active = true;

---

11. construction_phases

Fases constructivas para avance fisico detallado.

DDL Completo

-- ============================================================================
-- Schema: project_management
-- Tabla: construction_phases
-- Descripcion: Fases constructivas para calculo de avance fisico
-- Modulo: MAI-002
-- ============================================================================

CREATE TABLE IF NOT EXISTS project_management.construction_phases (
    -- Primary Key
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Multi-tenant
    tenant_id UUID NOT NULL REFERENCES core_tenants.tenants(id) ON DELETE CASCADE,

    -- Relacion con vivienda
    housing_unit_id UUID NOT NULL REFERENCES project_management.housing_units(id) ON DELETE CASCADE,

    -- Fase constructiva
    phase_name project_management.construction_phase_name NOT NULL,
    weight_percentage DECIMAL(5,2) NOT NULL, -- % de peso en avance total
    progress_percentage DECIMAL(5,2) NOT NULL DEFAULT 0, -- % de avance de esta fase

    -- Fechas
    start_date DATE,
    end_date DATE,

    -- Estado
    status project_management.construction_phase_status NOT NULL DEFAULT 'not_started',

    -- Notas
    notes TEXT,

    -- Auditoria
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by UUID REFERENCES core_users.users(id),
    updated_by UUID REFERENCES core_users.users(id),

    -- Soft delete
    is_active BOOLEAN NOT NULL DEFAULT true,
    deleted_at TIMESTAMPTZ,
    deleted_by UUID REFERENCES core_users.users(id),

    -- Constraints
    CONSTRAINT uq_construction_phases_unit_phase
        UNIQUE (housing_unit_id, phase_name),
    CONSTRAINT chk_construction_phases_weight
        CHECK (weight_percentage >= 0 AND weight_percentage <= 100),
    CONSTRAINT chk_construction_phases_progress
        CHECK (progress_percentage >= 0 AND progress_percentage <= 100),
    CONSTRAINT chk_construction_phases_dates
        CHECK (end_date IS NULL OR start_date IS NULL OR end_date >= start_date)
);

-- Comentarios
COMMENT ON TABLE project_management.construction_phases IS 'Fases constructivas para calculo de avance fisico ponderado';
COMMENT ON COLUMN project_management.construction_phases.weight_percentage IS 'Peso de la fase en el avance total (suma debe ser 100%)';
COMMENT ON COLUMN project_management.construction_phases.progress_percentage IS 'Avance de esta fase especifica';

Indices

-- ============================================================================
-- INDICES: construction_phases
-- ============================================================================

CREATE INDEX idx_construction_phases_tenant_id ON project_management.construction_phases(tenant_id);
CREATE INDEX idx_construction_phases_unit_id ON project_management.construction_phases(housing_unit_id);
CREATE INDEX idx_construction_phases_status ON project_management.construction_phases(status) WHERE is_active = true;

---

12. project_documents

Documentos y permisos del proyecto.

DDL Completo

-- ============================================================================
-- Schema: project_management
-- Tabla: project_documents
-- Descripcion: Documentos legales y administrativos del proyecto
-- Modulo: MAI-002
-- ============================================================================

CREATE TABLE IF NOT EXISTS project_management.project_documents (
    -- Primary Key
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Multi-tenant
    tenant_id UUID NOT NULL REFERENCES core_tenants.tenants(id) ON DELETE CASCADE,

    -- Relacion con proyecto
    project_id UUID NOT NULL REFERENCES project_management.projects(id) ON DELETE CASCADE,

    -- Tipo de documento
    document_type project_management.document_type NOT NULL,

    -- Informacion
    name VARCHAR(200) NOT NULL,
    description TEXT,
    document_number VARCHAR(50),

    -- Archivo
    file_path TEXT,
    file_size BIGINT,
    file_mime_type VARCHAR(100),

    -- Fechas
    issue_date DATE,
    expiration_date DATE,

    -- Auditoria
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by UUID REFERENCES core_users.users(id),
    updated_by UUID REFERENCES core_users.users(id),

    -- Soft delete
    is_active BOOLEAN NOT NULL DEFAULT true,
    deleted_at TIMESTAMPTZ,
    deleted_by UUID REFERENCES core_users.users(id),

    -- Constraints
    CONSTRAINT chk_project_documents_file_size
        CHECK (file_size IS NULL OR file_size > 0),
    CONSTRAINT chk_project_documents_dates
        CHECK (expiration_date IS NULL OR issue_date IS NULL OR expiration_date >= issue_date)
);

-- Comentarios
COMMENT ON TABLE project_management.project_documents IS 'Documentos legales y administrativos del proyecto';
COMMENT ON COLUMN project_management.project_documents.document_type IS 'Tipo: construction_license, environmental_impact, contract, etc.';

Indices

-- ============================================================================
-- INDICES: project_documents
-- ============================================================================

CREATE INDEX idx_project_documents_tenant_id ON project_management.project_documents(tenant_id);
CREATE INDEX idx_project_documents_project_id ON project_management.project_documents(project_id);
CREATE INDEX idx_project_documents_type ON project_management.project_documents(document_type);
CREATE INDEX idx_project_documents_expiration ON project_management.project_documents(expiration_date)
    WHERE is_active = true AND expiration_date IS NOT NULL;

---

Row Level Security (RLS)

Habilitar RLS en Todas las Tablas

-- ============================================================================
-- HABILITAR RLS
-- ============================================================================

ALTER TABLE project_management.projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE project_management.stages ENABLE ROW LEVEL SECURITY;
ALTER TABLE project_management.blocks ENABLE ROW LEVEL SECURITY;
ALTER TABLE project_management.lots ENABLE ROW LEVEL SECURITY;
ALTER TABLE project_management.housing_units ENABLE ROW LEVEL SECURITY;
ALTER TABLE project_management.housing_prototypes ENABLE ROW LEVEL SECURITY;
ALTER TABLE project_management.prototype_versions ENABLE ROW LEVEL SECURITY;
ALTER TABLE project_management.project_team_assignments ENABLE ROW LEVEL SECURITY;
ALTER TABLE project_management.project_milestones ENABLE ROW LEVEL SECURITY;
ALTER TABLE project_management.critical_dates ENABLE ROW LEVEL SECURITY;
ALTER TABLE project_management.construction_phases ENABLE ROW LEVEL SECURITY;
ALTER TABLE project_management.project_documents ENABLE ROW LEVEL SECURITY;

Politicas RLS por Tabla

-- ============================================================================
-- RLS POLICIES: projects
-- ============================================================================

CREATE POLICY tenant_isolation_select_projects ON project_management.projects
    FOR SELECT
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_insert_projects ON project_management.projects
    FOR INSERT
    WITH CHECK (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_update_projects ON project_management.projects
    FOR UPDATE
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_delete_projects ON project_management.projects
    FOR DELETE
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

-- ============================================================================
-- RLS POLICIES: stages
-- ============================================================================

CREATE POLICY tenant_isolation_select_stages ON project_management.stages
    FOR SELECT
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_insert_stages ON project_management.stages
    FOR INSERT
    WITH CHECK (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_update_stages ON project_management.stages
    FOR UPDATE
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_delete_stages ON project_management.stages
    FOR DELETE
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

-- ============================================================================
-- RLS POLICIES: blocks
-- ============================================================================

CREATE POLICY tenant_isolation_select_blocks ON project_management.blocks
    FOR SELECT
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_insert_blocks ON project_management.blocks
    FOR INSERT
    WITH CHECK (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_update_blocks ON project_management.blocks
    FOR UPDATE
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_delete_blocks ON project_management.blocks
    FOR DELETE
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

-- ============================================================================
-- RLS POLICIES: lots
-- ============================================================================

CREATE POLICY tenant_isolation_select_lots ON project_management.lots
    FOR SELECT
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_insert_lots ON project_management.lots
    FOR INSERT
    WITH CHECK (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_update_lots ON project_management.lots
    FOR UPDATE
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_delete_lots ON project_management.lots
    FOR DELETE
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

-- ============================================================================
-- RLS POLICIES: housing_units
-- ============================================================================

CREATE POLICY tenant_isolation_select_housing_units ON project_management.housing_units
    FOR SELECT
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_insert_housing_units ON project_management.housing_units
    FOR INSERT
    WITH CHECK (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_update_housing_units ON project_management.housing_units
    FOR UPDATE
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_delete_housing_units ON project_management.housing_units
    FOR DELETE
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

-- ============================================================================
-- RLS POLICIES: housing_prototypes
-- ============================================================================

CREATE POLICY tenant_isolation_select_prototypes ON project_management.housing_prototypes
    FOR SELECT
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_insert_prototypes ON project_management.housing_prototypes
    FOR INSERT
    WITH CHECK (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_update_prototypes ON project_management.housing_prototypes
    FOR UPDATE
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_delete_prototypes ON project_management.housing_prototypes
    FOR DELETE
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

-- ============================================================================
-- RLS POLICIES: prototype_versions
-- ============================================================================

CREATE POLICY tenant_isolation_select_prototype_versions ON project_management.prototype_versions
    FOR SELECT
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_insert_prototype_versions ON project_management.prototype_versions
    FOR INSERT
    WITH CHECK (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_update_prototype_versions ON project_management.prototype_versions
    FOR UPDATE
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_delete_prototype_versions ON project_management.prototype_versions
    FOR DELETE
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

-- ============================================================================
-- RLS POLICIES: project_team_assignments
-- ============================================================================

CREATE POLICY tenant_isolation_select_team_assignments ON project_management.project_team_assignments
    FOR SELECT
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_insert_team_assignments ON project_management.project_team_assignments
    FOR INSERT
    WITH CHECK (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_update_team_assignments ON project_management.project_team_assignments
    FOR UPDATE
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_delete_team_assignments ON project_management.project_team_assignments
    FOR DELETE
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

-- ============================================================================
-- RLS POLICIES: project_milestones
-- ============================================================================

CREATE POLICY tenant_isolation_select_milestones ON project_management.project_milestones
    FOR SELECT
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_insert_milestones ON project_management.project_milestones
    FOR INSERT
    WITH CHECK (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_update_milestones ON project_management.project_milestones
    FOR UPDATE
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_delete_milestones ON project_management.project_milestones
    FOR DELETE
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

-- ============================================================================
-- RLS POLICIES: critical_dates
-- ============================================================================

CREATE POLICY tenant_isolation_select_critical_dates ON project_management.critical_dates
    FOR SELECT
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_insert_critical_dates ON project_management.critical_dates
    FOR INSERT
    WITH CHECK (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_update_critical_dates ON project_management.critical_dates
    FOR UPDATE
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_delete_critical_dates ON project_management.critical_dates
    FOR DELETE
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

-- ============================================================================
-- RLS POLICIES: construction_phases
-- ============================================================================

CREATE POLICY tenant_isolation_select_construction_phases ON project_management.construction_phases
    FOR SELECT
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_insert_construction_phases ON project_management.construction_phases
    FOR INSERT
    WITH CHECK (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_update_construction_phases ON project_management.construction_phases
    FOR UPDATE
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_delete_construction_phases ON project_management.construction_phases
    FOR DELETE
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

-- ============================================================================
-- RLS POLICIES: project_documents
-- ============================================================================

CREATE POLICY tenant_isolation_select_project_documents ON project_management.project_documents
    FOR SELECT
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_insert_project_documents ON project_management.project_documents
    FOR INSERT
    WITH CHECK (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_update_project_documents ON project_management.project_documents
    FOR UPDATE
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_delete_project_documents ON project_management.project_documents
    FOR DELETE
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

---

Funciones SQL

1. Actualizar updated_at

-- ============================================================================
-- FUNCION: set_updated_at
-- Descripcion: Actualiza automaticamente el timestamp updated_at
-- ============================================================================

CREATE OR REPLACE FUNCTION project_management.set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

---

2. Generar Codigo de Proyecto

-- ============================================================================
-- FUNCION: generate_project_code
-- Descripcion: Genera codigo auto-incremental por tenant y año (PROJ-2025-001)
-- ============================================================================

CREATE OR REPLACE FUNCTION project_management.generate_project_code(p_tenant_id UUID)
RETURNS VARCHAR AS $$
DECLARE
    v_year INTEGER;
    v_sequence INTEGER;
    v_code VARCHAR(20);
BEGIN
    v_year := EXTRACT(YEAR FROM CURRENT_DATE);

    -- Obtener siguiente numero secuencial del año actual
    SELECT COALESCE(MAX(
        CAST(
            SUBSTRING(project_code FROM '\d+$') AS INTEGER
        )
    ), 0) + 1
    INTO v_sequence
    FROM project_management.projects
    WHERE tenant_id = p_tenant_id
      AND project_code LIKE 'PROJ-' || v_year || '-%';

    -- Generar codigo: PROJ-2025-001
    v_code := 'PROJ-' || v_year || '-' || LPAD(v_sequence::TEXT, 3, '0');

    RETURN v_code;
END;
$$ LANGUAGE plpgsql;

---

3. Calcular Avance Fisico de Proyecto

-- ============================================================================
-- FUNCION: calculate_project_physical_progress
-- Descripcion: Calcula avance fisico del proyecto desde housing_units
-- ============================================================================

CREATE OR REPLACE FUNCTION project_management.calculate_project_physical_progress(p_project_id UUID)
RETURNS DECIMAL AS $$
DECLARE
    v_progress DECIMAL(5,2);
BEGIN
    SELECT COALESCE(AVG(physical_progress), 0)
    INTO v_progress
    FROM project_management.housing_units
    WHERE project_id = p_project_id
      AND is_active = true;

    RETURN ROUND(v_progress, 2);
END;
$$ LANGUAGE plpgsql STABLE;

---

4. Calcular Avance Fisico de Vivienda

-- ============================================================================
-- FUNCION: calculate_housing_unit_progress
-- Descripcion: Calcula avance ponderado desde construction_phases
-- ============================================================================

CREATE OR REPLACE FUNCTION project_management.calculate_housing_unit_progress(p_housing_unit_id UUID)
RETURNS DECIMAL AS $$
DECLARE
    v_progress DECIMAL(5,2);
BEGIN
    SELECT COALESCE(SUM(
        (weight_percentage / 100.0) * (progress_percentage / 100.0)
    ) * 100, 0)
    INTO v_progress
    FROM project_management.construction_phases
    WHERE housing_unit_id = p_housing_unit_id
      AND is_active = true;

    RETURN ROUND(v_progress, 2);
END;
$$ LANGUAGE plpgsql STABLE;

---

5. Validar Workload del Usuario

-- ============================================================================
-- FUNCION: validate_user_workload
-- Descripcion: Valida que el usuario no exceda limite de workload por rol
-- ============================================================================

CREATE OR REPLACE FUNCTION project_management.validate_user_workload(
    p_user_id UUID,
    p_role_type project_management.team_role_type,
    p_new_workload INTEGER,
    p_exclude_assignment_id UUID DEFAULT NULL
)
RETURNS BOOLEAN AS $$
DECLARE
    v_current_workload INTEGER;
    v_max_workload INTEGER;
BEGIN
    -- Limites por rol
    v_max_workload := CASE p_role_type
        WHEN 'director' THEN 500
        WHEN 'residente' THEN 200
        WHEN 'ingeniero_civil' THEN 800
        WHEN 'ingeniero_electrico' THEN 800
        WHEN 'ingeniero_hidraulico' THEN 800
        WHEN 'supervisor' THEN 300
        WHEN 'gerente_compras' THEN 400
        WHEN 'coordinador_calidad' THEN 500
        WHEN 'coordinador_seguridad' THEN 500
        ELSE 100
    END;

    -- Calcular workload actual
    SELECT COALESCE(SUM(workload_percentage), 0)
    INTO v_current_workload
    FROM project_management.project_team_assignments
    WHERE user_id = p_user_id
      AND role_type = p_role_type
      AND is_active = true
      AND (assignment_end_date IS NULL OR assignment_end_date >= CURRENT_DATE)
      AND (p_exclude_assignment_id IS NULL OR id != p_exclude_assignment_id);

    -- Validar que no exceda el limite
    RETURN (v_current_workload + p_new_workload) <= v_max_workload;
END;
$$ LANGUAGE plpgsql STABLE;

---

6. Snapshot de Prototipo

-- ============================================================================
-- FUNCION: create_prototype_snapshot
-- Descripcion: Crea snapshot JSON del prototipo para housing_unit
-- ============================================================================

CREATE OR REPLACE FUNCTION project_management.create_prototype_snapshot(p_prototype_id UUID)
RETURNS JSONB AS $$
DECLARE
    v_snapshot JSONB;
BEGIN
    SELECT jsonb_build_object(
        'prototype_id', id,
        'code', code,
        'name', name,
        'category', category,
        'segment', segment,
        'version', current_version,
        'construction_area', construction_area,
        'land_area', land_area,
        'bedrooms', bedrooms,
        'bathrooms', bathrooms,
        'specifications', specifications,
        'estimated_cost', estimated_cost,
        'snapshot_date', NOW()
    )
    INTO v_snapshot
    FROM project_management.housing_prototypes
    WHERE id = p_prototype_id;

    RETURN v_snapshot;
END;
$$ LANGUAGE plpgsql STABLE;

---

Triggers

1. Auto-generar Codigo de Proyecto

-- ============================================================================
-- TRIGGER: Auto-generar project_code
-- ============================================================================

CREATE OR REPLACE FUNCTION project_management.trg_generate_project_code()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.project_code IS NULL OR NEW.project_code = '' THEN
        NEW.project_code := project_management.generate_project_code(NEW.tenant_id);
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_projects_generate_code
    BEFORE INSERT ON project_management.projects
    FOR EACH ROW
    EXECUTE FUNCTION project_management.trg_generate_project_code();

---

2. Actualizar updated_at

-- ============================================================================
-- TRIGGERS: Actualizar updated_at en todas las tablas
-- ============================================================================

CREATE TRIGGER trg_projects_update_timestamp
    BEFORE UPDATE ON project_management.projects
    FOR EACH ROW
    EXECUTE FUNCTION project_management.set_updated_at();

CREATE TRIGGER trg_stages_update_timestamp
    BEFORE UPDATE ON project_management.stages
    FOR EACH ROW
    EXECUTE FUNCTION project_management.set_updated_at();

CREATE TRIGGER trg_blocks_update_timestamp
    BEFORE UPDATE ON project_management.blocks
    FOR EACH ROW
    EXECUTE FUNCTION project_management.set_updated_at();

CREATE TRIGGER trg_lots_update_timestamp
    BEFORE UPDATE ON project_management.lots
    FOR EACH ROW
    EXECUTE FUNCTION project_management.set_updated_at();

CREATE TRIGGER trg_housing_units_update_timestamp
    BEFORE UPDATE ON project_management.housing_units
    FOR EACH ROW
    EXECUTE FUNCTION project_management.set_updated_at();

CREATE TRIGGER trg_prototypes_update_timestamp
    BEFORE UPDATE ON project_management.housing_prototypes
    FOR EACH ROW
    EXECUTE FUNCTION project_management.set_updated_at();

CREATE TRIGGER trg_team_assignments_update_timestamp
    BEFORE UPDATE ON project_management.project_team_assignments
    FOR EACH ROW
    EXECUTE FUNCTION project_management.set_updated_at();

CREATE TRIGGER trg_milestones_update_timestamp
    BEFORE UPDATE ON project_management.project_milestones
    FOR EACH ROW
    EXECUTE FUNCTION project_management.set_updated_at();

CREATE TRIGGER trg_critical_dates_update_timestamp
    BEFORE UPDATE ON project_management.critical_dates
    FOR EACH ROW
    EXECUTE FUNCTION project_management.set_updated_at();

CREATE TRIGGER trg_construction_phases_update_timestamp
    BEFORE UPDATE ON project_management.construction_phases
    FOR EACH ROW
    EXECUTE FUNCTION project_management.set_updated_at();

CREATE TRIGGER trg_project_documents_update_timestamp
    BEFORE UPDATE ON project_management.project_documents
    FOR EACH ROW
    EXECUTE FUNCTION project_management.set_updated_at();

---

3. Validar Workload en Asignacion

-- ============================================================================
-- TRIGGER: Validar workload antes de asignar
-- ============================================================================

CREATE OR REPLACE FUNCTION project_management.trg_validate_team_assignment_workload()
RETURNS TRIGGER AS $$
BEGIN
    IF NOT project_management.validate_user_workload(
        NEW.user_id,
        NEW.role_type,
        NEW.workload_percentage,
        NEW.id
    ) THEN
        RAISE EXCEPTION 'User workload exceeds limit for role %', NEW.role_type;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_team_assignments_validate_workload
    BEFORE INSERT OR UPDATE ON project_management.project_team_assignments
    FOR EACH ROW
    EXECUTE FUNCTION project_management.trg_validate_team_assignment_workload();

---

4. Actualizar Metricas de Proyecto

-- ============================================================================
-- TRIGGER: Actualizar metricas del proyecto
-- ============================================================================

CREATE OR REPLACE FUNCTION project_management.trg_update_project_metrics()
RETURNS TRIGGER AS $$
DECLARE
    v_project_id UUID;
BEGIN
    -- Obtener project_id segun la tabla
    IF TG_TABLE_NAME = 'housing_units' THEN
        v_project_id := COALESCE(NEW.project_id, OLD.project_id);
    ELSIF TG_TABLE_NAME = 'lots' THEN
        v_project_id := COALESCE(NEW.project_id, OLD.project_id);
    END IF;

    -- Actualizar total_housing_units
    UPDATE project_management.projects
    SET total_housing_units = (
        SELECT COUNT(*)
        FROM project_management.housing_units
        WHERE project_id = v_project_id
          AND is_active = true
    ),
    physical_progress = project_management.calculate_project_physical_progress(v_project_id)
    WHERE id = v_project_id;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_housing_units_update_project_metrics
    AFTER INSERT OR UPDATE OR DELETE ON project_management.housing_units
    FOR EACH ROW
    EXECUTE FUNCTION project_management.trg_update_project_metrics();

---

5. Actualizar Avance de Vivienda desde Fases

-- ============================================================================
-- TRIGGER: Actualizar avance de vivienda desde construction_phases
-- ============================================================================

CREATE OR REPLACE FUNCTION project_management.trg_update_housing_unit_progress()
RETURNS TRIGGER AS $$
DECLARE
    v_housing_unit_id UUID;
BEGIN
    v_housing_unit_id := COALESCE(NEW.housing_unit_id, OLD.housing_unit_id);

    UPDATE project_management.housing_units
    SET physical_progress = project_management.calculate_housing_unit_progress(v_housing_unit_id)
    WHERE id = v_housing_unit_id;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_construction_phases_update_progress
    AFTER INSERT OR UPDATE OR DELETE ON project_management.construction_phases
    FOR EACH ROW
    EXECUTE FUNCTION project_management.trg_update_housing_unit_progress();

---

6. Crear Version de Prototipo

-- ============================================================================
-- TRIGGER: Crear version automatica al actualizar prototipo
-- ============================================================================

CREATE OR REPLACE FUNCTION project_management.trg_create_prototype_version()
RETURNS TRIGGER AS $$
BEGIN
    -- Solo si cambio algo relevante
    IF OLD.construction_area IS DISTINCT FROM NEW.construction_area OR
       OLD.bedrooms IS DISTINCT FROM NEW.bedrooms OR
       OLD.estimated_cost IS DISTINCT FROM NEW.estimated_cost OR
       OLD.specifications IS DISTINCT FROM NEW.specifications THEN

        -- Incrementar version
        NEW.current_version := OLD.current_version + 1;

        -- Crear registro de version
        INSERT INTO project_management.prototype_versions (
            tenant_id,
            prototype_id,
            version_number,
            change_description,
            specifications,
            estimated_cost,
            created_by
        ) VALUES (
            NEW.tenant_id,
            NEW.id,
            NEW.current_version,
            'Auto-generated version',
            jsonb_build_object(
                'construction_area', NEW.construction_area,
                'bedrooms', NEW.bedrooms,
                'bathrooms', NEW.bathrooms,
                'specifications', NEW.specifications
            ),
            NEW.estimated_cost,
            NEW.updated_by
        );
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_prototypes_create_version
    BEFORE UPDATE ON project_management.housing_prototypes
    FOR EACH ROW
    EXECUTE FUNCTION project_management.trg_create_prototype_version();

---

7. Crear Snapshot al Asignar Prototipo

-- ============================================================================
-- TRIGGER: Crear snapshot del prototipo al asignar a housing_unit
-- ============================================================================

CREATE OR REPLACE FUNCTION project_management.trg_create_housing_unit_snapshot()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.prototype_id IS NOT NULL AND NEW.prototype_snapshot IS NULL THEN
        NEW.prototype_snapshot := project_management.create_prototype_snapshot(NEW.prototype_id);

        -- Heredar caracteristicas del prototipo
        SELECT
            construction_area,
            land_area,
            bedrooms,
            bathrooms,
            estimated_cost
        INTO
            NEW.construction_area,
            NEW.land_area,
            NEW.bedrooms,
            NEW.bathrooms,
            NEW.estimated_cost
        FROM project_management.housing_prototypes
        WHERE id = NEW.prototype_id;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_housing_units_create_snapshot
    BEFORE INSERT ON project_management.housing_units
    FOR EACH ROW
    EXECUTE FUNCTION project_management.trg_create_housing_unit_snapshot();

---

Seed Data

Prototipos de Ejemplo

-- ============================================================================
-- SEED: Prototipos de ejemplo por tenant
-- ============================================================================

-- Nota: Estos seeds se ejecutan durante el onboarding del tenant
-- El tenant_id se sustituye en tiempo de ejecucion

INSERT INTO project_management.housing_prototypes (
    tenant_id,
    code,
    name,
    category,
    segment,
    construction_area,
    land_area,
    bedrooms,
    bathrooms,
    parking_spaces,
    estimated_cost
) VALUES
(
    '{{tenant_id}}',
    'CASA-A-001',
    'Casa Tipo A - Interes Social',
    'casa_unifamiliar',
    'interes_social',
    50.00,
    90.00,
    2,
    1.0,
    1,
    450000.00
),
(
    '{{tenant_id}}',
    'CASA-B-001',
    'Casa Tipo B - Interes Medio',
    'casa_unifamiliar',
    'interes_medio',
    75.00,
    120.00,
    3,
    2.0,
    2,
    850000.00
),
(
    '{{tenant_id}}',
    'DEPTO-A-001',
    'Departamento Tipo A',
    'departamento',
    'interes_medio',
    60.00,
    NULL,
    2,
    1.0,
    1,
    650000.00
);

---

Consideraciones de Performance

Tabla	Volumen Esperado	Estrategia
projects	Medio (~100-500/tenant)	Indices compuestos, cache
stages	Medio (~3-10 por proyecto)	Indices en project_id
blocks	Medio (~5-20 por etapa)	Indices en stage_id
lots	Alto (~100-5000 por proyecto)	Particionamiento futuro, bulk operations
housing_units	Alto (~100-5000 por proyecto)	Indices GIN, paginacion
housing_prototypes	Bajo (~10-50/tenant)	Cache agresivo
project_team_assignments	Medio (~5-15 por proyecto)	Indices compuestos
construction_phases	Alto (~9 por vivienda)	Indices en housing_unit_id

Optimizaciones Recomendadas

1. Bulk Operations: Crear lotes en lote (hasta 500) usando INSERT ... SELECT
2. Materialized Views: Para dashboards con metricas agregadas
3. Particionamiento: Considerar particionar lots y housing_units por proyecto en instalaciones grandes
4. Cache: Cachear prototipos, ENUMs y metadatos del proyecto
5. Indices Parciales: Usar WHERE is_active = true en indices de busqueda

---

Historial de Cambios

Version	Fecha	Autor	Cambios
1.0	2025-12-06	Requirements-Analyst	Creacion inicial

---

Aprobaciones

Rol	Nombre	Fecha	Firma
DBA	-	-	[ ]
Tech Lead	-	-	[ ]
Architect	-	-	[ ]