CREATE TABLE users(id INT AUTO_INCREMENT PRIMARY KEY,email VARCHAR(190) UNIQUE NOT NULL,name VARCHAR(120),pass_hash VARCHAR(255) NOT NULL,api_token CHAR(64) UNIQUE,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE projects(id INT AUTO_INCREMENT PRIMARY KEY,owner_id INT NOT NULL,title VARCHAR(190) NOT NULL,bpm SMALLINT DEFAULT 120,state_json MEDIUMTEXT,updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,FOREIGN KEY(owner_id) REFERENCES users(id));
CREATE TABLE project_members(project_id INT,user_id INT,role ENUM('viewer','editor') DEFAULT 'editor',PRIMARY KEY(project_id,user_id));
CREATE TABLE tracks(id INT AUTO_INCREMENT PRIMARY KEY,project_id INT NOT NULL,name VARCHAR(190),kind ENUM('recording','import','stem') DEFAULT 'import',file_path VARCHAR(255) NOT NULL,volume FLOAT DEFAULT 1,pan FLOAT DEFAULT 0,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(project_id) REFERENCES projects(id) ON DELETE CASCADE);
CREATE TABLE library(id INT AUTO_INCREMENT PRIMARY KEY,user_id INT NOT NULL,name VARCHAR(190),file_path VARCHAR(255) NOT NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE stem_jobs(id INT AUTO_INCREMENT PRIMARY KEY,project_id INT NOT NULL,source_track_id INT NOT NULL,status ENUM('queued','running','done','failed') DEFAULT 'queued',message VARCHAR(255),options TEXT,result_ids VARCHAR(500),created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);