-- Create the database (run manually if needed)
-- CREATE DATABASE music_library;
-- USE music_library;

-- Drop table if exists
DROP TABLE IF EXISTS tracks;

-- Create tracks table
CREATE TABLE tracks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    album VARCHAR(255) NOT NULL,
    bpm INT NOT NULL,
    music_key VARCHAR(10) NOT NULL,
    genre VARCHAR(100) NOT NULL,
    mood VARCHAR(100) NOT NULL,
    brightness FLOAT NOT NULL CHECK (brightness >= 0.0 AND brightness <= 1.0),
    saturation FLOAT NOT NULL CHECK (saturation >= 0.0 AND saturation <= 1.0),
    shape ENUM('circle', 'square', 'triangle', 'hex') NOT NULL,
    level ENUM('high', 'loud', 'middle', 'soft', 'extra', 'slow') NOT NULL,
    position INT NOT NULL DEFAULT 0,
    cover_url VARCHAR(500) DEFAULT NULL,
    preview_url VARCHAR(500) DEFAULT NULL,
    artist VARCHAR(255) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_position (position),
    INDEX idx_mood (mood),
    INDEX idx_genre (genre)
);

-- Seed data with sample tracks
INSERT INTO tracks (name, album, bpm, music_key, genre, mood, brightness, saturation, shape, level, position) VALUES
('Sunrise', 'Dawn EP', 120, 'Fm', 'Ambient', 'Calm', 0.7, 0.6, 'circle', 'middle', 1),
('Midnight Drive', 'Nocturne', 95, 'Am', 'Electronic', 'Melancholic', 0.3, 0.8, 'square', 'soft', 2),
('Summer Breeze', 'Seasons', 128, 'C', 'Pop', 'Happy', 0.9, 0.9, 'circle', 'loud', 3),
('Deep Thoughts', 'Introspection', 80, 'Dm', 'Lo-Fi', 'Thoughtful', 0.4, 0.5, 'triangle', 'extra', 4),
('Energy Boost', 'Workout Mix', 140, 'G', 'EDM', 'Energetic', 1.0, 1.0, 'hex', 'high', 5),
('Rainy Day', 'Weather Moods', 75, 'Em', 'Acoustic', 'Sad', 0.2, 0.4, 'square', 'slow', 6),
('City Lights', 'Urban Stories', 110, 'Bb', 'Hip-Hop', 'Cool', 0.6, 0.7, 'hex', 'middle', 7),
('Ocean Waves', 'Nature Sounds', 60, 'F', 'Ambient', 'Peaceful', 0.5, 0.3, 'circle', 'soft', 8);
