CREATE TABLE IF NOT EXISTS `stt_jobs` (
    `id`               INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    `bid`              VARCHAR(100)    NOT NULL COMMENT 'Business ID e.g. mcubefiles112',
    `call_id`          VARCHAR(255)    NOT NULL COMMENT 'PK from {bid}_raw_calls',
    `recording_url`    TEXT            NOT NULL,
    `status`           ENUM('pending','processing','done','failed') NOT NULL DEFAULT 'pending',
    `retry_count`      TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `error_message`    TEXT            NULL,
    `transcript`       LONGTEXT        NULL,
    `speaker_segments` JSON            NULL COMMENT 'Array of {speaker_id, text, start_ms, end_ms}',
    `speaker_count`    TINYINT UNSIGNED NULL,
    `duration_seconds` FLOAT           NULL,
    `stt_provider`     VARCHAR(50)     NULL DEFAULT 'sarvam',
    `metadata`         JSON            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 `uq_bid_callid` (`bid`, `call_id`),
    KEY `idx_status` (`status`),
    KEY `idx_bid_status` (`bid`, `status`),
    KEY `idx_updated` (`updated_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
