CREATE TABLE "certificate_sightings" (
	"id" text PRIMARY KEY NOT NULL,
	"certificate_id" text NOT NULL,
	"kind" text NOT NULL,
	"target_id" text,
	"source_id" text NOT NULL,
	"connector_id" text,
	"source_url" text,
	"price_usd" numeric(18, 4),
	"currency" text,
	"price" numeric(18, 4),
	"observed_at" timestamp with time zone NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "certificates" (
	"id" text PRIMARY KEY NOT NULL,
	"grader" text NOT NULL,
	"cert_number" text NOT NULL,
	"asset_id" text,
	"variant_id" text,
	"grade" text,
	"qualifier" text,
	"verify_url" text,
	"first_seen_at" timestamp with time zone NOT NULL,
	"last_seen_at" timestamp with time zone NOT NULL,
	"sightings" integer DEFAULT 0 NOT NULL,
	"source_ids" text[] DEFAULT '{}'::text[] NOT NULL,
	"last_source_url" text,
	"last_price_usd" numeric(18, 4),
	"verified_at" timestamp with time zone,
	"verification" jsonb DEFAULT '{}'::jsonb NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "connector_backfills" (
	"id" text PRIMARY KEY NOT NULL,
	"connector_id" text NOT NULL,
	"status" text DEFAULT 'running' NOT NULL,
	"started_at" timestamp with time zone NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
	"finished_at" timestamp with time zone,
	"backfill_start_date" date,
	"backfill_end_date" date,
	"reached_date" date,
	"pages_processed" integer DEFAULT 0 NOT NULL,
	"total_pages" integer,
	"items_processed" integer DEFAULT 0 NOT NULL,
	"last_cursor" jsonb DEFAULT '{}'::jsonb NOT NULL,
	"last_successful_page" integer,
	"runs" integer DEFAULT 0 NOT NULL,
	"errors" integer DEFAULT 0 NOT NULL,
	"retry_count" integer DEFAULT 0 NOT NULL,
	"last_error" text,
	"percent" real
);
--> statement-breakpoint
CREATE TABLE "connector_field_stats" (
	"connector_id" text NOT NULL,
	"day" date NOT NULL,
	"field" text NOT NULL,
	"total" integer DEFAULT 0 NOT NULL,
	"nulls" integer DEFAULT 0 NOT NULL,
	CONSTRAINT "connector_field_stats_connector_id_day_field_pk" PRIMARY KEY("connector_id","day","field")
);
--> statement-breakpoint
ALTER TABLE "connectors" ADD COLUMN "meta" jsonb DEFAULT '{}'::jsonb NOT NULL;--> statement-breakpoint
CREATE INDEX "certificate_sightings_cert_idx" ON "certificate_sightings" USING btree ("certificate_id","observed_at");--> statement-breakpoint
CREATE UNIQUE INDEX "certificate_sightings_uq" ON "certificate_sightings" USING btree ("certificate_id","kind","target_id");--> statement-breakpoint
CREATE UNIQUE INDEX "certificates_grader_cert_uq" ON "certificates" USING btree ("grader","cert_number");--> statement-breakpoint
CREATE INDEX "certificates_asset_idx" ON "certificates" USING btree ("asset_id");--> statement-breakpoint
CREATE INDEX "certificates_last_seen_idx" ON "certificates" USING btree ("last_seen_at");--> statement-breakpoint
CREATE INDEX "connector_backfills_connector_idx" ON "connector_backfills" USING btree ("connector_id","status");