CREATE TABLE "accounts" (
	"user_id" text PRIMARY KEY,
	"email" text NOT NULL,
	"credits" integer DEFAULT 0 NOT NULL,
	"default_sender_id" text,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "campaigns" (
	"id" serial PRIMARY KEY,
	"user_id" text NOT NULL,
	"sender_id" text NOT NULL,
	"message" text NOT NULL,
	"recipients" jsonb NOT NULL,
	"recipient_count" integer NOT NULL,
	"segments" integer NOT NULL,
	"credits_used" integer NOT NULL,
	"status" text DEFAULT 'sending' NOT NULL,
	"provider_response" text,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "payments" (
	"id" serial PRIMARY KEY,
	"user_id" text NOT NULL,
	"reference" text NOT NULL UNIQUE,
	"package_id" text NOT NULL,
	"credits" integer NOT NULL,
	"amount" numeric(10,2) NOT NULL,
	"currency" text DEFAULT 'GHS' NOT NULL,
	"payer_phone" text,
	"status" text DEFAULT 'pending' NOT NULL,
	"provider_reference" text,
	"checkout_url" text,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"completed_at" timestamp with time zone
);
--> statement-breakpoint
CREATE INDEX "campaigns_user_idx" ON "campaigns" ("user_id");--> statement-breakpoint
CREATE INDEX "payments_user_idx" ON "payments" ("user_id");--> statement-breakpoint
ALTER TABLE "campaigns" ADD CONSTRAINT "campaigns_user_id_accounts_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "accounts"("user_id");--> statement-breakpoint
ALTER TABLE "payments" ADD CONSTRAINT "payments_user_id_accounts_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "accounts"("user_id");