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


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;


-- 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;
