CREATE TABLE IF NOT EXISTS users (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    device_id VARCHAR(128) NOT NULL UNIQUE,
    apple_user_id VARCHAR(255) NULL,
    credit_balance INT NOT NULL DEFAULT 0,
    total_purchased_credits INT NOT NULL DEFAULT 0,
    total_used_credits INT NOT NULL DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_apple_user_id (apple_user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS translation_trial_usage (
    id INT AUTO_INCREMENT PRIMARY KEY,
    device_id VARCHAR(128) NOT NULL UNIQUE,
    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
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS iap_transactions (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    device_id VARCHAR(128) NOT NULL,
    product_id VARCHAR(255) NOT NULL,
    transaction_id VARCHAR(128) NOT NULL UNIQUE,
    original_transaction_id VARCHAR(128) NULL,
    purchase_date DATETIME NULL,
    environment VARCHAR(50) DEFAULT NULL,
    credits_added INT NOT NULL DEFAULT 0,
    raw_payload MEDIUMTEXT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_device_id (device_id),
    INDEX idx_product_id (product_id),
    INDEX idx_original_transaction_id (original_transaction_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS credit_ledger (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    device_id VARCHAR(128) NOT NULL,
    type ENUM('trial','purchase','usage','refund','adjustment') NOT NULL,
    amount INT NOT NULL,
    balance_after INT NOT NULL,
    reference_id VARCHAR(128) NULL,
    note TEXT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_device_id (device_id),
    INDEX idx_type (type),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS translation_usage_logs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    device_id VARCHAR(128) NOT NULL,
    char_count INT NOT NULL,
    charged_chars INT NOT NULL,
    target_lang VARCHAR(10) NOT NULL,
    source_lang VARCHAR(10) DEFAULT 'auto',
    provider VARCHAR(50) NOT NULL,
    balance_before INT DEFAULT NULL,
    balance_after INT DEFAULT NULL,
    status VARCHAR(50) NOT NULL,
    error_message TEXT NULL,
    ip_address VARCHAR(64) DEFAULT NULL,
    user_agent 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 UNIQUE,
    char_count INT NOT NULL DEFAULT 0,
    request_count INT NOT NULL DEFAULT 0,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;