/*
  Warnings:

  - The `status` column on the `knowledge_articles` table would be dropped and recreated. This will lead to data loss if there is data in the column.

*/
-- CreateEnum
CREATE TYPE "platform_knowledge"."KnowledgeArticleStatus" AS ENUM ('DRAFT', 'PUBLISHED', 'ARCHIVED');

-- CreateEnum
CREATE TYPE "platform_knowledge"."DocAuthorityLevel" AS ENUM ('OFFICIAL', 'EXPERT', 'PUBLISHED', 'DRAFT');

-- CreateEnum
CREATE TYPE "platform_knowledge"."DocLifecycleStatus" AS ENUM ('DRAFT', 'UNDER_REVIEW', 'PUBLISHED', 'ARCHIVED', 'DEPRECATED');

-- CreateEnum
CREATE TYPE "platform_knowledge"."DocType" AS ENUM ('POLICY', 'MANUAL', 'TUTORIAL', 'FAQ', 'GENERAL');

-- CreateEnum
CREATE TYPE "platform_knowledge"."SyncStatus" AS ENUM ('PENDING', 'SYNCING', 'COMPLETED', 'FAILED');

-- CreateEnum
CREATE TYPE "platform_knowledge"."SearchType" AS ENUM ('KEYWORD', 'SEMANTIC', 'HYBRID');

-- CreateEnum
CREATE TYPE "platform_knowledge"."LikeType" AS ENUM ('LIKE', 'DISLIKE');

-- CreateEnum
CREATE TYPE "platform_knowledge"."QAFeedback" AS ENUM ('THUMBS_UP', 'THUMBS_DOWN');

-- AlterTable
ALTER TABLE "platform_knowledge"."knowledge_articles" ALTER COLUMN "id" DROP DEFAULT,
DROP COLUMN "status",
ADD COLUMN     "status" "platform_knowledge"."KnowledgeArticleStatus" NOT NULL DEFAULT 'DRAFT',
ALTER COLUMN "updated_at" DROP DEFAULT;

-- DropEnum
DROP TYPE "platform_knowledge"."knowledge_article_status";

