-- HostPanel - Hosting Billing + WHM Automation
-- MySQL 5.7+ / MariaDB 10.3+
-- NOTE: connect to the database configured in includes/config.php; the
-- installer runs each statement against that connection. Do NOT include
-- CREATE DATABASE / USE here (shared hosting users can't create DBs).

CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  phone VARCHAR(20),
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('user','admin') DEFAULT 'user',
  wallet_balance DECIMAL(10,2) DEFAULT 0.00,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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 whm_servers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  hostname VARCHAR(190) NOT NULL,
  port INT DEFAULT 2087,
  whm_user VARCHAR(80) DEFAULT 'root',
  api_token VARCHAR(255) NOT NULL,
  nameserver1 VARCHAR(120),
  nameserver2 VARCHAR(120),
  is_active TINYINT(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  plan_id INT NOT NULL,
  cycle ENUM('monthly','yearly') DEFAULT 'monthly',
  domain VARCHAR(190) NOT NULL,
  cpanel_username VARCHAR(16),
  cpanel_password VARCHAR(120),
  price DECIMAL(10,2) NOT NULL,
  discount DECIMAL(10,2) DEFAULT 0,
  coupon_code VARCHAR(60) NULL,
  total DECIMAL(10,2) NOT NULL,
  payment_method ENUM('wallet','upi','manual') DEFAULT 'upi',
  payment_ref VARCHAR(120),
  payment_status ENUM('pending','paid','failed','refunded') DEFAULT 'pending',
  order_status  ENUM('pending','approved','provisioned','suspended','terminated','rejected') DEFAULT 'pending',
  whm_server_id INT NULL,
  auto_renew TINYINT(1) DEFAULT 0,
  expires_at DATE NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (plan_id) REFERENCES plans(id),
  FOREIGN KEY (whm_server_id) REFERENCES whm_servers(id) ON DELETE SET NULL
) 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;

-- SEED DATA
-- Default admin (installer resets email + password): admin@hostpanel.local / Admin@123
INSERT IGNORE INTO users (name,email,password_hash,role) VALUES
('Super Admin','admin@hostpanel.local','$2y$10$Q9m6Xk2hV5jI3wYbT9uHqOe1Kx0Kz.n4Rc6mS7fT8u9V0wX1yZ2Aa','admin');

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