/home/ejrndhmu/.trash/add_tables.sql.7
-- Tabel untuk mengatur akses reseller ke produk
CREATE TABLE IF NOT EXISTS 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 IF NOT EXISTS 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 IGNORE 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 IF NOT EXISTS idx_reseller_products_reseller ON reseller_products(reseller_id);
CREATE INDEX IF NOT EXISTS idx_reseller_products_product ON reseller_products(product_id);
CREATE INDEX IF NOT EXISTS idx_download_logs_product ON download_logs(product_id);
CREATE INDEX IF NOT EXISTS idx_download_logs_user ON download_logs(user_id);
CREATE INDEX IF NOT EXISTS idx_download_logs_date ON download_logs(downloaded_at);