SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS=0;
CREATE TABLE users (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(255) NOT NULL,email VARCHAR(255) NOT NULL UNIQUE,password VARCHAR(255) NOT NULL,role ENUM('admin','manager','editor','researcher','designer') NOT NULL DEFAULT 'editor',is_active TINYINT(1) NOT NULL DEFAULT 1,remember_token VARCHAR(100) NULL,created_at TIMESTAMP NULL,updated_at TIMESTAMP NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE brands (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(255) NOT NULL,page_name VARCHAR(255) NULL,logo_path VARCHAR(255) NULL,primary_color VARCHAR(20) NULL,secondary_color VARCHAR(20) NULL,font_family VARCHAR(255) NULL,website VARCHAR(255) NULL,phone VARCHAR(255) NULL,default_cta VARCHAR(255) NULL,brand_rules TEXT NULL,facebook_url VARCHAR(255) NULL,instagram_url VARCHAR(255) NULL,tiktok_url VARCHAR(255) NULL,is_active TINYINT(1) NOT NULL DEFAULT 1,created_at TIMESTAMP NULL,updated_at TIMESTAMP NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE categories (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(255) NOT NULL UNIQUE,slug VARCHAR(255) NOT NULL UNIQUE,created_at TIMESTAMP NULL,updated_at TIMESTAMP NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE products (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,brand_id BIGINT UNSIGNED NOT NULL,category_id BIGINT UNSIGNED NULL,name VARCHAR(255) NOT NULL,sku VARCHAR(255) NULL,description TEXT NULL,features TEXT NULL,target_audience VARCHAR(255) NULL,product_url VARCHAR(255) NULL,supplier_url VARCHAR(255) NULL,status ENUM('research','selected','active','paused','archived') NOT NULL DEFAULT 'research',created_at TIMESTAMP NULL,updated_at TIMESTAMP NULL,INDEX(brand_id,name),CONSTRAINT products_brand_fk FOREIGN KEY(brand_id) REFERENCES brands(id) ON DELETE CASCADE,CONSTRAINT products_category_fk FOREIGN KEY(category_id) REFERENCES categories(id) ON DELETE SET NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE tasks (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,title VARCHAR(255) NOT NULL,description TEXT NULL,brand_id BIGINT UNSIGNED NOT NULL,product_id BIGINT UNSIGNED NULL,assigned_to BIGINT UNSIGNED NULL,created_by BIGINT UNSIGNED NOT NULL,task_type ENUM('research','download','script','shoot','edit','design','voiceover','review','publish','other') NOT NULL DEFAULT 'edit',platform VARCHAR(255) NULL,priority ENUM('low','normal','high','urgent') NOT NULL DEFAULT 'normal',status ENUM('backlog','assigned','in_progress','review','revision','approved','published','completed') NOT NULL DEFAULT 'assigned',due_at DATETIME NULL,deliverables TEXT NULL,brief LONGTEXT NULL,approved_at DATETIME NULL,created_at TIMESTAMP NULL,updated_at TIMESTAMP NULL,INDEX(assigned_to,status),INDEX(brand_id,status),CONSTRAINT tasks_brand_fk FOREIGN KEY(brand_id) REFERENCES brands(id) ON DELETE CASCADE,CONSTRAINT tasks_product_fk FOREIGN KEY(product_id) REFERENCES products(id) ON DELETE SET NULL,CONSTRAINT tasks_assignee_fk FOREIGN KEY(assigned_to) REFERENCES users(id) ON DELETE SET NULL,CONSTRAINT tasks_creator_fk FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE research_sources (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,product_id BIGINT UNSIGNED NOT NULL,platform VARCHAR(255) NOT NULL,channel_name VARCHAR(255) NULL,url VARCHAR(255) NOT NULL,instructions TEXT NULL,added_by BIGINT UNSIGNED NULL,created_at TIMESTAMP NULL,updated_at TIMESTAMP NULL,CONSTRAINT sources_product_fk FOREIGN KEY(product_id) REFERENCES products(id) ON DELETE CASCADE,CONSTRAINT sources_user_fk FOREIGN KEY(added_by) REFERENCES users(id) ON DELETE SET NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE assets (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,brand_id BIGINT UNSIGNED NOT NULL,product_id BIGINT UNSIGNED NULL,task_id BIGINT UNSIGNED NULL,uploaded_by BIGINT UNSIGNED NULL,name VARCHAR(255) NOT NULL,asset_type ENUM('reference_video','raw_video','edited_video','image','thumbnail','script','voiceover','prompt','document','other') NOT NULL DEFAULT 'other',file_path VARCHAR(255) NULL,external_url VARCHAR(255) NULL,version VARCHAR(255) NOT NULL DEFAULT 'V1',status ENUM('draft','review','revision','approved','final','published') NOT NULL DEFAULT 'draft',tags TEXT NULL,notes TEXT NULL,created_at TIMESTAMP NULL,updated_at TIMESTAMP NULL,FULLTEXT KEY assets_search(name,tags,notes),CONSTRAINT assets_brand_fk FOREIGN KEY(brand_id) REFERENCES brands(id) ON DELETE CASCADE,CONSTRAINT assets_product_fk FOREIGN KEY(product_id) REFERENCES products(id) ON DELETE SET NULL,CONSTRAINT assets_task_fk FOREIGN KEY(task_id) REFERENCES tasks(id) ON DELETE SET NULL,CONSTRAINT assets_user_fk FOREIGN KEY(uploaded_by) REFERENCES users(id) ON DELETE SET NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE task_comments (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,task_id BIGINT UNSIGNED NOT NULL,user_id BIGINT UNSIGNED NOT NULL,body TEXT NOT NULL,comment_type ENUM('comment','revision','approval') NOT NULL DEFAULT 'comment',created_at TIMESTAMP NULL,updated_at TIMESTAMP NULL,CONSTRAINT comments_task_fk FOREIGN KEY(task_id) REFERENCES tasks(id) ON DELETE CASCADE,CONSTRAINT comments_user_fk FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE sessions (id VARCHAR(255) PRIMARY KEY,user_id BIGINT UNSIGNED NULL,ip_address VARCHAR(45) NULL,user_agent TEXT NULL,payload LONGTEXT NOT NULL,last_activity INT NOT NULL,INDEX(user_id),INDEX(last_activity)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE cache (`key` VARCHAR(255) PRIMARY KEY,value MEDIUMTEXT NOT NULL,expiration INT NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE cache_locks (`key` VARCHAR(255) PRIMARY KEY,owner VARCHAR(255) NOT NULL,expiration INT NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
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 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO users(name,email,password,role,is_active,created_at,updated_at) VALUES('Rapid Admin','admin@rapid.local','$2y$12$nDWzrgKXA8vIvX/iIqSCxujcj.3pLzAuvXEV6WxaMFPAlWJaV90L6','admin',1,NOW(),NOW());
INSERT INTO brands(name,page_name,phone,website,default_cta,primary_color,secondary_color,brand_rules,is_active,created_at,updated_at) VALUES('BG MART','BG MART','01833444348','https://bgmart.com.bd','এখনই অর্ডার করুন','#111827','#f59e0b','প্রোডাক্ট অপরিবর্তিত রাখুন। অনুমতি ছাড়া Creative-এ price বা Free Home Delivery লিখবেন না।',1,NOW(),NOW());
INSERT INTO categories(name,slug,created_at,updated_at) VALUES ('Gadget','gadget',NOW(),NOW()),('Household','household',NOW(),NOW()),('Kitchen','kitchen',NOW(),NOW()),('Beauty','beauty',NOW(),NOW()),('Fashion','fashion',NOW(),NOW()),('Watch','watch',NOW(),NOW()),('Bag','bag',NOW(),NOW()),('Toy','toy',NOW(),NOW()),('Fitness','fitness',NOW(),NOW()),('Pet','pet',NOW(),NOW()),('Travel','travel',NOW(),NOW()),('Office','office',NOW(),NOW());
SET FOREIGN_KEY_CHECKS=1;