-- CreateSchema
CREATE SCHEMA IF NOT EXISTS "public";

-- CreateEnum
CREATE TYPE "MembershipRole" AS ENUM ('OWNER', 'ADMIN', 'EDITOR', 'CONTRIBUTOR', 'MEMBER', 'VIEWER');

-- CreateEnum
CREATE TYPE "MembershipStatus" AS ENUM ('ACTIVE', 'REMOVED');

-- CreateEnum
CREATE TYPE "InvitationStatus" AS ENUM ('PENDING', 'ACCEPTED', 'EXPIRED', 'REVOKED');

-- CreateEnum
CREATE TYPE "Gender" AS ENUM ('MALE', 'FEMALE', 'UNKNOWN');

-- CreateEnum
CREATE TYPE "LifeStatus" AS ENUM ('ALIVE', 'DECEASED', 'UNKNOWN');

-- CreateEnum
CREATE TYPE "PersonFactField" AS ENUM ('FULL_NAME', 'NICKNAME', 'BIRTH_DATE', 'BIRTH_PLACE', 'DEATH_DATE', 'DEATH_PLACE');

-- CreateEnum
CREATE TYPE "PrecisionQualifier" AS ENUM ('EXACT', 'YEAR_ONLY', 'MONTH_YEAR', 'CIRCA', 'BEFORE', 'AFTER');

-- CreateEnum
CREATE TYPE "VerificationStatus" AS ENUM ('VERIFIED', 'PROBABLE', 'UNCERTAIN', 'DISPUTED');

-- CreateEnum
CREATE TYPE "ContactType" AS ENUM ('WHATSAPP', 'PHONE', 'EMAIL', 'OTHER');

-- CreateEnum
CREATE TYPE "ContactVisibility" AS ENUM ('ONLY_ME', 'FAMILY', 'PUBLIC');

-- CreateEnum
CREATE TYPE "RelationshipType" AS ENUM ('BIOLOGICAL_PARENT_CHILD', 'ADOPTIVE_PARENT_CHILD', 'STEP_PARENT_CHILD', 'PARTNER', 'SIBLING');

-- CreateEnum
CREATE TYPE "PartnerStatus" AS ENUM ('MARRIED', 'DIVORCED', 'WIDOWED', 'ENDED');

-- CreateEnum
CREATE TYPE "AuditEntityType" AS ENUM ('PERSON', 'RELATIONSHIP');

-- CreateEnum
CREATE TYPE "AuditAction" AS ENUM ('CREATE', 'UPDATE');

