-- ============================================================
--  Wavely Music Streaming App -- MySQL Schema & Seed Data
--  Run once:  mysql -u root -p < database.sql
-- ============================================================


CREATE TABLE IF NOT EXISTS users (
  id               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  username         VARCHAR(50)  NOT NULL UNIQUE,
  email            VARCHAR(120) NOT NULL UNIQUE,
  password         VARCHAR(255) NOT NULL,
  avatar           VARCHAR(10)  NOT NULL DEFAULT '🎧',
  plan             ENUM('free','premium') NOT NULL DEFAULT 'free',
  preferred_type   ENUM('all','gospel','inspirational') NOT NULL DEFAULT 'all',
  created_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS artists (
  id        INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id   INT UNSIGNED UNIQUE,
  name      VARCHAR(120) NOT NULL,
  genre     VARCHAR(100),
  followers VARCHAR(20)  DEFAULT '0',
  emoji     VARCHAR(10),
  color     VARCHAR(20),
  accent    VARCHAR(20),
  bio       TEXT,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS albums (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title        VARCHAR(150) NOT NULL,
  artist_id    INT UNSIGNED NOT NULL,
  year         YEAR,
  emoji        VARCHAR(10),
  cover_path   VARCHAR(255) DEFAULT NULL,
  genre_type   ENUM('gospel','inspirational') DEFAULT NULL,
  release_type ENUM('ep','album') DEFAULT NULL,
  uploaded_by  INT UNSIGNED DEFAULT NULL,
  created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (artist_id)   REFERENCES artists(id) ON DELETE CASCADE,
  FOREIGN KEY (uploaded_by) REFERENCES users(id)   ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS tracks (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title        VARCHAR(150) NOT NULL,
  artist_id    INT UNSIGNED NOT NULL,
  album_id     INT UNSIGNED,
  duration     VARCHAR(8)   NOT NULL DEFAULT '0:00',
  duration_s   SMALLINT     NOT NULL DEFAULT 200,
  emoji        VARCHAR(10),
  color        VARCHAR(20),
  accent       VARCHAR(20),
  plays        VARCHAR(20)  DEFAULT '0',
  play_count   INT UNSIGNED NOT NULL DEFAULT 0,
  genre_type   ENUM('gospel','inspirational') DEFAULT NULL,
  release_type ENUM('ep','single','album','preaching','spoken_word') DEFAULT NULL,
  audio_path   VARCHAR(255) DEFAULT NULL,
  cover_path   VARCHAR(255) DEFAULT NULL,
  uploaded_by  INT UNSIGNED DEFAULT NULL,
  created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (artist_id)   REFERENCES artists(id) ON DELETE CASCADE,
  FOREIGN KEY (album_id)    REFERENCES albums(id)  ON DELETE SET NULL,
  FOREIGN KEY (uploaded_by) REFERENCES users(id)   ON DELETE SET NULL,
  INDEX idx_genre_type (genre_type),
  INDEX idx_uploaded_by (uploaded_by),
  INDEX idx_created_at (created_at),
  FULLTEXT INDEX idx_title_ft (title)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS playlists (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id     INT UNSIGNED,
  title       VARCHAR(150) NOT NULL,
  description VARCHAR(255),
  emoji       VARCHAR(10),
  color       VARCHAR(20),
  accent      VARCHAR(20),
  is_featured TINYINT(1) NOT NULL DEFAULT 0,
  created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS playlist_tracks (
  playlist_id INT UNSIGNED NOT NULL,
  track_id    INT UNSIGNED NOT NULL,
  position    SMALLINT     NOT NULL DEFAULT 0,
  PRIMARY KEY (playlist_id, track_id),
  FOREIGN KEY (playlist_id) REFERENCES playlists(id) ON DELETE CASCADE,
  FOREIGN KEY (track_id)    REFERENCES tracks(id)    ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS liked_tracks (
  user_id    INT UNSIGNED NOT NULL,
  track_id   INT UNSIGNED NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (user_id, track_id),
  FOREIGN KEY (user_id)  REFERENCES users(id)  ON DELETE CASCADE,
  FOREIGN KEY (track_id) REFERENCES tracks(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS followed_artists (
  user_id   INT UNSIGNED NOT NULL,
  artist_id INT UNSIGNED NOT NULL,
  PRIMARY KEY (user_id, artist_id),
  FOREIGN KEY (user_id)   REFERENCES users(id)   ON DELETE CASCADE,
  FOREIGN KEY (artist_id) REFERENCES artists(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS comments (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  track_id   INT UNSIGNED NOT NULL,
  user_id    INT UNSIGNED NOT NULL,
  body       VARCHAR(500) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (track_id) REFERENCES tracks(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id)  REFERENCES users(id)  ON DELETE CASCADE,
  INDEX idx_track (track_id, created_at)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS messages (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  sender_id    INT UNSIGNED NOT NULL,
  recipient_id INT UNSIGNED NOT NULL,
  body         VARCHAR(1000) NOT NULL,
  read_at      TIMESTAMP NULL DEFAULT NULL,
  created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (sender_id)    REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (recipient_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_parties (sender_id, recipient_id, created_at),
  INDEX idx_recipient (recipient_id, created_at)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS notifications (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id    INT UNSIGNED NOT NULL,
  type       ENUM('new_upload','new_supporter','new_message','new_comment') NOT NULL,
  title      VARCHAR(150) NOT NULL,
  body       VARCHAR(255),
  link_type  VARCHAR(20),
  link_id    INT UNSIGNED,
  is_read    TINYINT(1) NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_user (user_id, is_read, created_at)
) ENGINE=InnoDB;

-- One row per fresh play (see api/stream.php), kept separate from tracks.play_count so
-- that fast counter still works for sorting/trending without scanning this log. user_id
-- is nullable — a play is logged whether or not the listener is signed in.
CREATE TABLE IF NOT EXISTS track_plays (
  id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  track_id   INT UNSIGNED NOT NULL,
  user_id    INT UNSIGNED DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (track_id) REFERENCES tracks(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id)  REFERENCES users(id)  ON DELETE SET NULL,
  INDEX idx_track_time (track_id, created_at),
  INDEX idx_time (created_at)
) ENGINE=InnoDB;

-- Records a "support" pledge from a fan to an artist. IMPORTANT: this is a ledger only —
-- no money actually moves here. Wire api/support.php up to a real payment processor
-- (Stripe, Paddle, PayPal, etc.) before accepting real payments; see README.
CREATE TABLE IF NOT EXISTS supporters (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id    INT UNSIGNED NOT NULL,
  artist_id  INT UNSIGNED NOT NULL,
  amount     DECIMAL(8,2) NOT NULL,
  message    VARCHAR(240),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id)   REFERENCES users(id)   ON DELETE CASCADE,
  FOREIGN KEY (artist_id) REFERENCES artists(id) ON DELETE CASCADE,
  INDEX idx_artist (artist_id)
) ENGINE=InnoDB;

-- Seed artists
INSERT IGNORE INTO artists (id,name,genre,followers,emoji,color,accent,bio) VALUES
(1,'Sylvan Echo','Electronic / Dream Pop','2.4M','🌌','#2d1b4e','#c77dff','LA-based producer blending ethereal synths with introspective lyrics. Known for cinematic soundscapes and late-night vibes.'),
(2,'Marea','Indie / Ambient','1.1M','🌊','#0f3460','#4fc3f7','Brazilian-born artist crafting oceanic soundscapes. Each album is a journey through coastal textures and open water.'),
(3,'The Lowland Tide','Alternative Rock','890K','🎸','#1c1c1c','#ff4500','Portland four-piece channeling desert noir and dusty americana into a modern rock framework.'),
(4,'Pavo Nuit','Neo-Classical / Ambient','3.2M','🎹','#1b3a2d','#52b788','Berlin-based composer merging classical piano training with ambient electronic production.'),
(5,'Cass Hollow','Indie Folk','760K','🍂','#2c1810','#d4a017','Nashville songwriter known for warm, textured folk songs rooted in the American South.');

-- Seed albums
INSERT IGNORE INTO albums (id,title,artist_id,year,emoji) VALUES
(1,'Ultraviolet Drift',1,2024,'💿'),(2,'Solstice EP',1,2023,'✨'),
(3,'Tidewater',2,2024,'🌊'),(4,'Dust & Wire',3,2023,'🎸'),
(5,'Aerial Rooms',4,2024,'🏛️'),(6,'Night Static',5,2023,'🍂');

-- Seed tracks
INSERT IGNORE INTO tracks (id,title,artist_id,album_id,duration,duration_s,emoji,color,accent,plays) VALUES
(1,'Neon Horizons',1,1,'3:24',204,'🎵','#2d1b4e','#c77dff','2.4M'),
(2,'Violet Undertow',1,1,'4:32',272,'💜','#2d1b4e','#c77dff','1.8M'),
(3,'Drifting Coastlines',2,3,'4:01',241,'🌊','#0f3460','#4fc3f7','1.1M'),
(4,'Coastfire',2,3,'3:11',191,'🔥','#0f3460','#4fc3f7','950K'),
(5,'Desert Chrome',3,4,'2:58',178,'🎸','#1c1c1c','#ff4500','890K'),
(6,'Glass Canopy',4,5,'5:14',314,'🎹','#1b3a2d','#52b788','3.2M'),
(7,'Pale Architecture',4,5,'6:02',362,'🏛️','#1b3a2d','#52b788','2.1M'),
(8,'Ember Radio',5,6,'3:47',227,'🥁','#2c1810','#d4a017','760K');

-- Seed playlists (system)
INSERT IGNORE INTO playlists (id,user_id,title,description,emoji,color,accent,is_featured) VALUES
(1,NULL,'Late Night Drive','Dim lights. Open road. No destination.','🌙','#1a1a2e','#c77dff',1),
(2,NULL,'Focus Mode','Get in the zone and stay there.','🌿','#0f3460','#52b788',1),
(3,NULL,'Workout Mix','Push harder. Move faster. Feel everything.','🔥','#3a1a00','#ff4500',1),
(4,NULL,'Sunday Morning','Slow it down. You have earned it.','☀️','#1b3a2d','#d4a017',1);

INSERT IGNORE INTO playlist_tracks (playlist_id,track_id,position) VALUES
(1,1,1),(1,2,2),(1,3,3),(1,6,4),(1,7,5),
(2,6,1),(2,7,2),(2,3,3),(2,1,4),(2,4,5),
(3,5,1),(3,8,2),(3,4,3),(3,2,4),(3,1,5),
(4,8,1),(4,6,2),(4,3,3),(4,5,4),(4,7,5);

-- Demo user  (password = demo1234)
INSERT IGNORE INTO users (id,username,email,password,avatar,plan,preferred_type) VALUES
(1,'demo','demo@wavely.com','$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi','🎧','free','all');

-- ── Gospel & Inspirational seed content ─────────────────────
INSERT IGNORE INTO artists (id,name,genre,followers,emoji,color,accent,bio) VALUES
(6,'Grace & Glory Choir','Gospel','540K','🙌','#0a2647','#2563eb','A city-wide praise choir known for high-energy live worship recordings.'),
(7,'Josiah Bankole','Gospel','310K','🎙️','#0d3b66','#1d4ed8','Minister and vocalist blending contemporary gospel with traditional hymns.'),
(8,'Ruth Amara','Inspirational','275K','🕊️','#123a5e','#3b82f6','Spoken-word artist and life coach sharing faith-rooted encouragement.'),
(9,'The Overflow','Gospel','198K','🔥','#0a2647','#60a5fa','Youth-led gospel band known for high-tempo praise breaks.');

INSERT IGNORE INTO albums (id,title,artist_id,year,emoji) VALUES
(7,'Songs of Ascent',6,2024,'🙌'),(8,'Yielded',7,2024,'🎙️'),
(9,'Still Small Voice',8,2023,'🕊️'),(10,'Overflow Live',9,2024,'🔥');

INSERT IGNORE INTO tracks (id,title,artist_id,album_id,duration,duration_s,emoji,color,accent,plays,genre_type,release_type) VALUES
(9,'Ancient Gates',6,7,'4:12',252,'🙌','#0a2647','#2563eb','410K','gospel','album'),
(10,'Yielded Vessel',7,8,'3:48',228,'🎙️','#0d3b66','#1d4ed8','260K','gospel','single'),
(11,'Overflow (Live)',9,10,'5:02',302,'🔥','#0a2647','#60a5fa','180K','gospel','ep'),
(12,'Sunday Word: Rest',7,NULL,'18:40',1120,'🎙️','#0d3b66','#1d4ed8','88K','gospel','preaching'),
(13,'Still Small Voice',8,9,'3:20',200,'🕊️','#123a5e','#3b82f6','230K','inspirational','single'),
(14,'You Are Not Behind',8,9,'4:05',245,'🕊️','#123a5e','#3b82f6','195K','inspirational','spoken_word'),
(15,'Renewed Mind',8,NULL,'6:15',375,'🕊️','#123a5e','#3b82f6','142K','inspirational','ep');