music-people.sql

-- Enable foreign keys in SQLite
PRAGMA foreign_keys = ON;

-- Users Table
CREATE TABLE IF NOT EXISTS users (
    user_id INTEGER PRIMARY KEY AUTOINCREMENT,  
    name TEXT NOT NULL,
    bio TEXT,
    profile_image_path TEXT, -- Path: "users/{user_id}/profile-pictures/profile.jpg"
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Albums Table (for digital music albums)
CREATE TABLE IF NOT EXISTS albums (
    album_id INTEGER PRIMARY KEY AUTOINCREMENT,  
    user_id INTEGER NOT NULL, 
    title TEXT NOT NULL,
    release_date TIMESTAMP,
    price REAL NOT NULL CHECK(price >= 0),
    description TEXT,
    cover_image_path TEXT, -- Path: "users/{user_id}/albums/{album_id}/cover.jpg"
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

-- Tracks Table (for individual tracks in albums)
CREATE TABLE IF NOT EXISTS tracks (
    track_id INTEGER PRIMARY KEY AUTOINCREMENT,  
    album_id INTEGER NOT NULL, 
    title TEXT NOT NULL,
    duration INTEGER CHECK(duration >= 0), 
    stream_path TEXT, -- Path: "users/{user_id}/albums/{album_id}/tracks/trackname.mp3"
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (album_id) REFERENCES albums(album_id)
);

-- Playlists Table (for managing user playlists)
CREATE TABLE IF NOT EXISTS playlists (
    playlist_id INTEGER PRIMARY KEY AUTOINCREMENT,  
    user_id INTEGER NOT NULL,
    name TEXT NOT NULL,
    is_liked_songs BOOLEAN DEFAULT 0,
    playlist_image_path TEXT, -- Path: "users/{user_id}/playlist/{playlist_id}/cover.jpg"
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

-- Playlist Tracks Table (tracks the tracks in a playlist)
CREATE TABLE IF NOT EXISTS playlist_tracks (
    playlist_track_id INTEGER PRIMARY KEY AUTOINCREMENT,  
    playlist_id INTEGER NOT NULL,
    track_id INTEGER NOT NULL,
    added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (playlist_id) REFERENCES playlists(playlist_id),
    FOREIGN KEY (track_id) REFERENCES tracks(track_id)
);

-- Merchandise Table (updated to optionally link with albums)
CREATE TABLE IF NOT EXISTS merchandise (
    merchandise_id INTEGER PRIMARY KEY AUTOINCREMENT,
    type TEXT NOT NULL CHECK(type IN ('physical', 'digital')),
    album_id INTEGER, -- Optional, for linking to an album if related
    track_id INTEGER, -- Optional, for linking to a track if related
    user_id INTEGER NOT NULL,
    title TEXT NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (album_id) REFERENCES albums(album_id),
    FOREIGN KEY (track_id) REFERENCES tracks(track_id),
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

-- Variants Table (for physical merchandise with JSON attributes)
CREATE TABLE IF NOT EXISTS variants (
    variant_id INTEGER PRIMARY KEY AUTOINCREMENT,
    merchandise_id INTEGER,
    sku VARCHAR(255) UNIQUE NOT NULL,
    price REAL NOT NULL CHECK(price >= 0),
    attributes TEXT, -- JSON stored as TEXT
    FOREIGN KEY (merchandise_id) REFERENCES merchandise(merchandise_id)
);

-- Inventory Table (tracks inventory for physical variants)
CREATE TABLE IF NOT EXISTS inventory (
    inventory_id INTEGER PRIMARY KEY AUTOINCREMENT,
    variant_id INTEGER,
    quantity INTEGER DEFAULT 0,
    FOREIGN KEY (variant_id) REFERENCES variants(variant_id)
);

-- Addresses Table (for storing shipping addresses for physical merchandise)
CREATE TABLE IF NOT EXISTS addresses (
    address_id INTEGER PRIMARY KEY AUTOINCREMENT,  
    user_id INTEGER NOT NULL,
    address_line1 TEXT NOT NULL,
    address_line2 TEXT,
    city TEXT NOT NULL,
    state TEXT NOT NULL,
    postal_code TEXT NOT NULL,
    country TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

-- Orders Table (tracks orders for both digital and physical products)
CREATE TABLE IF NOT EXISTS orders (
    order_id INTEGER PRIMARY KEY AUTOINCREMENT,  
    user_id INTEGER NOT NULL,
    address_id INTEGER, -- Null for digital-only orders
    total_amount REAL NOT NULL CHECK(total_amount >= 0),
    status TEXT NOT NULL CHECK(status IN ('pending', 'shipped', 'delivered', 'cancelled')),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    FOREIGN KEY (address_id) REFERENCES addresses(address_id)
);

-- Order Items Table (tracks items within an order, both digital and physical)
CREATE TABLE IF NOT EXISTS order_items (
    order_item_id INTEGER PRIMARY KEY AUTOINCREMENT,  
    order_id INTEGER NOT NULL,
    album_id INTEGER, -- Null if not a digital album
    track_id INTEGER, -- Null if not a digital track
    merchandise_id INTEGER, -- Null if not physical merchandise
    variant_id INTEGER, -- Null if not physical merchandise
    quantity INTEGER NOT NULL CHECK(quantity > 0), 
    price REAL NOT NULL CHECK(price >= 0),
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (album_id) REFERENCES albums(album_id),
    FOREIGN KEY (track_id) REFERENCES tracks(track_id),
    FOREIGN KEY (merchandise_id) REFERENCES merchandise(merchandise_id),
    FOREIGN KEY (variant_id) REFERENCES variants(variant_id)
);

-- User Purchases Table (for tracking digital content purchases)
CREATE TABLE IF NOT EXISTS user_purchases (
    purchase_id INTEGER PRIMARY KEY AUTOINCREMENT,  
    user_id INTEGER NOT NULL,
    album_id INTEGER,
    track_id INTEGER,
    merchandise_id INTEGER,
    variant_id INTEGER,
    purchase_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    FOREIGN KEY (album_id) REFERENCES albums(album_id),
    FOREIGN KEY (track_id) REFERENCES tracks(track_id),
    FOREIGN KEY (merchandise_id) REFERENCES merchandise(merchandise_id),
    FOREIGN KEY (variant_id) REFERENCES variants(variant_id)
);

-- Posts Table (for social features)
CREATE TABLE IF NOT EXISTS posts (
    post_id INTEGER PRIMARY KEY AUTOINCREMENT,  
    user_id INTEGER NOT NULL,
    content TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

-- Post Links Table (for storing links in posts)
CREATE TABLE IF NOT EXISTS post_links (
    post_link_id INTEGER PRIMARY KEY AUTOINCREMENT,  
    post_id INTEGER NOT NULL,
    link_url TEXT NOT NULL,
    link_type TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (post_id) REFERENCES posts(post_id)
);

-- Likes Table (tracks likes on posts)
CREATE TABLE IF NOT EXISTS likes (
    like_id INTEGER PRIMARY KEY AUTOINCREMENT,  
    user_id INTEGER NOT NULL,
    post_id INTEGER NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    FOREIGN KEY (post_id) REFERENCES posts(post_id)
);

-- Comments Table (tracks comments on posts)
CREATE TABLE IF NOT EXISTS comments (
    comment_id INTEGER PRIMARY KEY AUTOINCREMENT,  
    user_id INTEGER NOT NULL,
    post_id INTEGER NOT NULL,
    content TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    FOREIGN KEY (post_id) REFERENCES posts(post_id)
);

-- Cart Table (for tracking user shopping carts)
CREATE TABLE IF NOT EXISTS carts (
    cart_id INTEGER PRIMARY KEY AUTOINCREMENT,  
    user_id INTEGER NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

-- Cart Items Table (tracks items within a cart)
CREATE TABLE IF NOT EXISTS cart_items (
    cart_item_id INTEGER PRIMARY KEY AUTOINCREMENT,  
    cart_id INTEGER NOT NULL,
    album_id INTEGER, -- For digital albums
    track_id INTEGER, -- For digital tracks
    merchandise_id INTEGER, -- For physical or digital merchandise
    variant_id INTEGER, -- For physical merchandise with variants
    quantity INTEGER NOT NULL CHECK(quantity > 0),
    price REAL NOT NULL CHECK(price >= 0),
    added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (cart_id) REFERENCES carts(cart_id),
    FOREIGN KEY (album_id) REFERENCES albums(album_id),
    FOREIGN KEY (track_id) REFERENCES tracks(track_id),
    FOREIGN KEY (merchandise_id) REFERENCES merchandise(merchandise_id),
    FOREIGN KEY (variant_id) REFERENCES variants(variant_id)
);

-- Follows Table (tracks user follow relationships)
CREATE TABLE IF NOT EXISTS follows (
    follow_id INTEGER PRIMARY KEY AUTOINCREMENT,  
    follower_id INTEGER NOT NULL, -- The user who follows another user
    followee_id INTEGER NOT NULL, -- The user being followed
    followed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (follower_id) REFERENCES users(user_id),
    FOREIGN KEY (followee_id) REFERENCES users(user_id),
    CONSTRAINT unique_follow UNIQUE (follower_id, followee_id) -- To prevent duplicate follows
);