-- =====================================================
-- SCHEMA: hr (EXTENSION)
-- PROPOSITO: Extensiones de RRHH para construccion
-- MODULOS: MAI-007 (RRHH y Asistencias)
-- FECHA: 2025-11-24
-- TIPO: Extension del ERP Generico (MGN-010)
-- =====================================================

-- NOTA: Este archivo contiene SOLO las extensiones especificas
-- de construccion. Las tablas base estan en el ERP Generico.

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

CREATE TYPE hr.attendance_type_construction AS ENUM (
  'manual',          -- Captura manual
  'biometric',       -- Biometrico
  'gps',             -- GPS desde app movil
  'qr_code'          -- Codigo QR
);

CREATE TYPE hr.worker_type AS ENUM (
  'permanent',       -- Planta
  'temporary',       -- Temporal
  'subcontract',     -- Subcontratado
  'piece_rate'       -- Destajista
);

CREATE TYPE hr.destajo_status AS ENUM (
  'pending',         -- Pendiente medicion
  'measured',        -- Medido
  'approved',        -- Aprobado
  'paid'             -- Pagado
);

-- =====================================================
-- TABLES - EXTENSIONES CONSTRUCCION
-- =====================================================

-- Tabla: employee_construction (extension de empleados)
-- Extiende: hr.employees
CREATE TABLE hr.employee_construction (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
  employee_id UUID NOT NULL, -- FK a hr.employees (ERP Generico)
  worker_type hr.worker_type NOT NULL DEFAULT 'permanent',
  subcontratista_id UUID REFERENCES construction.subcontratistas(id),

  -- Seguro Social
  nss VARCHAR(15),
  imss_registration_date DATE,
  salary_type VARCHAR(20), -- fijo, mixto, variable

  -- Capacitacion
  stps_dc3_number VARCHAR(50), -- Constancia DC-3
  stps_dc3_expiry DATE,
  safety_training_date DATE,
  first_aid_training_date DATE,

  -- Documentos
  has_medical_exam BOOLEAN DEFAULT FALSE,
  medical_exam_date DATE,
  has_ine BOOLEAN DEFAULT FALSE,
  has_curp BOOLEAN DEFAULT FALSE,
  has_rfc BOOLEAN DEFAULT FALSE,

  -- Asignacion
  default_fraccionamiento_id UUID REFERENCES construction.fraccionamientos(id),
  specialty VARCHAR(100), -- Especialidad: alba~nil, electricista, plomero

  -- 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_employee_construction_employee_id UNIQUE (employee_id)
);

-- Tabla: asistencias (registro de asistencia)
CREATE TABLE hr.asistencias (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
  employee_id UUID NOT NULL, -- FK a hr.employees (ERP Generico)
  fraccionamiento_id UUID NOT NULL REFERENCES construction.fraccionamientos(id),
  attendance_date DATE NOT NULL,
  attendance_type hr.attendance_type_construction NOT NULL DEFAULT 'manual',

  -- Horarios
  check_in_time TIMESTAMP,
  check_out_time TIMESTAMP,
  worked_hours DECIMAL(4,2) GENERATED ALWAYS AS (
    CASE
      WHEN check_out_time IS NOT NULL AND check_in_time IS NOT NULL
      THEN EXTRACT(EPOCH FROM (check_out_time - check_in_time)) / 3600
      ELSE NULL
    END
  ) STORED,
  overtime_hours DECIMAL(4,2) DEFAULT 0,

  -- GPS
  check_in_location GEOMETRY(POINT, 4326),
  check_out_location GEOMETRY(POINT, 4326),
  check_in_accuracy DECIMAL(8,2), -- Precision en metros
  check_out_accuracy DECIMAL(8,2),

  -- Validacion
  is_valid BOOLEAN DEFAULT TRUE,
  validation_notes TEXT,
  validated_by UUID REFERENCES auth.users(id),
  validated_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 uq_asistencias_employee_date UNIQUE (employee_id, attendance_date)
);

-- Tabla: asistencia_biometrico (registros biometricos)
CREATE TABLE hr.asistencia_biometrico (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
  asistencia_id UUID NOT NULL REFERENCES hr.asistencias(id) ON DELETE CASCADE,
  device_id VARCHAR(50), -- ID del dispositivo biometrico
  device_name VARCHAR(100),
  biometric_type VARCHAR(20), -- fingerprint, face, iris
  verification_score DECIMAL(5,2), -- Score de verificacion 0-100
  raw_data TEXT, -- Datos crudos del dispositivo

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

-- Tabla: geocercas (geocercas para validacion GPS)
CREATE TABLE hr.geocercas (
  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),
  name VARCHAR(100) NOT NULL,
  polygon GEOMETRY(POLYGON, 4326) NOT NULL,
  radius_meters DECIMAL(8,2), -- Radio de tolerancia
  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)
);

