-- =====================================================
-- SCHEMA: construction
-- PROPOSITO: Obras, proyectos, lotes, prototipos, avances
-- MODULOS: MAI-002, MAI-003, MAI-005, MAI-009, MAI-012
-- FECHA: 2025-11-24
-- ESPECIFICO: 100% construccion
-- =====================================================

-- Crear schema
CREATE SCHEMA IF NOT EXISTS construction;

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

CREATE TYPE construction.project_status AS ENUM (
  'draft',           -- Borrador
  'planning',        -- En planeacion
  'in_progress',     -- En ejecucion
  'paused',          -- Pausado
  'completed',       -- Completado
  'cancelled'        -- Cancelado
);

CREATE TYPE construction.lot_status AS ENUM (
  'available',       -- Disponible para venta
  'reserved',        -- Reservado
  'sold',            -- Vendido
  'under_construction', -- En construccion
  'delivered',       -- Entregado
  'warranty'         -- En periodo de garantia
);

CREATE TYPE construction.prototype_type AS ENUM (
  'horizontal',      -- Casa horizontal
  'vertical',        -- Departamento en torre
  'commercial',      -- Local comercial
  'mixed'            -- Uso mixto
);

CREATE TYPE construction.advance_status AS ENUM (
  'pending',         -- Pendiente captura
  'captured',        -- Capturado
  'reviewed',        -- Revisado
  'approved',        -- Aprobado
  'rejected'         -- Rechazado
);

CREATE TYPE construction.quality_status AS ENUM (
  'pending',         -- Pendiente revision
  'in_review',       -- En revision
  'approved',        -- Aprobado
  'rejected',        -- Rechazado
  'rework'           -- Requiere retrabajo
);

CREATE TYPE construction.contract_type AS ENUM (
  'fixed_price',     -- Precio fijo
  'unit_price',      -- Precio unitario
  'cost_plus',       -- Costo + porcentaje
  'mixed'            -- Mixto
);

CREATE TYPE construction.contract_status AS ENUM (
  'draft',           -- Borrador
  'pending_approval', -- Pendiente aprobacion
  'active',          -- Activo
  'suspended',       -- Suspendido
  'terminated',      -- Terminado
  'closed'           -- Cerrado
);

-- =====================================================
-- TABLES - ESTRUCTURA DE PROYECTO
-- =====================================================

-- Tabla: fraccionamientos (desarrollo inmobiliario)
CREATE TABLE construction.fraccionamientos (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
  code VARCHAR(20) NOT NULL,
  name VARCHAR(255) NOT NULL,
  description TEXT,
  address TEXT,
  city VARCHAR(100),
  state VARCHAR(100),
  zip_code VARCHAR(10),
  location GEOMETRY(POINT, 4326), -- PostGIS
  total_area_m2 DECIMAL(12,2),
  buildable_area_m2 DECIMAL(12,2),
  total_lots INTEGER DEFAULT 0,
  status construction.project_status NOT NULL DEFAULT 'draft',
  start_date DATE,
  expected_end_date DATE,
  actual_end_date DATE,
  metadata JSONB DEFAULT '{}',

  -- Auditoria
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by UUID REFERENCES auth.users(id),
  updated_at TIMESTAMP,
  updated_by UUID REFERENCES auth.users(id),
  deleted_at TIMESTAMP,
  deleted_by UUID REFERENCES auth.users(id),

  CONSTRAINT uq_fraccionamientos_code_tenant UNIQUE (tenant_id, code)
);

-- Tabla: etapas (fases del fraccionamiento)
CREATE TABLE construction.etapas (
  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) ON DELETE CASCADE,
  code VARCHAR(20) NOT NULL,
  name VARCHAR(100) NOT NULL,
  description TEXT,
  sequence INTEGER NOT NULL DEFAULT 1,
  total_lots INTEGER DEFAULT 0,
  status construction.project_status NOT NULL DEFAULT 'draft',
  start_date DATE,
  expected_end_date DATE,
  actual_end_date DATE,

  -- Auditoria
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by UUID REFERENCES auth.users(id),
  updated_at TIMESTAMP,
  updated_by UUID REFERENCES auth.users(id),
  deleted_at TIMESTAMP,
  deleted_by UUID REFERENCES auth.users(id),

  CONSTRAINT uq_etapas_code_fracc UNIQUE (fraccionamiento_id, code)
);

