-- BANDUNGCRAFT MySQL schema (Phase 4)
-- Engine InnoDB, utf8mb4, DECIMAL untuk uang. Import ke DB cPanel via phpMyAdmin.
SET NAMES utf8mb4;
CREATE TABLE IF NOT EXISTS roles (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL UNIQUE, slug VARCHAR(255) NOT NULL UNIQUE, created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS products (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, sku VARCHAR(50) NOT NULL UNIQUE, slug VARCHAR(255) NOT NULL UNIQUE, name VARCHAR(255) NOT NULL, category_id BIGINT UNSIGNED NOT NULL, cost_price DECIMAL(15,2) DEFAULT 0, base_price DECIMAL(15,2) NOT NULL, stock_qty INT DEFAULT 0, active TINYINT(1) DEFAULT 1, featured TINYINT(1) DEFAULT 0, deleted_at TIMESTAMP NULL, created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS product_images (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, product_id BIGINT UNSIGNED NOT NULL, path VARCHAR(255) NOT NULL, mime VARCHAR(50) NULL, sort INT DEFAULT 0, created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, CONSTRAINT fk_img_product FOREIGN KEY(product_id) REFERENCES products(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS orders (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, order_number VARCHAR(50) NOT NULL UNIQUE, customer_id BIGINT UNSIGNED NOT NULL, subtotal DECIMAL(15,2) NOT NULL, discount DECIMAL(15,2) DEFAULT 0, shipping_cost DECIMAL(15,2) DEFAULT 0, total DECIMAL(15,2) NOT NULL, status VARCHAR(50) DEFAULT 'menunggu_pembayaran', deleted_at TIMESTAMP NULL, created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS payments (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, order_id BIGINT UNSIGNED NOT NULL, idempotency_key CHAR(36) NOT NULL UNIQUE, amount DECIMAL(15,2) NOT NULL, kind VARCHAR(20) DEFAULT 'pembayaran', status VARCHAR(20) DEFAULT 'pending', proof_path VARCHAR(255) NULL, created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, CONSTRAINT fk_pay_order FOREIGN KEY(order_id) REFERENCES orders(id) ON DELETE CASCADE, INDEX idx_pay_order_status(order_id,status)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS quotations (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, quotation_number VARCHAR(50) NOT NULL UNIQUE, custom_order_id BIGINT UNSIGNED NULL, total DECIMAL(15,2) NOT NULL, status VARCHAR(20) DEFAULT 'draft', deleted_at TIMESTAMP NULL, created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS transactions (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, transaction_number VARCHAR(50) NOT NULL UNIQUE, type VARCHAR(20) NOT NULL, total DECIMAL(15,2) NOT NULL, status VARCHAR(20) DEFAULT 'lunas', created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- Lanjutan: lihat backend/database/migrations/ untuk semua FK, index, dan soft delete lainnya.