USE ibrain_db;

CREATE TABLE IF NOT EXISTS item_types (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE,
    icon VARCHAR(50) DEFAULT 'tag',
    color VARCHAR(20) DEFAULT '#3b82f6',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert initial types
INSERT IGNORE INTO item_types (name, icon, color) VALUES 
('Link', 'link', '#3b82f6'),
('Video', 'play-circle', '#ef4444'),
('Artículo', 'newspaper', '#10b981'),
('Página', 'globe', '#8b5cf6'),
('Prompt', 'terminal', '#f59e0b');

-- Add type_id to items
ALTER TABLE items ADD COLUMN type_id INT AFTER category_id;

-- Seed type_id based on legacy type ENUM
UPDATE items SET type_id = (SELECT id FROM item_types WHERE name = 'Link' LIMIT 1) WHERE type = 'page';
UPDATE items SET type_id = (SELECT id FROM item_types WHERE name = 'Video' LIMIT 1) WHERE type = 'video';
UPDATE items SET type_id = (SELECT id FROM item_types WHERE name = 'Artículo' LIMIT 1) WHERE type = 'article';
UPDATE items SET type_id = (SELECT id FROM item_types WHERE name = 'Prompt' LIMIT 1) WHERE type = 'prompt';

-- Optional: Add foreign key
ALTER TABLE items ADD CONSTRAINT fk_items_type FOREIGN KEY (type_id) REFERENCES item_types(id) ON DELETE SET NULL;