-- Tabla: manzanas (agrupacion de lotes)
CREATE TABLE construction.manzanas (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
  etapa_id UUID NOT NULL REFERENCES construction.etapas(id) ON DELETE CASCADE,
  code VARCHAR(20) NOT NULL,
  name VARCHAR(100),
  total_lots INTEGER DEFAULT 0,
  polygon GEOMETRY(POLYGON, 4326), -- PostGIS

  -- Auditoria
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by UUID REFERENCES auth.users(id),
  updated_at TIMESTAMP,
  updated_by UUID REFERENCES auth.users(id),
  deleted_at TIMESTAMP,
  deleted_by UUID REFERENCES auth.users(id),

  CONSTRAINT uq_manzanas_code_etapa UNIQUE (etapa_id, code)
);

-- Tabla: lotes (unidades vendibles)
CREATE TABLE construction.lotes (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
  manzana_id UUID NOT NULL REFERENCES construction.manzanas(id) ON DELETE CASCADE,
  prototipo_id UUID REFERENCES construction.prototipos(id),
  code VARCHAR(30) NOT NULL,
  official_number VARCHAR(50), -- Numero oficial catastral
  area_m2 DECIMAL(10,2),
  front_m DECIMAL(8,2),
  depth_m DECIMAL(8,2),
  status construction.lot_status NOT NULL DEFAULT 'available',
  location GEOMETRY(POINT, 4326),
  polygon GEOMETRY(POLYGON, 4326),
  price_base DECIMAL(14,2),
  price_final DECIMAL(14,2),
  buyer_id UUID, -- FK a cliente/derechohabiente
  sale_date DATE,
  delivery_date DATE,

  -- Auditoria
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by UUID REFERENCES auth.users(id),
  updated_at TIMESTAMP,
  updated_by UUID REFERENCES auth.users(id),
  deleted_at TIMESTAMP,
  deleted_by UUID REFERENCES auth.users(id),

  CONSTRAINT uq_lotes_code_manzana UNIQUE (manzana_id, code)
);

-- =====================================================
-- TABLES - ESTRUCTURA VERTICAL (TORRES)
-- =====================================================

-- Tabla: torres (edificios verticales)
CREATE TABLE construction.torres (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
  etapa_id UUID NOT NULL REFERENCES construction.etapas(id) ON DELETE CASCADE,
  code VARCHAR(20) NOT NULL,
  name VARCHAR(100) NOT NULL,
  total_floors INTEGER NOT NULL DEFAULT 1,
  total_units INTEGER DEFAULT 0,
  status construction.project_status NOT NULL DEFAULT 'draft',
  location GEOMETRY(POINT, 4326),

  -- Auditoria
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by UUID REFERENCES auth.users(id),
  updated_at TIMESTAMP,
  updated_by UUID REFERENCES auth.users(id),
  deleted_at TIMESTAMP,
  deleted_by UUID REFERENCES auth.users(id),

  CONSTRAINT uq_torres_code_etapa UNIQUE (etapa_id, code)
);

-- Tabla: niveles (pisos de torre)
CREATE TABLE construction.niveles (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
  torre_id UUID NOT NULL REFERENCES construction.torres(id) ON DELETE CASCADE,
  floor_number INTEGER NOT NULL,
  name VARCHAR(50),
  total_units INTEGER DEFAULT 0,

  -- Auditoria
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by UUID REFERENCES auth.users(id),
  updated_at TIMESTAMP,
  updated_by UUID REFERENCES auth.users(id),
  deleted_at TIMESTAMP,
  deleted_by UUID REFERENCES auth.users(id),

  CONSTRAINT uq_niveles_floor_torre UNIQUE (torre_id, floor_number)
);

-- Tabla: departamentos (unidades en torre)
CREATE TABLE construction.departamentos (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
  nivel_id UUID NOT NULL REFERENCES construction.niveles(id) ON DELETE CASCADE,
  prototipo_id UUID REFERENCES construction.prototipos(id),
  code VARCHAR(30) NOT NULL,
  unit_number VARCHAR(20) NOT NULL,
  area_m2 DECIMAL(10,2),
  status construction.lot_status NOT NULL DEFAULT 'available',
  price_base DECIMAL(14,2),
  price_final DECIMAL(14,2),
  buyer_id UUID,
  sale_date DATE,
  delivery_date DATE,

  -- Auditoria
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by UUID REFERENCES auth.users(id),
  updated_at TIMESTAMP,
  updated_by UUID REFERENCES auth.users(id),
  deleted_at TIMESTAMP,
  deleted_by UUID REFERENCES auth.users(id),

  CONSTRAINT uq_departamentos_code_nivel UNIQUE (nivel_id, code)
);

-- =====================================================
-- TABLES - PROTOTIPOS Y PRESUPUESTOS
-- =====================================================

