-- mariadb.sql

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

USE user_cabinet;


-- Таблица пользователей
CREATE TABLE IF NOT EXISTS users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    name VARCHAR(100) NOT NULL DEFAULT '',

    password_hash VARCHAR(255) NOT NULL,

    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT uq_users_username UNIQUE (username),
    CONSTRAINT uq_users_email UNIQUE (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Таблица OAuth-клиентов
CREATE TABLE IF NOT EXISTS oauth_clients (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    user_id INT UNSIGNED NOT NULL,

    name VARCHAR(100) NOT NULL,
    client_id VARCHAR(80) NOT NULL,
    client_secret VARCHAR(255) NOT NULL,
    redirect_uri VARCHAR(255) NOT NULL,

    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT uq_oauth_clients_client_id UNIQUE (client_id),

    CONSTRAINT fk_oauth_clients_user
        FOREIGN KEY (user_id)
        REFERENCES users(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Таблица временных кодов авторизации
CREATE TABLE IF NOT EXISTS oauth_authorization_codes (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    client_id INT UNSIGNED NOT NULL,
    user_id INT UNSIGNED NOT NULL,

    code VARCHAR(128) NOT NULL,
    redirect_uri VARCHAR(255) NOT NULL,

    expires_at DATETIME NOT NULL,
    used TINYINT(1) NOT NULL DEFAULT 0,

    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT uq_oauth_authorization_codes_code UNIQUE (code),

    CONSTRAINT fk_oauth_codes_client
        FOREIGN KEY (client_id)
        REFERENCES oauth_clients(id)
        ON DELETE CASCADE,

    CONSTRAINT fk_oauth_codes_user
        FOREIGN KEY (user_id)
        REFERENCES users(id)
        ON DELETE CASCADE,

    INDEX idx_oauth_codes_client_id (client_id),
    INDEX idx_oauth_codes_user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Таблица access-токенов
CREATE TABLE IF NOT EXISTS oauth_access_tokens (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    client_id INT UNSIGNED NOT NULL,
    user_id INT UNSIGNED NOT NULL,

    token VARCHAR(128) NOT NULL,

    expires_at DATETIME NOT NULL,

    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT uq_oauth_access_tokens_token UNIQUE (token),

    CONSTRAINT fk_oauth_tokens_client
        FOREIGN KEY (client_id)
        REFERENCES oauth_clients(id)
        ON DELETE CASCADE,

    CONSTRAINT fk_oauth_tokens_user
        FOREIGN KEY (user_id)
        REFERENCES users(id)
        ON DELETE CASCADE,

    INDEX idx_oauth_tokens_client_id (client_id),
    INDEX idx_oauth_tokens_user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;