-- WAAM AI Bot Backend - Database Schema
-- Version: 2.0.0 - Client data stays on WordPress; server stores only site config and optional usage logs.

-- Create database (run this first if needed)
-- CREATE DATABASE waam_backend CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- USE waam_backend;

-- Sites table - registered WordPress installations (config only; no files/embeddings/conversations)
CREATE TABLE IF NOT EXISTS sites (
    id INT AUTO_INCREMENT PRIMARY KEY,
    site_url VARCHAR(500) NOT NULL,
    site_key VARCHAR(64) NOT NULL UNIQUE,
    license_key VARCHAR(100) NOT NULL,
    api_key_encrypted TEXT,
    model VARCHAR(50) DEFAULT 'gpt-4o-mini',
    embedding_model VARCHAR(50) DEFAULT 'text-embedding-3-small',
    max_tokens INT DEFAULT 1000,
    temperature DECIMAL(3,2) DEFAULT 0.70,
    system_prompt TEXT,
    widget_settings JSON,
    functions JSON,
    status ENUM('active', 'suspended', 'expired') DEFAULT 'active',
    -- Unix timestamp of the last successful/attempted license validation.
    last_validated_at INT UNSIGNED NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_site_key (site_key),
    INDEX idx_license (license_key),
    INDEX idx_status (status),
    INDEX idx_last_validated (last_validated_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- If upgrading an existing database, you may need to run:
--   ALTER TABLE sites ADD COLUMN last_validated_at INT UNSIGNED NULL;
--   CREATE INDEX idx_last_validated ON sites(last_validated_at);

-- Usage logs table - optional API usage tracking (your analytics only)
CREATE TABLE IF NOT EXISTS usage_logs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    site_id INT NOT NULL,
    endpoint VARCHAR(100) NOT NULL,
    prompt_tokens INT DEFAULT 0,
    completion_tokens INT DEFAULT 0,
    total_tokens INT DEFAULT 0,
    model VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (site_id) REFERENCES sites(id) ON DELETE CASCADE,
    INDEX idx_site (site_id),
    INDEX idx_created (created_at),
    INDEX idx_endpoint (endpoint)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- NOTE: Files, embeddings, and conversations are stored on the client (WordPress) only.
-- The server provides only: auth, settings sync, embeddings/create (returns vector), chat (accepts context, returns reply).