73 lines
2.9 KiB
SQL
73 lines
2.9 KiB
SQL
-- 001_initial_schema.sql
|
|
|
|
-- Users table for members and admins
|
|
CREATE TABLE IF NOT EXISTS `users` (
|
|
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
|
`username` VARCHAR(255) NOT NULL UNIQUE,
|
|
`email` VARCHAR(255) NOT NULL UNIQUE,
|
|
`password_hash` VARCHAR(255) NOT NULL,
|
|
`role` ENUM('member', 'admin', 'writer') NOT NULL DEFAULT 'member',
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- Writer applications table
|
|
CREATE TABLE IF NOT EXISTS `writer_applications` (
|
|
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
|
`user_id` INT NOT NULL,
|
|
`gov_id_path` VARCHAR(255) NOT NULL,
|
|
`work_proof_path` VARCHAR(255) NOT NULL,
|
|
`status` ENUM('pending', 'approved', 'rejected') NOT NULL DEFAULT 'pending',
|
|
`requested_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
`reviewed_at` TIMESTAMP NULL DEFAULT NULL,
|
|
FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- Books table
|
|
CREATE TABLE IF NOT EXISTS `books` (
|
|
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
|
`title` VARCHAR(255) NOT NULL,
|
|
`author_name` VARCHAR(255) NOT NULL,
|
|
`description` TEXT,
|
|
`cover_image_url` VARCHAR(255),
|
|
`google_books_id` VARCHAR(255) UNIQUE,
|
|
`open_library_id` VARCHAR(255) UNIQUE,
|
|
`added_by_user_id` INT,
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (`added_by_user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- User libraries table (connecting users and books)
|
|
CREATE TABLE IF NOT EXISTS `user_libraries` (
|
|
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
|
`user_id` INT NOT NULL,
|
|
`book_id` INT NOT NULL,
|
|
`review` TEXT,
|
|
`character_sketches` TEXT,
|
|
`added_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
|
|
FOREIGN KEY (`book_id`) REFERENCES `books`(`id`) ON DELETE CASCADE,
|
|
UNIQUE KEY `user_book_unique` (`user_id`, `book_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- Author followers table
|
|
CREATE TABLE IF NOT EXISTS `author_followers` (
|
|
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
|
`follower_id` INT NOT NULL,
|
|
`author_id` INT NOT NULL,
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (`follower_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
|
|
FOREIGN KEY (`author_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
|
|
UNIQUE KEY `follower_author_unique` (`follower_id`, `author_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- Library likes table
|
|
CREATE TABLE IF NOT EXISTS `library_likes` (
|
|
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
|
`liker_id` INT NOT NULL,
|
|
`library_owner_id` INT NOT NULL,
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (`liker_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
|
|
FOREIGN KEY (`library_owner_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
|
|
UNIQUE KEY `liker_owner_unique` (`liker_id`, `library_owner_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|