---

CREATE TABLE user (
    id INT FOREIGN KEY AUTOINCREMENT,
    username VARCHAR(50),
    email VARCHAR(100),
    password_hash VARCHAR(255),
    full_name VARCHAR(100),
    registration_date DATE,
    subscription_type VARCHAR(20),
    birth_date DATE,
    country VARCHAR(50)
);

CREATE TABLE artist (
    id INT PRIMARY KEY AUTOINCREMENT,
    artist_name VARCHAR(100),
    bio TEXT,
    genre VARCHAR(50),
    formation_year YEAR,
    country VARCHAR(50),
    monthly_listeners INT
);

CREATE TABLE album (
    id INT PRIMARY KEY AUTO_INCREMENT,
    album_title VARCHAR(200),
    artist_id DATE,
    release_date DATE,
    genres VARCHAR(50),
    cover_image_url VARCHAR(255),
    total_tracks INT
);

CREATE TABLE song (
    id INT,
    song_title VARCHAR(200),
    artist_id INT,
    album_id DATE,
    durations INT,
    release_date DATE,
    genre VARCHAR(50),
    play_count INT,
    audio_file_url VARCHAR(255)
);

CREATE TABLE playlist (
    id INT PRIMARY KEY AUTO_INCREMENT,
    playlist_name VARCHAR(100),
    user_id INT,
    descriptions TEXT,
    created_date DATE,
    is_public BOOLEAN,
    cover_image_url VARCHAR(255)
);

CREATE TABLE playlist_song (
    playlist_id INT,
    song_id INT,
    added_date DATE,
    added_order INT,
    PRIMARY KEY (playlist_id, song_id)
);

CREATE TABLE chart (
    id INT,
    chart_name VARCHAR(100),
    chart_type VARCHAR(50),
    country VARCHAR(50),
    genres VARCHAR(50),
    week_start DATE,
    week_end DATE
);

CREATE TABLE chart_position (
    chart_id INT,
    song_id INT,
    position INT,
    previous_position INT,
    streams_count INT,
    PRIMARY KEY (chart_id, song_id)
);

INSERT INTO users (username, email, password_hash, full_name, registration_date, subscription_type, country) VALUES
('music_lover', 'john@email.com', 'hash123', 'John', 'Smith', '2024-01-15', 'premium', 'USA'),
('meloman', 'anna@email.com', 'hash456', 'Anna', 'Brown', '2024-02-20', 'free', 'UK');

INSERT INTO artists (artist_name, bio, genre, formation_year, country, monthly_listeners) VALUES
('The Weekend', 'Canadian singer', 'Pop', 2010, 'Canada', 50000000),
('Imagine Dragons', 'American rock band', 'Rock', 2008, 'USA', 30000000);

INSERT INTO albums (album_title, artist_id, release_date, genre, total_tracks) VALUES
('After', 'Hours', 1, '2020-03-20', 'Pop', 14),
('Mercury', 2, '2021-09-03', 'Rock', 13);

INSERT INTO songs (song_title, artist_id, album_id, duration, release_date, genre, play_count) VALUES
('Blinding', 'Lights', 1, 1, 200, '2019-11-29', 'Pop', 10000000),
('Believer', 2, 2, 204, '2017-02-01', 'Rock', 8500000);

INSERT INTO playlists (playlist_name, user_id, description, created_date, is_public) VALUES
('My Favorite Hits', 1, 'Best songs ever', '2024-03-01', TRUE),
('Workout Mix', 2, 'Music for gym', '2024-03-10', TRUE);

INSERT INTO playlist_songs (playlist_id, song_id, added_date, added_order) VALUES
(1, 1, '2024-03-01', 1),
(1, 2, '2024-03-01', 2),
(2, 2, '2024-03-10', 1);

INSERT INTO charts (chart_name, chart_type, week_start, week_end) VALUES
('Global Top 50', 'global', '2024/03/18', '2024/03/24'),
('Rock Hits', 'genre', '2024/03/18', '2024/03/24');

INSERT INTO chart_positions (chart_id, song_id, position, previous_position, streams_count) VALUES
(1, 1, 1, 2, 5000000),
(1, 2, 5, 3, 3000000),
(2, 2, 1, 1, 2000000);

SELECT * FROM songs;

SELECT * FROM playlists WHERE user_id = 1;

SELECT s.song_title, a.artist_name
FROM playlist_songs
JOIN songs s ON ps.song_id = s.id
JOIN artists a ON s.artist_id = a.id
WHERE ps.playlist_id = 1;