/home/ejrndhmu/me.klikyua.com/database.sql
-- Database untuk Panel Digital Member Area
-- Buat database baru
CREATE DATABASE IF NOT EXISTS panel_digital;
USE panel_digital;
-- Tabel untuk menyimpan data admin
CREATE TABLE admins (
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,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
-- Tabel untuk menyimpan data user dan reseller
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,
role ENUM('user', 'reseller') NOT NULL DEFAULT 'user',
status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
reseller_id INT NULL, -- ID reseller yang menambahkan user ini (jika ada)
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (reseller_id) REFERENCES users(id) ON DELETE SET NULL
);
-- Tabel untuk menyimpan produk digital
CREATE TABLE products (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(200) NOT NULL,
description TEXT,
price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
file_url VARCHAR(500), -- URL atau path file produk
thumbnail VARCHAR(500), -- URL atau path thumbnail
category VARCHAR(100),
status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
-- Tabel untuk mengatur akses user ke produk
CREATE TABLE user_products (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
product_id INT NOT NULL,
granted_by INT, -- ID admin/reseller yang memberikan akses
granted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
expires_at TIMESTAMP NULL, -- Tanggal kadaluarsa akses (opsional)
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
FOREIGN KEY (granted_by) REFERENCES users(id) ON DELETE SET NULL,
UNIQUE KEY unique_user_product (user_id, product_id)
);
-- Tabel untuk menyimpan pengaturan sistem
CREATE TABLE settings (
id INT AUTO_INCREMENT PRIMARY KEY,
setting_key VARCHAR(100) UNIQUE NOT NULL,
setting_value TEXT,
description VARCHAR(255),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
-- Insert data admin default
INSERT INTO admins (username, email, password, full_name) VALUES
('admin', 'admin@paneldigital.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Administrator');
-- Password default: password
-- Insert data reseller dan user contoh
INSERT INTO users (username, email, password, full_name, role) VALUES
('reseller1', 'reseller1@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Reseller Satu', 'reseller'),
('user1', 'user1@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'User Satu', 'user'),
('user2', 'user2@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'User Dua', 'user');
-- Password default untuk semua: password
-- Insert produk contoh
INSERT INTO products (name, description, price, category) VALUES
('E-book Marketing Digital', 'Panduan lengkap marketing digital untuk pemula', 99000.00, 'E-book'),
('Video Course Web Development', 'Kursus video lengkap web development dari dasar', 299000.00, 'Video Course'),
('Template Website Business', 'Template website siap pakai untuk bisnis', 149000.00, 'Template'),
('Plugin WordPress Premium', 'Plugin WordPress untuk optimasi SEO', 199000.00, 'Plugin');
-- Berikan akses produk ke user contoh
INSERT INTO user_products (user_id, product_id, granted_by) VALUES
(2, 1, 1), -- user1 akses E-book
(2, 2, 1), -- user1 akses Video Course
(3, 1, 1); -- user2 akses E-book
-- Insert pengaturan sistem
INSERT INTO settings (setting_key, setting_value, description) VALUES
('site_name', 'Panel Digital', 'Nama website'),
('site_description', 'Platform Member Area Produk Digital', 'Deskripsi website'),
('contact_email', 'support@paneldigital.com', 'Email kontak'),
('max_users_per_reseller', '100', 'Maksimal user yang bisa ditambahkan reseller');
-- Tabel untuk mengatur akses reseller ke produk
CREATE TABLE reseller_products (
id INT AUTO_INCREMENT PRIMARY KEY,
reseller_id INT NOT NULL,
product_id INT NOT NULL,
granted_by INT, -- ID admin yang memberikan akses
granted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
expires_at TIMESTAMP NULL, -- Tanggal kadaluarsa akses (opsional)
FOREIGN KEY (reseller_id) REFERENCES users(id) ON DELETE CASCADE,
FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
FOREIGN KEY (granted_by) REFERENCES admins(id) ON DELETE SET NULL,
UNIQUE KEY unique_reseller_product (reseller_id, product_id)
);
-- Tabel untuk log aktivitas download
CREATE TABLE download_logs (
id INT AUTO_INCREMENT PRIMARY KEY,
product_id INT NOT NULL,
user_id INT NOT NULL,
user_type ENUM('user', 'reseller', 'admin') NOT NULL,
downloaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
ip_address VARCHAR(45),
user_agent TEXT,
FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
);
-- Berikan akses produk ke reseller contoh
INSERT INTO reseller_products (reseller_id, product_id, granted_by) VALUES
(1, 1, 1), -- reseller1 akses E-book
(1, 3, 1); -- reseller1 akses Template
-- Index untuk performa
CREATE INDEX idx_users_role ON users(role);
CREATE INDEX idx_users_status ON users(status);
CREATE INDEX idx_products_status ON products(status);
CREATE INDEX idx_user_products_user ON user_products(user_id);
CREATE INDEX idx_user_products_product ON user_products(product_id);
CREATE INDEX idx_reseller_products_reseller ON reseller_products(reseller_id);
CREATE INDEX idx_reseller_products_product ON reseller_products(product_id);
CREATE INDEX idx_download_logs_product ON download_logs(product_id);
CREATE INDEX idx_download_logs_user ON download_logs(user_id);
CREATE INDEX idx_download_logs_date ON download_logs(downloaded_at);