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

-- CreateEnum
CREATE TYPE "platform_site_attendance"."SiteGeoPolicy" AS ENUM ('STRICT_BLOCK', 'ALLOW_WITH_FLAG', 'SKIP');
CREATE TYPE "platform_site_attendance"."SiteEventType" AS ENUM ('CHECK_IN', 'CHECK_OUT');
CREATE TYPE "platform_site_attendance"."SiteAuthMethod" AS ENUM ('AUTHENTICATED', 'UNAUTHENTICATED');
CREATE TYPE "platform_site_attendance"."SiteGeoStatus" AS ENUM ('VALID', 'OUT_OF_RANGE', 'LOW_ACCURACY', 'PERMISSION_DENIED', 'UNAVAILABLE', 'TIMEOUT', 'SKIPPED');

-- CreateTable
CREATE TABLE "platform_site_attendance"."site_checkpoints" (
    "id" VARCHAR(32) NOT NULL,
    "code" VARCHAR(32) NOT NULL,
    "name" VARCHAR(200) NOT NULL,
    "description" TEXT,
    "address" VARCHAR(500),
    "timezone" VARCHAR(50) NOT NULL,
    "latitude" DOUBLE PRECISION NOT NULL,
    "longitude" DOUBLE PRECISION NOT NULL,
    "geo_policy" "platform_site_attendance"."SiteGeoPolicy" NOT NULL DEFAULT 'SKIP',
    "geo_radius" INTEGER NOT NULL DEFAULT 200,
    "geo_accuracy_threshold" INTEGER NOT NULL DEFAULT 100,
    "allow_unauthenticated_checkin" BOOLEAN NOT NULL DEFAULT false,
    "is_active" BOOLEAN NOT NULL DEFAULT true,
    "created_by" UUID NOT NULL,
    "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMPTZ(3) NOT NULL,

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

-- CreateTable
CREATE TABLE "platform_site_attendance"."site_attendance_events" (
    "id" VARCHAR(32) NOT NULL,
    "checkpoint_id" VARCHAR(32) NOT NULL,
    "user_id" UUID NOT NULL,
    "event_type" "platform_site_attendance"."SiteEventType" NOT NULL,
    "timestamp" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "local_date" VARCHAR(10) NOT NULL,
    "auth_method" "platform_site_attendance"."SiteAuthMethod" NOT NULL,
    "latitude" DOUBLE PRECISION,
    "longitude" DOUBLE PRECISION,
    "accuracy" DOUBLE PRECISION,
    "geo_status" "platform_site_attendance"."SiteGeoStatus" NOT NULL,
    "distance_to_checkpoint" DOUBLE PRECISION,
    "device_id" VARCHAR(200),
    "user_agent" TEXT,
    "ip_address" VARCHAR(45),
    "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

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

-- CreateTable
CREATE TABLE "platform_site_attendance"."site_daily_summaries" (
    "id" VARCHAR(32) NOT NULL,
    "checkpoint_id" VARCHAR(32) NOT NULL,
    "user_id" UUID NOT NULL,
    "local_date" VARCHAR(10) NOT NULL,
    "first_check_in_at" TIMESTAMPTZ(3),
    "last_check_out_at" TIMESTAMPTZ(3),
    "check_in_count" INTEGER NOT NULL DEFAULT 0,
    "check_out_count" INTEGER NOT NULL DEFAULT 0,
    "has_geo_anomaly" BOOLEAN NOT NULL DEFAULT false,
    "last_event_id" VARCHAR(32),
    "updated_at" TIMESTAMPTZ(3) NOT NULL,

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

-- CreateIndex
CREATE UNIQUE INDEX "site_checkpoints_code_key" ON "platform_site_attendance"."site_checkpoints"("code");
CREATE INDEX "site_checkpoints_is_active_idx" ON "platform_site_attendance"."site_checkpoints"("is_active");

CREATE INDEX "site_attendance_events_checkpoint_id_user_id_local_date_idx" ON "platform_site_attendance"."site_attendance_events"("checkpoint_id", "user_id", "local_date");
CREATE INDEX "site_attendance_events_checkpoint_id_local_date_idx" ON "platform_site_attendance"."site_attendance_events"("checkpoint_id", "local_date");
CREATE INDEX "site_attendance_events_user_id_local_date_idx" ON "platform_site_attendance"."site_attendance_events"("user_id", "local_date");
CREATE INDEX "site_attendance_events_timestamp_idx" ON "platform_site_attendance"."site_attendance_events"("timestamp");

CREATE UNIQUE INDEX "site_daily_summaries_checkpoint_id_user_id_local_date_key" ON "platform_site_attendance"."site_daily_summaries"("checkpoint_id", "user_id", "local_date");
CREATE INDEX "site_daily_summaries_checkpoint_id_local_date_idx" ON "platform_site_attendance"."site_daily_summaries"("checkpoint_id", "local_date");
CREATE INDEX "site_daily_summaries_user_id_local_date_idx" ON "platform_site_attendance"."site_daily_summaries"("user_id", "local_date");

-- AddForeignKey
ALTER TABLE "platform_site_attendance"."site_checkpoints" ADD CONSTRAINT "site_checkpoints_created_by_fkey" FOREIGN KEY ("created_by") REFERENCES "platform_iam"."users"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE "platform_site_attendance"."site_attendance_events" ADD CONSTRAINT "site_attendance_events_checkpoint_id_fkey" FOREIGN KEY ("checkpoint_id") REFERENCES "platform_site_attendance"."site_checkpoints"("id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "platform_site_attendance"."site_attendance_events" ADD CONSTRAINT "site_attendance_events_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "platform_iam"."users"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE "platform_site_attendance"."site_daily_summaries" ADD CONSTRAINT "site_daily_summaries_checkpoint_id_fkey" FOREIGN KEY ("checkpoint_id") REFERENCES "platform_site_attendance"."site_checkpoints"("id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "platform_site_attendance"."site_daily_summaries" ADD CONSTRAINT "site_daily_summaries_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "platform_iam"."users"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
