-- 清理绩效模块废弃模型、枚举值和字段
-- PRD 已将以下功能移到 Out-of-scope：指标库、绩效面谈、持续反馈、360 发布、年度 KPI 分解

-- AlterEnum: 移除 Evaluation360Status.RELEASED
BEGIN;
CREATE TYPE "platform_performance"."Evaluation360Status_new" AS ENUM ('DRAFT', 'IN_PROGRESS', 'COLLECTING', 'COMPLETED', 'CANCELLED');
ALTER TABLE "platform_performance"."evaluation_360" ALTER COLUMN "status" DROP DEFAULT;
ALTER TABLE "platform_performance"."evaluation_360" ALTER COLUMN "status" TYPE "platform_performance"."Evaluation360Status_new" USING ("status"::text::"platform_performance"."Evaluation360Status_new");
ALTER TYPE "platform_performance"."Evaluation360Status" RENAME TO "Evaluation360Status_old";
ALTER TYPE "platform_performance"."Evaluation360Status_new" RENAME TO "Evaluation360Status";
DROP TYPE "platform_performance"."Evaluation360Status_old";
ALTER TABLE "platform_performance"."evaluation_360" ALTER COLUMN "status" SET DEFAULT 'DRAFT';
COMMIT;

-- AlterEnum: 移除 KpiAssignmentStatus.CONFIRMED
BEGIN;
CREATE TYPE "platform_performance"."KpiAssignmentStatus_new" AS ENUM ('DRAFT', 'SUBMITTED', 'APPROVED', 'REJECTED');
ALTER TABLE "platform_performance"."kpi_assignment" ALTER COLUMN "assignment_status" DROP DEFAULT;
ALTER TABLE "platform_performance"."kpi_assignment" ALTER COLUMN "assignment_status" TYPE "platform_performance"."KpiAssignmentStatus_new" USING ("assignment_status"::text::"platform_performance"."KpiAssignmentStatus_new");
ALTER TYPE "platform_performance"."KpiAssignmentStatus" RENAME TO "KpiAssignmentStatus_old";
ALTER TYPE "platform_performance"."KpiAssignmentStatus_new" RENAME TO "KpiAssignmentStatus";
DROP TYPE "platform_performance"."KpiAssignmentStatus_old";
ALTER TABLE "platform_performance"."kpi_assignment" ALTER COLUMN "assignment_status" SET DEFAULT 'DRAFT';
COMMIT;

-- DropForeignKey
ALTER TABLE "platform_performance"."interview" DROP CONSTRAINT IF EXISTS "interview_cycle_id_fkey";
ALTER TABLE "platform_performance"."kpi_assignment" DROP CONSTRAINT IF EXISTS "kpi_assignment_indicator_id_fkey";
ALTER TABLE "platform_performance"."kpi_indicator" DROP CONSTRAINT IF EXISTS "kpi_indicator_organization_id_fkey";

-- DropIndex
DROP INDEX IF EXISTS "platform_performance"."kpi_assignment_indicator_id_idx";

-- AlterTable: 移除废弃字段
ALTER TABLE "platform_performance"."evaluation_360" DROP COLUMN IF EXISTS "released_at";
ALTER TABLE "platform_performance"."kpi_assignment" DROP COLUMN IF EXISTS "annual_target_value",
DROP COLUMN IF EXISTS "indicator_id",
DROP COLUMN IF EXISTS "quarter_targets";

-- AlterTable: 新增 appeal_response 字段
ALTER TABLE "platform_performance"."performance_result" ADD COLUMN IF NOT EXISTS "appeal_response" TEXT;

-- DropTable: 废弃模型
DROP TABLE IF EXISTS "platform_performance"."continuous_feedback";
DROP TABLE IF EXISTS "platform_performance"."interview";
DROP TABLE IF EXISTS "platform_performance"."kpi_indicator";

-- DropEnum: 废弃枚举
DROP TYPE IF EXISTS "platform_performance"."InterviewStatus";
DROP TYPE IF EXISTS "platform_performance"."InterviewType";
DROP TYPE IF EXISTS "platform_performance"."PerformanceFeedbackType";

-- Recruitment 模块（IF NOT EXISTS 防重复）
CREATE SCHEMA IF NOT EXISTS "corp_recruitment";
DO $$ BEGIN CREATE TYPE "corp_recruitment"."ResumeSource" AS ENUM ('EMAIL', 'MANUAL'); EXCEPTION WHEN duplicate_object THEN null; END $$;
DO $$ BEGIN CREATE TYPE "corp_recruitment"."ResumeStatus" AS ENUM ('PENDING', 'SCREENING', 'SCREENED', 'SHORTLISTED', 'REJECTED', 'INTERVIEW', 'OFFERED', 'HIRED'); EXCEPTION WHEN duplicate_object THEN null; END $$;
DO $$ BEGIN CREATE TYPE "corp_recruitment"."JobPositionStatus" AS ENUM ('DRAFT', 'OPEN', 'CLOSED'); EXCEPTION WHEN duplicate_object THEN null; END $$;
DO $$ BEGIN CREATE TYPE "corp_recruitment"."ScreeningRecommendation" AS ENUM ('STRONG_MATCH', 'MATCH', 'WEAK', 'NO_MATCH'); EXCEPTION WHEN duplicate_object THEN null; END $$;
DO $$ BEGIN CREATE TYPE "corp_recruitment"."EmailFetchStatus" AS ENUM ('SUCCESS', 'PARTIAL', 'FAILED'); EXCEPTION WHEN duplicate_object THEN null; END $$;

