CREATE TABLE `filing_taxonomy_snapshot` (
	`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
	`filing_id` integer NOT NULL,
	`ticker` text NOT NULL,
	`filing_date` text NOT NULL,
	`filing_type` text NOT NULL,
	`parse_status` text NOT NULL,
	`parse_error` text,
	`source` text NOT NULL,
	`periods` text,
	`statement_rows` text,
	`derived_metrics` text,
	`validation_result` text,
	`facts_count` integer DEFAULT 0 NOT NULL,
	`concepts_count` integer DEFAULT 0 NOT NULL,
	`dimensions_count` integer DEFAULT 0 NOT NULL,
	`created_at` text NOT NULL,
	`updated_at` text NOT NULL,
	FOREIGN KEY (`filing_id`) REFERENCES `filing`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
CREATE UNIQUE INDEX `filing_taxonomy_snapshot_filing_uidx` ON `filing_taxonomy_snapshot` (`filing_id`);
--> statement-breakpoint
CREATE INDEX `filing_taxonomy_snapshot_ticker_date_idx` ON `filing_taxonomy_snapshot` (`ticker`,`filing_date`);
--> statement-breakpoint
CREATE INDEX `filing_taxonomy_snapshot_status_idx` ON `filing_taxonomy_snapshot` (`parse_status`);
--> statement-breakpoint
CREATE TABLE `filing_taxonomy_asset` (
	`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
	`snapshot_id` integer NOT NULL,
	`asset_type` text NOT NULL,
	`name` text NOT NULL,
	`url` text NOT NULL,
	`size_bytes` integer,
	`score` numeric,
	`is_selected` integer DEFAULT false NOT NULL,
	`created_at` text NOT NULL,
	FOREIGN KEY (`snapshot_id`) REFERENCES `filing_taxonomy_snapshot`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
CREATE INDEX `filing_taxonomy_asset_snapshot_idx` ON `filing_taxonomy_asset` (`snapshot_id`);
--> statement-breakpoint
CREATE INDEX `filing_taxonomy_asset_type_idx` ON `filing_taxonomy_asset` (`snapshot_id`,`asset_type`);
--> statement-breakpoint
CREATE TABLE `filing_taxonomy_concept` (
	`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
	`snapshot_id` integer NOT NULL,
	`concept_key` text NOT NULL,
	`qname` text NOT NULL,
	`namespace_uri` text NOT NULL,
	`local_name` text NOT NULL,
	`label` text,
	`is_extension` integer DEFAULT false NOT NULL,
	`statement_kind` text,
	`role_uri` text,
	`presentation_order` numeric,
	`presentation_depth` integer,
	`parent_concept_key` text,
	`is_abstract` integer DEFAULT false NOT NULL,
	`created_at` text NOT NULL,
	FOREIGN KEY (`snapshot_id`) REFERENCES `filing_taxonomy_snapshot`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
CREATE INDEX `filing_taxonomy_concept_snapshot_idx` ON `filing_taxonomy_concept` (`snapshot_id`);
--> statement-breakpoint
CREATE INDEX `filing_taxonomy_concept_statement_idx` ON `filing_taxonomy_concept` (`snapshot_id`,`statement_kind`);
--> statement-breakpoint
CREATE UNIQUE INDEX `filing_taxonomy_concept_uidx` ON `filing_taxonomy_concept` (`snapshot_id`,`concept_key`,`role_uri`,`presentation_order`);
--> statement-breakpoint
CREATE TABLE `filing_taxonomy_fact` (
	`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
	`snapshot_id` integer NOT NULL,
	`concept_key` text NOT NULL,
	`qname` text NOT NULL,
	`namespace_uri` text NOT NULL,
	`local_name` text NOT NULL,
	`statement_kind` text,
	`role_uri` text,
	`context_id` text NOT NULL,
	`unit` text,
	`decimals` text,
	`value_num` numeric NOT NULL,
	`period_start` text,
	`period_end` text,
	`period_instant` text,
	`dimensions` text NOT NULL,
	`is_dimensionless` integer DEFAULT true NOT NULL,
	`source_file` text,
	`created_at` text NOT NULL,
	FOREIGN KEY (`snapshot_id`) REFERENCES `filing_taxonomy_snapshot`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
CREATE INDEX `filing_taxonomy_fact_snapshot_idx` ON `filing_taxonomy_fact` (`snapshot_id`);
--> statement-breakpoint
CREATE INDEX `filing_taxonomy_fact_concept_idx` ON `filing_taxonomy_fact` (`snapshot_id`,`concept_key`);
--> statement-breakpoint
CREATE INDEX `filing_taxonomy_fact_period_idx` ON `filing_taxonomy_fact` (`snapshot_id`,`period_end`,`period_instant`);
--> statement-breakpoint
CREATE INDEX `filing_taxonomy_fact_statement_idx` ON `filing_taxonomy_fact` (`snapshot_id`,`statement_kind`);
--> statement-breakpoint
CREATE TABLE `filing_taxonomy_metric_validation` (
	`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
	`snapshot_id` integer NOT NULL,
	`metric_key` text NOT NULL,
	`taxonomy_value` numeric,
	`llm_value` numeric,
	`absolute_diff` numeric,
	`relative_diff` numeric,
	`status` text NOT NULL,
	`evidence_pages` text NOT NULL,
	`pdf_url` text,
	`provider` text,
	`model` text,
	`error` text,
	`created_at` text NOT NULL,
	`updated_at` text NOT NULL,
	FOREIGN KEY (`snapshot_id`) REFERENCES `filing_taxonomy_snapshot`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
CREATE INDEX `filing_taxonomy_metric_validation_snapshot_idx` ON `filing_taxonomy_metric_validation` (`snapshot_id`);
--> statement-breakpoint
CREATE INDEX `filing_taxonomy_metric_validation_status_idx` ON `filing_taxonomy_metric_validation` (`snapshot_id`,`status`);
--> statement-breakpoint
CREATE UNIQUE INDEX `filing_taxonomy_metric_validation_uidx` ON `filing_taxonomy_metric_validation` (`snapshot_id`,`metric_key`);