CREATE TABLE users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  role ENUM('admin','staff_support','editor','customer') NOT NULL DEFAULT 'customer',
  email VARCHAR(190) NOT NULL UNIQUE,
  username VARCHAR(60) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  email_verified_at DATETIME NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  last_login_at DATETIME NULL,
  last_login_ip VARCHAR(45) NULL,
  is_banned TINYINT(1) NOT NULL DEFAULT 0,
  ban_reason VARCHAR(255) NULL,
  twofa_enabled TINYINT(1) NOT NULL DEFAULT 0,
  twofa_secret VARCHAR(255) NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE plugins (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  slug VARCHAR(120) NOT NULL UNIQUE,
  name VARCHAR(150) NOT NULL,
  short_description VARCHAR(255) NOT NULL,
  description_html MEDIUMTEXT,
  price_monthly_try DECIMAL(10,2) NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  theme_primary VARCHAR(20) NOT NULL DEFAULT '#1a73e8',
  theme_accent VARCHAR(20) NOT NULL DEFAULT '#34a853',
  icon_path VARCHAR(255) NULL,
  banner_path VARCHAR(255) NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE plugin_versions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  plugin_id BIGINT UNSIGNED NOT NULL,
  version VARCHAR(40) NOT NULL,
  file_path VARCHAR(255) NOT NULL,
  file_sha256 VARCHAR(64) NOT NULL,
  changelog_md MEDIUMTEXT,
  created_at DATETIME NOT NULL,
  uploaded_by BIGINT UNSIGNED NOT NULL,
  is_public TINYINT(1) NOT NULL DEFAULT 1,
  FOREIGN KEY (plugin_id) REFERENCES plugins(id),
  FOREIGN KEY (uploaded_by) REFERENCES users(id),
  INDEX idx_plugin_version (plugin_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE wiki_pages (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  plugin_id BIGINT UNSIGNED NULL,
  slug VARCHAR(150) NOT NULL,
  title VARCHAR(200) NOT NULL,
  content_md MEDIUMTEXT,
  content_html MEDIUMTEXT,
  sort_order INT NOT NULL DEFAULT 0,
  is_public TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  updated_by BIGINT UNSIGNED NOT NULL,
  FOREIGN KEY (plugin_id) REFERENCES plugins(id),
  FOREIGN KEY (updated_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE comments (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  plugin_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  rating TINYINT NULL,
  content TEXT NOT NULL,
  status ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  moderated_by BIGINT UNSIGNED NULL,
  FOREIGN KEY (plugin_id) REFERENCES plugins(id),
  FOREIGN KEY (user_id) REFERENCES users(id),
  INDEX idx_comments_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE bug_reports (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  plugin_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NULL,
  title VARCHAR(200) NOT NULL,
  description TEXT NOT NULL,
  steps_to_reproduce TEXT,
  expected_result TEXT,
  actual_result TEXT,
  severity ENUM('low','medium','high','critical') NOT NULL DEFAULT 'low',
  status ENUM('open','investigating','resolved','closed','duplicate') NOT NULL DEFAULT 'open',
  admin_notes TEXT,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  assigned_to BIGINT UNSIGNED NULL,
  FOREIGN KEY (plugin_id) REFERENCES plugins(id),
  FOREIGN KEY (user_id) REFERENCES users(id),
  FOREIGN KEY (assigned_to) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE orders (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_no VARCHAR(40) NOT NULL UNIQUE,
  user_id BIGINT UNSIGNED NOT NULL,
  total_try DECIMAL(10,2) NOT NULL,
  status ENUM('pending','paid','failed','canceled','refunded') NOT NULL DEFAULT 'pending',
  provider ENUM('paytr','shopier','iyzico') NOT NULL,
  provider_payment_id VARCHAR(120) NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  paid_at DATETIME NULL,
  FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE order_items (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_id BIGINT UNSIGNED NOT NULL,
  plugin_id BIGINT UNSIGNED NOT NULL,
  plan ENUM('monthly') NOT NULL DEFAULT 'monthly',
  price_try DECIMAL(10,2) NOT NULL,
  period_days INT NOT NULL DEFAULT 30,
  FOREIGN KEY (order_id) REFERENCES orders(id),
  FOREIGN KEY (plugin_id) REFERENCES plugins(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE licenses (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  license_key VARCHAR(64) NOT NULL UNIQUE,
  user_id BIGINT UNSIGNED NOT NULL,
  plugin_id BIGINT UNSIGNED NOT NULL,
  status ENUM('active','expired','suspended','revoked') NOT NULL DEFAULT 'active',
  starts_at DATETIME NOT NULL,
  expires_at DATETIME NOT NULL,
  last_check_at DATETIME NULL,
  bound_servers_json JSON NULL,
  notes TEXT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id),
  FOREIGN KEY (plugin_id) REFERENCES plugins(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE payments_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  provider VARCHAR(50) NOT NULL,
  direction ENUM('request','webhook') NOT NULL,
  order_id BIGINT UNSIGNED NULL,
  payload_json JSON NOT NULL,
  signature_valid TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL,
  ip_address VARCHAR(45) NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE settings (
  id INT PRIMARY KEY,
  site_name VARCHAR(120) NOT NULL,
  base_url VARCHAR(255) NOT NULL,
  support_email VARCHAR(190) NULL,
  contact_email VARCHAR(190) NULL,
  contact_phone VARCHAR(50) NULL,
  social_links_json JSON NULL,
  footer_privacy_md MEDIUMTEXT,
  footer_kvkk_md MEDIUMTEXT,
  footer_terms_md MEDIUMTEXT,
  maintenance_mode TINYINT(1) NOT NULL DEFAULT 0,
  maintenance_message TEXT NULL,
  smtp_host VARCHAR(190) NULL,
  smtp_port VARCHAR(10) NULL,
  smtp_user VARCHAR(190) NULL,
  smtp_pass_enc VARCHAR(255) NULL,
  smtp_from VARCHAR(190) NULL,
  smtp_secure VARCHAR(20) NULL,
  recaptcha_site_key VARCHAR(190) NULL,
  recaptcha_secret VARCHAR(190) NULL,
  app_key VARCHAR(255) NOT NULL,
  updated_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE admin_audit_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  admin_user_id BIGINT UNSIGNED NOT NULL,
  action VARCHAR(120) NOT NULL,
  target_type VARCHAR(120) NOT NULL,
  target_id VARCHAR(120) NULL,
  meta_json JSON NULL,
  created_at DATETIME NOT NULL,
  ip_address VARCHAR(45) NULL,
  FOREIGN KEY (admin_user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE download_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  plugin_id BIGINT UNSIGNED NOT NULL,
  version_id BIGINT UNSIGNED NOT NULL,
  license_id BIGINT UNSIGNED NOT NULL,
  ip_address VARCHAR(45) NULL,
  user_agent TEXT NULL,
  created_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE coupons (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(40) NOT NULL UNIQUE,
  type ENUM('percent','fixed') NOT NULL,
  value DECIMAL(10,2) NOT NULL,
  max_uses INT NULL,
  used_count INT NOT NULL DEFAULT 0,
  expires_at DATETIME NULL,
  plugin_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE announcements (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(190) NOT NULL,
  content TEXT NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE email_queue (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  recipient VARCHAR(190) NOT NULL,
  subject VARCHAR(190) NOT NULL,
  body MEDIUMTEXT NOT NULL,
  status ENUM('pending','sent','failed') NOT NULL DEFAULT 'pending',
  created_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE api_tokens (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  token VARCHAR(80) NOT NULL UNIQUE,
  description VARCHAR(190) NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE rate_limits (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  identifier VARCHAR(64) NOT NULL UNIQUE,
  attempts INT NOT NULL,
  expires_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE api_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  provider_token VARCHAR(80) NOT NULL,
  request_json JSON NOT NULL,
  response_json JSON NOT NULL,
  ip_address VARCHAR(45) NULL,
  created_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE download_tokens (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  token VARCHAR(80) NOT NULL UNIQUE,
  version_id BIGINT UNSIGNED NOT NULL,
  expires_at DATETIME NOT NULL,
  FOREIGN KEY (version_id) REFERENCES plugin_versions(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE password_resets (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(190) NOT NULL,
  token VARCHAR(80) NOT NULL UNIQUE,
  expires_at DATETIME NOT NULL,
  INDEX idx_password_resets_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE support_tickets (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  subject VARCHAR(190) NOT NULL,
  message TEXT NOT NULL,
  status ENUM('open','pending','closed') NOT NULL DEFAULT 'open',
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
