

-- AI Providers configuration
CREATE TABLE ai_providers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    api_endpoint VARCHAR(255) NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    default_voice VARCHAR(100),
    supported_formats JSON,
    rate_limit_per_minute INT DEFAULT 60,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Insert default AI providers
INSERT INTO ai_providers (name, api_endpoint, default_voice, supported_formats) VALUES
('OpenAI', 'https://api.openai.com/v1/audio/speech', 'alloy', '["mp3", "wav"]'),
('ElevenLabs', 'https://api.elevenlabs.io/v1/text-to-speech', 'Adam', '["mp3", "wav"]'),
('Google', 'https://texttospeech.googleapis.com/v1/text:synthesize', 'en-US-Standard-A', '["mp3", "wav"]');

-- Projects (IVR Trees) table
CREATE TABLE projects (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    ai_provider_id INT DEFAULT 1,
    voice_settings JSON,
    style_prompt TEXT,
    payment_status ENUM('free', 'paid', 'hosted') DEFAULT 'free',
    payment_date DATETIME NULL,
    payment_expires DATETIME NULL,
    stripe_payment_id VARCHAR(255) NULL,
    is_public BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (ai_provider_id) REFERENCES ai_providers(id),
    INDEX idx_user_id (user_id),
    INDEX idx_payment_status (payment_status)
);

-- IVR Tree Nodes
CREATE TABLE ivr_nodes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    project_id INT NOT NULL,
    parent_id INT NULL,
    node_key VARCHAR(10) NOT NULL, -- '0', '1', '2', etc. for keypress options
    title VARCHAR(255) NOT NULL,
    message_text TEXT NOT NULL,
    voice_settings JSON, -- Override project voice settings if needed
    sort_order INT DEFAULT 0,
    is_root BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    FOREIGN KEY (parent_id) REFERENCES ivr_nodes(id) ON DELETE CASCADE,
    INDEX idx_project_id (project_id),
    INDEX idx_parent_id (parent_id),
    UNIQUE KEY unique_project_node_key (project_id, parent_id, node_key)
);

-- Generated Audio Files
CREATE TABLE audio_files (
    id INT AUTO_INCREMENT PRIMARY KEY,
    project_id INT NOT NULL,
    node_id INT NOT NULL,
    file_path VARCHAR(500) NOT NULL,
    file_format ENUM('mp3', 'wav') NOT NULL,
    file_size INT NOT NULL,
    duration_seconds DECIMAL(10,2),
    ai_provider_id INT NOT NULL,
    generation_settings JSON,
    is_temporary BOOLEAN DEFAULT TRUE,
    expires_at DATETIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    FOREIGN KEY (node_id) REFERENCES ivr_nodes(id) ON DELETE CASCADE,
    FOREIGN KEY (ai_provider_id) REFERENCES ai_providers(id),
    INDEX idx_project_id (project_id),
    INDEX idx_node_id (node_id),
    INDEX idx_expires_at (expires_at)
);

-- API Rate Limiting
CREATE TABLE api_rate_limits (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NULL,
    ip_address VARCHAR(45) NOT NULL,
    endpoint VARCHAR(255) NOT NULL,
    requests_count INT DEFAULT 1,
    window_start DATETIME NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_ip_endpoint (user_id, ip_address, endpoint),
    INDEX idx_window_start (window_start)
);

-- Activity Logs
CREATE TABLE activity_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NULL,
    action VARCHAR(100) NOT NULL,
    resource_type VARCHAR(50),
    resource_id INT NULL,
    details JSON,
    ip_address VARCHAR(45),
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_user_id (user_id),
    INDEX idx_action (action),
    INDEX idx_created_at (created_at)
);

-- Create indexes for better performance
CREATE INDEX idx_projects_updated_at ON projects(updated_at);
CREATE INDEX idx_ivr_nodes_updated_at ON ivr_nodes(updated_at);
CREATE INDEX idx_audio_files_created_at ON audio_files(created_at);

-- Create view for complete tree structure
CREATE VIEW complete_tree_view AS
SELECT 
    n.id as node_id,
    n.project_id,
    n.parent_id,
    n.node_key,
    n.title,
    n.message_text,
    n.voice_settings,
    n.sort_order,
    n.is_root,
    p.name as project_name,
    p.user_id,
    af.file_path,
    af.file_format,
    af.duration_seconds
FROM ivr_nodes n
LEFT JOIN projects p ON n.project_id = p.id
LEFT JOIN audio_files af ON n.id = af.node_id AND af.file_format = 'mp3'
ORDER BY n.project_id, n.parent_id, n.sort_order; 