CREATE TABLE `diseases` (
	`id` text PRIMARY KEY NOT NULL,
	`plant_id` text NOT NULL,
	`name` text NOT NULL,
	`scientific_name` text DEFAULT '' NOT NULL,
	`causal_agent_type` text NOT NULL,
	`description` text DEFAULT '' NOT NULL,
	`symptoms` text DEFAULT '[]' NOT NULL,
	`causes` text DEFAULT '[]' NOT NULL,
	`treatment` text DEFAULT '[]' NOT NULL,
	`prevention` text DEFAULT '[]' NOT NULL,
	`lookalike_ids` text DEFAULT '[]' NOT NULL,
	`severity` text NOT NULL,
	`source_url` text DEFAULT '' NOT NULL,
	`created_at` text DEFAULT (datetime('now')) NOT NULL,
	`updated_at` text DEFAULT (datetime('now')) NOT NULL,
	FOREIGN KEY (`plant_id`) REFERENCES `plants`(`id`) ON UPDATE no action ON DELETE no action
);
--> statement-breakpoint
CREATE INDEX `idx_diseases_plant_id` ON `diseases` (`plant_id`);--> statement-breakpoint
CREATE INDEX `idx_diseases_causal_agent` ON `diseases` (`causal_agent_type`);--> statement-breakpoint
CREATE INDEX `idx_diseases_severity` ON `diseases` (`severity`);--> statement-breakpoint
CREATE TABLE `plants` (
	`id` text PRIMARY KEY NOT NULL,
	`common_name` text NOT NULL,
	`scientific_name` text NOT NULL,
	`family` text NOT NULL,
	`category` text NOT NULL,
	`care_summary` text DEFAULT '' NOT NULL,
	`image_url` text DEFAULT '' NOT NULL,
	`created_at` text DEFAULT (datetime('now')) NOT NULL,
	`updated_at` text DEFAULT (datetime('now')) NOT NULL
);
--> statement-breakpoint
CREATE INDEX `idx_plants_category` ON `plants` (`category`);--> statement-breakpoint
CREATE INDEX `idx_plants_common_name` ON `plants` (`common_name`);--> statement-breakpoint
CREATE TABLE `scrape_sources` (
	`id` text PRIMARY KEY NOT NULL,
	`source_type` text NOT NULL,
	`source_url` text NOT NULL,
	`last_scraped_at` text,
	`entries_count` integer DEFAULT 0,
	`status` text DEFAULT 'pending' NOT NULL,
	`error_message` text,
	`created_at` text DEFAULT (datetime('now')) NOT NULL
);