CREATE TABLE categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  icon VARCHAR(50),
  sort_order INT DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE prompts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  category_id INT,
  title VARCHAR(255),
  prompt_text TEXT NOT NULL,
  image_path VARCHAR(255),
  prompt_type VARCHAR(10) NOT NULL DEFAULT 'Free',
  is_trending TINYINT(1) DEFAULT 0,
  is_featured TINYINT(1) DEFAULT 0,
  sort_order INT DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
);

CREATE TABLE articles (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  thumbnail_path VARCHAR(255),
  content TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE settings (
  id INT AUTO_INCREMENT PRIMARY KEY,
  key_name VARCHAR(100) UNIQUE NOT NULL,
  value TEXT
);

CREATE TABLE admin_users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(100) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL
);

CREATE TABLE prompt_requests (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  description TEXT NOT NULL,
  email VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO settings (key_name, value) VALUES
  ('follow_me_url', 'https://instagram.com/yourpage'),
  ('how_to_use_url', 'https://yoursite.com/how-to-use'),
  ('privacy_policy_url', 'https://yoursite.com/privacy'),
  ('about_us_text', 'ImageGuru helps you find perfect AI prompts.'),
  ('share_text', 'Check out ImageGuru Prompt app!'),
  ('rate_us_url', 'https://play.google.com/store/apps/details?id=com.imageguru'),
  ('facebook_url', ''),
  ('twitter_url', ''),
  ('youtube_url', ''),
  ('tiktok_url', ''),
  ('admob_banner_id', 'ca-app-pub-XXXXX/XXXXX'),
  ('admob_rewarded_id', 'ca-app-pub-XXXXX/XXXXX'),
  ('home_section_title', 'Editor\'s Picks'),
  ('gemini_api_key', 'YOUR_GEMINI_API_KEY');
