-- CreateTable
CREATE TABLE `forum_categories` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(100) NOT NULL,
    `description` TEXT NOT NULL,
    `slug` VARCHAR(100) NOT NULL,
    `color` VARCHAR(7) NOT NULL,
    `position` INTEGER NOT NULL DEFAULT 0,
    `is_active` BOOLEAN NOT NULL DEFAULT true,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updated_at` DATETIME(3) NOT NULL,

    UNIQUE INDEX `forum_categories_slug_key`(`slug`),
    INDEX `forum_categories_slug_idx`(`slug`),
    INDEX `forum_categories_position_idx`(`position`),
    INDEX `forum_categories_is_active_idx`(`is_active`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `forum_threads` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `title` VARCHAR(255) NOT NULL,
    `slug` VARCHAR(255) NOT NULL,
    `content` TEXT NOT NULL,
    `is_sticky` BOOLEAN NOT NULL DEFAULT false,
    `is_locked` BOOLEAN NOT NULL DEFAULT false,
    `is_pinned` BOOLEAN NOT NULL DEFAULT false,
    `is_deleted` BOOLEAN NOT NULL DEFAULT false,
    `view_count` INTEGER NOT NULL DEFAULT 0,
    `reply_count` INTEGER NOT NULL DEFAULT 0,
    `category_id` INTEGER NOT NULL,
    `user_id` INTEGER NOT NULL,
    `last_reply_id` INTEGER NULL,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updated_at` DATETIME(3) NOT NULL,

    UNIQUE INDEX `forum_threads_slug_key`(`slug`),
    INDEX `forum_threads_category_id_idx`(`category_id`),
    INDEX `forum_threads_user_id_idx`(`user_id`),
    INDEX `forum_threads_slug_idx`(`slug`),
    INDEX `forum_threads_is_sticky_is_pinned_created_at_idx`(`is_sticky`, `is_pinned`, `created_at`),
    INDEX `forum_threads_is_deleted_idx`(`is_deleted`),
    INDEX `forum_threads_last_reply_id_idx`(`last_reply_id`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `forum_replies` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `content` TEXT NOT NULL,
    `is_deleted` BOOLEAN NOT NULL DEFAULT false,
    `thread_id` INTEGER NOT NULL,
    `user_id` INTEGER NOT NULL,
    `parent_id` INTEGER NULL,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updated_at` DATETIME(3) NOT NULL,

    INDEX `forum_replies_thread_id_idx`(`thread_id`),
    INDEX `forum_replies_user_id_idx`(`user_id`),
    INDEX `forum_replies_parent_id_idx`(`parent_id`),
    INDEX `forum_replies_is_deleted_idx`(`is_deleted`),
    INDEX `forum_replies_created_at_idx`(`created_at`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `forum_thread_votes` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `is_upvote` BOOLEAN NOT NULL,
    `thread_id` INTEGER NOT NULL,
    `user_id` INTEGER NOT NULL,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updated_at` DATETIME(3) NOT NULL,

    INDEX `forum_thread_votes_thread_id_idx`(`thread_id`),
    INDEX `forum_thread_votes_user_id_idx`(`user_id`),
    UNIQUE INDEX `forum_thread_votes_thread_id_user_id_key`(`thread_id`, `user_id`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `forum_reply_votes` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `is_upvote` BOOLEAN NOT NULL,
    `reply_id` INTEGER NOT NULL,
    `user_id` INTEGER NOT NULL,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updated_at` DATETIME(3) NOT NULL,

    INDEX `forum_reply_votes_reply_id_idx`(`reply_id`),
    INDEX `forum_reply_votes_user_id_idx`(`user_id`),
    UNIQUE INDEX `forum_reply_votes_reply_id_user_id_key`(`reply_id`, `user_id`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- AddForeignKey
ALTER TABLE `forum_threads` ADD CONSTRAINT `forum_threads_category_id_fkey` FOREIGN KEY (`category_id`) REFERENCES `forum_categories`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `forum_threads` ADD CONSTRAINT `forum_threads_user_id_fkey` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `forum_threads` ADD CONSTRAINT `forum_threads_last_reply_id_fkey` FOREIGN KEY (`last_reply_id`) REFERENCES `forum_replies`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `forum_replies` ADD CONSTRAINT `forum_replies_thread_id_fkey` FOREIGN KEY (`thread_id`) REFERENCES `forum_threads`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `forum_replies` ADD CONSTRAINT `forum_replies_user_id_fkey` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `forum_replies` ADD CONSTRAINT `forum_replies_parent_id_fkey` FOREIGN KEY (`parent_id`) REFERENCES `forum_replies`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `forum_thread_votes` ADD CONSTRAINT `forum_thread_votes_thread_id_fkey` FOREIGN KEY (`thread_id`) REFERENCES `forum_threads`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `forum_thread_votes` ADD CONSTRAINT `forum_thread_votes_user_id_fkey` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `forum_reply_votes` ADD CONSTRAINT `forum_reply_votes_reply_id_fkey` FOREIGN KEY (`reply_id`) REFERENCES `forum_replies`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `forum_reply_votes` ADD CONSTRAINT `forum_reply_votes_user_id_fkey` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;
