-- BMMG Records — Database Schema
-- MySQL 8+

USE tokensto_bmmg_records;

-- ─────────────────────────────────────────────
-- Admins
-- ─────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS admins (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name          VARCHAR(120)        NOT NULL,
  email         VARCHAR(190)        NOT NULL UNIQUE,
  password_hash VARCHAR(255)        NOT NULL,

  -- Password recovery — reset_token_hash stores a SHA-256 hash of the
  -- emailed token, never the raw token, same discipline as password_hash
  reset_token_hash       VARCHAR(64)  NULL,
  reset_token_expires_at TIMESTAMP    NULL,

  created_at    TIMESTAMP           NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    TIMESTAMP           NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ─────────────────────────────────────────────
-- Songs
-- ─────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS songs (
  id                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

  -- Fetched automatically from Spotify — never entered manually
  title             VARCHAR(255)    NOT NULL,
  artist            VARCHAR(255)    NOT NULL,
  album             VARCHAR(255)    NULL,
  artwork_url       VARCHAR(500)    NOT NULL,

  -- Source of truth / links
  spotify_track_id  VARCHAR(64)     NOT NULL,
  spotify_url       VARCHAR(500)    NOT NULL,
  apple_music_url   VARCHAR(500)    NULL,
  apple_match_score TINYINT UNSIGNED NULL COMMENT '0-100 confidence of Apple Music match',

  -- Manually entered by the admin — Audiomack and Boomplay have no
  -- key-less public search API to auto-match against, unlike Apple Music
  audiomack_url     VARCHAR(500)    NULL,
  boomplay_url      VARCHAR(500)    NULL,

  -- Admin-controlled fields
  slug              VARCHAR(160)    NOT NULL UNIQUE,
  status            ENUM('draft','published') NOT NULL DEFAULT 'draft',
  release_date      DATE            NULL,

  -- Per-song marketing pixels (each artist's ad campaigns need their own
  -- attribution, so these live on the song, not as a site-wide default)
  meta_pixel_id       VARCHAR(32)   NULL COMMENT 'Meta/Facebook Pixel ID',
  tiktok_pixel_id     VARCHAR(32)   NULL COMMENT 'TikTok Pixel ID',
  custom_head_script  TEXT          NULL COMMENT 'Raw script/HTML injected as-is into <head> for other pixels',

  created_by        INT UNSIGNED    NULL,
  published_at      TIMESTAMP       NULL,
  created_at        TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at        TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  CONSTRAINT fk_songs_admin FOREIGN KEY (created_by) REFERENCES admins(id) ON DELETE SET NULL,
  INDEX idx_songs_status (status),
  INDEX idx_songs_slug (slug)
) ENGINE=InnoDB;
