/home/ejrndhmu/rwberdaya.com/database.sql
-- Database: tokopro
-- Struktur database untuk aplikasi toko online
CREATE DATABASE IF NOT EXISTS tokopro;
USE tokopro;
-- Tabel users (admin dan customer)
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) UNIQUE NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
password VARCHAR(255) NOT NULL,
full_name VARCHAR(100) NOT NULL,
phone VARCHAR(20),
address TEXT,
role ENUM('admin', 'customer') DEFAULT 'customer',
status ENUM('active', 'inactive') DEFAULT 'active',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
-- Tabel categories
CREATE TABLE categories (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
description TEXT,
image VARCHAR(255),
status ENUM('active', 'inactive') DEFAULT 'active',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
-- Tabel products
CREATE TABLE products (
id INT AUTO_INCREMENT PRIMARY KEY,
category_id INT,
name VARCHAR(200) NOT NULL,
description TEXT,
price DECIMAL(10,2) NOT NULL,
stock INT DEFAULT 0,
weight DECIMAL(8,2) DEFAULT 0,
image VARCHAR(255),
status ENUM('active', 'inactive') DEFAULT 'active',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
);
-- Tabel cart
CREATE TABLE cart (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT DEFAULT 1,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
);
-- Tabel payment_methods
CREATE TABLE payment_methods (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
description TEXT,
account_number VARCHAR(100),
account_name VARCHAR(100),
status ENUM('active', 'inactive') DEFAULT 'active',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Tabel shipping_methods
CREATE TABLE shipping_methods (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
description TEXT,
cost DECIMAL(10,2) DEFAULT 0,
status ENUM('active', 'inactive') DEFAULT 'active',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Tabel orders
CREATE TABLE orders (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
order_number VARCHAR(50) UNIQUE NOT NULL,
total_amount DECIMAL(10,2) NOT NULL,
shipping_cost DECIMAL(10,2) DEFAULT 0,
payment_method_id INT,
shipping_method_id INT,
shipping_address TEXT NOT NULL,
status ENUM('pending', 'confirmed', 'processing', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending',
payment_status ENUM('pending', 'paid', 'failed') DEFAULT 'pending',
payment_proof VARCHAR(255),
notes TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
FOREIGN KEY (payment_method_id) REFERENCES payment_methods(id) ON DELETE SET NULL,
FOREIGN KEY (shipping_method_id) REFERENCES shipping_methods(id) ON DELETE SET NULL
);
-- Tabel order_items
CREATE TABLE order_items (
id INT AUTO_INCREMENT PRIMARY KEY,
order_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL,
price DECIMAL(10,2) NOT NULL,
subtotal DECIMAL(10,2) NOT NULL,
FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
);
-- Tabel settings
CREATE TABLE settings (
id INT AUTO_INCREMENT PRIMARY KEY,
setting_key VARCHAR(100) UNIQUE NOT NULL,
setting_value TEXT,
description TEXT,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
-- Insert data default
-- Admin user default
INSERT INTO users (username, email, password, full_name, role) VALUES
('admin', 'admin@tokopro.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Administrator', 'admin');
-- Password: password
-- Categories default
INSERT INTO categories (name, description) VALUES
('Elektronik', 'Produk elektronik dan gadget'),
('Fashion', 'Pakaian dan aksesoris'),
('Makanan & Minuman', 'Produk makanan dan minuman'),
('Kesehatan & Kecantikan', 'Produk kesehatan dan kecantikan'),
('Olahraga', 'Peralatan dan perlengkapan olahraga');
-- Payment methods default
INSERT INTO payment_methods (name, description, account_number, account_name) VALUES
('Transfer Bank BCA', 'Transfer ke rekening BCA', '1234567890', 'Toko Pro'),
('Transfer Bank Mandiri', 'Transfer ke rekening Mandiri', '0987654321', 'Toko Pro'),
('E-Wallet OVO', 'Pembayaran melalui OVO', '081234567890', 'Toko Pro');
-- Shipping methods default
INSERT INTO shipping_methods (name, description, cost) VALUES
('Reguler', 'Pengiriman reguler 3-5 hari', 15000),
('Express', 'Pengiriman express 1-2 hari', 25000),
('Same Day', 'Pengiriman hari yang sama', 35000);
-- Settings default
INSERT INTO settings (setting_key, setting_value, description) VALUES
('site_name', 'Toko Pro', 'Nama website'),
('site_description', 'Toko Online Terpercaya', 'Deskripsi website'),
('site_logo', '', 'Logo website'),
('contact_phone', '081234567890', 'Nomor telepon kontak'),
('contact_email', 'info@tokopro.com', 'Email kontak'),
('contact_address', 'Jl. Contoh No. 123, Jakarta', 'Alamat toko');
-- Tabel product_media untuk multiple gambar dan video
CREATE TABLE product_media (
id INT AUTO_INCREMENT PRIMARY KEY,
product_id INT NOT NULL,
media_type ENUM('image', 'youtube') NOT NULL,
media_url VARCHAR(500) NOT NULL,
media_title VARCHAR(200),
sort_order INT DEFAULT 0,
is_primary BOOLEAN DEFAULT FALSE,
status ENUM('active', 'inactive') DEFAULT 'active',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
);
-- Sample products
INSERT INTO products (category_id, name, description, price, stock, weight, image) VALUES
(1, 'Smartphone Android', 'Smartphone Android terbaru dengan fitur canggih', 2500000, 10, 0.2, 'smartphone.jpg'),
(1, 'Laptop Gaming', 'Laptop gaming dengan spesifikasi tinggi', 15000000, 5, 2.5, 'laptop.jpg'),
(2, 'Kaos Polos', 'Kaos polos berkualitas tinggi', 75000, 50, 0.2, 'kaos.jpg'),
(2, 'Celana Jeans', 'Celana jeans premium', 150000, 30, 0.5, 'jeans.jpg'),
(3, 'Kopi Arabica', 'Kopi arabica premium 250gr', 85000, 100, 0.25, 'kopi.jpg');
-- Sample product media
INSERT INTO product_media (product_id, media_type, media_url, media_title, sort_order, is_primary) VALUES
(1, 'image', 'uploads/products/smartphone.jpg', 'Smartphone Depan', 1, TRUE),
(1, 'image', 'uploads/products/smartphone-back.jpg', 'Smartphone Belakang', 2, FALSE),
(1, 'youtube', 'https://www.youtube.com/watch?v=dQw4w9WgXcQ', 'Review Smartphone', 3, FALSE),
(2, 'image', 'uploads/products/laptop.jpg', 'Laptop Gaming', 1, TRUE),
(2, 'youtube', 'https://www.youtube.com/watch?v=dQw4w9WgXcQ', 'Demo Gaming Laptop', 2, FALSE);