-- Zika Beee AI Story Production Studio / MySQL 8.0+ / UTF8MB4
CREATE TABLE IF NOT EXISTS users (id CHAR(32) PRIMARY KEY,name VARCHAR(120) NOT NULL,email VARCHAR(190) NOT NULL UNIQUE,password_hash VARCHAR(255) NOT NULL,role ENUM('creator','admin') NOT NULL DEFAULT 'creator',storage_quota_bytes BIGINT UNSIGNED NOT NULL DEFAULT 1073741824,active TINYINT(1) NOT NULL DEFAULT 1,created_at DATETIME NOT NULL) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS projects (id CHAR(32) PRIMARY KEY,owner_id CHAR(32) NOT NULL,name VARCHAR(190) NOT NULL,aspect_ratio ENUM('9:16','16:9') NOT NULL,status VARCHAR(30) NOT NULL,created_at DATETIME NOT NULL,FOREIGN KEY(owner_id) REFERENCES users(id),INDEX(owner_id)) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS source_documents (id CHAR(32) PRIMARY KEY,project_id CHAR(32) NOT NULL,title VARCHAR(190) NOT NULL,content LONGTEXT NOT NULL,status VARCHAR(30) NOT NULL,created_at DATETIME NOT NULL,FOREIGN KEY(project_id) REFERENCES projects(id)) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS episodes (id CHAR(32) PRIMARY KEY,project_id CHAR(32) NOT NULL,number INT NOT NULL,title VARCHAR(190),created_at DATETIME NOT NULL,FOREIGN KEY(project_id) REFERENCES projects(id),UNIQUE KEY uniq_episode(project_id,number)) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS scenes (id CHAR(32) PRIMARY KEY,project_id CHAR(32) NOT NULL,episode_id CHAR(32) NOT NULL,number INT NOT NULL,title VARCHAR(190),location_name VARCHAR(190),screenplay LONGTEXT,created_at DATETIME NOT NULL,FOREIGN KEY(project_id) REFERENCES projects(id),FOREIGN KEY(episode_id) REFERENCES episodes(id),UNIQUE KEY uniq_scene(episode_id,number)) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS characters (id CHAR(32) PRIMARY KEY,project_id CHAR(32) NOT NULL,name VARCHAR(190) NOT NULL,description TEXT,created_at DATETIME NOT NULL,FOREIGN KEY(project_id) REFERENCES projects(id)) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS character_references (id CHAR(32) PRIMARY KEY,project_id CHAR(32) NOT NULL,character_id CHAR(32) NOT NULL,asset_id CHAR(32) NOT NULL,kind ENUM('face','body','body_state','grooming') NOT NULL,approved TINYINT(1) NOT NULL DEFAULT 0,created_at DATETIME NOT NULL,FOREIGN KEY(project_id) REFERENCES projects(id),FOREIGN KEY(character_id) REFERENCES characters(id)) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS location_groups (id CHAR(32) PRIMARY KEY,project_id CHAR(32) NOT NULL,name VARCHAR(190) NOT NULL,description TEXT,created_at DATETIME NOT NULL,FOREIGN KEY(project_id) REFERENCES projects(id)) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS locations (id CHAR(32) PRIMARY KEY,project_id CHAR(32) NOT NULL,group_id CHAR(32) NOT NULL,name VARCHAR(190) NOT NULL,panel_label VARCHAR(190),description TEXT,created_at DATETIME NOT NULL,FOREIGN KEY(project_id) REFERENCES projects(id),FOREIGN KEY(group_id) REFERENCES location_groups(id)) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS location_reference_assets (id CHAR(32) PRIMARY KEY,project_id CHAR(32) NOT NULL,group_id CHAR(32) NOT NULL,asset_id CHAR(32) NOT NULL,approved TINYINT(1) NOT NULL DEFAULT 0,created_at DATETIME NOT NULL,FOREIGN KEY(project_id) REFERENCES projects(id),FOREIGN KEY(group_id) REFERENCES location_groups(id)) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS wardrobe_groups (id CHAR(32) PRIMARY KEY,project_id CHAR(32) NOT NULL,name VARCHAR(190) NOT NULL,description TEXT,created_at DATETIME NOT NULL,FOREIGN KEY(project_id) REFERENCES projects(id)) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS wardrobe_assignments (id CHAR(32) PRIMARY KEY,project_id CHAR(32) NOT NULL,character_id CHAR(32) NOT NULL,scene_id CHAR(32) NOT NULL,reference_asset_id CHAR(32),panel_label VARCHAR(190),description TEXT,created_at DATETIME NOT NULL,FOREIGN KEY(project_id) REFERENCES projects(id),FOREIGN KEY(character_id) REFERENCES characters(id),FOREIGN KEY(scene_id) REFERENCES scenes(id)) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS production_clips (id CHAR(32) PRIMARY KEY,project_id CHAR(32) NOT NULL,scene_id CHAR(32) NOT NULL,clip_label VARCHAR(30),spoken_words INT,duration_seconds INT,prompt LONGTEXT,approval_status VARCHAR(30) NOT NULL DEFAULT 'draft',created_at DATETIME NOT NULL,FOREIGN KEY(project_id) REFERENCES projects(id),FOREIGN KEY(scene_id) REFERENCES scenes(id)) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS production_frames (id CHAR(32) PRIMARY KEY,project_id CHAR(32) NOT NULL,scene_id CHAR(32) NOT NULL,frame_label VARCHAR(30),frame_type ENUM('A','B') NOT NULL,prompt LONGTEXT,approval_status VARCHAR(30) NOT NULL DEFAULT 'draft',created_at DATETIME NOT NULL,FOREIGN KEY(project_id) REFERENCES projects(id),FOREIGN KEY(scene_id) REFERENCES scenes(id)) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS prompt_runs (id CHAR(32) PRIMARY KEY,project_id CHAR(32) NOT NULL,kind VARCHAR(60) NOT NULL,prompt LONGTEXT NOT NULL,status VARCHAR(30) NOT NULL,created_at DATETIME NOT NULL,FOREIGN KEY(project_id) REFERENCES projects(id)) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS assets (id CHAR(32) PRIMARY KEY,user_id CHAR(32) NOT NULL,project_id CHAR(32) NOT NULL,original_name VARCHAR(190) NOT NULL,mime VARCHAR(150) NOT NULL,bytes BIGINT UNSIGNED NOT NULL,path TEXT NOT NULL,sha256 CHAR(64) NOT NULL,created_at DATETIME NOT NULL,deleted_at DATETIME NULL,FOREIGN KEY(user_id) REFERENCES users(id),FOREIGN KEY(project_id) REFERENCES projects(id),INDEX(user_id,deleted_at)) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS ai_requests (id CHAR(32) PRIMARY KEY,project_id CHAR(32) NOT NULL,user_id CHAR(32) NOT NULL,provider VARCHAR(30) NOT NULL,kind VARCHAR(30) NOT NULL,model VARCHAR(100),input_text LONGTEXT,output_text LONGTEXT,asset_id CHAR(32),credits INT NOT NULL,status VARCHAR(30) NOT NULL,created_at DATETIME NOT NULL,FOREIGN KEY(project_id) REFERENCES projects(id),FOREIGN KEY(user_id) REFERENCES users(id)) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS credit_ledger (id CHAR(32) PRIMARY KEY,user_id CHAR(32) NOT NULL,delta BIGINT NOT NULL,reason VARCHAR(60) NOT NULL,reference VARCHAR(100) NOT NULL,created_at DATETIME NOT NULL,FOREIGN KEY(user_id) REFERENCES users(id),UNIQUE KEY uniq_credit_event(user_id,reason,reference)) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS purchase_packages (id CHAR(32) PRIMARY KEY,kind ENUM('credits','storage') NOT NULL,units BIGINT NOT NULL,amount_kobo BIGINT NOT NULL,active TINYINT(1) NOT NULL DEFAULT 1) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS payment_orders (id CHAR(32) PRIMARY KEY,user_id CHAR(32) NOT NULL,reference VARCHAR(100) NOT NULL UNIQUE,kind ENUM('credits','storage') NOT NULL,units BIGINT NOT NULL,amount_kobo BIGINT NOT NULL,status ENUM('pending','paid','failed') NOT NULL DEFAULT 'pending',created_at DATETIME NOT NULL,paid_at DATETIME NULL,FOREIGN KEY(user_id) REFERENCES users(id)) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS audit_logs (id CHAR(32) PRIMARY KEY,user_id CHAR(32),action VARCHAR(100) NOT NULL,target_id CHAR(32),metadata JSON,created_at DATETIME NOT NULL,INDEX(user_id,created_at)) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS jobs (id CHAR(32) PRIMARY KEY,project_id CHAR(32) NOT NULL,user_id CHAR(32) NOT NULL,kind VARCHAR(60) NOT NULL,payload JSON NOT NULL,status ENUM('queued','running','completed','failed') NOT NULL DEFAULT 'queued',attempts INT NOT NULL DEFAULT 0,error_message TEXT,created_at DATETIME NOT NULL,updated_at DATETIME NOT NULL,FOREIGN KEY(project_id) REFERENCES projects(id),FOREIGN KEY(user_id) REFERENCES users(id)) ENGINE=InnoDB;