
CREATE TABLE web_users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) UNIQUE NOT NULL,
  password TEXT NOT NULL,
  role ENUM('admin','user') DEFAULT 'user'
);

CREATE TABLE balances (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  amount DECIMAL(10,2) DEFAULT 0,
  FOREIGN KEY (user_id) REFERENCES web_users(id)
);

CREATE TABLE vpn_servers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100),
  ip_address VARCHAR(100),
  api_port INT,
  api_user VARCHAR(100),
  api_pass TEXT
);

CREATE TABLE vpn_prices (
  id INT AUTO_INCREMENT PRIMARY KEY,
  price_per_month DECIMAL(10,2) NOT NULL DEFAULT 20000
);

CREATE TABLE vpn_accounts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT,
  server_id INT,
  username VARCHAR(50),
  vpn_ip VARCHAR(20),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES web_users(id),
  FOREIGN KEY (server_id) REFERENCES vpn_servers(id)
);

-- Admin default password: 'vpnku123'
INSERT INTO web_users (username, password, role) VALUES
('admin', '$2y$10$qCjZcMnlw4lfXQ0dTFOOXe8eBhjKjLzEYZg0.z8O9zMwN39Xx/z3S', 'admin');

INSERT INTO vpn_prices (price_per_month) VALUES (20000);

CREATE TABLE topup_requests (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  method ENUM('tripay','manual') DEFAULT 'manual',
  status ENUM('pending','success','failed') DEFAULT 'pending',
  reference VARCHAR(100),
  note TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES web_users(id)
);

ALTER TABLE vpn_accounts ADD COLUMN expired_at DATETIME;
