CREATE DATABASE IF NOT EXISTS subapi_db
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE subapi_db;

CREATE TABLE IF NOT EXISTS api_users (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    username VARCHAR(100) NOT NULL,
    api_key_hash CHAR(64) NOT NULL,
    api_limit INT UNSIGNED NOT NULL DEFAULT 5,
    used_count INT UNSIGNED NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_api_key_hash (api_key_hash),
    KEY idx_user_created (created_at)
) ENGINE=InnoDB;

-- Stores request status only. Do not store NID, DOB, or full API responses.
CREATE TABLE IF NOT EXISTS api_request_logs (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id BIGINT UNSIGNED NOT NULL,
    result_code SMALLINT UNSIGNED NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_user_created (user_id, created_at),
    CONSTRAINT fk_request_user
        FOREIGN KEY (user_id) REFERENCES api_users(id)
        ON DELETE CASCADE
) ENGINE=InnoDB;