CREATE DATABASE IF NOT EXISTS call_quality;
USE call_quality;

CREATE TABLE IF NOT EXISTS calls (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id VARCHAR(100) NOT NULL,
    call_id VARCHAR(100) NOT NULL,
    agent_id VARCHAR(100) NOT NULL,
    chunk_count INT NOT NULL DEFAULT 0,
    tags TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_tenant_call (tenant_id, call_id),
    INDEX idx_tenant_agent (tenant_id, agent_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS call_chunks (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id VARCHAR(100) NOT NULL,
    call_id VARCHAR(100) NOT NULL,
    agent_id VARCHAR(100) NOT NULL,
    chunk_index INT NOT NULL,
    chunk_id VARCHAR(150) NOT NULL,
    speaker VARCHAR(50) NOT NULL,
    text LONGTEXT NOT NULL,
    timestamp DATETIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_tenant_call_chunk (tenant_id, call_id, chunk_id),
    INDEX idx_tenant_call_idx (tenant_id, call_id, chunk_index)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS call_scores (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id VARCHAR(100) NOT NULL,
    call_id VARCHAR(100) NOT NULL,
    agent_id VARCHAR(100) NOT NULL,
    score_json JSON NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_tenant_call_score (tenant_id, call_id),
    INDEX idx_tenant_agent_score (tenant_id, agent_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