-- CreateTable
CREATE TABLE "platform_knowledge"."sp_document_index" (
    "id" UUID NOT NULL,
    "sp_item_id" VARCHAR(255) NOT NULL,
    "sp_drive_id" VARCHAR(255) NOT NULL,
    "sp_site_id" VARCHAR(255) NOT NULL,
    "title" VARCHAR(500) NOT NULL,
    "web_url" TEXT NOT NULL,
    "file_type" VARCHAR(100),
    "file_extension" VARCHAR(20),
    "size" BIGINT,
    "doc_authority_level" "platform_knowledge"."DocAuthorityLevel" NOT NULL DEFAULT 'DRAFT',
    "doc_lifecycle_status" "platform_knowledge"."DocLifecycleStatus" NOT NULL DEFAULT 'DRAFT',
    "doc_type" "platform_knowledge"."DocType" NOT NULL DEFAULT 'GENERAL',
    "created_by" VARCHAR(255),
    "last_modified_by" VARCHAR(255),
    "sp_created_at" TIMESTAMPTZ(3),
    "sp_modified_at" TIMESTAMPTZ(3),
    "text_content" TEXT,
    "text_hash" VARCHAR(64),
    "sync_status" "platform_knowledge"."SyncStatus" NOT NULL DEFAULT 'PENDING',
    "sync_error" TEXT,
    "indexed_at" TIMESTAMPTZ(3),
    "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMPTZ(3) NOT NULL,

    CONSTRAINT "sp_document_index_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "platform_knowledge"."document_chunks" (
    "id" UUID NOT NULL,
    "document_id" UUID NOT NULL,
    "chunk_index" INTEGER NOT NULL,
    "chunk_text" TEXT NOT NULL,
    "chunk_hash" VARCHAR(64) NOT NULL,
    "embedding_id" VARCHAR(255),
    "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT "document_chunks_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "platform_knowledge"."ai_qa_logs" (
    "id" UUID NOT NULL,
    "user_id" UUID NOT NULL,
    "question" TEXT NOT NULL,
    "answer" TEXT NOT NULL,
    "source_documents" JSONB,
    "confidence" DOUBLE PRECISION,
    "model_used" VARCHAR(100),
    "tokens_used" INTEGER,
    "cost" DOUBLE PRECISION,
    "response_time_ms" INTEGER,
    "feedback" "platform_knowledge"."QAFeedback",
    "feedback_comment" TEXT,
    "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT "ai_qa_logs_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "platform_knowledge"."search_logs" (
    "id" UUID NOT NULL,
    "user_id_hash" VARCHAR(64) NOT NULL,
    "query" TEXT NOT NULL,
    "result_count" INTEGER NOT NULL,
    "response_time_ms" INTEGER NOT NULL,
    "search_type" "platform_knowledge"."SearchType" NOT NULL,
    "filters" JSONB,
    "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT "search_logs_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "platform_knowledge"."document_views" (
    "id" UUID NOT NULL,
    "sp_document_id" UUID,
    "article_id" UUID,
    "user_id" UUID NOT NULL,
    "viewed_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "view_date" DATE NOT NULL,

    CONSTRAINT "document_views_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "platform_knowledge"."document_likes" (
    "id" UUID NOT NULL,
    "sp_document_id" UUID,
    "article_id" UUID,
    "user_id" UUID NOT NULL,
    "type" "platform_knowledge"."LikeType" NOT NULL,
    "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT "document_likes_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "platform_knowledge"."document_comments" (
    "id" UUID NOT NULL,
    "sp_document_id" UUID,
    "article_id" UUID,
    "user_id" UUID NOT NULL,
    "content" TEXT NOT NULL,
    "parent_id" UUID,
    "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMPTZ(3) NOT NULL,
    "deleted_at" TIMESTAMPTZ(3),

    CONSTRAINT "document_comments_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "platform_knowledge"."user_mentions" (
    "id" UUID NOT NULL,
    "comment_id" UUID NOT NULL,
    "mentioned_user_id" UUID NOT NULL,
    "read_at" TIMESTAMPTZ(3),
    "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT "user_mentions_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "platform_knowledge"."sync_tasks" (
    "id" UUID NOT NULL,
    "task_type" VARCHAR(50) NOT NULL,
    "status" "platform_knowledge"."SyncStatus" NOT NULL DEFAULT 'PENDING',
    "total_items" INTEGER,
    "processed_items" INTEGER NOT NULL DEFAULT 0,
    "failed_items" INTEGER NOT NULL DEFAULT 0,
    "error" TEXT,
    "started_at" TIMESTAMPTZ(3),
    "completed_at" TIMESTAMPTZ(3),
    "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT "sync_tasks_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "platform_knowledge"."webhook_subscriptions" (
    "id" UUID NOT NULL,
    "subscription_id" VARCHAR(255) NOT NULL,
    "drive_id" VARCHAR(255) NOT NULL,
    "resource" VARCHAR(500) NOT NULL,
    "expiration_date_time" TIMESTAMPTZ(3) NOT NULL,
    "is_active" BOOLEAN NOT NULL DEFAULT true,
    "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMPTZ(3) NOT NULL,

    CONSTRAINT "webhook_subscriptions_pkey" PRIMARY KEY ("id")
);

-- CreateIndex
CREATE INDEX "sp_document_index_sp_site_id_idx" ON "platform_knowledge"."sp_document_index"("sp_site_id");

-- CreateIndex
CREATE INDEX "sp_document_index_doc_authority_level_idx" ON "platform_knowledge"."sp_document_index"("doc_authority_level");

-- CreateIndex
CREATE INDEX "sp_document_index_doc_lifecycle_status_idx" ON "platform_knowledge"."sp_document_index"("doc_lifecycle_status");

-- CreateIndex
CREATE INDEX "sp_document_index_doc_type_idx" ON "platform_knowledge"."sp_document_index"("doc_type");

-- CreateIndex
CREATE INDEX "sp_document_index_sync_status_idx" ON "platform_knowledge"."sp_document_index"("sync_status");

-- CreateIndex
CREATE INDEX "sp_document_index_sp_modified_at_idx" ON "platform_knowledge"."sp_document_index"("sp_modified_at");

-- CreateIndex
CREATE UNIQUE INDEX "sp_document_index_sp_item_id_sp_drive_id_key" ON "platform_knowledge"."sp_document_index"("sp_item_id", "sp_drive_id");

-- CreateIndex
CREATE INDEX "document_chunks_embedding_id_idx" ON "platform_knowledge"."document_chunks"("embedding_id");

-- CreateIndex
CREATE UNIQUE INDEX "document_chunks_document_id_chunk_index_key" ON "platform_knowledge"."document_chunks"("document_id", "chunk_index");

-- CreateIndex
CREATE INDEX "ai_qa_logs_user_id_idx" ON "platform_knowledge"."ai_qa_logs"("user_id");

-- CreateIndex
CREATE INDEX "ai_qa_logs_created_at_idx" ON "platform_knowledge"."ai_qa_logs"("created_at");

-- CreateIndex
CREATE INDEX "ai_qa_logs_feedback_idx" ON "platform_knowledge"."ai_qa_logs"("feedback");

-- CreateIndex
CREATE INDEX "search_logs_created_at_idx" ON "platform_knowledge"."search_logs"("created_at");

-- CreateIndex
CREATE INDEX "search_logs_search_type_idx" ON "platform_knowledge"."search_logs"("search_type");

-- CreateIndex
CREATE INDEX "document_views_user_id_idx" ON "platform_knowledge"."document_views"("user_id");

-- CreateIndex
CREATE INDEX "document_views_viewed_at_idx" ON "platform_knowledge"."document_views"("viewed_at");

-- CreateIndex
CREATE UNIQUE INDEX "document_views_sp_document_id_user_id_view_date_key" ON "platform_knowledge"."document_views"("sp_document_id", "user_id", "view_date");

-- CreateIndex
CREATE UNIQUE INDEX "document_views_article_id_user_id_view_date_key" ON "platform_knowledge"."document_views"("article_id", "user_id", "view_date");

-- CreateIndex
CREATE INDEX "document_likes_user_id_idx" ON "platform_knowledge"."document_likes"("user_id");

-- CreateIndex
CREATE INDEX "document_likes_type_idx" ON "platform_knowledge"."document_likes"("type");

-- CreateIndex
CREATE UNIQUE INDEX "document_likes_sp_document_id_user_id_key" ON "platform_knowledge"."document_likes"("sp_document_id", "user_id");

-- CreateIndex
CREATE UNIQUE INDEX "document_likes_article_id_user_id_key" ON "platform_knowledge"."document_likes"("article_id", "user_id");

-- CreateIndex
CREATE INDEX "document_comments_sp_document_id_idx" ON "platform_knowledge"."document_comments"("sp_document_id");

-- CreateIndex
CREATE INDEX "document_comments_article_id_idx" ON "platform_knowledge"."document_comments"("article_id");

-- CreateIndex
CREATE INDEX "document_comments_user_id_idx" ON "platform_knowledge"."document_comments"("user_id");

-- CreateIndex
CREATE INDEX "document_comments_parent_id_idx" ON "platform_knowledge"."document_comments"("parent_id");

-- CreateIndex
CREATE INDEX "document_comments_created_at_idx" ON "platform_knowledge"."document_comments"("created_at");

-- CreateIndex
CREATE INDEX "user_mentions_mentioned_user_id_idx" ON "platform_knowledge"."user_mentions"("mentioned_user_id");

-- CreateIndex
CREATE INDEX "user_mentions_read_at_idx" ON "platform_knowledge"."user_mentions"("read_at");

-- CreateIndex
CREATE UNIQUE INDEX "user_mentions_comment_id_mentioned_user_id_key" ON "platform_knowledge"."user_mentions"("comment_id", "mentioned_user_id");

-- CreateIndex
CREATE INDEX "sync_tasks_status_idx" ON "platform_knowledge"."sync_tasks"("status");

-- CreateIndex
CREATE INDEX "sync_tasks_created_at_idx" ON "platform_knowledge"."sync_tasks"("created_at");

-- CreateIndex
CREATE UNIQUE INDEX "webhook_subscriptions_subscription_id_key" ON "platform_knowledge"."webhook_subscriptions"("subscription_id");

-- CreateIndex
CREATE INDEX "webhook_subscriptions_drive_id_idx" ON "platform_knowledge"."webhook_subscriptions"("drive_id");

-- CreateIndex
CREATE INDEX "webhook_subscriptions_is_active_idx" ON "platform_knowledge"."webhook_subscriptions"("is_active");

-- CreateIndex
CREATE INDEX "webhook_subscriptions_expiration_date_time_idx" ON "platform_knowledge"."webhook_subscriptions"("expiration_date_time");

-- CreateIndex
CREATE INDEX "knowledge_articles_status_idx" ON "platform_knowledge"."knowledge_articles"("status");

-- AddForeignKey
ALTER TABLE "platform_knowledge"."document_chunks" ADD CONSTRAINT "document_chunks_document_id_fkey" FOREIGN KEY ("document_id") REFERENCES "platform_knowledge"."sp_document_index"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "platform_knowledge"."ai_qa_logs" ADD CONSTRAINT "ai_qa_logs_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "platform_iam"."users"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "platform_knowledge"."document_views" ADD CONSTRAINT "document_views_sp_document_id_fkey" FOREIGN KEY ("sp_document_id") REFERENCES "platform_knowledge"."sp_document_index"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "platform_knowledge"."document_views" ADD CONSTRAINT "document_views_article_id_fkey" FOREIGN KEY ("article_id") REFERENCES "platform_knowledge"."knowledge_articles"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "platform_knowledge"."document_views" ADD CONSTRAINT "document_views_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "platform_iam"."users"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "platform_knowledge"."document_likes" ADD CONSTRAINT "document_likes_sp_document_id_fkey" FOREIGN KEY ("sp_document_id") REFERENCES "platform_knowledge"."sp_document_index"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "platform_knowledge"."document_likes" ADD CONSTRAINT "document_likes_article_id_fkey" FOREIGN KEY ("article_id") REFERENCES "platform_knowledge"."knowledge_articles"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "platform_knowledge"."document_likes" ADD CONSTRAINT "document_likes_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "platform_iam"."users"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "platform_knowledge"."document_comments" ADD CONSTRAINT "document_comments_sp_document_id_fkey" FOREIGN KEY ("sp_document_id") REFERENCES "platform_knowledge"."sp_document_index"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "platform_knowledge"."document_comments" ADD CONSTRAINT "document_comments_article_id_fkey" FOREIGN KEY ("article_id") REFERENCES "platform_knowledge"."knowledge_articles"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "platform_knowledge"."document_comments" ADD CONSTRAINT "document_comments_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "platform_iam"."users"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "platform_knowledge"."document_comments" ADD CONSTRAINT "document_comments_parent_id_fkey" FOREIGN KEY ("parent_id") REFERENCES "platform_knowledge"."document_comments"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "platform_knowledge"."user_mentions" ADD CONSTRAINT "user_mentions_comment_id_fkey" FOREIGN KEY ("comment_id") REFERENCES "platform_knowledge"."document_comments"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "platform_knowledge"."user_mentions" ADD CONSTRAINT "user_mentions_mentioned_user_id_fkey" FOREIGN KEY ("mentioned_user_id") REFERENCES "platform_iam"."users"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
