CREATE TABLE "drug_codes" (
	"id" bigserial PRIMARY KEY NOT NULL,
	"drug_id" varchar(32) NOT NULL,
	"system" text NOT NULL,
	"code" text NOT NULL,
	"label" text,
	"match_type" text DEFAULT 'EXACT_IDENTIFIER' NOT NULL,
	"source_id" varchar(32)
);
--> statement-breakpoint
CREATE TABLE "drug_pipeline" (
	"id" bigserial PRIMARY KEY NOT NULL,
	"drug_id" varchar(32) NOT NULL,
	"cancer_id" varchar(32),
	"stage" text NOT NULL,
	"max_phase" text,
	"active_trials" integer DEFAULT 0 NOT NULL,
	"recruiting_trials" integer DEFAULT 0 NOT NULL,
	"phase3_trials" integer DEFAULT 0 NOT NULL,
	"total_trials" integer DEFAULT 0 NOT NULL,
	"approvals" integer DEFAULT 0 NOT NULL,
	"jurisdictions" text[] DEFAULT '{}' NOT NULL,
	"first_approval_date" text,
	"latest_approval_date" text,
	"first_trial_date" text,
	"formula_version" text NOT NULL,
	"inputs" jsonb DEFAULT '{}'::jsonb NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "research_gap_components" (
	"id" bigserial PRIMARY KEY NOT NULL,
	"cancer_id" varchar(32) NOT NULL,
	"geography" text NOT NULL,
	"year" integer NOT NULL,
	"sex" text DEFAULT 'all' NOT NULL,
	"burden_source_id" varchar(32) NOT NULL,
	"deaths" double precision,
	"incidence" double precision,
	"active_trials" integer DEFAULT 0 NOT NULL,
	"phase3_trials" integer DEFAULT 0 NOT NULL,
	"publications_5y" integer DEFAULT 0 NOT NULL,
	"approved_drugs" integer DEFAULT 0 NOT NULL,
	"death_share" real,
	"trial_share" real,
	"publication_share" real,
	"trial_gap_ratio" real,
	"research_gap_ratio" real,
	"trials_per_1000_deaths" real,
	"publications_per_1000_deaths" real,
	"eligible" boolean DEFAULT true NOT NULL,
	"ineligible_reason" text,
	"formula_version" text NOT NULL,
	"inputs" jsonb DEFAULT '{}'::jsonb NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "trial_intelligence" (
	"id" bigserial PRIMARY KEY NOT NULL,
	"cancer_id" varchar(32) NOT NULL,
	"entity_level" text DEFAULT 'all' NOT NULL,
	"total_trials" integer DEFAULT 0 NOT NULL,
	"active_trials" integer DEFAULT 0 NOT NULL,
	"recruiting_trials" integer DEFAULT 0 NOT NULL,
	"phase1_active" integer DEFAULT 0 NOT NULL,
	"phase2_active" integer DEFAULT 0 NOT NULL,
	"phase3_active" integer DEFAULT 0 NOT NULL,
	"phase3_recruiting" integer DEFAULT 0 NOT NULL,
	"phase4_active" integer DEFAULT 0 NOT NULL,
	"completed_trials" integer DEFAULT 0 NOT NULL,
	"terminated_trials" integer DEFAULT 0 NOT NULL,
	"withdrawn_trials" integer DEFAULT 0 NOT NULL,
	"suspended_trials" integer DEFAULT 0 NOT NULL,
	"with_results" integer DEFAULT 0 NOT NULL,
	"new_trials_12m" integer DEFAULT 0 NOT NULL,
	"new_trials_prior_12m" integer DEFAULT 0 NOT NULL,
	"trial_growth_yoy" real,
	"avg_enrollment" real,
	"median_enrollment" real,
	"total_enrollment_active" integer,
	"distinct_sponsors" integer DEFAULT 0 NOT NULL,
	"industry_share" real,
	"sponsor_hhi" real,
	"top_sponsor" text,
	"top_sponsor_share" real,
	"distinct_countries" integer DEFAULT 0 NOT NULL,
	"us_share" real,
	"top_country" text,
	"top_country_share" real,
	"country_hhi" real,
	"termination_share" real,
	"why_stopped_breakdown" jsonb DEFAULT '{}'::jsonb NOT NULL,
	"trials_per_1000_deaths" real,
	"trials_per_100k_cases" real,
	"burden_geography" text,
	"burden_year" integer,
	"burden_source_id" varchar(32),
	"formula_version" text NOT NULL,
	"inputs" jsonb DEFAULT '{}'::jsonb NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "trial_site_country_counts" (
	"id" bigserial PRIMARY KEY NOT NULL,
	"cancer_id" varchar(32),
	"phase" text,
	"recruiting_only" boolean DEFAULT false NOT NULL,
	"country" text NOT NULL,
	"iso3" text,
	"sites" integer NOT NULL,
	"trials" integer NOT NULL,
	"formula_version" text NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE UNIQUE INDEX "drug_codes_uq" ON "drug_codes" USING btree ("drug_id","system","code");--> statement-breakpoint
CREATE INDEX "drug_codes_lookup_idx" ON "drug_codes" USING btree ("system","code");--> statement-breakpoint
CREATE UNIQUE INDEX "drug_pipeline_uq" ON "drug_pipeline" USING btree ("drug_id","cancer_id");--> statement-breakpoint
CREATE INDEX "drug_pipeline_stage_idx" ON "drug_pipeline" USING btree ("stage");--> statement-breakpoint
CREATE INDEX "drug_pipeline_cancer_idx" ON "drug_pipeline" USING btree ("cancer_id","stage");--> statement-breakpoint
CREATE UNIQUE INDEX "research_gap_components_uq" ON "research_gap_components" USING btree ("cancer_id","geography","year","sex","burden_source_id");--> statement-breakpoint
CREATE INDEX "research_gap_scope_idx" ON "research_gap_components" USING btree ("geography","year","sex");--> statement-breakpoint
CREATE UNIQUE INDEX "trial_intelligence_uq" ON "trial_intelligence" USING btree ("cancer_id","entity_level");--> statement-breakpoint
CREATE INDEX "trial_intelligence_active_idx" ON "trial_intelligence" USING btree ("entity_level","active_trials");--> statement-breakpoint
CREATE UNIQUE INDEX "trial_site_country_uq" ON "trial_site_country_counts" USING btree ("cancer_id","phase","recruiting_only","country");--> statement-breakpoint
CREATE INDEX "trial_site_country_lookup_idx" ON "trial_site_country_counts" USING btree ("cancer_id","phase","recruiting_only");