CREATE DATABASE IF NOT EXISTS promptx CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE promptx;

CREATE TABLE IF NOT EXISTS users(
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 phone VARCHAR(15) NOT NULL UNIQUE,
 name VARCHAR(80) NULL,
 role ENUM('user','admin') NOT NULL DEFAULT 'user',
 is_active TINYINT(1) NOT NULL DEFAULT 1,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS otp_codes(
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 phone VARCHAR(15) NOT NULL,
 code_hash VARCHAR(255) NOT NULL,
 expires_at DATETIME NOT NULL,
 attempts TINYINT UNSIGNED NOT NULL DEFAULT 0,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 INDEX(phone)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS categories(
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(100) NOT NULL,
 slug VARCHAR(100) UNIQUE NOT NULL,
 icon VARCHAR(20) DEFAULT '✦',
 sort_order INT NOT NULL DEFAULT 0,
 is_active TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS prompts(
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 category_id INT UNSIGNED NULL,
 title VARCHAR(220) NOT NULL,
 slug VARCHAR(240) UNIQUE NOT NULL,
 description TEXT NULL,
 prompt_text LONGTEXT NOT NULL,
 negative_prompt TEXT NULL,
 image_url VARCHAR(1000) NOT NULL,
 price DECIMAL(12,0) NOT NULL DEFAULT 0,
 model_name VARCHAR(100) NULL,
 aspect_ratio VARCHAR(30) NULL,
 is_featured TINYINT(1) NOT NULL DEFAULT 0,
 status ENUM('published','draft','disabled') NOT NULL DEFAULT 'published',
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 FOREIGN KEY(category_id) REFERENCES categories(id) ON DELETE SET NULL,
 INDEX(price),INDEX(category_id),INDEX(status)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS orders(
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL,
 order_number VARCHAR(40) UNIQUE NOT NULL,
 amount DECIMAL(12,0) NOT NULL,
 status ENUM('pending','paid','failed','cancelled') NOT NULL DEFAULT 'pending',
 authority VARCHAR(100) NULL,
 ref_id VARCHAR(100) NULL,
 paid_at DATETIME NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
 INDEX(status),INDEX(created_at)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS order_items(
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 order_id BIGINT UNSIGNED NOT NULL,
 prompt_id BIGINT UNSIGNED NOT NULL,
 unit_price DECIMAL(12,0) NOT NULL,
 FOREIGN KEY(order_id) REFERENCES orders(id) ON DELETE CASCADE,
 FOREIGN KEY(prompt_id) REFERENCES prompts(id) ON DELETE RESTRICT,
 UNIQUE KEY uq_order_prompt(order_id,prompt_id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS purchases(
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL,
 prompt_id BIGINT UNSIGNED NOT NULL,
 order_id BIGINT UNSIGNED NOT NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
 FOREIGN KEY(prompt_id) REFERENCES prompts(id) ON DELETE RESTRICT,
 FOREIGN KEY(order_id) REFERENCES orders(id) ON DELETE CASCADE,
 UNIQUE KEY uq_user_prompt(user_id,prompt_id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS comments(
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL,
 prompt_id BIGINT UNSIGNED NOT NULL,
 rating TINYINT UNSIGNED NOT NULL DEFAULT 5,
 body TEXT NOT NULL,
 status ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
 FOREIGN KEY(prompt_id) REFERENCES prompts(id) ON DELETE CASCADE,
 INDEX(prompt_id,status),INDEX(user_id,status)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS support_tickets(
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL,
 subject VARCHAR(220) NOT NULL,
 status ENUM('open','waiting_admin','waiting_user','closed') NOT NULL DEFAULT 'open',
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
 INDEX(user_id,status),INDEX(updated_at)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS ticket_messages(
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 ticket_id BIGINT UNSIGNED NOT NULL,
 sender_type ENUM('user','admin') NOT NULL,
 sender_id BIGINT UNSIGNED NOT NULL,
 message TEXT NULL,
 attachment_path VARCHAR(1000) NULL,
 attachment_type VARCHAR(20) NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(ticket_id) REFERENCES support_tickets(id) ON DELETE CASCADE,
 INDEX(ticket_id,created_at)
) ENGINE=InnoDB;

INSERT INTO categories(name,slug,icon,sort_order,is_active) VALUES
('تک‌نفره','single','✦',1,1),('کاپلی','couple','♡',2,1),('فشن','fashion','✧',3,1),
('سینمایی','cinematic','◉',4,1),('محصول','product','◇',5,1),('پرتره','portrait','◌',6,1),
('فانتزی','fantasy','✺',7,1),('تبلیغاتی','commercial','▣',8,1)
ON DUPLICATE KEY UPDATE name=VALUES(name);

INSERT INTO prompts(category_id,title,slug,description,prompt_text,negative_prompt,image_url,price,model_name,aspect_ratio,is_featured,status)
VALUES
(1,'پرتره خیابانی لوکس','luxury-street-portrait','پرتره شهری با فلش و حس ادیتوریال لوکس.','Ultra-realistic luxury street fashion portrait, modern city architecture, direct flash, cinematic contrast, realistic skin texture, sharp eyes, 85mm lens, premium editorial styling, photorealistic.','blurry, deformed hands, extra fingers, duplicate person','https://images.unsplash.com/photo-1500648767791-00dcc994a43e?auto=format&fit=crop&w=1200&q=85',0,'Flux','4:5',1,'published'),
(2,'کاپل در نور طلایی','golden-couple','پرتره عاشقانه سینمایی برای دو نفر.','Cinematic couple portrait during golden hour, intimate natural pose, premium fashion, soft rim light, film grain, 50mm lens, realistic skin and hair, high-end editorial photography.','bad anatomy, distorted face, extra limbs','https://images.unsplash.com/photo-1529333166437-7750a6dd5a70?auto=format&fit=crop&w=1200&q=85',149000,'Flux','4:5',1,'published'),
(3,'فشن مینیمال مشکی','black-fashion','استایل ادیتوریال کاملاً مشکی و لوکس.','High-end black fashion editorial, minimalist architecture, dramatic rim lighting, hard flash, luxury magazine aesthetic, realistic skin pores, clean composition, photorealistic detail.','cartoon, CGI, plastic skin','https://images.unsplash.com/photo-1483985988355-763728e1935b?auto=format&fit=crop&w=1200&q=85',189000,'Midjourney','3:4',1,'published'),
(5,'عکس تبلیغاتی ساعت','watch-commercial','تصویر محصول لوکس برای کمپین تبلیغاتی.','Premium commercial product photography of a luxury smartwatch on reflective stone, controlled studio lighting, dramatic highlights, clean luxury background, crisp details, advertising campaign aesthetic.','blurred object, warped logo, text artifacts','https://images.unsplash.com/photo-1523275335684-37898b6baf30?auto=format&fit=crop&w=1200&q=85',99000,'Flux','1:1',0,'published'),
(6,'هدشات سینمایی','cinematic-headshot','هدشات حرفه‌ای با نورپردازی سینمایی.','Cinematic professional headshot, confident expression, soft key light, subtle blue ambient fill, realistic skin texture, premium grooming, shallow depth of field, 85mm studio photography.','deformed face, over-smoothed skin','https://images.unsplash.com/photo-1506794778202-cad84cf45f1d?auto=format&fit=crop&w=1200&q=85',0,'Flux','4:5',0,'published')
ON DUPLICATE KEY UPDATE title=VALUES(title),price=VALUES(price),status=VALUES(status);