CREATE DATABASE IF NOT EXISTS swiftpay_api_marketplace CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE swiftpay_api_marketplace;

CREATE TABLE IF NOT EXISTS wallets (
    user_id INT PRIMARY KEY,
    balance DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    frozen TINYINT(1) NOT NULL DEFAULT 0,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS services (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    display_name VARCHAR(150) NOT NULL,
    description TEXT,
    category VARCHAR(60) DEFAULT 'general',
    endpoint_url VARCHAR(500) NOT NULL,
    request_method ENUM('GET','POST') DEFAULT 'POST',
    static_headers TEXT NULL,
    default_price DECIMAL(8,4) NOT NULL DEFAULT 0.0000,
    rate_limit_per_min INT NOT NULL DEFAULT 60,
    rate_limit_per_day INT NOT NULL DEFAULT 5000,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    docs TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS user_service_rates (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    service_id INT NOT NULL,
    custom_price DECIMAL(8,4) NULL,
    rate_limit_per_min INT NULL,
    rate_limit_per_day INT NULL,
    is_blocked TINYINT(1) NOT NULL DEFAULT 0,
    UNIQUE KEY user_service (user_id, service_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (service_id) REFERENCES services(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    service_id INT NULL,
    amount DECIMAL(12,4) NOT NULL,
    type ENUM('credit','debit') NOT NULL,
    description VARCHAR(255),
    status ENUM('success','failed') DEFAULT 'success',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (service_id) REFERENCES services(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS api_requests (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    service_id INT NOT NULL,
    ip_address VARCHAR(45),
    request_data TEXT,
    response_data TEXT,
    http_status INT,
    cost DECIMAL(10,4),
    status ENUM('success','failed') DEFAULT 'success',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (service_id) REFERENCES services(id)
) ENGINE=InnoDB;

CREATE INDEX idx_requests_user_time ON api_requests(user_id, created_at);
CREATE INDEX idx_requests_service_time ON api_requests(service_id, created_at);
