-- ============================================================================
-- WaverTech Router Database Schema
-- ============================================================================
-- This schema is designed to store router (device) authentication, subscription,
-- license, and blocklist data for local router operation
-- Compatible with MySQL 5.7.8+ (requires JSON support)
-- ============================================================================

-- Drop tables if they exist (in reverse order of dependencies)
DROP TABLE IF EXISTS licenses;

DROP TABLE IF EXISTS subscription;

DROP TABLE IF EXISTS tokens;

DROP TABLE IF EXISTS pairing_tokens;

DROP TABLE IF EXISTS blocklisted_companies;

DROP TABLE IF EXISTS connection;

-- ============================================================================
-- 1. Router Connection Table
-- ============================================================================
-- Stores the primary connection/registration data for the router
-- One record per router (single device)

CREATE TABLE connection (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_id CHAR(36) NOT NULL COMMENT 'UUID of the company from backend',
    company_name VARCHAR(255) NOT NULL COMMENT 'Name of the company',
    owner_fullname VARCHAR(256) NOT NULL COMMENT 'Full name of the company owner',
    owner_email VARCHAR(254) NOT NULL COMMENT 'Email of the company owner',
    device_id CHAR(36) NOT NULL COMMENT 'UUID of the device from backend',
    connection_status ENUM(
        'pending',
        'connected',
        'connection_rejected',
        'disconnected'
    ) DEFAULT 'pending' COMMENT 'Current connection status',
    is_paired BOOLEAN DEFAULT FALSE COMMENT 'Whether the device is paired with the router',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'When the connection was created',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Last update timestamp',
    INDEX idx_company_id (company_id),
    INDEX idx_device_id (device_id),
    INDEX idx_connection_status (connection_status),
    UNIQUE KEY unique_device (device_id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = 'Primary connection data for the router';

-- ============================================================================
-- 2. Router Blocklisted Companies Table
-- ============================================================================
-- Stores companies that are blocked from connecting to this router

CREATE TABLE blocklisted_companies (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_id CHAR(36) NOT NULL COMMENT 'UUID of the blocked company from backend',
    company_name VARCHAR(255) NOT NULL COMMENT 'Name of the blocked company',
    reason TEXT DEFAULT NULL COMMENT 'Reason for blocklisting', # KEEP-IT?
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'When the company was blocked',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Last update timestamp',
    
    INDEX idx_company_id (company_id),
    UNIQUE KEY unique_blocked_company (company_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 
COMMENT='Companies blocked from connecting to this router';

-- ============================================================================
-- 3. Router Subscription Table
-- ============================================================================
-- Stores the current active subscription for the router
-- One active subscription at a time

CREATE TABLE subscription (
    id INT AUTO_INCREMENT PRIMARY KEY,
    subscription_id CHAR(36) NOT NULL COMMENT 'UUID of the subscription from backend',
    pricing_plan_id CHAR(36) NOT NULL COMMENT 'UUID of the pricing plan from backend',
    pricing_plan_name VARCHAR(255) NOT NULL COMMENT 'Name of the pricing plan',
    pricing_plan_slug VARCHAR(255) DEFAULT NULL COMMENT 'Slug of the pricing plan',
    price_per_month DECIMAL(10, 2) NOT NULL COMMENT 'Monthly price',
    status ENUM(
        'active',
        'suspended',
        'canceled',
        'expired',
        'grace_period'
    ) DEFAULT 'active' COMMENT 'Current subscription status',
    start_date DATETIME NOT NULL COMMENT 'Subscription start date',
    end_date DATETIME NOT NULL COMMENT 'Subscription end date',
    grace_period_days INT DEFAULT 0 COMMENT 'Grace period in days',
    stripe_subscription_id VARCHAR(255) DEFAULT NULL COMMENT 'Stripe subscription ID',
    stripe_invoice_id VARCHAR(255) DEFAULT NULL COMMENT 'Stripe invoice ID',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'When the subscription record was created',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Last update timestamp',
    synced_at TIMESTAMP NULL DEFAULT NULL COMMENT 'Last time synced from backend',
    INDEX idx_status (status),
    INDEX idx_end_date (end_date),
    UNIQUE KEY unique_active_subscription (status)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = 'Active subscription for the router';

-- ============================================================================
-- 4. Router Licenses Table
-- ============================================================================
-- Stores licenses for services available in the subscription
-- Multiple licenses per subscription (one per service)
-- Capacities are stored as JSON array in the capacities column

CREATE TABLE licenses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    service_id CHAR(36) NOT NULL COMMENT 'UUID of the service from backend',
    service_name VARCHAR(255) NOT NULL COMMENT 'Name of the service',
    service_slug VARCHAR(255) DEFAULT NULL COMMENT 'Slug of the service',
    service_description TEXT DEFAULT NULL COMMENT 'Description of the service',
    service_jwt_key VARCHAR(255) NOT NULL COMMENT 'JWT key for service authentication',
    license_id CHAR(36) NOT NULL COMMENT 'UUID of the license from backend',
    license_key VARCHAR(255) NOT NULL COMMENT 'License key string',
    -- ✅ JSON stored as TEXT (MariaDB 10.1 compatible)
    capacities TEXT DEFAULT NULL COMMENT 'JSON array of capacity objects: [{"name":"Claude-4.5","unit":"Token","limit":25000,"used":0}]',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'When the license was created',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Last update timestamp',
    synced_at TIMESTAMP NULL DEFAULT NULL COMMENT 'Last time synced from backend',
    INDEX idx_service_id (service_id),
    INDEX idx_license_key (license_key),
    UNIQUE KEY unique_license (license_id),
    UNIQUE KEY unique_service_per_license (service_id, license_id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = 'Service licenses for the router';

-- ============================================================================
-- 5. Router Tokens Table
-- ============================================================================
-- Stores access and refresh tokens for the router device itself
-- Tokens are for the device, not per service or company

CREATE TABLE tokens (
    id INT AUTO_INCREMENT PRIMARY KEY,
    token_type ENUM('access', 'refresh') NOT NULL COMMENT 'Type of token',
    token TEXT NOT NULL COMMENT 'The actual token string (JWT)',
    expires_at DATETIME NOT NULL COMMENT 'When the token expires',
    is_active BOOLEAN DEFAULT TRUE COMMENT 'Whether this token is currently active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'When the token was created',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Last update timestamp',
    last_used_at TIMESTAMP NULL DEFAULT NULL COMMENT 'Last time this token was used',
    INDEX idx_token_type (token_type),
    INDEX idx_expires_at (expires_at),
    INDEX idx_is_active (is_active)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = 'Access and refresh tokens for the router device';

-- ============================================================================
-- 6. Pairing Tokens Table
-- ============================================================================
-- Stores single-use tokens for device pairing via QR code
-- Tokens expire after 5 minutes for security

CREATE TABLE pairing_tokens (
    id INT AUTO_INCREMENT PRIMARY KEY,
    token VARCHAR(191) NOT NULL UNIQUE COMMENT 'Single-use pairing token',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'When the token was created',
    expires_at DATETIME NOT NULL COMMENT 'When the token expires (5 minutes)',
    used TINYINT(1) DEFAULT 0 COMMENT 'Whether the token has been used',
    INDEX idx_expires_at (expires_at)
) ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci
COMMENT='Single-use pairing tokens with 5-minute expiry';