-- CreateTable
CREATE TABLE "sites" (
    "id" TEXT NOT NULL,
    "omada_site_id" TEXT NOT NULL,
    "name" TEXT NOT NULL,
    "region" TEXT,
    "timezone" TEXT,
    "country" TEXT,
    "address" TEXT,
    "contact" TEXT,
    "phone" TEXT,
    "email" TEXT,
    "note" TEXT,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP(3) NOT NULL,

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

-- CreateTable
CREATE TABLE "devices" (
    "id" TEXT NOT NULL,
    "omada_device_id" TEXT,
    "mac" TEXT NOT NULL,
    "site_id" TEXT NOT NULL,
    "name" TEXT NOT NULL,
    "ip" TEXT,
    "model" TEXT NOT NULL,
    "type" TEXT NOT NULL,
    "status" TEXT NOT NULL,
    "health_score" INTEGER,
    "firmware_version" TEXT,
    "license_status" TEXT,
    "license_due_date" TIMESTAMP(3),
    "last_seen_at" TIMESTAMP(3),
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP(3) NOT NULL,

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

-- CreateTable
CREATE TABLE "config_templates" (
    "id" TEXT NOT NULL,
    "name" TEXT NOT NULL,
    "type" TEXT NOT NULL,
    "payload" JSONB NOT NULL,
    "description" TEXT,
    "created_by" TEXT,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP(3) NOT NULL,

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

-- CreateTable
CREATE TABLE "device_config_applied" (
    "id" TEXT NOT NULL,
    "device_id" TEXT NOT NULL,
    "config_template_id" TEXT NOT NULL,
    "status" TEXT NOT NULL,
    "applied_at" TIMESTAMP(3),
    "result_payload" JSONB,
    "error_message" TEXT,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP(3) NOT NULL,

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

-- CreateTable
CREATE TABLE "audit_logs" (
    "id" TEXT NOT NULL,
    "user_id" TEXT,
    "service_account" TEXT,
    "action" TEXT NOT NULL,
    "target_type" TEXT NOT NULL,
    "target_id" TEXT NOT NULL,
    "details" JSONB,
    "timestamp" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

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

-- CreateIndex
CREATE UNIQUE INDEX "sites_omada_site_id_key" ON "sites"("omada_site_id");

-- CreateIndex
CREATE INDEX "sites_omada_site_id_idx" ON "sites"("omada_site_id");

-- CreateIndex
CREATE INDEX "devices_site_id_idx" ON "devices"("site_id");

-- CreateIndex
CREATE INDEX "devices_mac_idx" ON "devices"("mac");

-- CreateIndex
CREATE INDEX "devices_omada_device_id_idx" ON "devices"("omada_device_id");

-- CreateIndex
CREATE INDEX "devices_status_idx" ON "devices"("status");

-- CreateIndex
CREATE UNIQUE INDEX "devices_site_id_mac_key" ON "devices"("site_id", "mac");

-- CreateIndex
CREATE INDEX "config_templates_type_idx" ON "config_templates"("type");

-- CreateIndex
CREATE INDEX "device_config_applied_device_id_idx" ON "device_config_applied"("device_id");

-- CreateIndex
CREATE INDEX "device_config_applied_config_template_id_idx" ON "device_config_applied"("config_template_id");

-- CreateIndex
CREATE INDEX "device_config_applied_status_idx" ON "device_config_applied"("status");

-- CreateIndex
CREATE INDEX "audit_logs_user_id_idx" ON "audit_logs"("user_id");

-- CreateIndex
CREATE INDEX "audit_logs_action_idx" ON "audit_logs"("action");

-- CreateIndex
CREATE INDEX "audit_logs_target_type_target_id_idx" ON "audit_logs"("target_type", "target_id");

-- CreateIndex
CREATE INDEX "audit_logs_timestamp_idx" ON "audit_logs"("timestamp");

-- AddForeignKey
ALTER TABLE "devices" ADD CONSTRAINT "devices_site_id_fkey" FOREIGN KEY ("site_id") REFERENCES "sites"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "device_config_applied" ADD CONSTRAINT "device_config_applied_device_id_fkey" FOREIGN KEY ("device_id") REFERENCES "devices"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "device_config_applied" ADD CONSTRAINT "device_config_applied_config_template_id_fkey" FOREIGN KEY ("config_template_id") REFERENCES "config_templates"("id") ON DELETE RESTRICT ON UPDATE CASCADE;