-- Watchlist Groups Migration
-- MySQL / MariaDB
-- Run: mysql -u root koinkamu < migrations/002_watchlist_groups.sql

USE koinkamu;

-- Grup watchlist
CREATE TABLE IF NOT EXISTS watchlist_groups (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    display_order INT DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Add group_id to watchlist
ALTER TABLE watchlist ADD COLUMN group_id INT NULL AFTER coin_id;
ALTER TABLE watchlist ADD CONSTRAINT fk_watchlist_group
    FOREIGN KEY (group_id) REFERENCES watchlist_groups(id) ON DELETE SET NULL;

-- Drop old unique on coin_id, add composite unique
ALTER TABLE watchlist DROP INDEX coin_id;
ALTER TABLE watchlist ADD UNIQUE KEY uq_watchlist_coin_group (coin_id, group_id);