-- CreateEnum
CREATE TYPE "pool_type" AS ENUM ('Employees', 'Founders', 'Investors', 'Advisors', 'Community', 'Treasury', 'Other');

-- CreateEnum
CREATE TYPE "pool_category" AS ENUM ('INSIDER', 'OUTSIDER', 'UNKNOWN');

-- CreateEnum
CREATE TYPE "checklist_type" AS ENUM ('MVP');

-- CreateEnum
CREATE TYPE "checklist_item_status" AS ENUM ('NOT_STARTED', 'IN_PROGRESS', 'COMPLETED', 'BLOCKED');

-- CreateEnum
CREATE TYPE "task_phase" AS ENUM ('PRE_MINT', 'MINT', 'POST_MINT', 'POST_TGE');

-- CreateEnum
CREATE TYPE "task_domain" AS ENUM ('LEGAL', 'TECHNICAL', 'MARKETING', 'OPERATIONS', 'COMPLIANCE', 'GENERAL', 'TOKENOMICS', 'CUSTODY', 'TAX');

-- CreateEnum
CREATE TYPE "task_priority" AS ENUM ('LOW', 'MEDIUM', 'HIGH', 'CRITICAL');

-- CreateEnum
CREATE TYPE "industry_type" AS ENUM ('Network', 'DeFi', 'Stablecoin', 'NFT', 'Gaming', 'SocialFi', 'DePIN', 'RWA', 'DeAI', 'Infrastructure', 'DecentralizedStorage', 'Oracle', 'DecentralizedIdentity', 'Wallet', 'Other');

-- CreateEnum
CREATE TYPE "vesting_frequency" AS ENUM ('DAILY', 'WEEKLY', 'MONTHLY', 'QUARTERLY', 'ANNUALLY', 'ONE_TIME');

-- CreateEnum
CREATE TYPE "vesting_type" AS ENUM ('LINEAR', 'NONLINEAR');

