-- Enhance timeline_items table with rich historical data fields
-- Adds time ranges, categorization, importance, and related data arrays

-- Add time range columns
ALTER TABLE timeline_items ADD COLUMN IF NOT EXISTS time_from TIMESTAMP;
ALTER TABLE timeline_items ADD COLUMN IF NOT EXISTS time_to TIMESTAMP;

-- Add categorization columns
ALTER TABLE timeline_items ADD COLUMN IF NOT EXISTS category VARCHAR(50);
ALTER TABLE timeline_items ADD COLUMN IF NOT EXISTS kind VARCHAR(20);
ALTER TABLE timeline_items ADD COLUMN IF NOT EXISTS is_historical BOOLEAN;
ALTER TABLE timeline_items ADD COLUMN IF NOT EXISTS heritage BOOLEAN;

-- Add importance column
ALTER TABLE timeline_items ADD COLUMN IF NOT EXISTS importance INTEGER DEFAULT 1;

-- Add summary column (separate from content)
ALTER TABLE timeline_items ADD COLUMN IF NOT EXISTS summary TEXT;

-- Add JSONB columns for arrays
ALTER TABLE timeline_items ADD COLUMN IF NOT EXISTS locations JSONB DEFAULT '[]'::jsonb;
ALTER TABLE timeline_items ADD COLUMN IF NOT EXISTS actors JSONB DEFAULT '[]'::jsonb;
ALTER TABLE timeline_items ADD COLUMN IF NOT EXISTS related JSONB DEFAULT '[]'::jsonb;
ALTER TABLE timeline_items ADD COLUMN IF NOT EXISTS tags JSONB DEFAULT '[]'::jsonb;

-- Add indexes for commonly queried fields
CREATE INDEX IF NOT EXISTS idx_timeline_items_category ON timeline_items(category);
CREATE INDEX IF NOT EXISTS idx_timeline_items_kind ON timeline_items(kind);
CREATE INDEX IF NOT EXISTS idx_timeline_items_is_historical ON timeline_items(is_historical);
CREATE INDEX IF NOT EXISTS idx_timeline_items_importance ON timeline_items(importance);
CREATE INDEX IF NOT EXISTS idx_timeline_items_time_from ON timeline_items(time_from);
CREATE INDEX IF NOT EXISTS idx_timeline_items_time_to ON timeline_items(time_to);

-- Add GIN indexes for JSONB array columns
CREATE INDEX IF NOT EXISTS idx_timeline_items_locations ON timeline_items USING GIN(locations);
CREATE INDEX IF NOT EXISTS idx_timeline_items_actors ON timeline_items USING GIN(actors);
CREATE INDEX IF NOT EXISTS idx_timeline_items_tags ON timeline_items USING GIN(tags);