-- =============================================================================
-- Blaha YST API - Database Schema
-- =============================================================================
-- Run this migration against your MySQL database to set up the schema.
-- mysql -u your_user -p your_database < migrations/001_create_tables.sql

SET CHARACTER SET utf8mb4;

-- -----------------------------------------------------------------------------
-- Table: calls
-- Stores every inbound call received via Yeastar webhook (event 30012 CDR)
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `calls` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,

    -- Yeastar identifiers
    `yeastar_call_id` VARCHAR(64) NOT NULL COMMENT 'Unique call ID from Yeastar (call_id field)',
    `yeastar_uid` VARCHAR(64) DEFAULT NULL COMMENT 'Unique CDR ID from Yeastar (uid field)',

    -- Caller info (external)
    `caller_number` VARCHAR(32) NOT NULL COMMENT 'External caller phone number',
    `caller_name` VARCHAR(128) DEFAULT NULL COMMENT 'Caller name if available (caller ID)',

    -- Internal recipient info
    `answered_by_extension` VARCHAR(16) DEFAULT NULL COMMENT 'Internal extension number that answered',
    `answered_by_name` VARCHAR(128) DEFAULT NULL COMMENT 'Name of person who answered',

    -- Call metadata
    `call_type` ENUM('Inbound', 'Outbound', 'Internal') NOT NULL DEFAULT 'Inbound',
    `disposition` VARCHAR(32) NOT NULL DEFAULT 'ANSWERED' COMMENT 'ANSWERED, NO ANSWER, BUSY, FAILED, VOICEMAIL',
    `did_number` VARCHAR(32) DEFAULT NULL COMMENT 'DID number that was dialed',
    `trunk_name` VARCHAR(64) DEFAULT NULL COMMENT 'SIP trunk used',

    -- Timing
    `call_time` DATETIME NOT NULL COMMENT 'When the call started',
    `duration` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Total call duration in seconds',
    `ring_duration` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Ring duration in seconds',
    `talk_duration` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Talk duration in seconds',

    -- Notes & AI
    `call_note_remark` TEXT DEFAULT NULL COMMENT 'Agent remark/note from call note',
    `call_note_agent` VARCHAR(128) DEFAULT NULL COMMENT 'Agent who wrote the note',
    `disposition_codes` JSON DEFAULT NULL COMMENT 'Disposition codes as JSON array',
    `ai_transcription` TEXT DEFAULT NULL COMMENT 'AI transcription if available',

    -- Recording
    `recording_file` VARCHAR(255) DEFAULT NULL COMMENT 'Recording filename on Yeastar',

    -- Raw data
    `raw_payload` JSON DEFAULT NULL COMMENT 'Complete raw webhook payload for debugging',

    -- Timestamps
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_yeastar_call_id` (`yeastar_call_id`),
    INDEX `idx_caller_number` (`caller_number`),
    INDEX `idx_caller_number_time` (`caller_number`, `call_time` DESC),
    INDEX `idx_answered_by` (`answered_by_extension`),
    INDEX `idx_call_time` (`call_time` DESC),
    INDEX `idx_disposition` (`disposition`),
    INDEX `idx_did_number` (`did_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- Table: api_keys
-- Stores API keys for client authentication
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `api_keys` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(64) NOT NULL COMMENT 'Descriptive name (e.g. "Reception App")',
    `api_key` VARCHAR(64) NOT NULL COMMENT 'The API key (hashed)',
    `api_key_prefix` VARCHAR(8) NOT NULL COMMENT 'First 8 chars for identification',
    `scopes` JSON DEFAULT NULL COMMENT 'Allowed scopes: ["webhook", "read"]',
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `last_used_at` DATETIME DEFAULT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_api_key` (`api_key`),
    INDEX `idx_api_key_prefix` (`api_key_prefix`),
    INDEX `idx_is_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- Table: webhook_logs
-- Audit log of all incoming webhook requests
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `webhook_logs` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `event_type` INT UNSIGNED NOT NULL COMMENT 'Yeastar event type ID (e.g. 30012)',
    `yeastar_call_id` VARCHAR(64) DEFAULT NULL,
    `status` ENUM('processed', 'skipped', 'failed') NOT NULL DEFAULT 'processed',
    `error_message` TEXT DEFAULT NULL,
    `raw_payload` JSON DEFAULT NULL,
    `ip_address` VARCHAR(45) DEFAULT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (`id`),
    INDEX `idx_event_type` (`event_type`),
    INDEX `idx_created_at` (`created_at` DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
