54 lines
2.2 KiB
SQL
54 lines
2.2 KiB
SQL
-- ============================================================================
|
|
-- FINANCIAL EXTENSIONS - FASE 8 ERP-Core
|
|
-- ERP Vidrio Templado
|
|
-- ============================================================================
|
|
|
|
CREATE SCHEMA IF NOT EXISTS financial;
|
|
|
|
DO $$ BEGIN
|
|
CREATE TYPE financial.payment_method_type AS ENUM ('inbound', 'outbound');
|
|
EXCEPTION WHEN duplicate_object THEN NULL;
|
|
END $$;
|
|
|
|
-- Métodos de pago
|
|
CREATE TABLE IF NOT EXISTS financial.payment_methods (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL,
|
|
name VARCHAR(100) NOT NULL,
|
|
code VARCHAR(20) NOT NULL,
|
|
payment_type financial.payment_method_type NOT NULL,
|
|
-- Extensiones vidrio
|
|
aplica_anticipo BOOLEAN DEFAULT false,
|
|
porcentaje_anticipo NUMERIC(5,2) DEFAULT 0,
|
|
active BOOLEAN DEFAULT true,
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
CONSTRAINT uq_payment_methods_code UNIQUE(tenant_id, code)
|
|
);
|
|
|
|
-- Términos de pago
|
|
CREATE TABLE IF NOT EXISTS financial.payment_term_lines (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL,
|
|
payment_term_id UUID,
|
|
value_type VARCHAR(20) DEFAULT 'percent',
|
|
value NUMERIC(10,2) DEFAULT 0,
|
|
days INTEGER DEFAULT 0,
|
|
applies_to VARCHAR(50), -- 'anticipo', 'produccion', 'instalacion', 'finiquito'
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
-- Índices y RLS
|
|
CREATE INDEX IF NOT EXISTS idx_payment_methods_tenant ON financial.payment_methods(tenant_id);
|
|
CREATE INDEX IF NOT EXISTS idx_payment_term_lines_tenant ON financial.payment_term_lines(tenant_id);
|
|
|
|
ALTER TABLE financial.payment_methods ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE financial.payment_term_lines ENABLE ROW LEVEL SECURITY;
|
|
|
|
DROP POLICY IF EXISTS tenant_isolation_payment_methods ON financial.payment_methods;
|
|
CREATE POLICY tenant_isolation_payment_methods ON financial.payment_methods
|
|
USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);
|
|
|
|
DROP POLICY IF EXISTS tenant_isolation_payment_term_lines ON financial.payment_term_lines;
|
|
CREATE POLICY tenant_isolation_payment_term_lines ON financial.payment_term_lines
|
|
USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);
|