Spaces:
Running
Running
| -- FiveTech: esquema SQLite WASM/sql.js. Ejecutar PRAGMA foreign_keys=ON en cada conexión. | |
| PRAGMA foreign_keys = ON; | |
| CREATE TABLE IF NOT EXISTS suppliers ( | |
| id TEXT PRIMARY KEY, name TEXT NOT NULL, country TEXT NOT NULL, | |
| contact_email TEXT, lead_days INTEGER NOT NULL CHECK (lead_days >= 0), active INTEGER NOT NULL DEFAULT 1 CHECK (active IN (0,1)) | |
| ); | |
| CREATE TABLE IF NOT EXISTS products ( | |
| sku TEXT PRIMARY KEY, name TEXT NOT NULL, category TEXT NOT NULL, | |
| unit TEXT NOT NULL, sale_price_cents INTEGER NOT NULL CHECK (sale_price_cents >= 0), | |
| reorder_point INTEGER NOT NULL CHECK (reorder_point >= 0), target_stock INTEGER NOT NULL CHECK (target_stock >= reorder_point), | |
| active INTEGER NOT NULL DEFAULT 1 CHECK (active IN (0,1)) | |
| ); | |
| CREATE TABLE IF NOT EXISTS supplier_products ( | |
| supplier_id TEXT NOT NULL REFERENCES suppliers(id), sku TEXT NOT NULL REFERENCES products(sku), | |
| unit_cost_cents INTEGER NOT NULL CHECK (unit_cost_cents >= 0), min_order_qty INTEGER NOT NULL DEFAULT 1 CHECK (min_order_qty > 0), | |
| PRIMARY KEY (supplier_id, sku) | |
| ); | |
| CREATE TABLE IF NOT EXISTS inventory ( | |
| sku TEXT PRIMARY KEY REFERENCES products(sku), on_hand INTEGER NOT NULL DEFAULT 0 CHECK (on_hand >= 0), | |
| reserved INTEGER NOT NULL DEFAULT 0 CHECK (reserved >= 0 AND reserved <= on_hand), | |
| updated_at TEXT NOT NULL | |
| ); | |
| CREATE TABLE IF NOT EXISTS sales_orders ( | |
| id TEXT PRIMARY KEY, customer_name TEXT NOT NULL, region TEXT NOT NULL, | |
| placed_at TEXT NOT NULL, status TEXT NOT NULL CHECK (status IN ('pendiente','confirmado','enviado','cancelado')) | |
| ); | |
| CREATE TABLE IF NOT EXISTS sales_order_lines ( | |
| order_id TEXT NOT NULL REFERENCES sales_orders(id), line_no INTEGER NOT NULL CHECK (line_no > 0), | |
| sku TEXT NOT NULL REFERENCES products(sku), quantity INTEGER NOT NULL CHECK (quantity > 0), | |
| unit_price_cents INTEGER NOT NULL CHECK (unit_price_cents >= 0), PRIMARY KEY (order_id, line_no) | |
| ); | |
| CREATE TABLE IF NOT EXISTS purchase_proposals ( | |
| id TEXT PRIMARY KEY, sku TEXT NOT NULL, supplier_id TEXT NOT NULL, | |
| proposed_qty INTEGER NOT NULL CHECK (proposed_qty > 0), unit_cost_cents INTEGER NOT NULL CHECK (unit_cost_cents >= 0), | |
| currency TEXT NOT NULL DEFAULT 'USD' CHECK (length(currency) = 3), | |
| agent_id TEXT NOT NULL, rationale TEXT NOT NULL, created_at TEXT NOT NULL, | |
| FOREIGN KEY (supplier_id, sku) REFERENCES supplier_products(supplier_id, sku) | |
| ); | |
| -- Una decisión por propuesta. Las propuestas pendientes aún no tienen decidido_por/decidido_at. | |
| CREATE TABLE IF NOT EXISTS approval_queue ( | |
| proposal_id TEXT PRIMARY KEY REFERENCES purchase_proposals(id), | |
| status TEXT NOT NULL DEFAULT 'pendiente' CHECK (status IN ('pendiente','aprobada','rechazada')), | |
| decided_by TEXT, decided_at TEXT, decision_reason TEXT, | |
| CHECK ((status = 'pendiente' AND decided_by IS NULL AND decided_at IS NULL) | |
| OR (status <> 'pendiente' AND decided_by IS NOT NULL AND decided_at IS NOT NULL)) | |
| ); | |
| CREATE TABLE IF NOT EXISTS stock_movements ( | |
| id TEXT PRIMARY KEY, sku TEXT NOT NULL REFERENCES products(sku), | |
| delta INTEGER NOT NULL CHECK (delta <> 0), reason TEXT NOT NULL, proposal_id TEXT REFERENCES purchase_proposals(id), | |
| created_at TEXT NOT NULL | |
| ); | |
| CREATE TABLE IF NOT EXISTS audit_log ( | |
| id TEXT PRIMARY KEY, occurred_at TEXT NOT NULL, actor TEXT NOT NULL, action TEXT NOT NULL, | |
| entity_type TEXT NOT NULL, entity_id TEXT NOT NULL, details_json TEXT NOT NULL CHECK (json_valid(details_json)) | |
| ); | |
| CREATE INDEX IF NOT EXISTS idx_orders_placed ON sales_orders(placed_at); | |
| CREATE INDEX IF NOT EXISTS idx_lines_sku ON sales_order_lines(sku); | |
| CREATE INDEX IF NOT EXISTS idx_approval_status ON approval_queue(status); | |
| CREATE INDEX IF NOT EXISTS idx_audit_time ON audit_log(occurred_at); | |
| CREATE VIEW IF NOT EXISTS stock_status AS | |
| SELECT p.sku, p.name, i.on_hand, i.reserved, i.on_hand - i.reserved AS available, | |
| p.reorder_point, p.target_stock, | |
| CASE WHEN i.on_hand - i.reserved <= p.reorder_point THEN 1 ELSE 0 END AS needs_restock | |
| FROM products p JOIN inventory i ON i.sku = p.sku; | |
| CREATE TABLE IF NOT EXISTS workforce ( | |
| id TEXT PRIMARY KEY, name TEXT NOT NULL, role TEXT NOT NULL, team TEXT NOT NULL, | |
| capacity_hours INTEGER NOT NULL CHECK(capacity_hours >= 0), assigned_hours INTEGER NOT NULL CHECK(assigned_hours >= 0), | |
| status TEXT NOT NULL CHECK(status IN ('disponible','ocupado','ausente')) | |
| ); | |
| CREATE TABLE IF NOT EXISTS production_jobs ( | |
| id TEXT PRIMARY KEY, sku TEXT NOT NULL REFERENCES products(sku), quantity INTEGER NOT NULL CHECK(quantity > 0), | |
| stage TEXT NOT NULL, due_at TEXT NOT NULL, owner TEXT NOT NULL, order_id TEXT REFERENCES sales_orders(id) | |
| ); | |
| CREATE TABLE IF NOT EXISTS planning_tasks ( | |
| id TEXT PRIMARY KEY, title TEXT NOT NULL, due_at TEXT NOT NULL, team TEXT NOT NULL, | |
| status TEXT NOT NULL CHECK(status IN ('pendiente','en_curso','hecho')), linked_order_id TEXT REFERENCES sales_orders(id) | |
| ); | |