59 lines
2.0 KiB
SQL
59 lines
2.0 KiB
SQL
|
|
CREATE TABLE IF NOT EXISTS `users` (
|
|
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
|
`username` VARCHAR(50) NOT NULL UNIQUE,
|
|
`password` VARCHAR(255) NOT NULL,
|
|
`role` ENUM('regular', 'power_user', 'admin') NOT NULL DEFAULT 'regular',
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS `categories` (
|
|
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
|
`name` VARCHAR(255) NOT NULL,
|
|
`visibility` BOOLEAN NOT NULL DEFAULT TRUE,
|
|
`display_order` INT NOT NULL DEFAULT 0
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS `subcategories` (
|
|
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
|
`category_id` INT NOT NULL,
|
|
`name` VARCHAR(255) NOT NULL,
|
|
FOREIGN KEY (`category_id`) REFERENCES `categories`(`id`) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS `links` (
|
|
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
|
`user_id` INT NOT NULL,
|
|
`subcategory_id` INT NOT NULL,
|
|
`title` VARCHAR(255) NOT NULL,
|
|
`url` VARCHAR(2083) NOT NULL,
|
|
`description` TEXT,
|
|
`thumbnail_url` VARCHAR(2083),
|
|
`status` ENUM('pending', 'approved', 'rejected') NOT NULL DEFAULT 'pending',
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (`user_id`) REFERENCES `users`(`id`),
|
|
FOREIGN KEY (`subcategory_id`) REFERENCES `subcategories`(`id`)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS `moderation_logs` (
|
|
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
|
`link_id` INT NOT NULL,
|
|
`moderator_id` INT NOT NULL,
|
|
`action` ENUM('approved', 'rejected') NOT NULL,
|
|
`notes` TEXT,
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (`link_id`) REFERENCES `links`(`id`),
|
|
FOREIGN KEY (`moderator_id`) REFERENCES `users`(`id`)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS `visits` (
|
|
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
|
`link_id` INT,
|
|
`user_id` INT,
|
|
`ip_address` VARCHAR(45),
|
|
`user_agent` TEXT,
|
|
`visited_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (`link_id`) REFERENCES `links`(`id`),
|
|
FOREIGN KEY (`user_id`) REFERENCES `users`(`id`)
|
|
);
|