-- CreateTable
CREATE TABLE "allocation_pools" (
    "id" TEXT NOT NULL,
    "project_id" TEXT NOT NULL,
    "name" TEXT NOT NULL,
    "description" TEXT,
    "pool_type" "pool_type" NOT NULL,
    "pool_category" "pool_category" NOT NULL,
    "allocation_percent" DOUBLE PRECISION NOT NULL,
    "created_at" TIMESTAMPTZ(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMPTZ(6),

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

-- CreateTable
CREATE TABLE "allocations" (
    "allocation_id" TEXT NOT NULL,
    "project_id" TEXT NOT NULL,
    "allocation_pool_id" TEXT NOT NULL,
    "recipient" TEXT NOT NULL,
    "description" TEXT,
    "total_token_percent" DOUBLE PRECISION NOT NULL,
    "equity_percent" DOUBLE PRECISION,
    "equity_to_token_ratio" DOUBLE PRECISION,
    "system_generated" BOOLEAN NOT NULL,
    "recommendations" JSONB,
    "created_at" TIMESTAMPTZ(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMPTZ(6),

    CONSTRAINT "allocations_pkey" PRIMARY KEY ("allocation_id")
);

-- CreateTable
CREATE TABLE "checklist" (
    "checklist_id" TEXT NOT NULL,
    "project_id" TEXT NOT NULL,
    "type" "checklist_type" NOT NULL,
    "name" TEXT NOT NULL,
    "description" TEXT,
    "is_template" BOOLEAN NOT NULL DEFAULT false,
    "created_at" TIMESTAMPTZ(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMPTZ(6) NOT NULL,

    CONSTRAINT "checklist_pkey" PRIMARY KEY ("checklist_id")
);

-- CreateTable
CREATE TABLE "checklist_item" (
    "item_id" TEXT NOT NULL,
    "checklist_id" TEXT NOT NULL,
    "title" TEXT NOT NULL,
    "description" TEXT,
    "order" INTEGER NOT NULL DEFAULT 0,
    "href" TEXT,
    "priority" "task_priority" NOT NULL,
    "status" "checklist_item_status" NOT NULL DEFAULT 'NOT_STARTED',
    "phase" "task_phase" NOT NULL,
    "domain" "task_domain" NOT NULL DEFAULT 'GENERAL',
    "blocked_reason" TEXT,
    "due_date" TIMESTAMPTZ(6),
    "created_at" TIMESTAMPTZ(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMPTZ(6) NOT NULL,

    CONSTRAINT "checklist_item_pkey" PRIMARY KEY ("item_id")
);

-- CreateTable
CREATE TABLE "comment" (
    "comment_id" TEXT NOT NULL,
    "project_id" TEXT NOT NULL,
    "created_by" TEXT NOT NULL,
    "content" TEXT NOT NULL,
    "created_at" TIMESTAMPTZ(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT "comment_pkey" PRIMARY KEY ("comment_id")
);

-- CreateTable
CREATE TABLE "comment_views" (
    "view_id" TEXT NOT NULL,
    "comment_id" TEXT NOT NULL,
    "viewed_by" TEXT NOT NULL,
    "viewed_at" TIMESTAMPTZ(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT "comment_views_pkey" PRIMARY KEY ("view_id")
);

-- CreateTable
CREATE TABLE "project_snapshots" (
    "id" TEXT NOT NULL,
    "project_id" TEXT NOT NULL,
    "name" TEXT NOT NULL,
    "description" TEXT,
    "snapshot_data" JSONB NOT NULL,
    "snapshot_data_meta" JSONB NOT NULL,
    "created_at" TIMESTAMPTZ(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "created_by" TEXT NOT NULL,
    "updated_at" TIMESTAMPTZ(6),

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

-- CreateTable
CREATE TABLE "project" (
    "project_id" TEXT NOT NULL,
    "organization_id" TEXT,
    "name" TEXT NOT NULL,
    "symbol" TEXT,
    "website" TEXT NOT NULL,
    "description" TEXT,
    "industry" "industry_type"[],
    "created_at" TIMESTAMPTZ(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMPTZ(6),
    "tge_date" TIMESTAMPTZ(6) NOT NULL,
    "team_size" INTEGER,
    "investor_size" INTEGER,
    "total_token_supply" BIGINT,
    "insiderAllocationPoolPercent" DOUBLE PRECISION DEFAULT 0,
    "outsiderAllocationPoolPercent" DOUBLE PRECISION DEFAULT 0,

    CONSTRAINT "project_pkey" PRIMARY KEY ("project_id")
);

-- CreateTable
CREATE TABLE "unlock_schedules" (
    "id" TEXT NOT NULL,
    "allocationId" TEXT NOT NULL,
    "projectId" TEXT NOT NULL,
    "lockup_cliff_duration_months" INTEGER,
    "lockup_total_duration_months" INTEGER,
    "unlock_start_date" TIMESTAMPTZ(6),
    "initial_unlock_percent" DOUBLE PRECISION,
    "unlock_frequency" "vesting_frequency",
    "unlock_percent_per_period" DOUBLE PRECISION,
    "created_at" TIMESTAMPTZ(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMPTZ(6),

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

-- CreateTable
CREATE TABLE "user" (
    "clerk_user_id" TEXT NOT NULL,
    "orgId" TEXT NOT NULL,
    "email" TEXT NOT NULL,
    "firstName" TEXT,
    "lastName" TEXT,
    "imageUrl" TEXT,
    "companyRole" TEXT,
    "created_at" TIMESTAMPTZ(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMPTZ(6) NOT NULL,
    "deleted_at" TIMESTAMPTZ(6),

    CONSTRAINT "user_pkey" PRIMARY KEY ("clerk_user_id")
);

-- CreateTable
CREATE TABLE "vesting_milestones" (
    "id" TEXT NOT NULL,
    "name" TEXT,
    "description" TEXT,
    "vesting_date" TIMESTAMPTZ(6) NOT NULL,
    "vesting_percent" DOUBLE PRECISION,
    "vesting_token_amount" DOUBLE PRECISION,
    "is_tge" BOOLEAN NOT NULL DEFAULT false,
    "is_cliff" BOOLEAN NOT NULL DEFAULT false,
    "sort_order" INTEGER NOT NULL DEFAULT 0,
    "created_at" TIMESTAMPTZ(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMPTZ(6),
    "vesting_schedule_id" TEXT NOT NULL,

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

-- CreateTable
CREATE TABLE "vesting_schedules" (
    "id" TEXT NOT NULL,
    "name" TEXT,
    "description" TEXT,
    "vesting_type" "vesting_type" NOT NULL DEFAULT 'LINEAR',
    "cliff_duration" TEXT,
    "vesting_start_date" TIMESTAMPTZ(6),
    "vesting_end_date" TIMESTAMPTZ(6),
    "vesting_frequency" "vesting_frequency",
    "tge_date" TIMESTAMPTZ(6),
    "tge_unlock_percent" DOUBLE PRECISION,
    "created_at" TIMESTAMPTZ(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMPTZ(6),
    "allocationId" TEXT,
    "projectId" TEXT,

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

-- CreateTable
CREATE TABLE "_AllocationToComment" (
    "A" TEXT NOT NULL,
    "B" TEXT NOT NULL,

    CONSTRAINT "_AllocationToComment_AB_pkey" PRIMARY KEY ("A","B")
);

-- CreateTable
CREATE TABLE "_ChecklistItemToUser" (
    "A" TEXT NOT NULL,
    "B" TEXT NOT NULL,

    CONSTRAINT "_ChecklistItemToUser_AB_pkey" PRIMARY KEY ("A","B")
);

-- CreateTable
CREATE TABLE "_ChecklistItemToComment" (
    "A" TEXT NOT NULL,
    "B" TEXT NOT NULL,

    CONSTRAINT "_ChecklistItemToComment_AB_pkey" PRIMARY KEY ("A","B")
);

-- CreateIndex
CREATE INDEX "allocation_pools_project_id_idx" ON "allocation_pools"("project_id");

-- CreateIndex
CREATE INDEX "allocation_pools_pool_type_idx" ON "allocation_pools"("pool_type");

-- CreateIndex
CREATE INDEX "allocations_project_id_idx" ON "allocations"("project_id");

-- CreateIndex
CREATE INDEX "allocations_allocation_pool_id_idx" ON "allocations"("allocation_pool_id");

-- CreateIndex
CREATE UNIQUE INDEX "checklist_project_id_type_key" ON "checklist"("project_id", "type");

-- CreateIndex
CREATE INDEX "comment_views_comment_id_idx" ON "comment_views"("comment_id");

-- CreateIndex
CREATE INDEX "comment_views_viewed_by_idx" ON "comment_views"("viewed_by");

-- CreateIndex
CREATE UNIQUE INDEX "comment_views_comment_id_viewed_by_key" ON "comment_views"("comment_id", "viewed_by");

-- CreateIndex
CREATE INDEX "project_snapshots_project_id_idx" ON "project_snapshots"("project_id");

-- CreateIndex
CREATE INDEX "project_snapshots_project_id_created_at_idx" ON "project_snapshots"("project_id", "created_at");

-- CreateIndex
CREATE UNIQUE INDEX "project_symbol_key" ON "project"("symbol");

-- CreateIndex
CREATE INDEX "project_industry_idx" ON "project"("industry");

-- CreateIndex
CREATE UNIQUE INDEX "unlock_schedules_allocationId_key" ON "unlock_schedules"("allocationId");

-- CreateIndex
CREATE INDEX "unlock_schedules_projectId_idx" ON "unlock_schedules"("projectId");

-- CreateIndex
CREATE INDEX "unlock_schedules_allocationId_idx" ON "unlock_schedules"("allocationId");

-- CreateIndex
CREATE UNIQUE INDEX "user_clerk_user_id_key" ON "user"("clerk_user_id");

-- CreateIndex
CREATE UNIQUE INDEX "user_email_key" ON "user"("email");

-- CreateIndex
CREATE INDEX "vesting_milestones_vesting_schedule_id_idx" ON "vesting_milestones"("vesting_schedule_id");

-- CreateIndex
CREATE INDEX "vesting_milestones_vesting_date_idx" ON "vesting_milestones"("vesting_date");

-- CreateIndex
CREATE INDEX "vesting_milestones_sort_order_idx" ON "vesting_milestones"("sort_order");

-- CreateIndex
CREATE UNIQUE INDEX "vesting_schedules_allocationId_key" ON "vesting_schedules"("allocationId");

-- CreateIndex
CREATE INDEX "vesting_schedules_name_idx" ON "vesting_schedules"("name");

-- CreateIndex
CREATE INDEX "vesting_schedules_vesting_type_idx" ON "vesting_schedules"("vesting_type");

-- CreateIndex
CREATE INDEX "_AllocationToComment_B_index" ON "_AllocationToComment"("B");

-- CreateIndex
CREATE INDEX "_ChecklistItemToUser_B_index" ON "_ChecklistItemToUser"("B");

-- CreateIndex
CREATE INDEX "_ChecklistItemToComment_B_index" ON "_ChecklistItemToComment"("B");

-- AddForeignKey
ALTER TABLE "allocation_pools" ADD CONSTRAINT "allocation_pools_project_id_fkey" FOREIGN KEY ("project_id") REFERENCES "project"("project_id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "allocations" ADD CONSTRAINT "allocations_project_id_project_project_id_fk" FOREIGN KEY ("project_id") REFERENCES "project"("project_id") ON DELETE NO ACTION ON UPDATE NO ACTION;

-- AddForeignKey
ALTER TABLE "allocations" ADD CONSTRAINT "allocations_allocation_pool_id_fkey" FOREIGN KEY ("allocation_pool_id") REFERENCES "allocation_pools"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "checklist" ADD CONSTRAINT "checklist_project_id_fkey" FOREIGN KEY ("project_id") REFERENCES "project"("project_id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "checklist_item" ADD CONSTRAINT "checklist_item_checklist_id_fkey" FOREIGN KEY ("checklist_id") REFERENCES "checklist"("checklist_id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "comment" ADD CONSTRAINT "comment_created_by_fkey" FOREIGN KEY ("created_by") REFERENCES "user"("clerk_user_id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "comment" ADD CONSTRAINT "comment_project_id_fkey" FOREIGN KEY ("project_id") REFERENCES "project"("project_id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "comment_views" ADD CONSTRAINT "comment_views_comment_id_fkey" FOREIGN KEY ("comment_id") REFERENCES "comment"("comment_id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "comment_views" ADD CONSTRAINT "comment_views_viewed_by_fkey" FOREIGN KEY ("viewed_by") REFERENCES "user"("clerk_user_id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "project_snapshots" ADD CONSTRAINT "project_snapshots_project_id_fkey" FOREIGN KEY ("project_id") REFERENCES "project"("project_id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "unlock_schedules" ADD CONSTRAINT "unlock_schedules_allocationId_fkey" FOREIGN KEY ("allocationId") REFERENCES "allocations"("allocation_id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "unlock_schedules" ADD CONSTRAINT "unlock_schedules_projectId_fkey" FOREIGN KEY ("projectId") REFERENCES "project"("project_id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "vesting_milestones" ADD CONSTRAINT "vesting_milestones_vesting_schedule_id_fkey" FOREIGN KEY ("vesting_schedule_id") REFERENCES "vesting_schedules"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "vesting_schedules" ADD CONSTRAINT "vesting_schedules_allocationId_fkey" FOREIGN KEY ("allocationId") REFERENCES "allocations"("allocation_id") ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "vesting_schedules" ADD CONSTRAINT "vesting_schedules_projectId_fkey" FOREIGN KEY ("projectId") REFERENCES "project"("project_id") ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "_AllocationToComment" ADD CONSTRAINT "_AllocationToComment_A_fkey" FOREIGN KEY ("A") REFERENCES "allocations"("allocation_id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "_AllocationToComment" ADD CONSTRAINT "_AllocationToComment_B_fkey" FOREIGN KEY ("B") REFERENCES "comment"("comment_id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "_ChecklistItemToUser" ADD CONSTRAINT "_ChecklistItemToUser_A_fkey" FOREIGN KEY ("A") REFERENCES "checklist_item"("item_id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "_ChecklistItemToUser" ADD CONSTRAINT "_ChecklistItemToUser_B_fkey" FOREIGN KEY ("B") REFERENCES "user"("clerk_user_id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "_ChecklistItemToComment" ADD CONSTRAINT "_ChecklistItemToComment_A_fkey" FOREIGN KEY ("A") REFERENCES "checklist_item"("item_id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "_ChecklistItemToComment" ADD CONSTRAINT "_ChecklistItemToComment_B_fkey" FOREIGN KEY ("B") REFERENCES "comment"("comment_id") ON DELETE CASCADE ON UPDATE CASCADE;