-- Tabla: destajo (trabajo a destajo)
CREATE TABLE hr.destajo (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
  employee_id UUID NOT NULL, -- FK a hr.employees
  fraccionamiento_id UUID NOT NULL REFERENCES construction.fraccionamientos(id),
  lote_id UUID REFERENCES construction.lotes(id),
  departamento_id UUID REFERENCES construction.departamentos(id),
  concepto_id UUID NOT NULL REFERENCES construction.conceptos(id),
  period_start DATE NOT NULL,
  period_end DATE NOT NULL,
  status hr.destajo_status NOT NULL DEFAULT 'pending',

  -- Cantidades
  quantity_measured DECIMAL(12,4) DEFAULT 0,
  unit_price DECIMAL(12,4) NOT NULL,
  total_amount DECIMAL(14,2) GENERATED ALWAYS AS (quantity_measured * unit_price) STORED,

  -- Medicion
  measured_by UUID REFERENCES auth.users(id),
  measured_at TIMESTAMP,

  -- Aprobacion
  approved_by UUID REFERENCES auth.users(id),
  approved_at TIMESTAMP,

  -- Pago
  payroll_id UUID, -- FK a nomina
  paid_at TIMESTAMP,

  notes TEXT,

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

-- Tabla: destajo_detalle (detalle de mediciones)
CREATE TABLE hr.destajo_detalle (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
  destajo_id UUID NOT NULL REFERENCES hr.destajo(id) ON DELETE CASCADE,
  description VARCHAR(255) NOT NULL,
  quantity DECIMAL(12,4) NOT NULL,
  formula TEXT, -- Formula de calculo
  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: cuadrilla (cuadrillas de trabajo)
CREATE TABLE hr.cuadrillas (
  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(20) NOT NULL,
  name VARCHAR(100) NOT NULL,
  leader_id UUID, -- FK a hr.employees (lider de cuadrilla)
  specialty VARCHAR(100),
  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_cuadrillas_code_fracc UNIQUE (fraccionamiento_id, code)
);

-- Tabla: cuadrilla_miembros (miembros de cuadrilla)
CREATE TABLE hr.cuadrilla_miembros (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
  cuadrilla_id UUID NOT NULL REFERENCES hr.cuadrillas(id) ON DELETE CASCADE,
  employee_id UUID NOT NULL, -- FK a hr.employees
  role VARCHAR(50), -- lider, oficial, ayudante
  start_date DATE NOT NULL,
  end_date DATE,
  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),

  CONSTRAINT uq_cuadrilla_employee UNIQUE (cuadrilla_id, employee_id, start_date)
);

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

-- Employee Construction
CREATE INDEX idx_employee_construction_tenant_id ON hr.employee_construction(tenant_id);
CREATE INDEX idx_employee_construction_employee_id ON hr.employee_construction(employee_id);
CREATE INDEX idx_employee_construction_worker_type ON hr.employee_construction(worker_type);
CREATE INDEX idx_employee_construction_subcontratista_id ON hr.employee_construction(subcontratista_id);

-- Asistencias
CREATE INDEX idx_asistencias_tenant_id ON hr.asistencias(tenant_id);
CREATE INDEX idx_asistencias_employee_id ON hr.asistencias(employee_id);
CREATE INDEX idx_asistencias_fraccionamiento_id ON hr.asistencias(fraccionamiento_id);
CREATE INDEX idx_asistencias_date ON hr.asistencias(attendance_date);
CREATE INDEX idx_asistencias_type ON hr.asistencias(attendance_type);

-- Asistencia Biometrico
CREATE INDEX idx_asist_biometrico_tenant_id ON hr.asistencia_biometrico(tenant_id);
CREATE INDEX idx_asist_biometrico_asistencia_id ON hr.asistencia_biometrico(asistencia_id);

-- Geocercas
CREATE INDEX idx_geocercas_tenant_id ON hr.geocercas(tenant_id);
CREATE INDEX idx_geocercas_fraccionamiento_id ON hr.geocercas(fraccionamiento_id);
CREATE INDEX idx_geocercas_polygon ON hr.geocercas USING GIST(polygon);

-- Destajo
CREATE INDEX idx_destajo_tenant_id ON hr.destajo(tenant_id);
CREATE INDEX idx_destajo_employee_id ON hr.destajo(employee_id);
CREATE INDEX idx_destajo_fraccionamiento_id ON hr.destajo(fraccionamiento_id);
CREATE INDEX idx_destajo_lote_id ON hr.destajo(lote_id);
CREATE INDEX idx_destajo_concepto_id ON hr.destajo(concepto_id);
CREATE INDEX idx_destajo_status ON hr.destajo(status);
CREATE INDEX idx_destajo_period ON hr.destajo(period_start, period_end);

-- Destajo Detalle
CREATE INDEX idx_destajo_detalle_tenant_id ON hr.destajo_detalle(tenant_id);
CREATE INDEX idx_destajo_detalle_destajo_id ON hr.destajo_detalle(destajo_id);

-- Cuadrillas
CREATE INDEX idx_cuadrillas_tenant_id ON hr.cuadrillas(tenant_id);
CREATE INDEX idx_cuadrillas_fraccionamiento_id ON hr.cuadrillas(fraccionamiento_id);
CREATE INDEX idx_cuadrillas_leader_id ON hr.cuadrillas(leader_id);

-- Cuadrilla Miembros
CREATE INDEX idx_cuadrilla_miembros_tenant_id ON hr.cuadrilla_miembros(tenant_id);
CREATE INDEX idx_cuadrilla_miembros_cuadrilla_id ON hr.cuadrilla_miembros(cuadrilla_id);
CREATE INDEX idx_cuadrilla_miembros_employee_id ON hr.cuadrilla_miembros(employee_id);

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

ALTER TABLE hr.employee_construction ENABLE ROW LEVEL SECURITY;
ALTER TABLE hr.asistencias ENABLE ROW LEVEL SECURITY;
ALTER TABLE hr.asistencia_biometrico ENABLE ROW LEVEL SECURITY;
ALTER TABLE hr.geocercas ENABLE ROW LEVEL SECURITY;
ALTER TABLE hr.destajo ENABLE ROW LEVEL SECURITY;
ALTER TABLE hr.destajo_detalle ENABLE ROW LEVEL SECURITY;
ALTER TABLE hr.cuadrillas ENABLE ROW LEVEL SECURITY;
ALTER TABLE hr.cuadrilla_miembros ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation_employee_construction ON hr.employee_construction
  USING (tenant_id = get_current_tenant_id());

CREATE POLICY tenant_isolation_asistencias ON hr.asistencias
  USING (tenant_id = get_current_tenant_id());

CREATE POLICY tenant_isolation_asist_biometrico ON hr.asistencia_biometrico
  USING (tenant_id = get_current_tenant_id());

CREATE POLICY tenant_isolation_geocercas ON hr.geocercas
  USING (tenant_id = get_current_tenant_id());

CREATE POLICY tenant_isolation_destajo ON hr.destajo
  USING (tenant_id = get_current_tenant_id());

CREATE POLICY tenant_isolation_destajo_detalle ON hr.destajo_detalle
  USING (tenant_id = get_current_tenant_id());

CREATE POLICY tenant_isolation_cuadrillas ON hr.cuadrillas
  USING (tenant_id = get_current_tenant_id());

CREATE POLICY tenant_isolation_cuadrilla_miembros ON hr.cuadrilla_miembros
  USING (tenant_id = get_current_tenant_id());

-- =====================================================
-- FUNCTIONS
-- =====================================================

-- Funcion: validar asistencia dentro de geocerca
CREATE OR REPLACE FUNCTION hr.validate_attendance_location(
  p_location GEOMETRY,
  p_fraccionamiento_id UUID
)
RETURNS BOOLEAN AS $$
DECLARE
  v_is_valid BOOLEAN;
BEGIN
  SELECT EXISTS (
    SELECT 1
    FROM hr.geocercas g
    WHERE g.fraccionamiento_id = p_fraccionamiento_id
      AND g.is_active = TRUE
      AND g.deleted_at IS NULL
      AND (
        ST_Contains(g.polygon, p_location) OR
        ST_DWithin(g.polygon::geography, p_location::geography, COALESCE(g.radius_meters, 50))
      )
  ) INTO v_is_valid;

  RETURN v_is_valid;
END;
$$ LANGUAGE plpgsql STABLE;

COMMENT ON FUNCTION hr.validate_attendance_location IS 'Valida si una ubicacion esta dentro de las geocercas del fraccionamiento';

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

COMMENT ON TABLE hr.employee_construction IS 'Extension: datos adicionales de empleados para construccion';
COMMENT ON TABLE hr.asistencias IS 'Extension: registro de asistencia con GPS/biometrico';
COMMENT ON TABLE hr.asistencia_biometrico IS 'Extension: detalles de registro biometrico';
COMMENT ON TABLE hr.geocercas IS 'Extension: geocercas para validacion de ubicacion';
COMMENT ON TABLE hr.destajo IS 'Extension: trabajo a destajo/precio unitario';
COMMENT ON TABLE hr.destajo_detalle IS 'Extension: detalle de mediciones de destajo';
COMMENT ON TABLE hr.cuadrillas IS 'Extension: cuadrillas de trabajo';
COMMENT ON TABLE hr.cuadrilla_miembros IS 'Extension: miembros de cuadrillas';

-- =====================================================
-- FIN DE EXTENSIONES HR
-- =====================================================