CREATE DATABASE IF NOT EXISTS jkmedia CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE jkmedia;
SET FOREIGN_KEY_CHECKS=0;
CREATE TABLE IF NOT EXISTS users(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,username VARCHAR(30) NOT NULL UNIQUE,email VARCHAR(190) NOT NULL UNIQUE,password_hash VARCHAR(255) NOT NULL,role ENUM('member','vip','agent','moderator','admin','super_admin') NOT NULL DEFAULT 'member',status ENUM('active','pending','locked') NOT NULL DEFAULT 'active',phone VARCHAR(30) NULL,zalo VARCHAR(80) NULL,total_deposited DECIMAL(18,2) NOT NULL DEFAULT 0,total_spent DECIMAL(18,2) NOT NULL DEFAULT 0,created_at DATETIME NOT NULL,updated_at DATETIME NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS wallets(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id BIGINT UNSIGNED NOT NULL UNIQUE,balance DECIMAL(18,2) NOT NULL DEFAULT 0,created_at DATETIME NOT NULL,updated_at DATETIME NULL,FOREIGN KEY(user_id) REFERENCES users(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS wallet_transactions(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id BIGINT UNSIGNED NOT NULL,type ENUM('credit','debit') NOT NULL,amount DECIMAL(18,2) NOT NULL,balance_after DECIMAL(18,2) NOT NULL,reference VARCHAR(100) NOT NULL UNIQUE,note VARCHAR(255) NULL,created_at DATETIME NOT NULL,INDEX(user_id,created_at),FOREIGN KEY(user_id) REFERENCES users(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS products(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(190) NOT NULL,slug VARCHAR(190) NOT NULL UNIQUE,short_description TEXT NULL,description MEDIUMTEXT NULL,price DECIMAL(18,2) NOT NULL,sale_price DECIMAL(18,2) NULL,category_id BIGINT UNSIGNED NULL,featured TINYINT(1) NOT NULL DEFAULT 0,status ENUM('draft','active','hidden') NOT NULL DEFAULT 'draft',created_at DATETIME NOT NULL,updated_at DATETIME NULL,INDEX(status,featured)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS orders(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,code VARCHAR(40) NOT NULL UNIQUE,user_id BIGINT UNSIGNED NOT NULL,order_type ENUM('resource','course','product','autopay') NOT NULL,total DECIMAL(18,2) NOT NULL,payment_method VARCHAR(30) NULL,status VARCHAR(30) NOT NULL,created_at DATETIME NOT NULL,paid_at DATETIME NULL,INDEX(user_id,created_at),FOREIGN KEY(user_id) REFERENCES users(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS autopay_orders(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,code VARCHAR(40) NOT NULL UNIQUE,user_id BIGINT UNSIGNED NOT NULL,platform VARCHAR(50) NOT NULL,service_name VARCHAR(190) NOT NULL,contact VARCHAR(190) NOT NULL,account_link VARCHAR(500) NOT NULL,description TEXT NOT NULL,status VARCHAR(30) NOT NULL DEFAULT 'pending',created_at DATETIME NOT NULL,FOREIGN KEY(user_id) REFERENCES users(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS deposits(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,code VARCHAR(40) NOT NULL UNIQUE,user_id BIGINT UNSIGNED NOT NULL,amount DECIMAL(18,2) NOT NULL,transfer_content VARCHAR(100) NOT NULL,status VARCHAR(30) NOT NULL DEFAULT 'pending',created_at DATETIME NOT NULL,paid_at DATETIME NULL,FOREIGN KEY(user_id) REFERENCES users(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS banks(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,provider VARCHAR(60) NOT NULL,bank_code VARCHAR(30) NULL,account_number VARCHAR(60) NOT NULL,account_name VARCHAR(190) NOT NULL,api_url VARCHAR(500) NULL,api_token_encrypted TEXT NULL,webhook_secret_encrypted TEXT NULL,is_active TINYINT(1) NOT NULL DEFAULT 0,created_at DATETIME NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS bank_transactions(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,provider VARCHAR(60) NOT NULL,transaction_id VARCHAR(190) NOT NULL,amount DECIMAL(18,2) NOT NULL,content VARCHAR(500) NULL,receiver_account VARCHAR(60) NULL,status VARCHAR(30) NOT NULL DEFAULT 'unmatched',payload_json JSON NULL,created_at DATETIME NOT NULL,UNIQUE KEY uq_provider_tx(provider,transaction_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS page_views(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,path VARCHAR(500) NOT NULL,referrer VARCHAR(500) NULL,visitor_hash CHAR(64) NOT NULL,viewed_at DATETIME NOT NULL,INDEX(viewed_at),INDEX(path(191))) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS notifications(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id BIGINT UNSIGNED NULL,title VARCHAR(190) NOT NULL,message TEXT NOT NULL,type VARCHAR(30) NOT NULL DEFAULT 'info',is_popup TINYINT(1) NOT NULL DEFAULT 0,starts_at DATETIME NULL,ends_at DATETIME NULL,created_at DATETIME NOT NULL,INDEX(user_id,created_at)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS settings(setting_key VARCHAR(100) PRIMARY KEY,setting_value MEDIUMTEXT NULL,updated_at DATETIME NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS admin_logs(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,admin_id BIGINT UNSIGNED NULL,action VARCHAR(100) NOT NULL,entity VARCHAR(100) NULL,entity_id BIGINT NULL,ip_address VARCHAR(45) NULL,details_json JSON NULL,created_at DATETIME NOT NULL,INDEX(created_at)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS courses(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,title VARCHAR(190) NOT NULL,slug VARCHAR(190) NOT NULL UNIQUE,description MEDIUMTEXT NULL,price DECIMAL(18,2) NOT NULL,sale_price DECIMAL(18,2) NULL,status VARCHAR(30) NOT NULL DEFAULT 'draft',created_at DATETIME NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS course_sections(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,course_id BIGINT UNSIGNED NOT NULL,title VARCHAR(190) NOT NULL,sort_order INT NOT NULL DEFAULT 0,FOREIGN KEY(course_id) REFERENCES courses(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS course_lessons(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,section_id BIGINT UNSIGNED NOT NULL,title VARCHAR(190) NOT NULL,body MEDIUMTEXT NULL,video_provider VARCHAR(50) NULL,video_reference VARCHAR(500) NULL,is_preview TINYINT(1) NOT NULL DEFAULT 0,sort_order INT NOT NULL DEFAULT 0,FOREIGN KEY(section_id) REFERENCES course_sections(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS course_enrollments(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id BIGINT UNSIGNED NOT NULL,course_id BIGINT UNSIGNED NOT NULL,order_id BIGINT UNSIGNED NULL,created_at DATETIME NOT NULL,UNIQUE KEY uq_enroll(user_id,course_id),FOREIGN KEY(user_id) REFERENCES users(id),FOREIGN KEY(course_id) REFERENCES courses(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS policies(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,slug VARCHAR(100) NOT NULL UNIQUE,title VARCHAR(190) NOT NULL,body MEDIUMTEXT NOT NULL,status VARCHAR(20) NOT NULL DEFAULT 'published',updated_at DATETIME NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
SET FOREIGN_KEY_CHECKS=1;

INSERT IGNORE INTO policies(slug,title,body,status,updated_at) VALUES
('terms','Điều khoản sử dụng','Nội dung mẫu – cần được rà soát pháp lý trước khi công bố.','draft',NOW()),
('privacy','Chính sách bảo mật','Nội dung mẫu – cần bổ sung thông tin xử lý dữ liệu thực tế.','draft',NOW()),
('refund','Chính sách thanh toán và hoàn tiền','Nội dung mẫu – cần quy định rõ điều kiện hoàn tiền theo từng loại dịch vụ.','draft',NOW());