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

CREATE TABLE IF NOT EXISTS translation_trial_usage (
    id INT AUTO_INCREMENT PRIMARY KEY,
    device_id VARCHAR(128) NOT NULL,
    request_count INT NOT NULL DEFAULT 0,
    char_count INT NOT NULL DEFAULT 0,
    first_used_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    last_used_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_device_id (device_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS translation_trial_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    device_id VARCHAR(128) NOT NULL,
    text_hash VARCHAR(64) DEFAULT NULL,
    char_count INT NOT NULL DEFAULT 0,
    target_lang VARCHAR(10) NOT NULL DEFAULT 'vi',
    source_lang VARCHAR(10) NOT NULL DEFAULT 'auto',
    provider VARCHAR(50) NOT NULL DEFAULT 'google_v2_trial',
    ip_address VARCHAR(64) DEFAULT NULL,
    user_agent TEXT DEFAULT NULL,
    status VARCHAR(50) NOT NULL DEFAULT 'success',
    error_message TEXT DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_device_id (device_id),
    INDEX idx_created_at (created_at),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS translation_global_usage (
    id INT AUTO_INCREMENT PRIMARY KEY,
    usage_month CHAR(7) NOT NULL,
    char_count INT NOT NULL DEFAULT 0,
    request_count INT NOT NULL DEFAULT 0,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_usage_month (usage_month)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;