-- Create database (run once, or create manually)
-- CREATE DATABASE credits_app CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE credits_app;

CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(191) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  apikey VARCHAR(64) UNIQUE NOT NULL,
  role ENUM('user','admin') NOT NULL DEFAULT 'user',
  status ENUM('Normal','Paid','Plus') NOT NULL DEFAULT 'Normal',
  credits BIGINT NOT NULL DEFAULT 0,
  free_requests_remaining INT NOT NULL DEFAULT 0,
  last_billing_month CHAR(7) DEFAULT NULL, -- e.g. '2025-09'
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS topups (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  amount BIGINT NOT NULL,
  method VARCHAR(64) DEFAULT 'manual',
  note VARCHAR(255) DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS api_logs (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  apikey VARCHAR(64) NOT NULL,
  user_status_at_call ENUM('Normal','Paid','Plus') NOT NULL,
  cost BIGINT NOT NULL DEFAULT 0, -- credits deducted this call
  credits_after BIGINT NOT NULL DEFAULT 0,
  status_returned VARCHAR(32) NOT NULL,
  meta JSON DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB;

-- Create an initial admin user (change password later)
-- INSERT INTO users(email,password_hash,apikey,role,status,credits,free_requests_remaining)
-- VALUES ('admin@example.com', SHA2('admin123',256), 'dev-api-key', 'admin','Normal',0,0);
