-- =====================================
-- 🎵 DATABASE: MusicStreamingSystem
-- =====================================

CREATE DATABASE IF NOT EXISTS MusicStreamingSystem
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;
USE MusicStreamingSystem;

-- =====================================
-- 1. USER
-- =====================================
CREATE TABLE User (
    UserID INT AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Email VARCHAR(150) NOT NULL UNIQUE,
    PasswordHash VARCHAR(255) NOT NULL,
    Role ENUM('user','artist','admin') DEFAULT 'user',
    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    Status ENUM('active','inactive','banned') DEFAULT 'active'
);

-- =====================================
-- 2. ARTIST
-- =====================================
CREATE TABLE Artist (
    ArtistID INT AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Bio TEXT,
    Country VARCHAR(100),
    DebutYear YEAR,
    AvatarURL VARCHAR(255),
    Status ENUM('active','retired','banned') DEFAULT 'active'
);

-- =====================================
-- 3. GENRE
-- =====================================
CREATE TABLE Genre (
    GenreID INT AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL UNIQUE,
    Description TEXT
);

-- =====================================
-- 4. ALBUM
-- =====================================
CREATE TABLE Album (
    AlbumID INT AUTO_INCREMENT PRIMARY KEY,
    ArtistID INT NOT NULL,
    Title VARCHAR(150) NOT NULL,
    ReleaseDate DATE,
    CoverURL VARCHAR(255),
    Status ENUM('published','unpublished','deleted') DEFAULT 'published',
    FOREIGN KEY (ArtistID) REFERENCES Artist(ArtistID)
        ON DELETE CASCADE
);

-- =====================================
-- 5. TRACK
-- =====================================
CREATE TABLE Track (
    TrackID INT AUTO_INCREMENT PRIMARY KEY,
    AlbumID INT NULL,
    Title VARCHAR(150) NOT NULL,
    Duration INT CHECK (Duration > 0),
    AudioURL VARCHAR(255) NOT NULL,
    Lyrics TEXT,
    GenreID INT NOT NULL,
    Explicit BOOLEAN DEFAULT FALSE,
    PublishStatus ENUM('draft','published','blocked') DEFAULT 'published',
    FOREIGN KEY (AlbumID) REFERENCES Album(AlbumID)
        ON DELETE SET NULL,
    FOREIGN KEY (GenreID) REFERENCES Genre(GenreID)
        ON DELETE RESTRICT
);

-- =====================================
-- 6. PLAYLIST
-- =====================================
CREATE TABLE Playlist (
    PlaylistID INT AUTO_INCREMENT PRIMARY KEY,
    OwnerUserID INT NOT NULL,
    Title VARCHAR(150) NOT NULL,
    Description TEXT,
    Visibility ENUM('public','private') DEFAULT 'private',
    CoverURL VARCHAR(255),
    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (OwnerUserID) REFERENCES User(UserID)
        ON DELETE CASCADE
);

-- =====================================
-- 7. PLAYLISTTRACK
-- =====================================
CREATE TABLE PlaylistTrack (
    PlaylistID INT NOT NULL,
    TrackID INT NOT NULL,
    SortOrder INT DEFAULT 0,
    AddedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (PlaylistID, TrackID),
    FOREIGN KEY (PlaylistID) REFERENCES Playlist(PlaylistID)
        ON DELETE CASCADE,
    FOREIGN KEY (TrackID) REFERENCES Track(TrackID)
        ON DELETE CASCADE
);

-- =====================================
-- 8. LIKE
-- =====================================
CREATE TABLE `Like` (
    UserID INT NOT NULL,
    TrackID INT NOT NULL,
    LikedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (UserID, TrackID),
    FOREIGN KEY (UserID) REFERENCES User(UserID)
        ON DELETE CASCADE,
    FOREIGN KEY (TrackID) REFERENCES Track(TrackID)
        ON DELETE CASCADE
);

-- =====================================
-- 9. FOLLOW
-- =====================================
CREATE TABLE Follow (
    UserID INT NOT NULL,
    ArtistID INT NOT NULL,
    FollowedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (UserID, ArtistID),
    FOREIGN KEY (UserID) REFERENCES User(UserID)
        ON DELETE CASCADE,
    FOREIGN KEY (ArtistID) REFERENCES Artist(ArtistID)
        ON DELETE CASCADE
);

-- =====================================
-- 10. PLAYHISTORY
-- =====================================
CREATE TABLE PlayHistory (
    HistoryID BIGINT AUTO_INCREMENT PRIMARY KEY,
    UserID INT NOT NULL,
    TrackID INT NOT NULL,
    PlayedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    Device VARCHAR(50),
    PositionSec INT DEFAULT 0 CHECK (PositionSec >= 0),
    FOREIGN KEY (UserID) REFERENCES User(UserID)
        ON DELETE CASCADE,
    FOREIGN KEY (TrackID) REFERENCES Track(TrackID)
        ON DELETE CASCADE
);

-- =====================================
-- 11. SUBSCRIPTION PLAN
-- =====================================
CREATE TABLE SubscriptionPlan (
    PlanID INT AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL UNIQUE,
    Price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    DurationDays INT NOT NULL CHECK (DurationDays > 0),
    Description TEXT,
    Status ENUM('active','inactive') DEFAULT 'active'
);

-- =====================================
-- 12. USER SUBSCRIPTION
-- =====================================
CREATE TABLE UserSubscription (
    SubscriptionID BIGINT AUTO_INCREMENT PRIMARY KEY,
    UserID INT NOT NULL,
    PlanID INT NOT NULL,
    StartDate DATETIME DEFAULT CURRENT_TIMESTAMP,
    EndDate DATETIME,
    AutoRenew BOOLEAN DEFAULT TRUE,
    Status ENUM('active','expired','canceled') DEFAULT 'active',
    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (UserID) REFERENCES User(UserID)
        ON DELETE CASCADE,
    FOREIGN KEY (PlanID) REFERENCES SubscriptionPlan(PlanID)
        ON DELETE RESTRICT
);

-- =====================================
-- ✅ DONE
-- =====================================
SHOW TABLES;
