CREATE DATABASE IF NOT EXISTS mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE mydatabase;

CREATE TABLE users (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, username VARCHAR(60) NOT NULL UNIQUE, email VARCHAR(190) NULL UNIQUE,
 password VARCHAR(255) NOT NULL, avatar VARCHAR(255) NULL, role ENUM('member','collaborator','admin') NOT NULL DEFAULT 'member',
 balance DECIMAL(14,0) NOT NULL DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;
CREATE TABLE packages (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(120) NOT NULL, image VARCHAR(255) NULL, price DECIMAL(14,0) NOT NULL,
 discount_percent TINYINT UNSIGNED NOT NULL DEFAULT 0, duration_months SMALLINT UNSIGNED NOT NULL DEFAULT 1, note TEXT NULL,
 active TINYINT(1) NOT NULL DEFAULT 1, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;
CREATE TABLE announcements (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, title VARCHAR(190) NOT NULL, body TEXT NOT NULL, is_hot TINYINT(1) DEFAULT 0,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;
CREATE TABLE orders (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, order_code VARCHAR(40) NOT NULL UNIQUE, user_id BIGINT UNSIGNED NOT NULL, package_id BIGINT UNSIGNED NOT NULL,
 locket_username VARCHAR(100) NOT NULL, amount DECIMAL(14,0) NOT NULL, payment_method ENUM('balance','bank') NOT NULL,
 status ENUM('waiting_payment','queued','processing','completed','failed','cancelled') NOT NULL DEFAULT 'waiting_payment', progress TINYINT UNSIGNED DEFAULT 0,
 provider_ref VARCHAR(190) NULL, provider_message TEXT NULL, expires_at DATETIME NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 FOREIGN KEY (user_id) REFERENCES users(id), FOREIGN KEY (package_id) REFERENCES packages(id), INDEX(status, created_at)
) ENGINE=InnoDB;
CREATE TABLE transactions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL, bank_ref VARCHAR(120) NULL UNIQUE, type ENUM('deposit','purchase','refund','adjustment') NOT NULL,
 amount DECIMAL(14,0) NOT NULL, content VARCHAR(255) NULL, status ENUM('pending','completed','rejected') DEFAULT 'completed', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY (user_id) REFERENCES users(id), INDEX(user_id, created_at)
) ENGINE=InnoDB;
CREATE TABLE admin_logs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, admin_id BIGINT UNSIGNED NOT NULL, action VARCHAR(190) NOT NULL, ip VARCHAR(45) NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY (admin_id) REFERENCES users(id)
) ENGINE=InnoDB;

INSERT INTO packages(name,price,discount_percent,duration_months,note) VALUES
('Locket Gold 1 tháng',89000,22,1,'Kích hoạt nhanh, hỗ trợ trong thời hạn gói'),
('Locket Gold 3 tháng',229000,30,3,'Lựa chọn phổ biến cho người dùng thường xuyên'),
('Locket Gold 12 tháng',699000,35,12,'Tiết kiệm nhất, ưu tiên xử lý');
INSERT INTO announcements(title,body,is_hot) VALUES
('Hệ thống phiên bản 4.0','Giao diện mới, lịch sử đơn hàng rõ ràng và xử lý hàng chờ minh bạch.',1),
('Lưu ý bảo mật','Không cung cấp mật khẩu Locket cho bất kỳ ai. Website chỉ cần username để tạo yêu cầu.',0);