CREATE TABLE companies (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(180) NOT NULL, slug VARCHAR(80) NOT NULL UNIQUE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE users (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, name VARCHAR(160) NOT NULL, email VARCHAR(190) NOT NULL, password_hash VARCHAR(255) NOT NULL, active TINYINT(1) NOT NULL DEFAULT 1, is_admin TINYINT(1) NOT NULL DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uniq_company_email(company_id,email), UNIQUE KEY uniq_company_user(company_id,id), FOREIGN KEY(company_id) REFERENCES companies(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE user_permissions (company_id BIGINT UNSIGNED NOT NULL, user_id BIGINT UNSIGNED NOT NULL, permission VARCHAR(70) NOT NULL, PRIMARY KEY(company_id,user_id,permission), FOREIGN KEY(company_id,user_id) REFERENCES users(company_id,id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE warehouses (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, name VARCHAR(150) NOT NULL, code VARCHAR(40) NOT NULL, UNIQUE KEY uq_wh(company_id,code), UNIQUE KEY uq_wh_id(company_id,id), FOREIGN KEY(company_id) REFERENCES companies(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE colors (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, name VARCHAR(100) NOT NULL, code VARCHAR(40), UNIQUE KEY uq_color(company_id,name), UNIQUE KEY uq_color_id(company_id,id), FOREIGN KEY(company_id) REFERENCES companies(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE suppliers (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, name VARCHAR(190) NOT NULL, email VARCHAR(190), phone VARCHAR(60), UNIQUE KEY uq_supplier_id(company_id,id), FOREIGN KEY(company_id) REFERENCES companies(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE articles (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, sku VARCHAR(90) NOT NULL, name VARCHAR(200) NOT NULL, category VARCHAR(120), unit VARCHAR(20) NOT NULL DEFAULT 'un', active TINYINT(1) NOT NULL DEFAULT 1, UNIQUE KEY uq_sku(company_id,sku), UNIQUE KEY uq_article_id(company_id,id), FOREIGN KEY(company_id) REFERENCES companies(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE variants (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, article_id BIGINT UNSIGNED NOT NULL, color_id BIGINT UNSIGNED NOT NULL, barcode VARCHAR(100), min_stock DECIMAL(14,3) NOT NULL DEFAULT 0, avg_cost DECIMAL(14,4) NOT NULL DEFAULT 0, UNIQUE KEY uq_variant(company_id,article_id,color_id), UNIQUE KEY uq_variant_id(company_id,id), FOREIGN KEY(company_id,article_id) REFERENCES articles(company_id,id), FOREIGN KEY(company_id,color_id) REFERENCES colors(company_id,id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE variant_suppliers (company_id BIGINT UNSIGNED NOT NULL, variant_id BIGINT UNSIGNED NOT NULL, supplier_id BIGINT UNSIGNED NOT NULL, supplier_ref VARCHAR(100), purchase_price DECIMAL(14,4), preferred TINYINT(1) NOT NULL DEFAULT 0, PRIMARY KEY(company_id,variant_id,supplier_id), FOREIGN KEY(company_id,variant_id) REFERENCES variants(company_id,id), FOREIGN KEY(company_id,supplier_id) REFERENCES suppliers(company_id,id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE stock_balances (company_id BIGINT UNSIGNED NOT NULL, warehouse_id BIGINT UNSIGNED NOT NULL, variant_id BIGINT UNSIGNED NOT NULL, qty DECIMAL(14,3) NOT NULL DEFAULT 0, PRIMARY KEY(company_id,warehouse_id,variant_id), FOREIGN KEY(company_id,warehouse_id) REFERENCES warehouses(company_id,id), FOREIGN KEY(company_id,variant_id) REFERENCES variants(company_id,id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE clients (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, name VARCHAR(190) NOT NULL, email VARCHAR(190), phone VARCHAR(60), UNIQUE KEY uq_client_id(company_id,id), FOREIGN KEY(company_id) REFERENCES companies(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE projects (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, client_id BIGINT UNSIGNED NOT NULL, code VARCHAR(70) NOT NULL, name VARCHAR(190) NOT NULL, status VARCHAR(30) NOT NULL DEFAULT 'planeamento', UNIQUE KEY uq_project(company_id,code), UNIQUE KEY uq_project_id(company_id,id), FOREIGN KEY(company_id,client_id) REFERENCES clients(company_id,id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE reservations (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, project_id BIGINT UNSIGNED NOT NULL, warehouse_id BIGINT UNSIGNED NOT NULL, variant_id BIGINT UNSIGNED NOT NULL, qty DECIMAL(14,3) NOT NULL, picked_qty DECIMAL(14,3) NOT NULL DEFAULT 0, status VARCHAR(20) NOT NULL DEFAULT 'active', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_res_id(company_id,id), FOREIGN KEY(company_id,project_id) REFERENCES projects(company_id,id), FOREIGN KEY(company_id,warehouse_id) REFERENCES warehouses(company_id,id), FOREIGN KEY(company_id,variant_id) REFERENCES variants(company_id,id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE pickings (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, project_id BIGINT UNSIGNED NOT NULL, warehouse_id BIGINT UNSIGNED NOT NULL, ref VARCHAR(75) NOT NULL, status VARCHAR(20) NOT NULL DEFAULT 'draft', version INT UNSIGNED NOT NULL DEFAULT 1, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_pick_ref(company_id,ref), UNIQUE KEY uq_pick_id(company_id,id), FOREIGN KEY(company_id,project_id) REFERENCES projects(company_id,id), FOREIGN KEY(company_id,warehouse_id) REFERENCES warehouses(company_id,id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE picking_items (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, picking_id BIGINT UNSIGNED NOT NULL, reservation_id BIGINT UNSIGNED NOT NULL, qty DECIMAL(14,3) NOT NULL, FOREIGN KEY(company_id,picking_id) REFERENCES pickings(company_id,id), FOREIGN KEY(company_id,reservation_id) REFERENCES reservations(company_id,id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE stock_movements (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, warehouse_id BIGINT UNSIGNED NOT NULL, variant_id BIGINT UNSIGNED NOT NULL, user_id BIGINT UNSIGNED NOT NULL, kind VARCHAR(30) NOT NULL, delta DECIMAL(14,3) NOT NULL, before_qty DECIMAL(14,3) NOT NULL, after_qty DECIMAL(14,3) NOT NULL, unit_cost DECIMAL(14,4) NOT NULL DEFAULT 0, project_id BIGINT UNSIGNED NULL, notes VARCHAR(500), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(company_id,warehouse_id) REFERENCES warehouses(company_id,id), FOREIGN KEY(company_id,variant_id) REFERENCES variants(company_id,id), FOREIGN KEY(company_id,user_id) REFERENCES users(company_id,id), FOREIGN KEY(company_id,project_id) REFERENCES projects(company_id,id), INDEX idx_movements(company_id,created_at)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE notification_rules (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, event_key VARCHAR(40) NOT NULL, recipient_email VARCHAR(190) NOT NULL, enabled TINYINT(1) NOT NULL DEFAULT 1, UNIQUE KEY uq_notif(company_id,event_key,recipient_email), FOREIGN KEY(company_id) REFERENCES companies(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE email_outbox (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, recipient_email VARCHAR(190) NOT NULL, subject VARCHAR(255) NOT NULL, body TEXT NOT NULL, status VARCHAR(20) NOT NULL DEFAULT 'pending', attempts INT NOT NULL DEFAULT 0, last_error VARCHAR(500), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, sent_at TIMESTAMP NULL, FOREIGN KEY(company_id) REFERENCES companies(id), INDEX idx_outbox(status,created_at)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE audit_logs (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, user_id BIGINT UNSIGNED NOT NULL, event VARCHAR(100) NOT NULL, details TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(company_id,user_id) REFERENCES users(company_id,id), INDEX idx_audit(company_id,created_at)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE company_branding (company_id BIGINT UNSIGNED PRIMARY KEY, logo_file VARCHAR(90), tax_id VARCHAR(40), address VARCHAR(500), phone VARCHAR(60), contact_email VARCHAR(190), brand_color CHAR(7) NOT NULL DEFAULT '#113f81', document_footer VARCHAR(350), FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;