CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(100) NOT NULL UNIQUE,
  password VARCHAR(255) NOT NULL,
  full_name VARCHAR(255),
  package_name VARCHAR(100),
  monthly_price DECIMAL(10,2) NOT NULL,
  mikrotik_pppoe_name VARCHAR(100) NOT NULL,
  active TINYINT(1) DEFAULT 1,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE bills (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  period_month INT NOT NULL,
  period_year INT NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  generated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  due_date DATE NOT NULL,
  status ENUM('unpaid','paid','cancelled') DEFAULT 'unpaid',
  paid_at DATETIME NULL,
  invoice_code VARCHAR(64) UNIQUE,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE transactions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  bill_id INT,
  gateway VARCHAR(50),
  gateway_id VARCHAR(128),
  amount DECIMAL(10,2),
  status VARCHAR(50),
  raw_response TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (bill_id) REFERENCES bills(id)
);

CREATE TABLE logs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT,
  action VARCHAR(255),
  note TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);