-- Gallery modülü: şube ve sınıf bazlı fotoğraf galerileri

CREATE TABLE "galleries" (
    "id"                TEXT NOT NULL,
    "tenant_id"         TEXT NOT NULL,
    "branch_id"         TEXT,
    "classroom_id"      TEXT,
    "title"             TEXT NOT NULL,
    "description"       TEXT,
    "cover_url"         TEXT,
    "is_active"         BOOLEAN NOT NULL DEFAULT true,
    "created_at"        TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at"        TIMESTAMP(3) NOT NULL,
    "created_by_user_id" TEXT,

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

CREATE TABLE "gallery_images" (
    "id"          TEXT NOT NULL,
    "tenant_id"   TEXT NOT NULL,
    "gallery_id"  TEXT NOT NULL,
    "url"         TEXT NOT NULL,
    "caption"     TEXT,
    "order"       INTEGER NOT NULL DEFAULT 0,
    "size"        INTEGER,
    "created_at"  TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

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

-- İndeksler
CREATE INDEX "galleries_tenant_id_idx" ON "galleries"("tenant_id");
CREATE INDEX "galleries_tenant_id_branch_id_idx" ON "galleries"("tenant_id", "branch_id");
CREATE INDEX "galleries_tenant_id_classroom_id_idx" ON "galleries"("tenant_id", "classroom_id");
CREATE INDEX "gallery_images_gallery_id_order_idx" ON "gallery_images"("gallery_id", "order");

-- Foreign keys
ALTER TABLE "galleries" ADD CONSTRAINT "galleries_tenant_id_fkey"
    FOREIGN KEY ("tenant_id") REFERENCES "tenants"("id") ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE "galleries" ADD CONSTRAINT "galleries_branch_id_fkey"
    FOREIGN KEY ("branch_id") REFERENCES "branches"("id") ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE "galleries" ADD CONSTRAINT "galleries_classroom_id_fkey"
    FOREIGN KEY ("classroom_id") REFERENCES "classrooms"("id") ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE "galleries" ADD CONSTRAINT "galleries_created_by_user_id_fkey"
    FOREIGN KEY ("created_by_user_id") REFERENCES "users"("id") ON DELETE SET NULL ON UPDATE CASCADE;

ALTER TABLE "gallery_images" ADD CONSTRAINT "gallery_images_gallery_id_fkey"
    FOREIGN KEY ("gallery_id") REFERENCES "galleries"("id") ON DELETE CASCADE ON UPDATE CASCADE;