-- Database creation
CREATE DATABASE IF NOT EXISTS api_platform;
USE api_platform;

-- 1. Users Table
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100) UNIQUE,
    password VARCHAR(255),
    api_key VARCHAR(64) UNIQUE, -- User ki apni unique key
    balance DECIMAL(10, 2) DEFAULT 0.00,
    status ENUM('active', 'blocked') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 2. Services Table (Master API Service Config)
CREATE TABLE services (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    slug VARCHAR(100) UNIQUE, -- URL format: adhar-verify
    endpoint TEXT,
    method VARCHAR(10) DEFAULT 'POST',
    price_per_hit DECIMAL(10, 2) DEFAULT 1.00,
    is_active TINYINT(1) DEFAULT 1
);

-- 3. Service Parameters (Header/Body config)
CREATE TABLE service_params (
    id INT AUTO_INCREMENT PRIMARY KEY,
    service_id INT,
    param_key VARCHAR(100),
    position ENUM('Header', 'Body'),
    is_required TINYINT(1) DEFAULT 1,
    FOREIGN KEY (service_id) REFERENCES services(id) ON DELETE CASCADE
);

-- 4. Transactions / Logs
CREATE TABLE transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    service_id INT,
    amount_deducted DECIMAL(10, 2),
    request_data TEXT,
    response_data TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE api_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    service_id INT,
    request_data TEXT,
    response_data TEXT,
    status_code INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);