db.sql

-- Create 'users' table
CREATE TABLE IF NOT EXISTS users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255),
  email VARCHAR(255) UNIQUE,
  password VARCHAR(255),
  address VARCHAR(255),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Create 'albums' table
CREATE TABLE IF NOT EXISTS albums (
  id INT PRIMARY KEY AUTO_INCREMENT,
  title VARCHAR(255),
  release_year INT NOT NULL,
  genre VARCHAR(255),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Create 'artists' table
CREATE TABLE IF NOT EXISTS artists (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255),
  country VARCHAR(255),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Create 'songs' table
CREATE TABLE IF NOT EXISTS songs (
  id INT PRIMARY KEY AUTO_INCREMENT,
  title VARCHAR(255),
  duration VARCHAR(255),
  album INT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (album) REFERENCES albums(id)
);

-- Create 'albums_artists' junction table
CREATE TABLE IF NOT EXISTS albums_artists (
  album INT NOT NULL,
  artist INT NOT NULL,
  PRIMARY KEY (album, artist),
  FOREIGN KEY (album) REFERENCES albums(id) ON DELETE CASCADE,
  FOREIGN KEY (artist) REFERENCES artists(id) ON DELETE CASCADE
);