-- ============================================================
-- MÓDULO: tenant_sucursales
-- Versión estándar: 2026-06-24
-- ============================================================
CREATE TABLE branches (
id UUID STORAGE PLAIN DEFAULT gen_random_uuid() NOT NULL,
tenant_id UUID STORAGE PLAIN NOT NULL,
code VARCHAR(20) NOT NULL, -- Inmutable tras creación
name_short VARCHAR(100) NOT NULL,
name_long VARCHAR(300),
branch_type VARCHAR(50) NOT NULL, -- 'admin_office' | 'sales_point' | 'warehouse' | 'other'
is_main BOOLEAN NOT NULL DEFAULT false, -- Solo una TRUE por tenant (índice parcial único)
address VARCHAR(500),
municipality_id VARCHAR(20), -- Código DANE (FK lógica → municipalities)
default_invoice_prefix VARCHAR(20), -- Prefijo facturación para esta sede
fiscal_establishment_code VARCHAR(50), -- Código DIAN del establecimiento
origin_id INTEGER STORAGE PLAIN NOT NULL,
state_id VARCHAR(100) NOT NULL, -- 'active' | 'inactive' | 'archived'
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,
updated_by UUID STORAGE PLAIN NOT NULL,
archived_at TIMESTAMP WITH TIME ZONE STORAGE PLAIN,
archived_by UUID STORAGE PLAIN,
CONSTRAINT branches_pkey PRIMARY KEY (id),
CONSTRAINT branches_tenant_code_key UNIQUE (tenant_id, code),
CONSTRAINT branches_state_check CHECK (state_id IN ('active', 'inactive', 'archived')),
CONSTRAINT branches_type_check CHECK (branch_type IN ('admin_office', 'sales_point', 'warehouse', 'other')),
CONSTRAINT branches_tenant_fkey FOREIGN KEY (tenant_id) REFERENCES tenants(id),
CONSTRAINT branches_origin_fkey FOREIGN KEY (origin_id) REFERENCES origins(id),
CONSTRAINT branches_created_fkey FOREIGN KEY (created_by) REFERENCES users(id),
CONSTRAINT branches_updated_fkey FOREIGN KEY (updated_by) REFERENCES users(id),
CONSTRAINT branches_archived_fkey FOREIGN KEY (archived_by) REFERENCES users(id)
);
-- Índices
CREATE INDEX idx_branches_tenant ON branches(tenant_id);
CREATE INDEX idx_branches_state ON branches(state_id);
CREATE INDEX idx_branches_type ON branches(branch_type);
CREATE INDEX idx_branches_active ON branches(tenant_id) WHERE archived_at IS NULL;
-- Solo una sucursal principal por tenant:
CREATE UNIQUE INDEX idx_branches_one_main ON branches(tenant_id) WHERE is_main = true;
-- ============================================================
-- TABLA HIJA: Teléfonos de sucursal
-- ============================================================
CREATE TABLE branch_phones (
id UUID STORAGE PLAIN DEFAULT gen_random_uuid() NOT NULL,
branch_id UUID STORAGE PLAIN NOT NULL,
country_code VARCHAR(5) NOT NULL DEFAULT '+57',
phone_type VARCHAR(20) NOT NULL DEFAULT 'movil', -- 'movil' | 'fijo'
phone VARCHAR(30) NOT NULL,
extension VARCHAR(10), -- Solo aplica a tipo 'fijo'
is_main BOOLEAN NOT NULL DEFAULT false,
CONSTRAINT branch_phones_pkey PRIMARY KEY (id),
CONSTRAINT branch_phones_branch_fkey FOREIGN KEY (branch_id) REFERENCES branches(id)
);
CREATE INDEX idx_branch_phones_branch ON branch_phones(branch_id);
-- ============================================================
-- TABLA HIJA: Emails de sucursal
-- ============================================================
CREATE TABLE branch_emails (
id UUID STORAGE PLAIN DEFAULT gen_random_uuid() NOT NULL,
branch_id UUID STORAGE PLAIN NOT NULL,
email VARCHAR(200) NOT NULL,
is_main BOOLEAN NOT NULL DEFAULT false,
CONSTRAINT branch_emails_pkey PRIMARY KEY (id),
CONSTRAINT branch_emails_branch_fkey FOREIGN KEY (branch_id) REFERENCES branches(id)
);
CREATE INDEX idx_branch_emails_branch ON branch_emails(branch_id);
-- ============================================================
-- REGISTRO SENTINEL (insertado automáticamente al aprovisionar tenant)
-- ============================================================
-- INSERT INTO branches (tenant_id, code, name_short, name_long, branch_type, is_main,
-- origin_id, state_id, created_by, updated_by)
-- VALUES (, '0', 'No aplica', 'Sin sucursal asignada',
-- 'other', false, 1, 'active', , );
-- ============================================================
-- REGLAS DE NEGOCIO
-- ============================================================
-- RN-001: code es inmutable tras creación (422 IMMUTABLE_FIELD)
-- RN-002: Solo una sucursal puede tener is_main = true por tenant (índice parcial único)
-- Si el usuario marca otra como principal, el backend desactiva la anterior en la misma TX
-- RN-003: Soft delete vía archived_at — nunca DELETE físico
-- RN-004: El sentinel (code='0') es de solo lectura — ni editable ni archivable
-- RN-005: branch_phones y branch_emails se crean/actualizan en la misma TX que branches
-- RN-006: is_main del sentinel siempre false — no puede ser principal
-- RN-007: municipality_id referencia lógica al catálogo de municipios DANE (Colombia)
-- Usado por el motor tributario para calcular tarifa ICA municipal
-- ============================================================
-- DATOS DE EJEMPLO — INSTITUCIÓN EDUCATIVA PÚBLICA COLOMBIA
-- ============================================================
-- ('0', 'No aplica', 'Sin sucursal asignada', 'other', false)
-- ('PRIN', 'Sede Principal', 'Sede Principal — Jornada M/T', 'admin_office', true )
-- ('SEDA', 'Sede A', 'Sede A — Básica Primaria', 'admin_office', false)
-- ('SEDB', 'Sede B', 'Sede B — Básica Rural', 'admin_office', false)
-- ============================================================