CREATE TABLE IF NOT EXISTS customers (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(180) NOT NULL, document VARCHAR(30), email VARCHAR(180), city VARCHAR(100), state CHAR(2), address TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE IF NOT EXISTS users (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(140) NOT NULL, email VARCHAR(190) NOT NULL UNIQUE, password_hash VARCHAR(255) NOT NULL, role ENUM('admin','manager','staff','customer') NOT NULL DEFAULT 'staff', customer_id BIGINT UNSIGNED NULL, totp_secret_enc TEXT NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (customer_id) REFERENCES customers(id));
CREATE TABLE IF NOT EXISTS orders (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, code VARCHAR(30) NOT NULL UNIQUE, customer_id BIGINT UNSIGNED NOT NULL, creator_id BIGINT UNSIGNED NOT NULL, factory ENUM('Araucária','Almirante','Guaramirim') NOT NULL, freight_mode ENUM('CIF','FOB') NOT NULL DEFAULT 'FOB', freight_per_ton DECIMAL(14,2) NOT NULL DEFAULT 0, region_city VARCHAR(120), region_state CHAR(2), payment_method VARCHAR(80), due_date DATE, withdrawal_start DATE, withdrawal_end DATE, delivery_address TEXT, notes TEXT, commission_percent DECIMAL(6,3) NOT NULL DEFAULT 0, status ENUM('draft','submitted','approved','archived','deleted') NOT NULL DEFAULT 'draft', approved_by BIGINT UNSIGNED NULL, approved_at DATETIME NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (customer_id) REFERENCES customers(id), FOREIGN KEY (creator_id) REFERENCES users(id));
CREATE TABLE IF NOT EXISTS order_items (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, order_id BIGINT UNSIGNED NOT NULL, product VARCHAR(180) NOT NULL, tons DECIMAL(13,3) NOT NULL, sulfur VARCHAR(40) NOT NULL DEFAULT '', unit_price DECIMAL(14,2) NOT NULL, packaging VARCHAR(80), FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE);
CREATE TABLE IF NOT EXISTS audit_logs (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NULL, action VARCHAR(60) NOT NULL, entity VARCHAR(60), entity_id BIGINT UNSIGNED NULL, ip VARCHAR(45), recorded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, metadata JSON NULL, INDEX (entity, entity_id));
CREATE TABLE IF NOT EXISTS login_attempts (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, email VARCHAR(190) NOT NULL, ip VARCHAR(45) NOT NULL, attempted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX (email, attempted_at), INDEX (ip, attempted_at));

CREATE TABLE IF NOT EXISTS products (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(180) NOT NULL UNIQUE, sulfur VARCHAR(40) DEFAULT '', packaging VARCHAR(80) DEFAULT '', is_active TINYINT(1) NOT NULL DEFAULT 1, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);