-- CreateEnum
CREATE TYPE "AuditType" AS ENUM ('INTERNAL', 'EXTERNAL');

-- CreateEnum
CREATE TYPE "AuditStatus" AS ENUM ('PLANNED', 'IN_PREPARATION', 'DONE');

-- CreateTable
CREATE TABLE "audits" (
    "id" TEXT NOT NULL,
    "tenant_id" TEXT NOT NULL,
    "type" "AuditType" NOT NULL,
    "title" TEXT NOT NULL,
    "planned_date" TEXT,
    "scope" TEXT,
    "assessment_level" TEXT,
    "provider" TEXT,
    "auditor_user_id" TEXT,
    "status" "AuditStatus" NOT NULL DEFAULT 'PLANNED',
    "preparation_deadline" TIMESTAMP(3),
    "result" TEXT,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP(3) NOT NULL,

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

-- CreateTable
CREATE TABLE "audit_evidence_items" (
    "id" TEXT NOT NULL,
    "tenant_id" TEXT NOT NULL,
    "audit_id" TEXT NOT NULL,
    "control" TEXT NOT NULL,
    "title" TEXT NOT NULL,
    "req_id" TEXT,
    "assigned_user_id" TEXT,
    "assigned_function_key" TEXT,
    "status" TEXT NOT NULL DEFAULT 'offen',
    "evidence_id" TEXT,
    "task_id" TEXT,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

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

-- CreateTable
CREATE TABLE "control_descriptions" (
    "id" TEXT NOT NULL,
    "tenant_id" TEXT NOT NULL,
    "control" TEXT NOT NULL,
    "req_id" TEXT NOT NULL,
    "draft_text" TEXT,
    "source_ref" TEXT,
    "confidence" TEXT,
    "status" TEXT NOT NULL DEFAULT 'open',
    "open_answer" TEXT,
    "updated_by_id" TEXT,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP(3) NOT NULL,

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

-- CreateIndex
CREATE INDEX "audits_tenant_id_status_idx" ON "audits"("tenant_id", "status");

-- CreateIndex
CREATE INDEX "audit_evidence_items_tenant_id_audit_id_idx" ON "audit_evidence_items"("tenant_id", "audit_id");

-- CreateIndex
CREATE INDEX "audit_evidence_items_tenant_id_assigned_user_id_status_idx" ON "audit_evidence_items"("tenant_id", "assigned_user_id", "status");

-- CreateIndex
CREATE INDEX "control_descriptions_tenant_id_control_idx" ON "control_descriptions"("tenant_id", "control");

-- CreateIndex
CREATE UNIQUE INDEX "control_descriptions_tenant_id_req_id_key" ON "control_descriptions"("tenant_id", "req_id");

-- AddForeignKey
ALTER TABLE "audit_evidence_items" ADD CONSTRAINT "audit_evidence_items_audit_id_fkey" FOREIGN KEY ("audit_id") REFERENCES "audits"("id") ON DELETE CASCADE ON UPDATE CASCADE;


-- ── Tenant-Isolation (RLS) für neue Audit-Tabellen (Muster wie F-04) ──────────
ALTER TABLE "audits" ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS tenant_isolation ON "audits";
CREATE POLICY tenant_isolation ON "audits" USING ("tenant_id" = current_setting('app.tenant_id', true)) WITH CHECK ("tenant_id" = current_setting('app.tenant_id', true));
ALTER TABLE "audits" FORCE ROW LEVEL SECURITY;
ALTER TABLE "audit_evidence_items" ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS tenant_isolation ON "audit_evidence_items";
CREATE POLICY tenant_isolation ON "audit_evidence_items" USING ("tenant_id" = current_setting('app.tenant_id', true)) WITH CHECK ("tenant_id" = current_setting('app.tenant_id', true));
ALTER TABLE "audit_evidence_items" FORCE ROW LEVEL SECURITY;
ALTER TABLE "control_descriptions" ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS tenant_isolation ON "control_descriptions";
CREATE POLICY tenant_isolation ON "control_descriptions" USING ("tenant_id" = current_setting('app.tenant_id', true)) WITH CHECK ("tenant_id" = current_setting('app.tenant_id', true));
ALTER TABLE "control_descriptions" FORCE ROW LEVEL SECURITY;