-- CreateTable
CREATE TABLE "users" (
    "id" TEXT NOT NULL,
    "email" TEXT,
    "phone" TEXT,
    "password_hash" TEXT NOT NULL,
    "display_name" TEXT,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP(3) NOT NULL,

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

-- CreateTable
CREATE TABLE "sessions" (
    "id" TEXT NOT NULL,
    "user_id" TEXT NOT NULL,
    "session_token" TEXT NOT NULL,
    "expires_at" TIMESTAMP(3) NOT NULL,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

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

-- CreateTable
CREATE TABLE "password_reset_tokens" (
    "id" TEXT NOT NULL,
    "user_id" TEXT NOT NULL,
    "code_hash" TEXT NOT NULL,
    "expires_at" TIMESTAMP(3) NOT NULL,
    "used_at" TIMESTAMP(3),
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

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

-- CreateTable
CREATE TABLE "trahs" (
    "id" TEXT NOT NULL,
    "name" TEXT NOT NULL,
    "created_by_user_id" TEXT NOT NULL,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

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

-- CreateTable
CREATE TABLE "memberships" (
    "id" TEXT NOT NULL,
    "trah_id" TEXT NOT NULL,
    "user_id" TEXT NOT NULL,
    "role" "MembershipRole" NOT NULL,
    "claimed_person_id" TEXT,
    "status" "MembershipStatus" NOT NULL DEFAULT 'ACTIVE',
    "joined_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

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

-- CreateTable
CREATE TABLE "invitations" (
    "id" TEXT NOT NULL,
    "trah_id" TEXT NOT NULL,
    "inviter_membership_id" TEXT NOT NULL,
    "target_email" TEXT,
    "target_phone" TEXT,
    "token" TEXT NOT NULL,
    "proposed_role" "MembershipRole" NOT NULL,
    "status" "InvitationStatus" NOT NULL DEFAULT 'PENDING',
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "expires_at" TIMESTAMP(3) NOT NULL,

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

-- CreateTable
CREATE TABLE "persons" (
    "id" TEXT NOT NULL,
    "trah_id" TEXT NOT NULL,
    "display_name" TEXT NOT NULL,
    "gender" "Gender" NOT NULL DEFAULT 'UNKNOWN',
    "life_status" "LifeStatus" NOT NULL DEFAULT 'UNKNOWN',
    "created_by_membership_id" TEXT NOT NULL,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP(3) NOT NULL,

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

-- CreateTable
CREATE TABLE "person_facts" (
    "id" TEXT NOT NULL,
    "person_id" TEXT NOT NULL,
    "trah_id" TEXT NOT NULL,
    "field" "PersonFactField" NOT NULL,
    "value" JSONB NOT NULL,
    "precision_qualifier" "PrecisionQualifier",
    "verification_status" "VerificationStatus" NOT NULL DEFAULT 'VERIFIED',
    "source" TEXT,
    "is_preferred" BOOLEAN NOT NULL DEFAULT true,
    "created_by_membership_id" TEXT NOT NULL,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

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

-- CreateTable
CREATE TABLE "residences" (
    "id" TEXT NOT NULL,
    "person_id" TEXT NOT NULL,
    "trah_id" TEXT NOT NULL,
    "place" TEXT NOT NULL,
    "start_date" DATE NOT NULL,
    "end_date" DATE,
    "is_current" BOOLEAN NOT NULL DEFAULT false,

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

-- CreateTable
CREATE TABLE "contacts" (
    "id" TEXT NOT NULL,
    "person_id" TEXT NOT NULL,
    "trah_id" TEXT NOT NULL,
    "type" "ContactType" NOT NULL,
    "value" TEXT NOT NULL,
    "visibility" "ContactVisibility" NOT NULL,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

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

-- CreateTable
CREATE TABLE "relationships" (
    "id" TEXT NOT NULL,
    "trah_id" TEXT NOT NULL,
    "person_a_id" TEXT NOT NULL,
    "person_b_id" TEXT NOT NULL,
    "type" "RelationshipType" NOT NULL,
    "start_date" DATE,
    "end_date" DATE,
    "partner_status" "PartnerStatus",
    "created_by_membership_id" TEXT NOT NULL,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP(3) NOT NULL,

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

-- CreateTable
CREATE TABLE "audit_log" (
    "id" TEXT NOT NULL,
    "trah_id" TEXT NOT NULL,
    "entity_type" "AuditEntityType" NOT NULL,
    "entity_id" TEXT NOT NULL,
    "actor_user_id" TEXT NOT NULL,
    "actor_membership_id" TEXT NOT NULL,
    "action" "AuditAction" NOT NULL,
    "changes" JSONB NOT NULL,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

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

-- CreateIndex
CREATE UNIQUE INDEX "users_email_key" ON "users"("email");

-- CreateIndex
CREATE UNIQUE INDEX "users_phone_key" ON "users"("phone");

-- CreateIndex
CREATE UNIQUE INDEX "sessions_session_token_key" ON "sessions"("session_token");

-- CreateIndex
CREATE INDEX "sessions_user_id_idx" ON "sessions"("user_id");

-- CreateIndex
CREATE INDEX "password_reset_tokens_user_id_idx" ON "password_reset_tokens"("user_id");

-- CreateIndex
CREATE UNIQUE INDEX "memberships_claimed_person_id_key" ON "memberships"("claimed_person_id");

-- CreateIndex
CREATE INDEX "memberships_trah_id_idx" ON "memberships"("trah_id");

-- CreateIndex
CREATE INDEX "memberships_user_id_idx" ON "memberships"("user_id");

-- CreateIndex
CREATE UNIQUE INDEX "memberships_trah_id_user_id_key" ON "memberships"("trah_id", "user_id");

-- CreateIndex
CREATE UNIQUE INDEX "invitations_token_key" ON "invitations"("token");

-- CreateIndex
CREATE INDEX "invitations_trah_id_idx" ON "invitations"("trah_id");

-- CreateIndex
CREATE INDEX "persons_trah_id_idx" ON "persons"("trah_id");

-- CreateIndex
CREATE INDEX "person_facts_trah_id_idx" ON "person_facts"("trah_id");

-- CreateIndex
CREATE INDEX "person_facts_person_id_field_idx" ON "person_facts"("person_id", "field");

-- CreateIndex
CREATE INDEX "residences_trah_id_idx" ON "residences"("trah_id");

-- CreateIndex
CREATE INDEX "residences_person_id_idx" ON "residences"("person_id");

-- CreateIndex
CREATE INDEX "contacts_trah_id_idx" ON "contacts"("trah_id");

-- CreateIndex
CREATE INDEX "contacts_person_id_idx" ON "contacts"("person_id");

-- CreateIndex
CREATE INDEX "relationships_trah_id_idx" ON "relationships"("trah_id");

-- CreateIndex
CREATE INDEX "relationships_person_a_id_idx" ON "relationships"("person_a_id");

-- CreateIndex
CREATE INDEX "relationships_person_b_id_idx" ON "relationships"("person_b_id");

-- CreateIndex
-- Hand-added (per spec/genealogy/tier-b-implementation.md): prevents a
-- duplicate identical directed edge for the non-PARTNER relationship
-- types. Prisma's declarative schema has no way to express a partial
-- unique index (`where type in (...)`), so this is added directly to the
-- generated migration SQL rather than via `@@unique` in schema.prisma.
CREATE UNIQUE INDEX "relationships_trah_id_person_a_id_person_b_id_type_key"
  ON "relationships"("trah_id", "person_a_id", "person_b_id", "type")
  WHERE "type" IN ('BIOLOGICAL_PARENT_CHILD', 'ADOPTIVE_PARENT_CHILD', 'STEP_PARENT_CHILD', 'SIBLING');

-- CreateIndex
-- Hand-added: prevents an exact duplicate entry of the same PARTNER union
-- (same pair, same start_date) while still allowing polygamy (different
-- person_b_id) and remarriage (different start_date). PARTNER is
-- deliberately excluded from the index above (REQ-REL-002 requires
-- multiple PARTNER rows for the same pair over time / concurrently).
CREATE UNIQUE INDEX "relationships_trah_id_person_a_id_person_b_id_start_date_key"
  ON "relationships"("trah_id", "person_a_id", "person_b_id", "start_date")
  WHERE "type" = 'PARTNER';

-- CreateIndex
CREATE INDEX "audit_log_trah_id_entity_type_entity_id_created_at_idx" ON "audit_log"("trah_id", "entity_type", "entity_id", "created_at");

-- AddForeignKey
ALTER TABLE "sessions" ADD CONSTRAINT "sessions_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "password_reset_tokens" ADD CONSTRAINT "password_reset_tokens_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "trahs" ADD CONSTRAINT "trahs_created_by_user_id_fkey" FOREIGN KEY ("created_by_user_id") REFERENCES "users"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "memberships" ADD CONSTRAINT "memberships_trah_id_fkey" FOREIGN KEY ("trah_id") REFERENCES "trahs"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "memberships" ADD CONSTRAINT "memberships_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "memberships" ADD CONSTRAINT "memberships_claimed_person_id_fkey" FOREIGN KEY ("claimed_person_id") REFERENCES "persons"("id") ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "invitations" ADD CONSTRAINT "invitations_trah_id_fkey" FOREIGN KEY ("trah_id") REFERENCES "trahs"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "invitations" ADD CONSTRAINT "invitations_inviter_membership_id_fkey" FOREIGN KEY ("inviter_membership_id") REFERENCES "memberships"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "persons" ADD CONSTRAINT "persons_trah_id_fkey" FOREIGN KEY ("trah_id") REFERENCES "trahs"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "persons" ADD CONSTRAINT "persons_created_by_membership_id_fkey" FOREIGN KEY ("created_by_membership_id") REFERENCES "memberships"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "person_facts" ADD CONSTRAINT "person_facts_person_id_fkey" FOREIGN KEY ("person_id") REFERENCES "persons"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "person_facts" ADD CONSTRAINT "person_facts_created_by_membership_id_fkey" FOREIGN KEY ("created_by_membership_id") REFERENCES "memberships"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "residences" ADD CONSTRAINT "residences_person_id_fkey" FOREIGN KEY ("person_id") REFERENCES "persons"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "contacts" ADD CONSTRAINT "contacts_person_id_fkey" FOREIGN KEY ("person_id") REFERENCES "persons"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "relationships" ADD CONSTRAINT "relationships_trah_id_fkey" FOREIGN KEY ("trah_id") REFERENCES "trahs"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "relationships" ADD CONSTRAINT "relationships_person_a_id_fkey" FOREIGN KEY ("person_a_id") REFERENCES "persons"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "relationships" ADD CONSTRAINT "relationships_person_b_id_fkey" FOREIGN KEY ("person_b_id") REFERENCES "persons"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "relationships" ADD CONSTRAINT "relationships_created_by_membership_id_fkey" FOREIGN KEY ("created_by_membership_id") REFERENCES "memberships"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "audit_log" ADD CONSTRAINT "audit_log_trah_id_fkey" FOREIGN KEY ("trah_id") REFERENCES "trahs"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "audit_log" ADD CONSTRAINT "audit_log_actor_user_id_fkey" FOREIGN KEY ("actor_user_id") REFERENCES "users"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "audit_log" ADD CONSTRAINT "audit_log_actor_membership_id_fkey" FOREIGN KEY ("actor_membership_id") REFERENCES "memberships"("id") ON DELETE RESTRICT ON UPDATE CASCADE;