355 lines
14 KiB
SQL
355 lines
14 KiB
SQL
-- ============================================================================
|
|
-- HR EXTENSIONS - FASE 8 ERP-Core
|
|
-- ERP Clínicas (Base Genérica)
|
|
-- ============================================================================
|
|
-- Fecha: 2026-01-04
|
|
-- Versión: 1.0
|
|
-- ============================================================================
|
|
|
|
-- Schema
|
|
CREATE SCHEMA IF NOT EXISTS hr;
|
|
|
|
-- ============================================================================
|
|
-- ENUMS
|
|
-- ============================================================================
|
|
|
|
DO $$ BEGIN
|
|
CREATE TYPE hr.expense_status AS ENUM (
|
|
'draft', 'submitted', 'approved', 'posted', 'paid', 'rejected'
|
|
);
|
|
EXCEPTION WHEN duplicate_object THEN NULL;
|
|
END $$;
|
|
|
|
DO $$ BEGIN
|
|
CREATE TYPE hr.resume_line_type AS ENUM (
|
|
'experience', 'education', 'certification', 'internal'
|
|
);
|
|
EXCEPTION WHEN duplicate_object THEN NULL;
|
|
END $$;
|
|
|
|
DO $$ BEGIN
|
|
CREATE TYPE hr.payslip_status AS ENUM (
|
|
'draft', 'verify', 'done', 'cancel'
|
|
);
|
|
EXCEPTION WHEN duplicate_object THEN NULL;
|
|
END $$;
|
|
|
|
-- ============================================================================
|
|
-- TABLAS
|
|
-- ============================================================================
|
|
|
|
-- Ubicaciones de trabajo (consultorios/sucursales)
|
|
CREATE TABLE IF NOT EXISTS hr.work_locations (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL,
|
|
name VARCHAR(100) NOT NULL,
|
|
address_id UUID,
|
|
-- Extensiones clínica
|
|
tipo_consultorio VARCHAR(50), -- 'general', 'especialidad', 'urgencias', 'quirofano', 'laboratorio'
|
|
capacidad INTEGER DEFAULT 1,
|
|
equipamiento TEXT[],
|
|
horario_apertura TIME,
|
|
horario_cierre TIME,
|
|
-- Control
|
|
active BOOLEAN DEFAULT true,
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
COMMENT ON TABLE hr.work_locations IS 'Ubicaciones de trabajo/consultorios - FASE 8';
|
|
COMMENT ON COLUMN hr.work_locations.tipo_consultorio IS 'Tipo de consultorio o área';
|
|
|
|
-- Tipos de habilidad
|
|
CREATE TABLE IF NOT EXISTS hr.skill_types (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL,
|
|
name VARCHAR(100) NOT NULL,
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
COMMENT ON TABLE hr.skill_types IS 'Tipos de habilidad (Especialidad, Certificación, etc.) - FASE 8';
|
|
|
|
-- Habilidades/Especialidades
|
|
CREATE TABLE IF NOT EXISTS hr.skills (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL,
|
|
skill_type_id UUID NOT NULL REFERENCES hr.skill_types(id) ON DELETE CASCADE,
|
|
name VARCHAR(100) NOT NULL,
|
|
-- Extensiones clínica
|
|
codigo_ssa VARCHAR(20),
|
|
requiere_cedula BOOLEAN DEFAULT false,
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
COMMENT ON TABLE hr.skills IS 'Habilidades/Especialidades médicas - FASE 8';
|
|
COMMENT ON COLUMN hr.skills.codigo_ssa IS 'Código SSA de la especialidad';
|
|
|
|
-- Niveles de habilidad
|
|
CREATE TABLE IF NOT EXISTS hr.skill_levels (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL,
|
|
skill_type_id UUID NOT NULL REFERENCES hr.skill_types(id) ON DELETE CASCADE,
|
|
name VARCHAR(100) NOT NULL,
|
|
level INTEGER NOT NULL DEFAULT 1,
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
COMMENT ON TABLE hr.skill_levels IS 'Niveles de habilidad - FASE 8';
|
|
|
|
-- Habilidades de empleado
|
|
CREATE TABLE IF NOT EXISTS hr.employee_skills (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL,
|
|
employee_id UUID NOT NULL,
|
|
skill_id UUID NOT NULL REFERENCES hr.skills(id) ON DELETE CASCADE,
|
|
skill_level_id UUID REFERENCES hr.skill_levels(id),
|
|
-- Extensiones clínica
|
|
cedula_profesional VARCHAR(20),
|
|
fecha_certificacion DATE,
|
|
fecha_vencimiento DATE,
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
COMMENT ON TABLE hr.employee_skills IS 'Habilidades asignadas a empleados - FASE 8';
|
|
COMMENT ON COLUMN hr.employee_skills.cedula_profesional IS 'Número de cédula profesional';
|
|
|
|
-- Hojas de gastos
|
|
CREATE TABLE IF NOT EXISTS hr.expense_sheets (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL,
|
|
employee_id UUID NOT NULL,
|
|
name VARCHAR(100) NOT NULL,
|
|
state hr.expense_status DEFAULT 'draft',
|
|
accounting_date DATE,
|
|
total_amount NUMERIC(12,2) DEFAULT 0,
|
|
-- Extensiones clínica
|
|
paciente_id UUID,
|
|
cita_id UUID,
|
|
centro_costo VARCHAR(50),
|
|
-- Control
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
COMMENT ON TABLE hr.expense_sheets IS 'Hojas de gastos - FASE 8';
|
|
COMMENT ON COLUMN hr.expense_sheets.paciente_id IS 'Paciente asociado al gasto (si aplica)';
|
|
|
|
-- Gastos individuales
|
|
CREATE TABLE IF NOT EXISTS hr.expenses (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL,
|
|
sheet_id UUID REFERENCES hr.expense_sheets(id) ON DELETE CASCADE,
|
|
employee_id UUID NOT NULL,
|
|
name VARCHAR(200) NOT NULL,
|
|
date DATE NOT NULL DEFAULT CURRENT_DATE,
|
|
product_id UUID,
|
|
quantity NUMERIC(10,2) DEFAULT 1,
|
|
unit_amount NUMERIC(12,2) NOT NULL,
|
|
total_amount NUMERIC(12,2) NOT NULL,
|
|
state hr.expense_status DEFAULT 'draft',
|
|
reference VARCHAR(100),
|
|
description TEXT,
|
|
-- Control
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
COMMENT ON TABLE hr.expenses IS 'Gastos individuales - FASE 8';
|
|
|
|
-- Líneas de currículum
|
|
CREATE TABLE IF NOT EXISTS hr.employee_resume_lines (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL,
|
|
employee_id UUID NOT NULL,
|
|
line_type hr.resume_line_type NOT NULL,
|
|
name VARCHAR(200) NOT NULL,
|
|
description TEXT,
|
|
date_start DATE,
|
|
date_end DATE,
|
|
-- Control
|
|
display_type VARCHAR(20) DEFAULT 'classic',
|
|
sequence INTEGER DEFAULT 10,
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
COMMENT ON TABLE hr.employee_resume_lines IS 'Líneas de currículum/experiencia - FASE 8';
|
|
|
|
-- Estructuras de nómina
|
|
CREATE TABLE IF NOT EXISTS hr.payslip_structures (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL,
|
|
name VARCHAR(100) NOT NULL,
|
|
code VARCHAR(20) NOT NULL,
|
|
-- Extensiones clínica
|
|
tipo_pago VARCHAR(50), -- 'quincenal', 'semanal', 'honorarios', 'guardia'
|
|
-- Control
|
|
active BOOLEAN DEFAULT true,
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
CONSTRAINT uq_payslip_structures_tenant_code UNIQUE(tenant_id, code)
|
|
);
|
|
|
|
COMMENT ON TABLE hr.payslip_structures IS 'Estructuras de nómina - FASE 8';
|
|
|
|
-- Nóminas
|
|
CREATE TABLE IF NOT EXISTS hr.payslips (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL,
|
|
employee_id UUID NOT NULL,
|
|
structure_id UUID REFERENCES hr.payslip_structures(id),
|
|
name VARCHAR(100),
|
|
number VARCHAR(50),
|
|
date_from DATE NOT NULL,
|
|
date_to DATE NOT NULL,
|
|
state hr.payslip_status DEFAULT 'draft',
|
|
-- Montos
|
|
basic_wage NUMERIC(12,2) DEFAULT 0,
|
|
gross NUMERIC(12,2) DEFAULT 0,
|
|
net NUMERIC(12,2) DEFAULT 0,
|
|
-- Extensiones clínica
|
|
consultorio_id UUID REFERENCES hr.work_locations(id),
|
|
-- Control
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
COMMENT ON TABLE hr.payslips IS 'Nóminas - FASE 8';
|
|
|
|
-- Líneas de nómina
|
|
CREATE TABLE IF NOT EXISTS hr.payslip_lines (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL,
|
|
payslip_id UUID NOT NULL REFERENCES hr.payslips(id) ON DELETE CASCADE,
|
|
name VARCHAR(100) NOT NULL,
|
|
code VARCHAR(20),
|
|
category VARCHAR(50),
|
|
sequence INTEGER DEFAULT 10,
|
|
quantity NUMERIC(10,2) DEFAULT 1,
|
|
rate NUMERIC(12,4) DEFAULT 0,
|
|
amount NUMERIC(12,2) DEFAULT 0,
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
COMMENT ON TABLE hr.payslip_lines IS 'Líneas de nómina - FASE 8';
|
|
|
|
-- ============================================================================
|
|
-- CAMPOS ADICIONALES A EMPLOYEES (si existe)
|
|
-- ============================================================================
|
|
|
|
DO $$
|
|
BEGIN
|
|
IF EXISTS (SELECT 1 FROM information_schema.tables
|
|
WHERE table_schema = 'hr' AND table_name = 'employees') THEN
|
|
|
|
-- work_location_id
|
|
IF NOT EXISTS (SELECT 1 FROM information_schema.columns
|
|
WHERE table_schema = 'hr' AND table_name = 'employees'
|
|
AND column_name = 'work_location_id') THEN
|
|
ALTER TABLE hr.employees ADD COLUMN work_location_id UUID
|
|
REFERENCES hr.work_locations(id);
|
|
END IF;
|
|
|
|
-- badge_id
|
|
IF NOT EXISTS (SELECT 1 FROM information_schema.columns
|
|
WHERE table_schema = 'hr' AND table_name = 'employees'
|
|
AND column_name = 'badge_id') THEN
|
|
ALTER TABLE hr.employees ADD COLUMN badge_id VARCHAR(50);
|
|
END IF;
|
|
|
|
-- cedula_profesional
|
|
IF NOT EXISTS (SELECT 1 FROM information_schema.columns
|
|
WHERE table_schema = 'hr' AND table_name = 'employees'
|
|
AND column_name = 'cedula_profesional') THEN
|
|
ALTER TABLE hr.employees ADD COLUMN cedula_profesional VARCHAR(20);
|
|
END IF;
|
|
|
|
END IF;
|
|
END $$;
|
|
|
|
-- ============================================================================
|
|
-- ÍNDICES
|
|
-- ============================================================================
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_work_locations_tenant ON hr.work_locations(tenant_id);
|
|
CREATE INDEX IF NOT EXISTS idx_work_locations_tipo ON hr.work_locations(tenant_id, tipo_consultorio);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_skill_types_tenant ON hr.skill_types(tenant_id);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_skills_tenant ON hr.skills(tenant_id);
|
|
CREATE INDEX IF NOT EXISTS idx_skills_type ON hr.skills(skill_type_id);
|
|
CREATE INDEX IF NOT EXISTS idx_skills_codigo ON hr.skills(codigo_ssa) WHERE codigo_ssa IS NOT NULL;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_skill_levels_tenant ON hr.skill_levels(tenant_id);
|
|
CREATE INDEX IF NOT EXISTS idx_skill_levels_type ON hr.skill_levels(skill_type_id);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_employee_skills_tenant ON hr.employee_skills(tenant_id);
|
|
CREATE INDEX IF NOT EXISTS idx_employee_skills_employee ON hr.employee_skills(employee_id);
|
|
CREATE INDEX IF NOT EXISTS idx_employee_skills_skill ON hr.employee_skills(skill_id);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_expense_sheets_tenant ON hr.expense_sheets(tenant_id);
|
|
CREATE INDEX IF NOT EXISTS idx_expense_sheets_employee ON hr.expense_sheets(employee_id);
|
|
CREATE INDEX IF NOT EXISTS idx_expense_sheets_state ON hr.expense_sheets(tenant_id, state);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_expenses_tenant ON hr.expenses(tenant_id);
|
|
CREATE INDEX IF NOT EXISTS idx_expenses_sheet ON hr.expenses(sheet_id);
|
|
CREATE INDEX IF NOT EXISTS idx_expenses_employee ON hr.expenses(employee_id);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_employee_resume_lines_tenant ON hr.employee_resume_lines(tenant_id);
|
|
CREATE INDEX IF NOT EXISTS idx_employee_resume_lines_employee ON hr.employee_resume_lines(employee_id);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_payslip_structures_tenant ON hr.payslip_structures(tenant_id);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_payslips_tenant ON hr.payslips(tenant_id);
|
|
CREATE INDEX IF NOT EXISTS idx_payslips_employee ON hr.payslips(employee_id);
|
|
CREATE INDEX IF NOT EXISTS idx_payslips_dates ON hr.payslips(tenant_id, date_from, date_to);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_payslip_lines_payslip ON hr.payslip_lines(payslip_id);
|
|
|
|
-- ============================================================================
|
|
-- RLS
|
|
-- ============================================================================
|
|
|
|
ALTER TABLE hr.work_locations ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE hr.skill_types ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE hr.skills ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE hr.skill_levels ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE hr.expense_sheets ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE hr.expenses ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE hr.payslip_structures ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE hr.payslips ENABLE ROW LEVEL SECURITY;
|
|
|
|
DROP POLICY IF EXISTS tenant_isolation_work_locations ON hr.work_locations;
|
|
CREATE POLICY tenant_isolation_work_locations ON hr.work_locations
|
|
USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);
|
|
|
|
DROP POLICY IF EXISTS tenant_isolation_skill_types ON hr.skill_types;
|
|
CREATE POLICY tenant_isolation_skill_types ON hr.skill_types
|
|
USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);
|
|
|
|
DROP POLICY IF EXISTS tenant_isolation_skills ON hr.skills;
|
|
CREATE POLICY tenant_isolation_skills ON hr.skills
|
|
USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);
|
|
|
|
DROP POLICY IF EXISTS tenant_isolation_skill_levels ON hr.skill_levels;
|
|
CREATE POLICY tenant_isolation_skill_levels ON hr.skill_levels
|
|
USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);
|
|
|
|
DROP POLICY IF EXISTS tenant_isolation_expense_sheets ON hr.expense_sheets;
|
|
CREATE POLICY tenant_isolation_expense_sheets ON hr.expense_sheets
|
|
USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);
|
|
|
|
DROP POLICY IF EXISTS tenant_isolation_expenses ON hr.expenses;
|
|
CREATE POLICY tenant_isolation_expenses ON hr.expenses
|
|
USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);
|
|
|
|
DROP POLICY IF EXISTS tenant_isolation_payslip_structures ON hr.payslip_structures;
|
|
CREATE POLICY tenant_isolation_payslip_structures ON hr.payslip_structures
|
|
USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);
|
|
|
|
DROP POLICY IF EXISTS tenant_isolation_payslips ON hr.payslips;
|
|
CREATE POLICY tenant_isolation_payslips ON hr.payslips
|
|
USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);
|
|
|
|
-- ============================================================================
|
|
-- FIN HR EXTENSIONS
|
|
-- ============================================================================
|