CREATE TYPE "OrderSource" AS ENUM ('stripe', 'manual');
CREATE TYPE "LicenseSource" AS ENUM ('stripe', 'manual', 'replacement');
CREATE TYPE "LicenseStatus" AS ENUM ('active', 'disabled', 'replaced', 'revoked', 'archived');
CREATE TYPE "EmailStatus" AS ENUM ('pending', 'sent', 'failed', 'skipped');

CREATE TABLE "admin_users" (
  "admin_user_id" TEXT NOT NULL,
  "email" TEXT NOT NULL,
  "name" TEXT,
  "password_hash" TEXT NOT NULL,
  "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updated_at" TIMESTAMP(3) NOT NULL,
  CONSTRAINT "admin_users_pkey" PRIMARY KEY ("admin_user_id")
);

CREATE TABLE "admin_sessions" (
  "admin_session_id" TEXT NOT NULL,
  "token_hash" TEXT NOT NULL,
  "csrf_token" TEXT NOT NULL,
  "admin_user_id" TEXT NOT NULL,
  "expires_at" TIMESTAMP(3) NOT NULL,
  "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "last_seen_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT "admin_sessions_pkey" PRIMARY KEY ("admin_session_id")
);

CREATE TABLE "customers" (
  "customer_id" TEXT NOT NULL,
  "full_name" TEXT,
  "email" TEXT,
  "company_name" TEXT,
  "admin_notes" TEXT,
  "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updated_at" TIMESTAMP(3) NOT NULL,
  CONSTRAINT "customers_pkey" PRIMARY KEY ("customer_id")
);

CREATE TABLE "orders" (
  "order_id" TEXT NOT NULL,
  "customer_id" TEXT NOT NULL,
  "source" "OrderSource" NOT NULL DEFAULT 'stripe',
  "stripe_checkout_session_id" TEXT,
  "stripe_payment_id" TEXT,
  "stripe_customer_id" TEXT,
  "product" TEXT NOT NULL,
  "purchase_amount" INTEGER,
  "currency" TEXT,
  "purchase_date" TIMESTAMP(3),
  "payment_status" TEXT,
  "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT "orders_pkey" PRIMARY KEY ("order_id")
);

CREATE TABLE "licenses" (
  "license_id" TEXT NOT NULL,
  "customer_id" TEXT NOT NULL,
  "order_id" TEXT,
  "replaced_license_id" TEXT,
  "license_key" TEXT NOT NULL,
  "product" TEXT NOT NULL,
  "license_status" "LicenseStatus" NOT NULL DEFAULT 'active',
  "source" "LicenseSource" NOT NULL,
  "email_status" "EmailStatus" NOT NULL DEFAULT 'pending',
  "email_sent_at" TIMESTAMP(3),
  "last_email_error" TEXT,
  "admin_notes" TEXT,
  "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updated_at" TIMESTAMP(3) NOT NULL,
  CONSTRAINT "licenses_pkey" PRIMARY KEY ("license_id")
);

CREATE TABLE "processed_stripe_events" (
  "processed_stripe_event_id" TEXT NOT NULL,
  "stripe_event_id" TEXT NOT NULL,
  "event_type" TEXT NOT NULL,
  "object_id" TEXT,
  "payload" JSONB,
  "processed_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT "processed_stripe_events_pkey" PRIMARY KEY ("processed_stripe_event_id")
);

CREATE TABLE "audit_events" (
  "audit_event_id" TEXT NOT NULL,
  "action" TEXT NOT NULL,
  "admin_user_id" TEXT,
  "customer_id" TEXT,
  "order_id" TEXT,
  "license_id" TEXT,
  "metadata" JSONB,
  "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT "audit_events_pkey" PRIMARY KEY ("audit_event_id")
);

CREATE UNIQUE INDEX "admin_users_email_key" ON "admin_users"("email");
CREATE UNIQUE INDEX "admin_sessions_token_hash_key" ON "admin_sessions"("token_hash");
CREATE INDEX "admin_sessions_admin_user_id_idx" ON "admin_sessions"("admin_user_id");
CREATE INDEX "admin_sessions_expires_at_idx" ON "admin_sessions"("expires_at");
CREATE UNIQUE INDEX "customers_email_key" ON "customers"("email");
CREATE INDEX "customers_full_name_idx" ON "customers"("full_name");
CREATE INDEX "customers_company_name_idx" ON "customers"("company_name");
CREATE UNIQUE INDEX "orders_stripe_checkout_session_id_key" ON "orders"("stripe_checkout_session_id");
CREATE UNIQUE INDEX "orders_stripe_payment_id_key" ON "orders"("stripe_payment_id");
CREATE INDEX "orders_customer_id_idx" ON "orders"("customer_id");
CREATE INDEX "orders_stripe_checkout_session_id_idx" ON "orders"("stripe_checkout_session_id");
CREATE INDEX "orders_stripe_payment_id_idx" ON "orders"("stripe_payment_id");
CREATE UNIQUE INDEX "licenses_license_key_key" ON "licenses"("license_key");
CREATE INDEX "licenses_customer_id_idx" ON "licenses"("customer_id");
CREATE INDEX "licenses_order_id_idx" ON "licenses"("order_id");
CREATE INDEX "licenses_license_status_idx" ON "licenses"("license_status");
CREATE UNIQUE INDEX "processed_stripe_events_stripe_event_id_key" ON "processed_stripe_events"("stripe_event_id");
CREATE INDEX "processed_stripe_events_object_id_idx" ON "processed_stripe_events"("object_id");
CREATE INDEX "audit_events_customer_id_idx" ON "audit_events"("customer_id");
CREATE INDEX "audit_events_order_id_idx" ON "audit_events"("order_id");
CREATE INDEX "audit_events_license_id_idx" ON "audit_events"("license_id");
CREATE INDEX "audit_events_created_at_idx" ON "audit_events"("created_at");

ALTER TABLE "admin_sessions" ADD CONSTRAINT "admin_sessions_admin_user_id_fkey" FOREIGN KEY ("admin_user_id") REFERENCES "admin_users"("admin_user_id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "orders" ADD CONSTRAINT "orders_customer_id_fkey" FOREIGN KEY ("customer_id") REFERENCES "customers"("customer_id") ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE "licenses" ADD CONSTRAINT "licenses_customer_id_fkey" FOREIGN KEY ("customer_id") REFERENCES "customers"("customer_id") ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE "licenses" ADD CONSTRAINT "licenses_order_id_fkey" FOREIGN KEY ("order_id") REFERENCES "orders"("order_id") ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE "licenses" ADD CONSTRAINT "licenses_replaced_license_id_fkey" FOREIGN KEY ("replaced_license_id") REFERENCES "licenses"("license_id") ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE "audit_events" ADD CONSTRAINT "audit_events_admin_user_id_fkey" FOREIGN KEY ("admin_user_id") REFERENCES "admin_users"("admin_user_id") ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE "audit_events" ADD CONSTRAINT "audit_events_customer_id_fkey" FOREIGN KEY ("customer_id") REFERENCES "customers"("customer_id") ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE "audit_events" ADD CONSTRAINT "audit_events_order_id_fkey" FOREIGN KEY ("order_id") REFERENCES "orders"("order_id") ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE "audit_events" ADD CONSTRAINT "audit_events_license_id_fkey" FOREIGN KEY ("license_id") REFERENCES "licenses"("license_id") ON DELETE SET NULL ON UPDATE CASCADE;