-- Tabla: prototipos (tipos de vivienda)
CREATE TABLE construction.prototipos (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
  code VARCHAR(20) NOT NULL,
  name VARCHAR(100) NOT NULL,
  description TEXT,
  type construction.prototype_type NOT NULL DEFAULT 'horizontal',
  area_construction_m2 DECIMAL(10,2),
  area_terrain_m2 DECIMAL(10,2),
  bedrooms INTEGER DEFAULT 0,
  bathrooms DECIMAL(3,1) DEFAULT 0,
  parking_spaces INTEGER DEFAULT 0,
  floors INTEGER DEFAULT 1,
  base_price DECIMAL(14,2),
  blueprint_url VARCHAR(500),
  render_url VARCHAR(500),
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  metadata JSONB DEFAULT '{}', -- Especificaciones tecnicas

  -- Auditoria
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by UUID REFERENCES auth.users(id),
  updated_at TIMESTAMP,
  updated_by UUID REFERENCES auth.users(id),
  deleted_at TIMESTAMP,
  deleted_by UUID REFERENCES auth.users(id),

  CONSTRAINT uq_prototipos_code_tenant UNIQUE (tenant_id, code)
);

-- Tabla: conceptos (catalogo de conceptos de obra)
CREATE TABLE construction.conceptos (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
  parent_id UUID REFERENCES construction.conceptos(id),
  code VARCHAR(50) NOT NULL,
  name VARCHAR(255) NOT NULL,
  description TEXT,
  unit_id UUID, -- FK a core.uom
  unit_price DECIMAL(12,4),
  is_composite BOOLEAN NOT NULL DEFAULT FALSE,
  level INTEGER NOT NULL DEFAULT 0, -- Nivel en jerarquia
  path VARCHAR(500), -- Path materializado para queries

  -- Auditoria
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by UUID REFERENCES auth.users(id),
  updated_at TIMESTAMP,
  updated_by UUID REFERENCES auth.users(id),
  deleted_at TIMESTAMP,
  deleted_by UUID REFERENCES auth.users(id),

  CONSTRAINT uq_conceptos_code_tenant UNIQUE (tenant_id, code)
);

-- Tabla: presupuestos (presupuesto por prototipo/obra)
CREATE TABLE construction.presupuestos (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
  fraccionamiento_id UUID REFERENCES construction.fraccionamientos(id),
  prototipo_id UUID REFERENCES construction.prototipos(id),
  code VARCHAR(30) NOT NULL,
  name VARCHAR(255) NOT NULL,
  description TEXT,
  version INTEGER NOT NULL DEFAULT 1,
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  total_amount DECIMAL(16,2) DEFAULT 0,
  currency_id UUID, -- FK a core.currencies
  approved_at TIMESTAMP,
  approved_by UUID REFERENCES auth.users(id),

  -- Auditoria
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by UUID REFERENCES auth.users(id),
  updated_at TIMESTAMP,
  updated_by UUID REFERENCES auth.users(id),
  deleted_at TIMESTAMP,
  deleted_by UUID REFERENCES auth.users(id),

  CONSTRAINT uq_presupuestos_code_version UNIQUE (tenant_id, code, version)
);

-- Tabla: presupuesto_partidas (lineas del presupuesto)
CREATE TABLE construction.presupuesto_partidas (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
  presupuesto_id UUID NOT NULL REFERENCES construction.presupuestos(id) ON DELETE CASCADE,
  concepto_id UUID NOT NULL REFERENCES construction.conceptos(id),
  sequence INTEGER NOT NULL DEFAULT 0,
  quantity DECIMAL(12,4) NOT NULL DEFAULT 0,
  unit_price DECIMAL(12,4) NOT NULL DEFAULT 0,
  total_amount DECIMAL(14,2) GENERATED ALWAYS AS (quantity * unit_price) STORED,

  -- Auditoria
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by UUID REFERENCES auth.users(id),
  updated_at TIMESTAMP,
  updated_by UUID REFERENCES auth.users(id),
  deleted_at TIMESTAMP,
  deleted_by UUID REFERENCES auth.users(id),

  CONSTRAINT uq_partidas_presupuesto_concepto UNIQUE (presupuesto_id, concepto_id)
);

-- =====================================================
-- TABLES - AVANCES Y CONTROL DE OBRA
-- =====================================================

