

CREATE TABLE IF NOT EXISTS plans (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  slug VARCHAR(80) UNIQUE,
  description TEXT,
  disk_gb INT DEFAULT 10,
  bandwidth_gb INT DEFAULT 100,
  addon_domains INT DEFAULT 0,
  email_accounts INT DEFAULT 10,
  databases_count INT DEFAULT 10,
  whm_package VARCHAR(120) NOT NULL,
  price_monthly DECIMAL(10,2) NOT NULL,
  price_yearly  DECIMAL(10,2) NOT NULL,
  is_popular TINYINT(1) DEFAULT 0,
  is_active  TINYINT(1) DEFAULT 1,
  sort_order INT DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS coupons (
  id INT AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(60) UNIQUE NOT NULL,
  type ENUM('percent','flat') DEFAULT 'percent',
  value DECIMAL(10,2) NOT NULL,
  max_uses INT DEFAULT 0,
  used_count INT DEFAULT 0,
  min_amount DECIMAL(10,2) DEFAULT 0,
  expires_at DATE NULL,
  is_active TINYINT(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS invoices (
  id INT AUTO_INCREMENT PRIMARY KEY,
  invoice_no VARCHAR(30) UNIQUE,
  user_id INT NOT NULL,
  order_id INT NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  status ENUM('unpaid','paid','void') DEFAULT 'unpaid',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS tickets (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  subject VARCHAR(190) NOT NULL,
  priority ENUM('low','medium','high') DEFAULT 'medium',
  status ENUM('open','answered','closed') DEFAULT 'open',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ticket_replies (
  id INT AUTO_INCREMENT PRIMARY KEY,
  ticket_id INT NOT NULL,
  user_id INT NOT NULL,
  is_staff TINYINT(1) DEFAULT 0,
  message TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (ticket_id) REFERENCES tickets(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

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

INSERT IGNORE INTO plans (name,slug,description,disk_gb,bandwidth_gb,addon_domains,email_accounts,databases_count,whm_package,price_monthly,price_yearly,is_popular,sort_order) VALUES
('Starter','starter','Perfect for a single small website',10,100,0,10,10,'starter_pkg',99.00,999.00,0,1),
('Business','business','Great for growing businesses',30,500,5,50,50,'business_pkg',249.00,2499.00,1,2),
('Pro','pro','High-traffic sites and stores',100,2000,25,200,200,'pro_pkg',599.00,5999.00,0,3);

INSERT IGNORE INTO coupons (code,type,value,max_uses,min_amount) VALUES
('WELCOME10','percent',10,0,0),
('SAVE50','flat',50,100,199);

INSERT IGNORE INTO settings (k,v) VALUES
('site_name','HostPanel'),
('currency','₹'),
('upi_id','yourupi@okhdfc'),
('support_email','support@hostpanel.local');
