-- MCP Case Knowledge System - Initial Schema
-- Generated on 2025-01-22

CREATE TABLE `cases` (
	`id` text PRIMARY KEY NOT NULL,
	`title` text NOT NULL,
	`category` text NOT NULL,
	`problem_description` text NOT NULL,
	`analysis` text NOT NULL,
	`solution` text NOT NULL,
	`metadata` text NOT NULL,
	`created_at` integer NOT NULL,
	`updated_at` integer NOT NULL
);

CREATE INDEX `cases_category_idx` ON `cases` (`category`);
CREATE INDEX `cases_created_at_idx` ON `cases` (`created_at`);
CREATE INDEX `cases_updated_at_idx` ON `cases` (`updated_at`);
CREATE INDEX `cases_title_idx` ON `cases` (`title`);

CREATE TABLE `case_tags` (
	`case_id` text NOT NULL,
	`tag` text NOT NULL,
	PRIMARY KEY(`case_id`, `tag`),
	FOREIGN KEY (`case_id`) REFERENCES `cases`(`id`) ON UPDATE no action ON DELETE cascade
);

CREATE INDEX `case_tags_case_id_idx` ON `case_tags` (`case_id`);
CREATE INDEX `case_tags_tag_idx` ON `case_tags` (`tag`);

CREATE TABLE `case_statistics` (
	`case_id` text PRIMARY KEY NOT NULL,
	`view_count` integer DEFAULT 0 NOT NULL,
	`applied_count` integer DEFAULT 0 NOT NULL,
	`success_rate` real DEFAULT 0 NOT NULL,
	`avg_time_to_resolve` integer,
	`last_used` integer,
	`helpful_ratings` integer DEFAULT 0 NOT NULL,
	`not_helpful_ratings` integer DEFAULT 0 NOT NULL,
	`updated_at` integer NOT NULL,
	FOREIGN KEY (`case_id`) REFERENCES `cases`(`id`) ON UPDATE no action ON DELETE cascade
);

CREATE INDEX `case_statistics_last_used_idx` ON `case_statistics` (`last_used`);
CREATE INDEX `case_statistics_success_rate_idx` ON `case_statistics` (`success_rate`);
CREATE INDEX `case_statistics_applied_count_idx` ON `case_statistics` (`applied_count`);

CREATE TABLE `search_index` (
	`case_id` text PRIMARY KEY NOT NULL,
	`content` text NOT NULL,
	`embedding` text,
	`keywords` text,
	`indexed_at` integer NOT NULL,
	FOREIGN KEY (`case_id`) REFERENCES `cases`(`id`) ON UPDATE no action ON DELETE cascade
);

CREATE INDEX `search_index_content_idx` ON `search_index` (`content`);
CREATE INDEX `search_index_indexed_at_idx` ON `search_index` (`indexed_at`);

CREATE TABLE `application_history` (
	`id` text PRIMARY KEY NOT NULL,
	`case_id` text NOT NULL,
	`applied_at` integer NOT NULL,
	`success` integer NOT NULL,
	`time_to_resolve` integer,
	`feedback` text,
	`context` text,
	`adaptations` text,
	FOREIGN KEY (`case_id`) REFERENCES `cases`(`id`) ON UPDATE no action ON DELETE cascade
);

CREATE INDEX `application_history_case_id_idx` ON `application_history` (`case_id`);
CREATE INDEX `application_history_applied_at_idx` ON `application_history` (`applied_at`);
CREATE INDEX `application_history_success_idx` ON `application_history` (`success`);

CREATE TABLE `system_config` (
	`key` text PRIMARY KEY NOT NULL,
	`value` text NOT NULL,
	`description` text,
	`updated_at` integer NOT NULL
);

CREATE TABLE `performance_metrics` (
	`id` text PRIMARY KEY NOT NULL,
	`operation` text NOT NULL,
	`start_time` integer NOT NULL,
	`end_time` integer NOT NULL,
	`duration` integer NOT NULL,
	`success` integer NOT NULL,
	`metrics` text,
	`recorded_at` integer NOT NULL
);

CREATE INDEX `performance_metrics_operation_idx` ON `performance_metrics` (`operation`);
CREATE INDEX `performance_metrics_recorded_at_idx` ON `performance_metrics` (`recorded_at`);
CREATE INDEX `performance_metrics_duration_idx` ON `performance_metrics` (`duration`);