CREATE TABLE `research_artifact` (
	`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
	`user_id` text NOT NULL,
	`organization_id` text,
	`ticker` text NOT NULL,
	`accession_number` text,
	`kind` text NOT NULL,
	`source` text NOT NULL DEFAULT 'user',
	`subtype` text,
	`title` text,
	`summary` text,
	`body_markdown` text,
	`search_text` text,
	`visibility_scope` text NOT NULL DEFAULT 'private',
	`tags` text,
	`metadata` text,
	`file_name` text,
	`mime_type` text,
	`file_size_bytes` integer,
	`storage_path` text,
	`created_at` text NOT NULL,
	`updated_at` text NOT NULL,
	FOREIGN KEY (`user_id`) REFERENCES `user`(`id`) ON UPDATE no action ON DELETE cascade,
	FOREIGN KEY (`organization_id`) REFERENCES `organization`(`id`) ON UPDATE no action ON DELETE set null
);
--> statement-breakpoint
CREATE INDEX `research_artifact_ticker_idx` ON `research_artifact` (`user_id`,`ticker`,`updated_at`);
--> statement-breakpoint
CREATE INDEX `research_artifact_kind_idx` ON `research_artifact` (`user_id`,`kind`,`updated_at`);
--> statement-breakpoint
CREATE INDEX `research_artifact_accession_idx` ON `research_artifact` (`user_id`,`accession_number`);
--> statement-breakpoint
CREATE INDEX `research_artifact_source_idx` ON `research_artifact` (`user_id`,`source`,`updated_at`);
--> statement-breakpoint

CREATE TABLE `research_memo` (
	`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
	`user_id` text NOT NULL,
	`organization_id` text,
	`ticker` text NOT NULL,
	`rating` text,
	`conviction` text,
	`time_horizon_months` integer,
	`packet_title` text,
	`packet_subtitle` text,
	`thesis_markdown` text NOT NULL DEFAULT '',
	`variant_view_markdown` text NOT NULL DEFAULT '',
	`catalysts_markdown` text NOT NULL DEFAULT '',
	`risks_markdown` text NOT NULL DEFAULT '',
	`disconfirming_evidence_markdown` text NOT NULL DEFAULT '',
	`next_actions_markdown` text NOT NULL DEFAULT '',
	`created_at` text NOT NULL,
	`updated_at` text NOT NULL,
	FOREIGN KEY (`user_id`) REFERENCES `user`(`id`) ON UPDATE no action ON DELETE cascade,
	FOREIGN KEY (`organization_id`) REFERENCES `organization`(`id`) ON UPDATE no action ON DELETE set null
);
--> statement-breakpoint
CREATE UNIQUE INDEX `research_memo_ticker_uidx` ON `research_memo` (`user_id`,`ticker`);
--> statement-breakpoint
CREATE INDEX `research_memo_updated_idx` ON `research_memo` (`user_id`,`updated_at`);
--> statement-breakpoint

CREATE TABLE `research_memo_evidence` (
	`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
	`memo_id` integer NOT NULL,
	`artifact_id` integer NOT NULL,
	`section` text NOT NULL,
	`annotation` text,
	`sort_order` integer NOT NULL DEFAULT 0,
	`created_at` text NOT NULL,
	FOREIGN KEY (`memo_id`) REFERENCES `research_memo`(`id`) ON UPDATE no action ON DELETE cascade,
	FOREIGN KEY (`artifact_id`) REFERENCES `research_artifact`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
CREATE INDEX `research_memo_evidence_memo_idx` ON `research_memo_evidence` (`memo_id`,`section`,`sort_order`);
--> statement-breakpoint
CREATE INDEX `research_memo_evidence_artifact_idx` ON `research_memo_evidence` (`artifact_id`);
--> statement-breakpoint
CREATE UNIQUE INDEX `research_memo_evidence_unique_uidx` ON `research_memo_evidence` (`memo_id`,`artifact_id`,`section`);
--> statement-breakpoint

CREATE VIRTUAL TABLE `research_artifact_fts` USING fts5(
	artifact_id UNINDEXED,
	user_id UNINDEXED,
	ticker UNINDEXED,
	title,
	summary,
	body_markdown,
	search_text,
	tags_text
);