SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS approval_reviews,automation_activity_logs,automation_runs,content_calendar_items,media_assets,script_scenes,scripts,videos,channels,topics,niches,automation_settings,jobs,job_batches,failed_jobs,users;
SET FOREIGN_KEY_CHECKS=1;
CREATE TABLE users (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(255) NOT NULL,email VARCHAR(255) NOT NULL UNIQUE,email_verified_at TIMESTAMP NULL,password VARCHAR(255) NOT NULL,remember_token VARCHAR(100) NULL,created_at TIMESTAMP NULL,updated_at TIMESTAMP NULL) ENGINE=InnoDB;
CREATE TABLE niches (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(255) NOT NULL,slug VARCHAR(255) NOT NULL UNIQUE,description TEXT NULL,keywords JSON NULL,active TINYINT(1) NOT NULL DEFAULT 1,created_at TIMESTAMP NULL,updated_at TIMESTAMP NULL) ENGINE=InnoDB;
CREATE TABLE topics (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,niche_id BIGINT UNSIGNED NULL,title VARCHAR(255) NOT NULL,country VARCHAR(255) NULL,city VARCHAR(255) NULL,description TEXT NULL,type VARCHAR(255) NOT NULL DEFAULT 'destination',score INT UNSIGNED NOT NULL DEFAULT 0,status VARCHAR(255) NOT NULL DEFAULT 'idea',opportunity_score DECIMAL(8,2) NULL,created_at TIMESTAMP NULL,updated_at TIMESTAMP NULL,INDEX(niche_id),INDEX(opportunity_score),FOREIGN KEY(niche_id) REFERENCES niches(id) ON DELETE SET NULL) ENGINE=InnoDB;
CREATE TABLE scripts (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,topic_id BIGINT UNSIGNED NOT NULL,title VARCHAR(255) NOT NULL,hook LONGTEXT NULL,content LONGTEXT NOT NULL,call_to_action LONGTEXT NULL,estimated_seconds INT UNSIGNED NULL,language VARCHAR(10) NOT NULL DEFAULT 'en',status VARCHAR(255) NOT NULL DEFAULT 'draft',seo_data JSON NULL,created_at TIMESTAMP NULL,updated_at TIMESTAMP NULL,INDEX(topic_id),FOREIGN KEY(topic_id) REFERENCES topics(id) ON DELETE CASCADE) ENGINE=InnoDB;
CREATE TABLE script_scenes (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,script_id BIGINT UNSIGNED NOT NULL,scene_number INT UNSIGNED NOT NULL,narration LONGTEXT NOT NULL,visual_prompt TEXT NULL,broll_keywords TEXT NULL,on_screen_text VARCHAR(255) NULL,duration INT UNSIGNED NOT NULL DEFAULT 10,created_at TIMESTAMP NULL,updated_at TIMESTAMP NULL,INDEX(script_id),FOREIGN KEY(script_id) REFERENCES scripts(id) ON DELETE CASCADE) ENGINE=InnoDB;
CREATE TABLE media_assets (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,script_scene_id BIGINT UNSIGNED NULL,type VARCHAR(255) NOT NULL,path VARCHAR(255) NOT NULL,duration INT UNSIGNED NULL,metadata JSON NULL,status VARCHAR(255) NOT NULL DEFAULT 'ready',created_at TIMESTAMP NULL,updated_at TIMESTAMP NULL,INDEX(script_scene_id),FOREIGN KEY(script_scene_id) REFERENCES script_scenes(id) ON DELETE SET NULL) ENGINE=InnoDB;
CREATE TABLE channels (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id BIGINT UNSIGNED NOT NULL,name VARCHAR(255) NOT NULL,youtube_channel_id VARCHAR(255) NULL UNIQUE,handle VARCHAR(255) NULL,description TEXT NULL,default_language VARCHAR(10) NOT NULL DEFAULT 'en',default_category VARCHAR(255) NOT NULL DEFAULT 'Travel & Events',oauth_access_token TEXT NULL,oauth_refresh_token TEXT NULL,token_expires_at TIMESTAMP NULL,active TINYINT(1) NOT NULL DEFAULT 1,created_at TIMESTAMP NULL,updated_at TIMESTAMP NULL,FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE) ENGINE=InnoDB;
CREATE TABLE videos (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,channel_id BIGINT UNSIGNED NOT NULL,topic_id BIGINT UNSIGNED NULL,script_id BIGINT UNSIGNED NULL,title VARCHAR(255) NOT NULL,description TEXT NULL,tags JSON NULL,video_path VARCHAR(255) NULL,thumbnail_path VARCHAR(255) NULL,youtube_video_id VARCHAR(255) NULL,status VARCHAR(255) NOT NULL DEFAULT 'draft',privacy_status VARCHAR(255) NOT NULL DEFAULT 'private',youtube_category_id VARCHAR(255) NOT NULL DEFAULT '19',scheduled_at TIMESTAMP NULL,publish_at TIMESTAMP NULL,youtube_url VARCHAR(255) NULL,views BIGINT UNSIGNED NOT NULL DEFAULT 0,likes BIGINT UNSIGNED NOT NULL DEFAULT 0,comments BIGINT UNSIGNED NOT NULL DEFAULT 0,ctr DECIMAL(8,2) NULL,average_view_duration DECIMAL(10,2) NULL,error_message TEXT NULL,created_at TIMESTAMP NULL,updated_at TIMESTAMP NULL,FOREIGN KEY(channel_id) REFERENCES channels(id) ON DELETE CASCADE,FOREIGN KEY(topic_id) REFERENCES topics(id) ON DELETE SET NULL,FOREIGN KEY(script_id) REFERENCES scripts(id) ON DELETE SET NULL) ENGINE=InnoDB;
CREATE TABLE automation_settings (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,`key` VARCHAR(255) NOT NULL UNIQUE,`value` LONGTEXT NULL,enabled TINYINT(1) NOT NULL DEFAULT 1,topics_per_run INT UNSIGNED NOT NULL DEFAULT 10,calendar_days INT UNSIGNED NOT NULL DEFAULT 14,videos_per_week INT UNSIGNED NOT NULL DEFAULT 3,approval_required TINYINT(1) NOT NULL DEFAULT 1,youtube_default_privacy VARCHAR(255) NOT NULL DEFAULT 'private',default_language VARCHAR(30) NOT NULL DEFAULT 'English',target_minutes INT UNSIGNED NOT NULL DEFAULT 8,created_at TIMESTAMP NULL,updated_at TIMESTAMP NULL);
INSERT INTO automation_settings (`key`,`value`) VALUES ('global',NULL);
CREATE TABLE content_calendar_items (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,niche_id BIGINT UNSIGNED NULL,topic_id BIGINT UNSIGNED NULL,script_id BIGINT UNSIGNED NULL,title VARCHAR(255) NULL,scheduled_for DATETIME NULL,status VARCHAR(255) NOT NULL DEFAULT 'planned',priority TINYINT UNSIGNED NOT NULL DEFAULT 5,notes TEXT NULL,created_at TIMESTAMP NULL,updated_at TIMESTAMP NULL,INDEX(scheduled_for),INDEX(status),FOREIGN KEY(niche_id) REFERENCES niches(id) ON DELETE SET NULL,FOREIGN KEY(topic_id) REFERENCES topics(id) ON DELETE SET NULL,FOREIGN KEY(script_id) REFERENCES scripts(id) ON DELETE SET NULL) ENGINE=InnoDB;
CREATE TABLE approval_reviews (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,video_id BIGINT UNSIGNED NOT NULL,user_id BIGINT UNSIGNED NULL,decision VARCHAR(255) NOT NULL DEFAULT 'pending',notes TEXT NULL,reviewed_at TIMESTAMP NULL,created_at TIMESTAMP NULL,updated_at TIMESTAMP NULL,FOREIGN KEY(video_id) REFERENCES videos(id) ON DELETE CASCADE,FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL) ENGINE=InnoDB;
CREATE TABLE automation_runs (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,run_key VARCHAR(255) NOT NULL,`status` VARCHAR(255) NOT NULL DEFAULT 'running',topics_discovered INT UNSIGNED NOT NULL DEFAULT 0,calendar_items_created INT UNSIGNED NOT NULL DEFAULT 0,pipelines_queued INT UNSIGNED NOT NULL DEFAULT 0,analytics_synced INT UNSIGNED NOT NULL DEFAULT 0,errors INT UNSIGNED NOT NULL DEFAULT 0,message TEXT NULL,started_at TIMESTAMP NULL,finished_at TIMESTAMP NULL,created_at TIMESTAMP NULL,updated_at TIMESTAMP NULL,INDEX(run_key),INDEX(status)) ENGINE=InnoDB;
CREATE TABLE automation_activity_logs (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,automation_run_id BIGINT UNSIGNED NULL,level VARCHAR(255) NOT NULL DEFAULT 'info',event VARCHAR(255) NOT NULL,message TEXT NULL,context JSON NULL,created_at TIMESTAMP NULL,updated_at TIMESTAMP NULL,FOREIGN KEY(automation_run_id) REFERENCES automation_runs(id) ON DELETE SET NULL) ENGINE=InnoDB;
CREATE TABLE jobs (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,queue VARCHAR(255) NOT NULL,payload LONGTEXT NOT NULL,attempts TINYINT UNSIGNED NOT NULL,reserved_at INT UNSIGNED NULL,available_at INT UNSIGNED NOT NULL,created_at INT UNSIGNED NOT NULL,INDEX(queue)) ENGINE=InnoDB;
CREATE TABLE job_batches (id VARCHAR(255) PRIMARY KEY,name VARCHAR(255) NOT NULL,total_jobs INT NOT NULL,pending_jobs INT NOT NULL,failed_jobs INT NOT NULL,failed_job_ids LONGTEXT NOT NULL,options MEDIUMTEXT NULL,cancelled_at INT NULL,created_at INT NOT NULL,finished_at INT NULL) ENGINE=InnoDB;
CREATE TABLE failed_jobs (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,uuid VARCHAR(255) NOT NULL UNIQUE,connection TEXT NOT NULL,queue TEXT NOT NULL,payload LONGTEXT NOT NULL,exception LONGTEXT NOT NULL,failed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB;