-- =============================================
-- CrispScan AI API - Database Setup
-- Run this on MySQL/MariaDB
-- =============================================

-- Create database (if not exists)
CREATE DATABASE IF NOT EXISTS doc_scanner CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE doc_scanner;

-- =============================================
-- AI Configuration Table
-- Stores API keys, model settings, limits
-- =============================================
CREATE TABLE IF NOT EXISTS ai_config (
    id INT PRIMARY KEY AUTO_INCREMENT,
    config_key VARCHAR(50) UNIQUE NOT NULL,
    config_value TEXT,
    description VARCHAR(255),
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- =============================================
-- AI Usage Tracking Table
-- Logs every AI request for analytics & rate limiting
-- =============================================
CREATE TABLE IF NOT EXISTS ai_usage (
    id INT PRIMARY KEY AUTO_INCREMENT,
    device_id VARCHAR(100) NOT NULL,
    action VARCHAR(50) NOT NULL,
    image_path VARCHAR(255),
    tokens_used INT DEFAULT 0,
    is_premium TINYINT DEFAULT 0,
    response_time_ms INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_device_date (device_id, created_at),
    INDEX idx_action (action),
    INDEX idx_created (created_at)
) ENGINE=InnoDB;

-- =============================================
-- Premium Users Table (optional)
-- Track premium subscriptions
-- =============================================
CREATE TABLE IF NOT EXISTS premium_users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    device_id VARCHAR(100) UNIQUE NOT NULL,
    plan VARCHAR(50) DEFAULT 'monthly',
    started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP,
    is_active TINYINT DEFAULT 1,
    
    INDEX idx_device (device_id),
    INDEX idx_active (is_active, expires_at)
) ENGINE=InnoDB;

-- =============================================
-- Insert Default Configuration
-- =============================================
INSERT INTO ai_config (config_key, config_value, description) VALUES
-- AI Provider Settings
('ai_provider', 'openai', 'AI provider: openai or anthropic'),
('ai_model', 'gpt-4o-mini', 'Model: gpt-4o-mini, gpt-4o, claude-sonnet-4-20250514, claude-opus-4-20250514'),

-- API Keys (UPDATE THESE!)
('openai_api_key', 'sk-your-openai-key-here', 'OpenAI API Key'),
('anthropic_api_key', 'sk-ant-your-anthropic-key-here', 'Anthropic API Key'),

-- Rate Limits
('free_daily_limit', '5', 'Free tier daily AI scan limit'),
('premium_daily_limit', '1000', 'Premium tier daily limit (effectively unlimited)'),

-- Image Settings
('max_image_size_bytes', '5242880', 'Max image size in bytes (5MB)'),
('recommended_image_width', '1024', 'Recommended max image width for processing')

ON DUPLICATE KEY UPDATE config_value = VALUES(config_value);

-- =============================================
-- Useful Queries
-- =============================================

-- View current config
-- SELECT * FROM ai_config;

-- Update AI provider to Claude
-- UPDATE ai_config SET config_value = 'anthropic' WHERE config_key = 'ai_provider';
-- UPDATE ai_config SET config_value = 'claude-sonnet-4-20250514' WHERE config_key = 'ai_model';

-- Update API keys
-- UPDATE ai_config SET config_value = 'sk-xxx' WHERE config_key = 'openai_api_key';
-- UPDATE ai_config SET config_value = 'sk-ant-xxx' WHERE config_key = 'anthropic_api_key';

-- Check today's usage by device
-- SELECT device_id, COUNT(*) as usage_count FROM ai_usage WHERE DATE(created_at) = CURDATE() GROUP BY device_id;

-- Check total tokens used today
-- SELECT SUM(tokens_used) as total_tokens FROM ai_usage WHERE DATE(created_at) = CURDATE();

-- Check usage by action type
-- SELECT action, COUNT(*) as count, SUM(tokens_used) as tokens FROM ai_usage GROUP BY action;
