-- ScoreAI MySQL schema (self-hosted backend, replaces Supabase)
-- Run this in phpMyAdmin (or `mysql -u user -p dbname < schema.sql`) against
-- the database you create in cPanel -> MySQL Databases.

CREATE TABLE IF NOT EXISTS users (
  id CHAR(36) PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NULL,           -- NULL for Google-only accounts
  google_sub VARCHAR(255) NULL UNIQUE,        -- Google's stable user id, if signed up via Google
  display_name VARCHAR(255) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS scores (
  id VARCHAR(64) PRIMARY KEY,
  owner_id CHAR(36) NOT NULL,
  title VARCHAR(255) NOT NULL DEFAULT 'Untitled Score',
  composer VARCHAR(255) DEFAULT 'Anonymous',
  `key` VARCHAR(8) DEFAULT 'C',
  tempo INT DEFAULT 100,
  time_signature VARCHAR(8) DEFAULT '4/4',
  instrument VARCHAR(32) DEFAULT 'piano',
  measures JSON NOT NULL,
  lyrics JSON NULL,
  is_public TINYINT(1) DEFAULT 0,
  share_id VARCHAR(64) NULL UNIQUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_owner (owner_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ai_generations (
  id CHAR(36) PRIMARY KEY,
  user_id CHAR(36) NOT NULL,
  kind VARCHAR(32) NOT NULL,
  score_id VARCHAR(64) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;