-- Rental Panel schema + seed
-- Import via phpMyAdmin into your database.

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS=0;

CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  password VARCHAR(255) NOT NULL,
  role ENUM('admin','customer') NOT NULL DEFAULT 'customer',
  phone VARCHAR(40) DEFAULT NULL,
  status TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  slug VARCHAR(140) NOT NULL UNIQUE,
  status TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS packages (
  id INT AUTO_INCREMENT PRIMARY KEY,
  category_id INT DEFAULT NULL,
  name VARCHAR(160) NOT NULL,
  description TEXT,
  features TEXT,
  price DECIMAL(10,2) NOT NULL DEFAULT 0,
  duration_days INT NOT NULL DEFAULT 30,
  image VARCHAR(255) DEFAULT NULL,
  status TINYINT(1) NOT NULL DEFAULT 1,
  is_featured TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS rentals (
  id INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT NOT NULL,
  package_id INT NOT NULL,
  start_date DATE NOT NULL,
  end_date DATE NOT NULL,
  status ENUM('pending','active','completed','cancelled') NOT NULL DEFAULT 'pending',
  notes TEXT,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (customer_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (package_id) REFERENCES packages(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  order_no VARCHAR(40) NOT NULL UNIQUE,
  customer_id INT NOT NULL,
  package_id INT NOT NULL,
  amount DECIMAL(10,2) NOT NULL DEFAULT 0,
  status ENUM('pending','paid','cancelled','refunded') NOT NULL DEFAULT 'pending',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (customer_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (package_id) REFERENCES packages(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS payments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  order_id INT NOT NULL,
  method VARCHAR(60) NOT NULL DEFAULT 'manual',
  txn_id VARCHAR(120) DEFAULT NULL,
  amount DECIMAL(10,2) NOT NULL DEFAULT 0,
  status ENUM('pending','success','failed','refunded') NOT NULL DEFAULT 'pending',
  paid_at DATETIME DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS settings (
  k VARCHAR(80) NOT NULL PRIMARY KEY,
  v LONGTEXT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS messages (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(190) NOT NULL,
  subject VARCHAR(200) DEFAULT NULL,
  message TEXT NOT NULL,
  is_read TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS media (
  id INT AUTO_INCREMENT PRIMARY KEY,
  filename VARCHAR(255) NOT NULL,
  original_name VARCHAR(255) NOT NULL,
  mime VARCHAR(100) NOT NULL,
  size_bytes INT NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS activity_logs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT DEFAULT NULL,
  action VARCHAR(120) NOT NULL,
  details TEXT,
  ip VARCHAR(60) DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS faqs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  question VARCHAR(255) NOT NULL,
  answer TEXT NOT NULL,
  sort_order INT NOT NULL DEFAULT 0,
  status TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS features (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(160) NOT NULL,
  description TEXT,
  icon VARCHAR(80) DEFAULT 'bi-stars',
  sort_order INT NOT NULL DEFAULT 0,
  status TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS=1;

-- Seed admin (password: Admin@12345)
INSERT INTO users (name,email,password,role) VALUES
('Administrator','admin@example.com','$2b$10$gEEjmRnrrQ3qCwSnQ7H8s.p2nuYnNZ3yxmWDGRTvktJtoZYyJw7ey','admin')
ON DUPLICATE KEY UPDATE email=email;

-- Seed categories (SMM Rental)
INSERT INTO categories (name,slug) VALUES
('SMM Panel Rental','smm-panel-rental'),
('Reseller Plans','reseller-plans'),
('Enterprise / Whitelabel','enterprise-whitelabel');

-- Seed packages (SMM Rental)
INSERT INTO packages (category_id,name,description,features,price,duration_days,is_featured) VALUES
(1,'Starter SMM Panel','Rent a ready-to-go SMM panel for individuals & small resellers.','1 Admin login\n5,000 services\nInstagram, TikTok, YouTube providers\nBasic support',9.99,30,0),
(2,'Pro SMM Reseller','Best for growing SMM resellers — full provider API access.','3 Admin logins\n20,000+ services\nAuto-order via API\nChild panel support\nPriority support',29.99,30,1),
(3,'Whitelabel SMM Enterprise','Whitelabel SMM platform with your branding & domain.','Unlimited services\nCustom domain & branding\nChild panels\n24/7 dedicated support\nAccount manager',99.99,30,0);

-- Seed features
INSERT INTO features (title,description,icon,sort_order) VALUES
('Lightning Fast','Optimized performance on every page.','bi-lightning-charge',1),
('Secure by Default','Best-practice security baked in.','bi-shield-lock',2),
('Fully Responsive','Looks great on any device.','bi-phone',3),
('24/7 Support','We are here whenever you need us.','bi-headset',4);

-- Seed FAQs
INSERT INTO faqs (question,answer,sort_order) VALUES
('How do I get started?','Sign up, pick a package, and you are live in minutes.',1),
('Can I cancel anytime?','Yes — cancel from your dashboard any time, no fees.',2),
('Do you offer refunds?','Yes, within 14 days of purchase.',3),
('Is my data secure?','All data is encrypted in transit and at rest.',4);

-- Seed settings
INSERT INTO settings (k,v) VALUES
('site_name','SMM Rental Panel'),
('site_tagline','Rent your own SMM panel — fully managed, instant setup.'),
('currency','USD'),
('currency_symbol',''),
('google_enabled','0'),
('google_client_id',''),
('google_client_secret',''),
('site_logo',''),
('site_banner',''),
('site_favicon',''),
('contact_email','hello@example.com'),
('contact_phone','+1 555 000 0000'),
('contact_address','123 Market St, Your City'),
('hero_title','Rent your own SMM Panel in minutes'),
('hero_subtitle','Fully managed SMM panel rentals — Instagram, TikTok, YouTube, Facebook services with API, child panels, and reseller tools.'),
('hero_cta_text','See Rental Packages'),
('hero_cta_url','packages.php'),
('about_html','<p>We help rental businesses grow with a powerful, easy-to-use platform.</p>'),
('link_admin_demo','#'),
('link_demo_panel','#'),
('link_whatsapp',''),
('link_telegram',''),
('link_discord',''),
('link_facebook',''),
('link_instagram',''),
('link_youtube',''),
('link_twitter',''),
('popup_enabled','0'),
('popup_title','Welcome!'),
('popup_message','Check out our new Pro plan — 20% off this week.'),
('popup_button_text','See Plans'),
('popup_button_url','pricing.php'),
('maintenance_mode','0'),
('maintenance_message','We are performing scheduled maintenance. We will be back shortly.'),
('seo_title','Rental Panel — Premium Rental Management'),
('seo_description','Modern, secure rental management panel for growing businesses.'),
('seo_keywords','rental, panel, management, packages'),
('seo_og_image',''),
('smtp_host',''),
('smtp_port','587'),
('smtp_user',''),
('smtp_pass',''),
('smtp_from','no-reply@example.com'),
('smtp_from_name','Rental Panel'),
('smtp_encryption','tls'),
('razorpay_enabled','0'),
('razorpay_key_id',''),
('razorpay_key_secret',''),
('razorpay_webhook_secret','')
ON DUPLICATE KEY UPDATE k=k;

-- ============= USER PANEL TABLES =============
CREATE TABLE IF NOT EXISTS wallets (
  user_id INT NOT NULL PRIMARY KEY,
  balance DECIMAL(12,2) NOT NULL DEFAULT 0,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS fund_requests (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  amount DECIMAL(12,2) NOT NULL,
  method VARCHAR(60) NOT NULL DEFAULT 'manual',
  txn_ref VARCHAR(120) DEFAULT NULL,
  note TEXT,
  status ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS user_panels (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  package_id INT DEFAULT NULL,
  panel_name VARCHAR(160) NOT NULL,
  panel_url VARCHAR(255) DEFAULT NULL,
  domain VARCHAR(190) DEFAULT NULL,
  status ENUM('pending','active','expired','suspended') NOT NULL DEFAULT 'pending',
  starts_on DATE DEFAULT NULL,
  expires_on DATE DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS affiliates (
  user_id INT NOT NULL PRIMARY KEY,
  ref_code VARCHAR(24) NOT NULL UNIQUE,
  commission_rate DECIMAL(5,2) NOT NULL DEFAULT 10.00,
  total_earned DECIMAL(12,2) NOT NULL DEFAULT 0,
  clicks INT NOT NULL DEFAULT 0,
  referrals INT NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;