CREATE TABLE IF NOT EXISTS users (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(120) NOT NULL,email VARCHAR(190) NOT NULL UNIQUE,password_hash VARCHAR(255) NOT NULL,role ENUM('admin','manager') NOT NULL DEFAULT 'manager',active TINYINT(1) NOT NULL DEFAULT 1,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS orders (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,customer_name VARCHAR(160) NOT NULL,address TEXT,phone VARCHAR(40) NOT NULL,baby_name VARCHAR(120) NOT NULL,baby_age VARCHAR(20),baby_gender ENUM('girl','boy') NOT NULL DEFAULT 'girl',theme VARCHAR(190) NOT NULL,language ENUM('en','bn') NOT NULL DEFAULT 'en',total_amount DECIMAL(12,2) NOT NULL DEFAULT 0,advance_amount DECIMAL(12,2) NOT NULL DEFAULT 0,status ENUM('placed','production','review','confirmed','delivery','delivered','canceled') NOT NULL DEFAULT 'placed',cancel_note TEXT,assigned_manager_id INT UNSIGNED NULL,created_by INT UNSIGNED NOT NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,INDEX(status),INDEX(created_at),FOREIGN KEY(assigned_manager_id) REFERENCES users(id) ON DELETE SET NULL,FOREIGN KEY(created_by) REFERENCES users(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS order_history (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,order_id INT UNSIGNED NOT NULL,status VARCHAR(30) NOT NULL,note TEXT,user_id INT UNSIGNED NOT NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,INDEX(order_id,status,created_at),FOREIGN KEY(order_id) REFERENCES orders(id) ON DELETE CASCADE,FOREIGN KEY(user_id) REFERENCES users(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS books (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,order_id INT UNSIGNED NOT NULL UNIQUE,front_cover VARCHAR(255),back_cover VARCHAR(255),updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,FOREIGN KEY(order_id) REFERENCES orders(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS production_books (id VARCHAR(64) PRIMARY KEY,order_id VARCHAR(64) NOT NULL,theme VARCHAR(190) NOT NULL,language ENUM('en','bn') NOT NULL DEFAULT 'en',book_json LONGTEXT NOT NULL,created_by INT UNSIGNED NULL,updated_by INT UNSIGNED NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,INDEX(order_id),INDEX(updated_at)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS book_pages (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,book_id INT UNSIGNED NOT NULL,page_no INT NOT NULL,title VARCHAR(255),story_text TEXT,image_prompt MEDIUMTEXT,page_image VARCHAR(255),text_position ENUM('top','bottom','left','right','full') DEFAULT 'bottom',UNIQUE(book_id,page_no),FOREIGN KEY(book_id) REFERENCES books(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS print_batches (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,batch_date DATE NOT NULL,books_count INT NOT NULL DEFAULT 0,printing_cost DECIMAL(12,2) DEFAULT 0,packaging_cost DECIMAL(12,2) DEFAULT 0,other_cost DECIMAL(12,2) DEFAULT 0,manager_received DECIMAL(12,2) DEFAULT 0,note TEXT,created_by INT UNSIGNED NOT NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,INDEX(batch_date),FOREIGN KEY(created_by) REFERENCES users(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS overheads (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,cost_date DATE NOT NULL,category ENUM('marketing','salary','other') NOT NULL,amount DECIMAL(12,2) NOT NULL,note TEXT,created_by INT UNSIGNED NOT NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,INDEX(cost_date),FOREIGN KEY(created_by) REFERENCES users(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS cms_state (state_key VARCHAR(80) PRIMARY KEY,state_json LONGTEXT NOT NULL,updated_by INT UNSIGNED NULL,updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,FOREIGN KEY(updated_by) REFERENCES users(id) ON DELETE SET NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS store_products (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,title VARCHAR(190) NOT NULL,title_bn VARCHAR(190) NOT NULL DEFAULT '',slug VARCHAR(190) NOT NULL UNIQUE,theme VARCHAR(190) NOT NULL,summary TEXT NOT NULL,summary_bn TEXT,price DECIMAL(12,2) NOT NULL DEFAULT 0,cover_image VARCHAR(255),gallery_json TEXT,active TINYINT(1) NOT NULL DEFAULT 1,sort_order INT NOT NULL DEFAULT 0,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,INDEX(active,sort_order,updated_at)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS store_coupons (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,code VARCHAR(60) NOT NULL UNIQUE,discount_type ENUM('fixed','percent') NOT NULL DEFAULT 'fixed',discount_value DECIMAL(12,2) NOT NULL DEFAULT 0,active TINYINT(1) NOT NULL DEFAULT 1,expires_at DATE,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS store_settings (setting_key VARCHAR(80) PRIMARY KEY,setting_value TEXT NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS store_sliders (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,title VARCHAR(190) NOT NULL DEFAULT '',subtitle TEXT,desktop_image VARCHAR(255),mobile_image VARCHAR(255),button_text VARCHAR(80) NOT NULL DEFAULT '',button_url VARCHAR(255) NOT NULL DEFAULT '',sort_order INT NOT NULL DEFAULT 0,active TINYINT(1) NOT NULL DEFAULT 1,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,INDEX(active,sort_order)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS store_orders (id VARCHAR(64) PRIMARY KEY,product_id INT UNSIGNED NOT NULL,customer_name VARCHAR(160) NOT NULL,phone VARCHAR(40) NOT NULL,customer_email VARCHAR(190),delivery_address TEXT NOT NULL,delivery_zone ENUM('inside','outside') NOT NULL,delivery_fee DECIMAL(12,2) NOT NULL,notes TEXT,character_name VARCHAR(120) NOT NULL,character_age VARCHAR(20) NOT NULL,character_gender ENUM('girl','boy') NOT NULL,book_language ENUM('en','bn') NOT NULL DEFAULT 'en',baby_photo VARCHAR(255),extra_photos_json TEXT,subtotal DECIMAL(12,2) NOT NULL,discount DECIMAL(12,2) NOT NULL DEFAULT 0,coupon_code VARCHAR(60),total DECIMAL(12,2) NOT NULL,status VARCHAR(30) NOT NULL DEFAULT 'placed',created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,INDEX(status,created_at),FOREIGN KEY(product_id) REFERENCES store_products(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS store_email_log (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,order_id VARCHAR(64) NOT NULL,recipient VARCHAR(190) NOT NULL,email_type ENUM('admin','customer') NOT NULL,sent TINYINT(1) NOT NULL DEFAULT 0,error_message VARCHAR(255),created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,INDEX(order_id,created_at)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
