Gestione y estructure su jerarquía financiera (PUC).
Esta cuenta será heredada automáticamente por todos los tenants.
-- ============================================================
-- MÓDULO: Gestor PUC Maestro (Super Admin)
-- Tabla principal: master_accounts
-- ============================================================
CREATE TABLE master_accounts (
-- Identificación
id UUID STORAGE PLAIN DEFAULT gen_random_uuid() NOT NULL,
tenant_id UUID STORAGE PLAIN, -- NULL = registro Super Admin
-- Jerarquía PUC
parent_id UUID STORAGE PLAIN, -- NULL = Nivel 1 (Clase). FK auto-referencial ON DELETE RESTRICT
code VARCHAR(20) NOT NULL, -- Inmutable post-creación. Único globalmente
level INTEGER STORAGE PLAIN NOT NULL, -- 1=Clase, 2=Grupo, 3=Cuenta, 4+=Subcuenta
-- Nombres (convención estándar)
name_short VARCHAR(100) NOT NULL, -- Nombre corto visible en árbol
name_long VARCHAR(300), -- Descripción completa
-- Atributos contables
account_type VARCHAR(20) NOT NULL, -- 'grouping' (Agrupadora) | 'detail' (Detalle)
nature VARCHAR(20) NOT NULL, -- 'debito' | 'credito'
classification VARCHAR(20) NOT NULL, -- 'asset'|'liability'|'equity'|'revenue'|'expense'
-- Parametrización estándar
icon_id INTEGER STORAGE PLAIN NOT NULL, -- FK → icons.id
state_id VARCHAR(100) NOT NULL, -- FK → statuses.id ('active'|'inactive'|'archived')
origin_id INTEGER STORAGE PLAIN NOT NULL, -- FK → origins.id ('master'|'inherited'|'local')
-- Auditoría estándar
created_at TIMESTAMP WITH TIME ZONE STORAGE PLAIN DEFAULT now() NOT NULL,
updated_at TIMESTAMP WITH TIME ZONE STORAGE PLAIN DEFAULT now() NOT NULL,
created_by UUID STORAGE PLAIN NOT NULL, -- FK → users.id
updated_by UUID STORAGE PLAIN NOT NULL, -- FK → users.id
archived_at TIMESTAMP WITH TIME ZONE STORAGE PLAIN, -- NULL = no archivado
archived_by UUID STORAGE PLAIN, -- NULL = no archivado. FK → users.id
-- Constraints
CONSTRAINT pk_master_accounts PRIMARY KEY (id),
CONSTRAINT uq_master_accounts_code UNIQUE (code),
CONSTRAINT chk_master_accounts_account_type CHECK (account_type IN ('grouping', 'detail')),
CONSTRAINT chk_master_accounts_nature CHECK (nature IN ('debito', 'credito')),
CONSTRAINT chk_master_accounts_classification CHECK (classification IN ('asset','liability','equity','revenue','expense')),
CONSTRAINT chk_master_accounts_level CHECK (level >= 1)
);
-- FK auto-referencial: bloquea eliminar agrupadora con hijos maestros
ALTER TABLE master_accounts
ADD CONSTRAINT fk_master_accounts_parent
FOREIGN KEY (parent_id)
REFERENCES master_accounts(id)
ON DELETE RESTRICT;
-- FK hacia tenant_accounts (se define en ese módulo):
-- ALTER TABLE tenant_accounts
-- ADD CONSTRAINT fk_tenant_accounts_master
-- FOREIGN KEY (master_account_id)
-- REFERENCES master_accounts(id)
-- ON DELETE RESTRICT;
-- Índices
CREATE INDEX idx_master_accounts_parent_id ON master_accounts(parent_id);
CREATE INDEX idx_master_accounts_code ON master_accounts(code);
CREATE INDEX idx_master_accounts_tenant_id ON master_accounts(tenant_id);
CREATE INDEX idx_master_accounts_level ON master_accounts(level);
CREATE INDEX idx_master_accounts_account_type ON master_accounts(account_type);
CREATE INDEX idx_master_accounts_origin_id ON master_accounts(origin_id);
CREATE INDEX idx_master_accounts_state_id ON master_accounts(state_id);
CREATE INDEX idx_master_accounts_archived_at ON master_accounts(archived_at) WHERE archived_at IS NULL;
-- ============================================================
-- REGLAS DE NEGOCIO CLAVE
-- ============================================================
-- 1. tenant_id NULL = registro maestro Super Admin
-- 2. code es inmutable post-creación (IMMUTABLE_FIELD)
-- 3. account_type='grouping' admite hijos; 'detail' no admite hijos
-- 4. Soft delete obligatorio: archived_at + archived_by (nunca DELETE físico)
-- 5. Eliminación bloqueada por FK RESTRICT en cadena:
-- master_accounts.parent_id → master_accounts.id
-- tenant_accounts.master_account_id → master_accounts.id
-- movimientos.account_id → tenant_accounts.id
-- 6. Toda operación CRU dispara registro en audit_logs (middleware GORM)
-- ============================================================