rckrdmrd
5043a640e4
- Replace old DDL structure with new numbered files (01-24) - Update migrations and seeds for new schema - Clean up deprecated files Co-Authored-By: Claude Opus 4.5 <noreply@anthropic.com>
286 lines
9.8 KiB
SQL
286 lines
9.8 KiB
SQL
-- =============================================================
|
|
-- ARCHIVO: 22-sales.sql
|
|
-- DESCRIPCION: Cotizaciones y ordenes de venta
|
|
-- VERSION: 1.0.0
|
|
-- PROYECTO: ERP-Core V2
|
|
-- FECHA: 2026-01-13
|
|
-- DEPENDE DE: 16-partners.sql, 17-products.sql
|
|
-- =============================================================
|
|
|
|
-- =====================
|
|
-- SCHEMA: sales
|
|
-- =====================
|
|
CREATE SCHEMA IF NOT EXISTS sales;
|
|
|
|
-- =====================
|
|
-- TABLA: quotations
|
|
-- Cotizaciones de venta
|
|
-- =====================
|
|
CREATE TABLE IF NOT EXISTS sales.quotations (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
|
|
|
|
-- Identificacion
|
|
quotation_number VARCHAR(30) NOT NULL,
|
|
|
|
-- Cliente
|
|
partner_id UUID NOT NULL REFERENCES partners.partners(id) ON DELETE RESTRICT,
|
|
partner_name VARCHAR(200), -- Snapshot del nombre
|
|
partner_email VARCHAR(255),
|
|
|
|
-- Direcciones
|
|
billing_address JSONB,
|
|
shipping_address JSONB,
|
|
|
|
-- Fechas
|
|
quotation_date DATE NOT NULL DEFAULT CURRENT_DATE,
|
|
valid_until DATE,
|
|
expected_close_date DATE,
|
|
|
|
-- Vendedor
|
|
sales_rep_id UUID REFERENCES auth.users(id),
|
|
|
|
-- Totales
|
|
currency VARCHAR(3) DEFAULT 'MXN',
|
|
subtotal DECIMAL(15, 2) NOT NULL DEFAULT 0,
|
|
tax_amount DECIMAL(15, 2) NOT NULL DEFAULT 0,
|
|
discount_amount DECIMAL(15, 2) NOT NULL DEFAULT 0,
|
|
total DECIMAL(15, 2) NOT NULL DEFAULT 0,
|
|
|
|
-- Terminos
|
|
payment_term_days INTEGER DEFAULT 0,
|
|
payment_method VARCHAR(50),
|
|
|
|
-- Estado
|
|
status VARCHAR(20) NOT NULL DEFAULT 'draft', -- draft, sent, accepted, rejected, expired, converted
|
|
|
|
-- Conversion
|
|
converted_to_order BOOLEAN DEFAULT FALSE,
|
|
order_id UUID,
|
|
converted_at TIMESTAMPTZ,
|
|
|
|
-- Notas
|
|
notes TEXT,
|
|
internal_notes TEXT,
|
|
terms_and_conditions TEXT,
|
|
|
|
-- Metadata
|
|
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
|
created_by UUID REFERENCES auth.users(id),
|
|
updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
|
updated_by UUID REFERENCES auth.users(id),
|
|
deleted_at TIMESTAMPTZ,
|
|
|
|
UNIQUE(tenant_id, quotation_number)
|
|
);
|
|
|
|
-- Indices para quotations
|
|
CREATE INDEX IF NOT EXISTS idx_quotations_tenant ON sales.quotations(tenant_id);
|
|
CREATE INDEX IF NOT EXISTS idx_quotations_number ON sales.quotations(quotation_number);
|
|
CREATE INDEX IF NOT EXISTS idx_quotations_partner ON sales.quotations(partner_id);
|
|
CREATE INDEX IF NOT EXISTS idx_quotations_status ON sales.quotations(status);
|
|
CREATE INDEX IF NOT EXISTS idx_quotations_date ON sales.quotations(quotation_date);
|
|
CREATE INDEX IF NOT EXISTS idx_quotations_valid_until ON sales.quotations(valid_until);
|
|
CREATE INDEX IF NOT EXISTS idx_quotations_sales_rep ON sales.quotations(sales_rep_id);
|
|
|
|
-- =====================
|
|
-- TABLA: quotation_items
|
|
-- Lineas de cotizacion
|
|
-- =====================
|
|
CREATE TABLE IF NOT EXISTS sales.quotation_items (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
quotation_id UUID NOT NULL REFERENCES sales.quotations(id) ON DELETE CASCADE,
|
|
product_id UUID REFERENCES products.products(id) ON DELETE SET NULL,
|
|
|
|
-- Linea
|
|
line_number INTEGER NOT NULL DEFAULT 1,
|
|
|
|
-- Producto
|
|
product_sku VARCHAR(50),
|
|
product_name VARCHAR(200) NOT NULL,
|
|
description TEXT,
|
|
|
|
-- Cantidad y precio
|
|
quantity DECIMAL(15, 4) NOT NULL DEFAULT 1,
|
|
uom VARCHAR(20) DEFAULT 'PZA',
|
|
unit_price DECIMAL(15, 4) NOT NULL DEFAULT 0,
|
|
|
|
-- Descuentos
|
|
discount_percent DECIMAL(5, 2) DEFAULT 0,
|
|
discount_amount DECIMAL(15, 2) DEFAULT 0,
|
|
|
|
-- Impuestos
|
|
tax_rate DECIMAL(5, 2) DEFAULT 16.00,
|
|
tax_amount DECIMAL(15, 2) DEFAULT 0,
|
|
|
|
-- Totales
|
|
subtotal DECIMAL(15, 2) NOT NULL DEFAULT 0,
|
|
total DECIMAL(15, 2) NOT NULL DEFAULT 0,
|
|
|
|
-- Metadata
|
|
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Indices para quotation_items
|
|
CREATE INDEX IF NOT EXISTS idx_quotation_items_quotation ON sales.quotation_items(quotation_id);
|
|
CREATE INDEX IF NOT EXISTS idx_quotation_items_product ON sales.quotation_items(product_id);
|
|
CREATE INDEX IF NOT EXISTS idx_quotation_items_line ON sales.quotation_items(quotation_id, line_number);
|
|
|
|
-- =====================
|
|
-- TABLA: sales_orders
|
|
-- Ordenes de venta
|
|
-- =====================
|
|
CREATE TABLE IF NOT EXISTS sales.sales_orders (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
|
|
|
|
-- Identificacion
|
|
order_number VARCHAR(30) NOT NULL,
|
|
|
|
-- Origen
|
|
quotation_id UUID REFERENCES sales.quotations(id),
|
|
|
|
-- Cliente
|
|
partner_id UUID NOT NULL REFERENCES partners.partners(id) ON DELETE RESTRICT,
|
|
partner_name VARCHAR(200),
|
|
partner_email VARCHAR(255),
|
|
|
|
-- Direcciones
|
|
billing_address JSONB,
|
|
shipping_address JSONB,
|
|
|
|
-- Fechas
|
|
order_date DATE NOT NULL DEFAULT CURRENT_DATE,
|
|
requested_date DATE, -- Fecha solicitada por cliente
|
|
promised_date DATE, -- Fecha prometida
|
|
shipped_date DATE,
|
|
delivered_date DATE,
|
|
|
|
-- Vendedor
|
|
sales_rep_id UUID REFERENCES auth.users(id),
|
|
|
|
-- Almacen
|
|
warehouse_id UUID REFERENCES inventory.warehouses(id),
|
|
|
|
-- Totales
|
|
currency VARCHAR(3) DEFAULT 'MXN',
|
|
subtotal DECIMAL(15, 2) NOT NULL DEFAULT 0,
|
|
tax_amount DECIMAL(15, 2) NOT NULL DEFAULT 0,
|
|
discount_amount DECIMAL(15, 2) NOT NULL DEFAULT 0,
|
|
shipping_amount DECIMAL(15, 2) DEFAULT 0,
|
|
total DECIMAL(15, 2) NOT NULL DEFAULT 0,
|
|
|
|
-- Terminos
|
|
payment_term_days INTEGER DEFAULT 0,
|
|
payment_method VARCHAR(50),
|
|
|
|
-- Estado
|
|
status VARCHAR(20) NOT NULL DEFAULT 'draft', -- draft, confirmed, processing, shipped, delivered, cancelled
|
|
|
|
-- Envio
|
|
shipping_method VARCHAR(50),
|
|
tracking_number VARCHAR(100),
|
|
carrier VARCHAR(100),
|
|
|
|
-- Notas
|
|
notes TEXT,
|
|
internal_notes TEXT,
|
|
|
|
-- Metadata
|
|
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
|
created_by UUID REFERENCES auth.users(id),
|
|
updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
|
updated_by UUID REFERENCES auth.users(id),
|
|
deleted_at TIMESTAMPTZ,
|
|
|
|
UNIQUE(tenant_id, order_number)
|
|
);
|
|
|
|
-- Indices para sales_orders
|
|
CREATE INDEX IF NOT EXISTS idx_sales_orders_tenant ON sales.sales_orders(tenant_id);
|
|
CREATE INDEX IF NOT EXISTS idx_sales_orders_number ON sales.sales_orders(order_number);
|
|
CREATE INDEX IF NOT EXISTS idx_sales_orders_quotation ON sales.sales_orders(quotation_id);
|
|
CREATE INDEX IF NOT EXISTS idx_sales_orders_partner ON sales.sales_orders(partner_id);
|
|
CREATE INDEX IF NOT EXISTS idx_sales_orders_status ON sales.sales_orders(status);
|
|
CREATE INDEX IF NOT EXISTS idx_sales_orders_date ON sales.sales_orders(order_date);
|
|
CREATE INDEX IF NOT EXISTS idx_sales_orders_warehouse ON sales.sales_orders(warehouse_id);
|
|
CREATE INDEX IF NOT EXISTS idx_sales_orders_sales_rep ON sales.sales_orders(sales_rep_id);
|
|
|
|
-- =====================
|
|
-- TABLA: sales_order_items
|
|
-- Lineas de orden de venta
|
|
-- =====================
|
|
CREATE TABLE IF NOT EXISTS sales.sales_order_items (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
order_id UUID NOT NULL REFERENCES sales.sales_orders(id) ON DELETE CASCADE,
|
|
product_id UUID REFERENCES products.products(id) ON DELETE SET NULL,
|
|
|
|
-- Linea
|
|
line_number INTEGER NOT NULL DEFAULT 1,
|
|
|
|
-- Producto
|
|
product_sku VARCHAR(50),
|
|
product_name VARCHAR(200) NOT NULL,
|
|
description TEXT,
|
|
|
|
-- Cantidad
|
|
quantity DECIMAL(15, 4) NOT NULL DEFAULT 1,
|
|
quantity_reserved DECIMAL(15, 4) DEFAULT 0,
|
|
quantity_shipped DECIMAL(15, 4) DEFAULT 0,
|
|
quantity_delivered DECIMAL(15, 4) DEFAULT 0,
|
|
quantity_returned DECIMAL(15, 4) DEFAULT 0,
|
|
uom VARCHAR(20) DEFAULT 'PZA',
|
|
|
|
-- Precio
|
|
unit_price DECIMAL(15, 4) NOT NULL DEFAULT 0,
|
|
unit_cost DECIMAL(15, 4) DEFAULT 0,
|
|
|
|
-- Descuentos
|
|
discount_percent DECIMAL(5, 2) DEFAULT 0,
|
|
discount_amount DECIMAL(15, 2) DEFAULT 0,
|
|
|
|
-- Impuestos
|
|
tax_rate DECIMAL(5, 2) DEFAULT 16.00,
|
|
tax_amount DECIMAL(15, 2) DEFAULT 0,
|
|
|
|
-- Totales
|
|
subtotal DECIMAL(15, 2) NOT NULL DEFAULT 0,
|
|
total DECIMAL(15, 2) NOT NULL DEFAULT 0,
|
|
|
|
-- Lote/Serie
|
|
lot_number VARCHAR(50),
|
|
serial_number VARCHAR(50),
|
|
|
|
-- Estado
|
|
status VARCHAR(20) DEFAULT 'pending', -- pending, reserved, shipped, delivered, cancelled
|
|
|
|
-- Metadata
|
|
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Indices para sales_order_items
|
|
CREATE INDEX IF NOT EXISTS idx_sales_order_items_order ON sales.sales_order_items(order_id);
|
|
CREATE INDEX IF NOT EXISTS idx_sales_order_items_product ON sales.sales_order_items(product_id);
|
|
CREATE INDEX IF NOT EXISTS idx_sales_order_items_line ON sales.sales_order_items(order_id, line_number);
|
|
CREATE INDEX IF NOT EXISTS idx_sales_order_items_status ON sales.sales_order_items(status);
|
|
|
|
-- =====================
|
|
-- COMENTARIOS
|
|
-- =====================
|
|
COMMENT ON TABLE sales.quotations IS 'Cotizaciones de venta a clientes';
|
|
COMMENT ON COLUMN sales.quotations.status IS 'Estado: draft, sent, accepted, rejected, expired, converted';
|
|
COMMENT ON COLUMN sales.quotations.converted_to_order IS 'Indica si la cotizacion fue convertida a orden de venta';
|
|
|
|
COMMENT ON TABLE sales.quotation_items IS 'Lineas de detalle de cotizaciones';
|
|
|
|
COMMENT ON TABLE sales.sales_orders IS 'Ordenes de venta confirmadas';
|
|
COMMENT ON COLUMN sales.sales_orders.status IS 'Estado: draft, confirmed, processing, shipped, delivered, cancelled';
|
|
COMMENT ON COLUMN sales.sales_orders.quotation_id IS 'Referencia a la cotizacion origen (si aplica)';
|
|
|
|
COMMENT ON TABLE sales.sales_order_items IS 'Lineas de detalle de ordenes de venta';
|
|
COMMENT ON COLUMN sales.sales_order_items.quantity_reserved IS 'Cantidad reservada en inventario';
|
|
COMMENT ON COLUMN sales.sales_order_items.quantity_shipped IS 'Cantidad enviada';
|
|
COMMENT ON COLUMN sales.sales_order_items.quantity_delivered IS 'Cantidad entregada al cliente';
|