-- Tabla: programa_obra (programa maestro)
CREATE TABLE construction.programa_obra (
  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),
  code VARCHAR(30) NOT NULL,
  name VARCHAR(255) NOT NULL,
  version INTEGER NOT NULL DEFAULT 1,
  start_date DATE NOT NULL,
  end_date DATE NOT NULL,
  is_active BOOLEAN NOT NULL DEFAULT TRUE,

  -- Auditoria
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by UUID REFERENCES auth.users(id),
  updated_at TIMESTAMP,
  updated_by UUID REFERENCES auth.users(id),
  deleted_at TIMESTAMP,
  deleted_by UUID REFERENCES auth.users(id),

  CONSTRAINT uq_programa_code_version UNIQUE (tenant_id, code, version)
);

-- Tabla: programa_actividades (actividades del programa)
CREATE TABLE construction.programa_actividades (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
  programa_id UUID NOT NULL REFERENCES construction.programa_obra(id) ON DELETE CASCADE,
  concepto_id UUID REFERENCES construction.conceptos(id),
  parent_id UUID REFERENCES construction.programa_actividades(id),
  name VARCHAR(255) NOT NULL,
  sequence INTEGER NOT NULL DEFAULT 0,
  planned_start DATE,
  planned_end DATE,
  planned_quantity DECIMAL(12,4) DEFAULT 0,
  planned_weight DECIMAL(8,4) DEFAULT 0, -- Peso ponderado %
  wbs_code VARCHAR(50), -- Work Breakdown Structure

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

-- Tabla: avances_obra (captura de avances)
CREATE TABLE construction.avances_obra (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
  lote_id UUID REFERENCES construction.lotes(id),
  departamento_id UUID REFERENCES construction.departamentos(id),
  concepto_id UUID NOT NULL REFERENCES construction.conceptos(id),
  capture_date DATE NOT NULL,
  quantity_executed DECIMAL(12,4) NOT NULL DEFAULT 0,
  percentage_executed DECIMAL(5,2) DEFAULT 0,
  status construction.advance_status NOT NULL DEFAULT 'pending',
  notes TEXT,
  captured_by UUID NOT NULL REFERENCES auth.users(id),
  reviewed_by UUID REFERENCES auth.users(id),
  reviewed_at TIMESTAMP,
  approved_by UUID REFERENCES auth.users(id),
  approved_at TIMESTAMP,

  -- Auditoria
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by UUID REFERENCES auth.users(id),
  updated_at TIMESTAMP,
  updated_by UUID REFERENCES auth.users(id),
  deleted_at TIMESTAMP,
  deleted_by UUID REFERENCES auth.users(id),

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

-- Tabla: fotos_avance (evidencia fotografica)
CREATE TABLE construction.fotos_avance (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
  avance_id UUID NOT NULL REFERENCES construction.avances_obra(id) ON DELETE CASCADE,
  file_url VARCHAR(500) NOT NULL,
  file_name VARCHAR(255),
  file_size INTEGER,
  mime_type VARCHAR(50),
  description TEXT,
  location GEOMETRY(POINT, 4326), -- GPS donde se tomo
  captured_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

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

-- Tabla: bitacora_obra (registro de bitacora)
CREATE TABLE construction.bitacora_obra (
  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),
  entry_date DATE NOT NULL,
  entry_number INTEGER NOT NULL,
  weather VARCHAR(50),
  temperature_max DECIMAL(4,1),
  temperature_min DECIMAL(4,1),
  workers_count INTEGER DEFAULT 0,
  description TEXT NOT NULL,
  observations TEXT,
  incidents TEXT,
  registered_by UUID NOT NULL REFERENCES auth.users(id),

  -- Auditoria
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by UUID REFERENCES auth.users(id),
  updated_at TIMESTAMP,
  updated_by UUID REFERENCES auth.users(id),
  deleted_at TIMESTAMP,
  deleted_by UUID REFERENCES auth.users(id),

  CONSTRAINT uq_bitacora_fracc_number UNIQUE (fraccionamiento_id, entry_number)
);

-- =====================================================
-- TABLES - CALIDAD Y POSTVENTA (MAI-009)
-- =====================================================

-- Tabla: checklists (plantillas de verificacion)
CREATE TABLE construction.checklists (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
  code VARCHAR(30) NOT NULL,
  name VARCHAR(255) NOT NULL,
  description TEXT,
  prototipo_id UUID REFERENCES construction.prototipos(id),
  is_active BOOLEAN NOT NULL DEFAULT TRUE,

  -- Auditoria
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by UUID REFERENCES auth.users(id),
  updated_at TIMESTAMP,
  updated_by UUID REFERENCES auth.users(id),
  deleted_at TIMESTAMP,
  deleted_by UUID REFERENCES auth.users(id),

  CONSTRAINT uq_checklists_code_tenant UNIQUE (tenant_id, code)
);

-- Tabla: checklist_items (items del checklist)
CREATE TABLE construction.checklist_items (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
  checklist_id UUID NOT NULL REFERENCES construction.checklists(id) ON DELETE CASCADE,
  sequence INTEGER NOT NULL DEFAULT 0,
  name VARCHAR(255) NOT NULL,
  description TEXT,
  is_required BOOLEAN NOT NULL DEFAULT TRUE,

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

-- Tabla: inspecciones (inspecciones de calidad)
CREATE TABLE construction.inspecciones (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
  checklist_id UUID NOT NULL REFERENCES construction.checklists(id),
  lote_id UUID REFERENCES construction.lotes(id),
  departamento_id UUID REFERENCES construction.departamentos(id),
  inspection_date DATE NOT NULL,
  status construction.quality_status NOT NULL DEFAULT 'pending',
  inspector_id UUID NOT NULL REFERENCES auth.users(id),
  notes TEXT,
  approved_by UUID REFERENCES auth.users(id),
  approved_at TIMESTAMP,

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

-- Tabla: inspeccion_resultados (resultados por item)
CREATE TABLE construction.inspeccion_resultados (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
  inspeccion_id UUID NOT NULL REFERENCES construction.inspecciones(id) ON DELETE CASCADE,
  checklist_item_id UUID NOT NULL REFERENCES construction.checklist_items(id),
  is_passed BOOLEAN,
  notes TEXT,
  photo_url VARCHAR(500),

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

-- Tabla: tickets_postventa (tickets de garantia)
CREATE TABLE construction.tickets_postventa (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
  lote_id UUID REFERENCES construction.lotes(id),
  departamento_id UUID REFERENCES construction.departamentos(id),
  ticket_number VARCHAR(30) NOT NULL,
  reported_date DATE NOT NULL,
  category VARCHAR(50), -- plomeria, electricidad, estructura, etc.
  description TEXT NOT NULL,
  priority VARCHAR(20) DEFAULT 'medium', -- low, medium, high, urgent
  status VARCHAR(20) NOT NULL DEFAULT 'open', -- open, in_progress, resolved, closed
  assigned_to UUID REFERENCES auth.users(id),
  resolution TEXT,
  resolved_at TIMESTAMP,
  resolved_by UUID REFERENCES auth.users(id),

  -- Auditoria
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by UUID REFERENCES auth.users(id),
  updated_at TIMESTAMP,
  updated_by UUID REFERENCES auth.users(id),
  deleted_at TIMESTAMP,
  deleted_by UUID REFERENCES auth.users(id),

  CONSTRAINT uq_tickets_number_tenant UNIQUE (tenant_id, ticket_number)
);

-- =====================================================
-- TABLES - CONTRATOS Y SUBCONTRATOS (MAI-012)
-- =====================================================

-- Tabla: subcontratistas
CREATE TABLE construction.subcontratistas (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
  partner_id UUID, -- FK a core.partners
  code VARCHAR(20) NOT NULL,
  name VARCHAR(255) NOT NULL,
  legal_name VARCHAR(255),
  tax_id VARCHAR(20),
  specialty VARCHAR(100), -- Especialidad: alba~nileria, electricidad, etc.
  contact_name VARCHAR(100),
  contact_phone VARCHAR(20),
  contact_email VARCHAR(100),
  address TEXT,
  rating DECIMAL(3,2), -- Calificacion 0-5
  is_active BOOLEAN NOT NULL DEFAULT TRUE,

  -- Auditoria
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by UUID REFERENCES auth.users(id),
  updated_at TIMESTAMP,
  updated_by UUID REFERENCES auth.users(id),
  deleted_at TIMESTAMP,
  deleted_by UUID REFERENCES auth.users(id),

  CONSTRAINT uq_subcontratistas_code_tenant UNIQUE (tenant_id, code)
);

-- Tabla: contratos (contratos con subcontratistas)
CREATE TABLE construction.contratos (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
  subcontratista_id UUID NOT NULL REFERENCES construction.subcontratistas(id),
  fraccionamiento_id UUID NOT NULL REFERENCES construction.fraccionamientos(id),
  contract_number VARCHAR(30) NOT NULL,
  contract_type construction.contract_type NOT NULL DEFAULT 'unit_price',
  name VARCHAR(255) NOT NULL,
  description TEXT,
  start_date DATE NOT NULL,
  end_date DATE,
  total_amount DECIMAL(16,2),
  advance_percentage DECIMAL(5,2) DEFAULT 0, -- % anticipo
  retention_percentage DECIMAL(5,2) DEFAULT 5, -- % retencion
  status construction.contract_status NOT NULL DEFAULT 'draft',
  signed_at TIMESTAMP,
  signed_by UUID REFERENCES auth.users(id),

  -- Auditoria
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by UUID REFERENCES auth.users(id),
  updated_at TIMESTAMP,
  updated_by UUID REFERENCES auth.users(id),
  deleted_at TIMESTAMP,
  deleted_by UUID REFERENCES auth.users(id),

  CONSTRAINT uq_contratos_number_tenant UNIQUE (tenant_id, contract_number)
);

-- Tabla: contrato_partidas (lineas del contrato)
CREATE TABLE construction.contrato_partidas (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
  contrato_id UUID NOT NULL REFERENCES construction.contratos(id) ON DELETE CASCADE,
  concepto_id UUID NOT NULL REFERENCES construction.conceptos(id),
  quantity DECIMAL(12,4) NOT NULL DEFAULT 0,
  unit_price DECIMAL(12,4) NOT NULL DEFAULT 0,
  total_amount DECIMAL(14,2) GENERATED ALWAYS AS (quantity * unit_price) STORED,

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

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

-- Fraccionamientos
CREATE INDEX idx_fraccionamientos_tenant_id ON construction.fraccionamientos(tenant_id);
CREATE INDEX idx_fraccionamientos_status ON construction.fraccionamientos(status);
CREATE INDEX idx_fraccionamientos_code ON construction.fraccionamientos(code);
CREATE INDEX idx_fraccionamientos_active ON construction.fraccionamientos(tenant_id) WHERE deleted_at IS NULL;

-- Etapas
CREATE INDEX idx_etapas_tenant_id ON construction.etapas(tenant_id);
CREATE INDEX idx_etapas_fraccionamiento_id ON construction.etapas(fraccionamiento_id);
CREATE INDEX idx_etapas_status ON construction.etapas(status);

-- Manzanas
CREATE INDEX idx_manzanas_tenant_id ON construction.manzanas(tenant_id);
CREATE INDEX idx_manzanas_etapa_id ON construction.manzanas(etapa_id);

-- Lotes
CREATE INDEX idx_lotes_tenant_id ON construction.lotes(tenant_id);
CREATE INDEX idx_lotes_manzana_id ON construction.lotes(manzana_id);
CREATE INDEX idx_lotes_prototipo_id ON construction.lotes(prototipo_id);
CREATE INDEX idx_lotes_status ON construction.lotes(status);
CREATE INDEX idx_lotes_buyer_id ON construction.lotes(buyer_id) WHERE buyer_id IS NOT NULL;

-- Torres
CREATE INDEX idx_torres_tenant_id ON construction.torres(tenant_id);
CREATE INDEX idx_torres_etapa_id ON construction.torres(etapa_id);

-- Niveles
CREATE INDEX idx_niveles_tenant_id ON construction.niveles(tenant_id);
CREATE INDEX idx_niveles_torre_id ON construction.niveles(torre_id);

-- Departamentos
CREATE INDEX idx_departamentos_tenant_id ON construction.departamentos(tenant_id);
CREATE INDEX idx_departamentos_nivel_id ON construction.departamentos(nivel_id);
CREATE INDEX idx_departamentos_status ON construction.departamentos(status);

-- Prototipos
CREATE INDEX idx_prototipos_tenant_id ON construction.prototipos(tenant_id);
CREATE INDEX idx_prototipos_type ON construction.prototipos(type);
CREATE INDEX idx_prototipos_active ON construction.prototipos(tenant_id) WHERE is_active = TRUE;

-- Conceptos
CREATE INDEX idx_conceptos_tenant_id ON construction.conceptos(tenant_id);
CREATE INDEX idx_conceptos_parent_id ON construction.conceptos(parent_id);
CREATE INDEX idx_conceptos_code ON construction.conceptos(code);
CREATE INDEX idx_conceptos_path ON construction.conceptos(path);

-- Presupuestos
CREATE INDEX idx_presupuestos_tenant_id ON construction.presupuestos(tenant_id);
CREATE INDEX idx_presupuestos_fraccionamiento_id ON construction.presupuestos(fraccionamiento_id);
CREATE INDEX idx_presupuestos_prototipo_id ON construction.presupuestos(prototipo_id);

-- Avances
CREATE INDEX idx_avances_tenant_id ON construction.avances_obra(tenant_id);
CREATE INDEX idx_avances_lote_id ON construction.avances_obra(lote_id);
CREATE INDEX idx_avances_departamento_id ON construction.avances_obra(departamento_id);
CREATE INDEX idx_avances_concepto_id ON construction.avances_obra(concepto_id);
CREATE INDEX idx_avances_capture_date ON construction.avances_obra(capture_date);
CREATE INDEX idx_avances_status ON construction.avances_obra(status);

-- Bitacora
CREATE INDEX idx_bitacora_tenant_id ON construction.bitacora_obra(tenant_id);
CREATE INDEX idx_bitacora_fraccionamiento_id ON construction.bitacora_obra(fraccionamiento_id);
CREATE INDEX idx_bitacora_entry_date ON construction.bitacora_obra(entry_date);

-- Inspecciones
CREATE INDEX idx_inspecciones_tenant_id ON construction.inspecciones(tenant_id);
CREATE INDEX idx_inspecciones_lote_id ON construction.inspecciones(lote_id);
CREATE INDEX idx_inspecciones_status ON construction.inspecciones(status);

-- Tickets
CREATE INDEX idx_tickets_tenant_id ON construction.tickets_postventa(tenant_id);
CREATE INDEX idx_tickets_status ON construction.tickets_postventa(status);

-- Subcontratistas
CREATE INDEX idx_subcontratistas_tenant_id ON construction.subcontratistas(tenant_id);
CREATE INDEX idx_subcontratistas_specialty ON construction.subcontratistas(specialty);

-- Contratos
CREATE INDEX idx_contratos_tenant_id ON construction.contratos(tenant_id);
CREATE INDEX idx_contratos_subcontratista_id ON construction.contratos(subcontratista_id);
CREATE INDEX idx_contratos_fraccionamiento_id ON construction.contratos(fraccionamiento_id);
CREATE INDEX idx_contratos_status ON construction.contratos(status);

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

-- Habilitar RLS en todas las tablas
ALTER TABLE construction.fraccionamientos ENABLE ROW LEVEL SECURITY;
ALTER TABLE construction.etapas ENABLE ROW LEVEL SECURITY;
ALTER TABLE construction.manzanas ENABLE ROW LEVEL SECURITY;
ALTER TABLE construction.lotes ENABLE ROW LEVEL SECURITY;
ALTER TABLE construction.torres ENABLE ROW LEVEL SECURITY;
ALTER TABLE construction.niveles ENABLE ROW LEVEL SECURITY;
ALTER TABLE construction.departamentos ENABLE ROW LEVEL SECURITY;
ALTER TABLE construction.prototipos ENABLE ROW LEVEL SECURITY;
ALTER TABLE construction.conceptos ENABLE ROW LEVEL SECURITY;
ALTER TABLE construction.presupuestos ENABLE ROW LEVEL SECURITY;
ALTER TABLE construction.presupuesto_partidas ENABLE ROW LEVEL SECURITY;
ALTER TABLE construction.programa_obra ENABLE ROW LEVEL SECURITY;
ALTER TABLE construction.programa_actividades ENABLE ROW LEVEL SECURITY;
ALTER TABLE construction.avances_obra ENABLE ROW LEVEL SECURITY;
ALTER TABLE construction.fotos_avance ENABLE ROW LEVEL SECURITY;
ALTER TABLE construction.bitacora_obra ENABLE ROW LEVEL SECURITY;
ALTER TABLE construction.checklists ENABLE ROW LEVEL SECURITY;
ALTER TABLE construction.checklist_items ENABLE ROW LEVEL SECURITY;
ALTER TABLE construction.inspecciones ENABLE ROW LEVEL SECURITY;
ALTER TABLE construction.inspeccion_resultados ENABLE ROW LEVEL SECURITY;
ALTER TABLE construction.tickets_postventa ENABLE ROW LEVEL SECURITY;
ALTER TABLE construction.subcontratistas ENABLE ROW LEVEL SECURITY;
ALTER TABLE construction.contratos ENABLE ROW LEVEL SECURITY;
ALTER TABLE construction.contrato_partidas ENABLE ROW LEVEL SECURITY;

-- Policies de tenant isolation
CREATE POLICY tenant_isolation_fraccionamientos ON construction.fraccionamientos
  USING (tenant_id = get_current_tenant_id());

CREATE POLICY tenant_isolation_etapas ON construction.etapas
  USING (tenant_id = get_current_tenant_id());

CREATE POLICY tenant_isolation_manzanas ON construction.manzanas
  USING (tenant_id = get_current_tenant_id());

CREATE POLICY tenant_isolation_lotes ON construction.lotes
  USING (tenant_id = get_current_tenant_id());

CREATE POLICY tenant_isolation_torres ON construction.torres
  USING (tenant_id = get_current_tenant_id());

CREATE POLICY tenant_isolation_niveles ON construction.niveles
  USING (tenant_id = get_current_tenant_id());

CREATE POLICY tenant_isolation_departamentos ON construction.departamentos
  USING (tenant_id = get_current_tenant_id());

CREATE POLICY tenant_isolation_prototipos ON construction.prototipos
  USING (tenant_id = get_current_tenant_id());

CREATE POLICY tenant_isolation_conceptos ON construction.conceptos
  USING (tenant_id = get_current_tenant_id());

CREATE POLICY tenant_isolation_presupuestos ON construction.presupuestos
  USING (tenant_id = get_current_tenant_id());

CREATE POLICY tenant_isolation_presupuesto_partidas ON construction.presupuesto_partidas
  USING (tenant_id = get_current_tenant_id());

CREATE POLICY tenant_isolation_programa_obra ON construction.programa_obra
  USING (tenant_id = get_current_tenant_id());

CREATE POLICY tenant_isolation_programa_actividades ON construction.programa_actividades
  USING (tenant_id = get_current_tenant_id());

CREATE POLICY tenant_isolation_avances_obra ON construction.avances_obra
  USING (tenant_id = get_current_tenant_id());

CREATE POLICY tenant_isolation_fotos_avance ON construction.fotos_avance
  USING (tenant_id = get_current_tenant_id());

CREATE POLICY tenant_isolation_bitacora_obra ON construction.bitacora_obra
  USING (tenant_id = get_current_tenant_id());

CREATE POLICY tenant_isolation_checklists ON construction.checklists
  USING (tenant_id = get_current_tenant_id());

CREATE POLICY tenant_isolation_checklist_items ON construction.checklist_items
  USING (tenant_id = get_current_tenant_id());

CREATE POLICY tenant_isolation_inspecciones ON construction.inspecciones
  USING (tenant_id = get_current_tenant_id());

CREATE POLICY tenant_isolation_inspeccion_resultados ON construction.inspeccion_resultados
  USING (tenant_id = get_current_tenant_id());

CREATE POLICY tenant_isolation_tickets_postventa ON construction.tickets_postventa
  USING (tenant_id = get_current_tenant_id());

CREATE POLICY tenant_isolation_subcontratistas ON construction.subcontratistas
  USING (tenant_id = get_current_tenant_id());

CREATE POLICY tenant_isolation_contratos ON construction.contratos
  USING (tenant_id = get_current_tenant_id());

CREATE POLICY tenant_isolation_contrato_partidas ON construction.contrato_partidas
  USING (tenant_id = get_current_tenant_id());

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

COMMENT ON SCHEMA construction IS 'Schema especifico de construccion: obras, lotes, avances, calidad';
COMMENT ON TABLE construction.fraccionamientos IS 'Desarrollos inmobiliarios/fraccionamientos';
COMMENT ON TABLE construction.etapas IS 'Etapas/fases de un fraccionamiento';
COMMENT ON TABLE construction.manzanas IS 'Manzanas dentro de una etapa';
COMMENT ON TABLE construction.lotes IS 'Lotes/terrenos vendibles (horizontal)';
COMMENT ON TABLE construction.torres IS 'Torres/edificios (vertical)';
COMMENT ON TABLE construction.niveles IS 'Pisos de una torre';
COMMENT ON TABLE construction.departamentos IS 'Departamentos/unidades en torre (vertical)';
COMMENT ON TABLE construction.prototipos IS 'Tipos de vivienda/prototipos';
COMMENT ON TABLE construction.conceptos IS 'Catalogo de conceptos de obra';
COMMENT ON TABLE construction.presupuestos IS 'Presupuestos por prototipo u obra';
COMMENT ON TABLE construction.avances_obra IS 'Captura de avances fisicos de obra';
COMMENT ON TABLE construction.bitacora_obra IS 'Bitacora diaria de obra';
COMMENT ON TABLE construction.checklists IS 'Plantillas de verificacion de calidad';
COMMENT ON TABLE construction.inspecciones IS 'Inspecciones de calidad';
COMMENT ON TABLE construction.tickets_postventa IS 'Tickets de garantia/postventa';
COMMENT ON TABLE construction.subcontratistas IS 'Catalogo de subcontratistas';
COMMENT ON TABLE construction.contratos IS 'Contratos con subcontratistas';

-- =====================================================
-- FIN DEL SCHEMA CONSTRUCTION
-- =====================================================