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

CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) NOT NULL UNIQUE,
  password VARCHAR(255) NOT NULL,
  role ENUM('customer','admin') DEFAULT 'customer',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  description TEXT,
  price DECIMAL(10,2) NOT NULL,
  original_price DECIMAL(10,2) DEFAULT NULL,
  category ENUM('Admin','Dashboard','Gameplay','Roleplay','Bundle') NOT NULL,
  image VARCHAR(50) DEFAULT '🛡️',
  features JSON,
  file_name VARCHAR(255) NOT NULL,
  downloads INT DEFAULT 0,
  rating DECIMAL(2,1) DEFAULT 5.0,
  tag VARCHAR(50) DEFAULT '',
  active BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  total DECIMAL(10,2) NOT NULL,
  discount_code VARCHAR(50) DEFAULT NULL,
  discount_amount DECIMAL(10,2) DEFAULT 0,
  payment_method ENUM('card','paypal','bank') NOT NULL,
  payment_detail VARCHAR(255) DEFAULT NULL,
  status ENUM('pending','completed','cancelled') DEFAULT 'completed',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS order_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  order_id INT NOT NULL,
  product_id INT NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS user_products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  product_id INT NOT NULL,
  granted_by ENUM('purchase','admin') DEFAULT 'purchase',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_user_product (user_id, product_id),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS discount_codes (
  id INT AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(50) NOT NULL UNIQUE,
  discount DECIMAL(10,2) NOT NULL,
  type ENUM('percent','fixed') NOT NULL,
  active BOOLEAN DEFAULT TRUE,
  usage_count INT DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

INSERT INTO products (name, description, price, original_price, category, image, features, file_name, downloads, rating, tag) VALUES
('FiveM Admin Panel','Das ultimative Admin-Panel. Spieler verwalten, Bans, Kicks, Warnungen, Teleport und mehr. ESX & QBCore kompatibel.',29.99,39.99,'Admin','🛡️','["Spieler-Verwaltung","Ban/Kick/Warn","Live-Spielerliste","Teleport & Noclip","Permission System","ESX & QBCore"]','fivem-admin-panel-v4.2.zip',4521,4.9,'BESTSELLER'),
('Gangzone Control Panel','Gangzonen, Territorien und Fraktionskriege. Echtzeit-Karte mit Zonen-Editor und automatische Belohnungen.',19.99,NULL,'Gameplay','🔫','["Zonen-Editor","Echtzeit-Karte","Punkte-System","Auto-Belohnungen","Fraktions-Stats"]','gangzone-panel-v2.0.zip',2103,4.7,'NEU'),
('Wirtschafts-Dashboard','Geld-Flows, Marktpreise, Spieler-Kontostände, Inflations-Tracker und Economy-Reports in Echtzeit.',34.99,49.99,'Dashboard','💰','["Geld-Flow Tracking","Marktpreis-Editor","Spieler-Konten","Inflations-Monitor","CSV Export"]','economy-dashboard-v3.1.zip',3287,4.8,'SALE'),
('Fahrzeug-Manager Panel','Garage-System, Tuning-Preise, Fahrzeug-Spawner, Blacklist und Performance-Einstellungen.',14.99,NULL,'Gameplay','🏎️','["Garage-Verwaltung","Tuning-Editor","Fahrzeug-Spawner","Blacklist","Handling Editor"]','vehicle-manager-v1.8.zip',5120,4.6,''),
('RP Charakter-Panel','Charakter-Verwaltung mit Inventar-Editor, Skill-System, Personalausweis-Generator und Charakter-Datenbank.',24.99,34.99,'Roleplay','👤','["Charakter-DB","Inventar-Editor","Skill-System","Ausweis-Generator","Vorstrafen-Register"]','rp-character-panel-v5.0.zip',3890,4.9,'TOP'),
('Server Stats Dashboard','Echtzeit-Statistiken: Spielerzahlen, TPS, RAM/CPU, Uptime und Crash-Reports.',17.99,NULL,'Dashboard','📊','["Live-Spielerzahl","TPS/RAM/CPU","Uptime Monitor","Crash-Reports","Historie & Charts"]','server-stats-v2.3.zip',6230,4.8,'BELIEBT'),
('Job & Fraktion Panel','Jobs, Fraktionen, Ränge und Gehälter verwalten. Dienst-System und automatische Auszahlungen.',22.99,29.99,'Roleplay','💼','["Job-Editor","Rang-System","Gehaltsverwaltung","Duty-Tracking","Auto-Paycheck"]','job-faction-panel-v3.5.zip',2456,4.7,''),
('Whitelist & Bewerbung Panel','Automatisches Whitelist-System mit Bewerbungsformular, Discord-Integration und Auto-Freischaltung.',12.99,NULL,'Admin','📋','["Bewerbungsformular","Admin-Review","Discord Webhook","Auto-Whitelist","Statistiken"]','whitelist-panel-v1.5.zip',7845,4.5,'STARTER'),
('Drogen & Crafting Panel','Drogenrouten, Crafting-Rezepte, Verarbeitungspunkte und Preise konfigurierbar.',19.99,24.99,'Gameplay','🧪','["Routen-Editor","Crafting-Rezepte","Preis-Manager","Verarbeitungspunkte","Karten-Ansicht"]','drugs-crafting-panel-v2.1.zip',4102,4.6,''),
('Mega Bundle — Alle Panels','Alle 9 Panels zum Sparpreis! Lebenslange Updates und Priority Support.',129.99,199.99,'Bundle','🎁','["9 Panels inklusive","Lebenslange Updates","Priority Support","Installations-Hilfe","35% Ersparnis"]','mega-bundle-all-panels-v1.0.zip',1543,5.0,'MEGA DEAL');

INSERT INTO discount_codes (code, discount, type, active, usage_count) VALUES
('FIVEM20',20,'percent',TRUE,89),
('NEWSERVER',15,'fixed',TRUE,34),
('LAUNCH50',50,'percent',FALSE,210);