CREATE TABLE IF NOT EXISTS "corp_recruitment"."job_positions" (
    "id" UUID NOT NULL,
    "title" VARCHAR(200) NOT NULL,
    "department" VARCHAR(200),
    "description" TEXT,
    "requirements" TEXT,
    "headcount" INTEGER NOT NULL DEFAULT 1,
    "status" "corp_recruitment"."JobPositionStatus" NOT NULL DEFAULT 'OPEN',
    "organization_id" UUID NOT NULL,
    "created_by" UUID NOT NULL,
    "created_at" TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMPTZ NOT NULL,
    CONSTRAINT "job_positions_pkey" PRIMARY KEY ("id")
);

CREATE TABLE IF NOT EXISTS "corp_recruitment"."resumes" (
    "id" UUID NOT NULL,
    "candidate_name" VARCHAR(100),
    "candidate_email" VARCHAR(200),
    "candidate_phone" VARCHAR(50),
    "education" VARCHAR(200),
    "years_of_experience" INTEGER,
    "skills" TEXT[] DEFAULT ARRAY[]::TEXT[],
    "raw_text" TEXT,
    "structured_data" JSONB,
    "file_url" VARCHAR(500) NOT NULL,
    "file_name" VARCHAR(300) NOT NULL,
    "file_type" VARCHAR(50) NOT NULL,
    "source" "corp_recruitment"."ResumeSource" NOT NULL DEFAULT 'EMAIL',
    "source_email_uid" VARCHAR(200),
    "source_email_subject" VARCHAR(500),
    "received_at" TIMESTAMPTZ,
    "status" "corp_recruitment"."ResumeStatus" NOT NULL DEFAULT 'PENDING',
    "position_id" UUID,
    "organization_id" UUID NOT NULL,
    "created_at" TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMPTZ NOT NULL,
    CONSTRAINT "resumes_pkey" PRIMARY KEY ("id")
);

CREATE TABLE IF NOT EXISTS "corp_recruitment"."resume_screenings" (
    "id" UUID NOT NULL,
    "resume_id" UUID NOT NULL,
    "position_id" UUID NOT NULL,
    "overall_score" DOUBLE PRECISION NOT NULL,
    "recommendation" "corp_recruitment"."ScreeningRecommendation" NOT NULL,
    "reasoning" TEXT,
    "detail_scores" JSONB,
    "model_used" VARCHAR(100),
    "created_at" TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT "resume_screenings_pkey" PRIMARY KEY ("id")
);

CREATE TABLE IF NOT EXISTS "corp_recruitment"."email_fetch_logs" (
    "id" UUID NOT NULL,
    "mailbox_config" VARCHAR(200) NOT NULL,
    "fetched_count" INTEGER NOT NULL DEFAULT 0,
    "new_count" INTEGER NOT NULL DEFAULT 0,
    "status" "corp_recruitment"."EmailFetchStatus" NOT NULL DEFAULT 'SUCCESS',
    "error_message" TEXT,
    "started_at" TIMESTAMPTZ NOT NULL,
    "finished_at" TIMESTAMPTZ,
    "created_at" TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT "email_fetch_logs_pkey" PRIMARY KEY ("id")
);

CREATE UNIQUE INDEX IF NOT EXISTS "resumes_source_email_uid_organization_id_key" ON "corp_recruitment"."resumes"("source_email_uid", "organization_id");
CREATE UNIQUE INDEX IF NOT EXISTS "resume_screenings_resume_id_position_id_key" ON "corp_recruitment"."resume_screenings"("resume_id", "position_id");

DO $$ BEGIN
ALTER TABLE "corp_recruitment"."resumes" ADD CONSTRAINT "resumes_position_id_fkey" FOREIGN KEY ("position_id") REFERENCES "corp_recruitment"."job_positions"("id") ON DELETE SET NULL ON UPDATE CASCADE;
EXCEPTION WHEN duplicate_object THEN null; END $$;

DO $$ BEGIN
ALTER TABLE "corp_recruitment"."resume_screenings" ADD CONSTRAINT "resume_screenings_resume_id_fkey" FOREIGN KEY ("resume_id") REFERENCES "corp_recruitment"."resumes"("id") ON DELETE CASCADE ON UPDATE CASCADE;
EXCEPTION WHEN duplicate_object THEN null; END $$;

DO $$ BEGIN
ALTER TABLE "corp_recruitment"."resume_screenings" ADD CONSTRAINT "resume_screenings_position_id_fkey" FOREIGN KEY ("position_id") REFERENCES "corp_recruitment"."job_positions"("id") ON DELETE CASCADE ON UPDATE CASCADE;
EXCEPTION WHEN duplicate_object THEN null; END $$;
