CREATE TABLE `daily_prices` (
	`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
	`item_id` integer NOT NULL,
	`date` text NOT NULL,
	`open_value` real,
	`close_value` real,
	`high_value` real,
	`low_value` real,
	`avg_volume` real,
	`chaos_rate` real,
	FOREIGN KEY (`item_id`) REFERENCES `items`(`id`) ON UPDATE no action ON DELETE no action
);
--> statement-breakpoint
CREATE UNIQUE INDEX `idx_daily_prices_item_date` ON `daily_prices` (`item_id`,`date`);--> statement-breakpoint
CREATE TABLE `items` (
	`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
	`external_id` text NOT NULL,
	`details_id` text NOT NULL,
	`league_id` integer NOT NULL,
	`category` text NOT NULL,
	`name` text NOT NULL,
	`icon_url` text,
	`created_at` integer,
	FOREIGN KEY (`league_id`) REFERENCES `leagues`(`id`) ON UPDATE no action ON DELETE no action
);
--> statement-breakpoint
CREATE INDEX `idx_items_league_category` ON `items` (`league_id`,`category`);--> statement-breakpoint
CREATE UNIQUE INDEX `idx_items_external_league` ON `items` (`external_id`,`league_id`);--> statement-breakpoint
CREATE TABLE `leagues` (
	`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
	`name` text NOT NULL,
	`display_name` text NOT NULL,
	`start_date` integer,
	`end_date` integer,
	`is_active` integer DEFAULT true,
	`created_at` integer
);
--> statement-breakpoint
CREATE UNIQUE INDEX `leagues_name_unique` ON `leagues` (`name`);--> statement-breakpoint
CREATE TABLE `price_history` (
	`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
	`item_id` integer NOT NULL,
	`snapshot_id` integer,
	`divine_value` real,
	`volume` real,
	`change_7d` real,
	`sparkline_data` text,
	`exalted_rate` real,
	`chaos_rate` real,
	`recorded_at` integer NOT NULL,
	FOREIGN KEY (`item_id`) REFERENCES `items`(`id`) ON UPDATE no action ON DELETE no action,
	FOREIGN KEY (`snapshot_id`) REFERENCES `snapshots`(`id`) ON UPDATE no action ON DELETE no action
);
--> statement-breakpoint
CREATE INDEX `idx_price_history_item_recorded` ON `price_history` (`item_id`,`recorded_at`);--> statement-breakpoint
CREATE INDEX `idx_price_history_snapshot` ON `price_history` (`snapshot_id`);--> statement-breakpoint
CREATE TABLE `snapshots` (
	`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
	`league_id` integer NOT NULL,
	`category` text NOT NULL,
	`scraped_at` integer NOT NULL,
	`status` text DEFAULT 'pending',
	`item_count` integer DEFAULT 0,
	`error_message` text,
	FOREIGN KEY (`league_id`) REFERENCES `leagues`(`id`) ON UPDATE no action ON DELETE no action
);