-- MCA Calling System Database Schema
-- Complete production-ready database structure

-- Merchants table (uses existing structure)
CREATE TABLE IF NOT EXISTS merchants (
    merchant_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    business_name VARCHAR(200) NOT NULL,
    monthly_revenue DECIMAL(12,2) DEFAULT 0,
    phone_number_1 VARCHAR(20),
    phone_number_2 VARCHAR(20),
    phone_number_3 VARCHAR(20),
    phone_number_4 VARCHAR(20),
    phone_number_5 VARCHAR(20),
    email VARCHAR(150),
    application_date DATE,
    status ENUM('new', 'calling', 'completed', 'failed', 'do_not_call') DEFAULT 'new',
    priority INT DEFAULT 5,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_status (status),
    INDEX idx_application_date (application_date),
    INDEX idx_priority (priority)
);

-- Call attempts table
CREATE TABLE IF NOT EXISTS call_attempts (
    call_id INT AUTO_INCREMENT PRIMARY KEY,
    merchant_id INT NOT NULL,
    phone_number_used VARCHAR(20) NOT NULL,
    retell_call_id VARCHAR(100),
    call_status ENUM('initiated', 'ringing', 'answered', 'completed', 'failed', 'busy', 'no_answer') DEFAULT 'initiated',
    call_duration INT DEFAULT 0,
    identity_verified BOOLEAN DEFAULT FALSE,
    transfer_successful BOOLEAN DEFAULT FALSE,
    disconnection_reason VARCHAR(200),
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (merchant_id) REFERENCES merchants(merchant_id) ON DELETE CASCADE,
    INDEX idx_merchant_id (merchant_id),
    INDEX idx_retell_call_id (retell_call_id),
    INDEX idx_call_status (call_status),
    INDEX idx_created_at (created_at)
);

-- Sales representatives table
CREATE TABLE IF NOT EXISTS sales_reps (
    rep_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    phone_number VARCHAR(20) NOT NULL,
    email VARCHAR(150),
    is_active BOOLEAN DEFAULT TRUE,
    max_concurrent_calls INT DEFAULT 3,
    current_calls INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_is_active (is_active)
);

-- System settings table
CREATE TABLE IF NOT EXISTS system_settings (
    setting_key VARCHAR(100) PRIMARY KEY,
    setting_value TEXT,
    description VARCHAR(255),
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Insert default sales rep if none exist
INSERT IGNORE INTO sales_reps (name, phone_number, email) VALUES 
('Default Sales Rep', '+1234567890', 'sales@clarityadvance.com');

-- Insert default system settings
INSERT IGNORE INTO system_settings (setting_key, setting_value, description) VALUES 
('business_hours_start', '09:00', 'Business hours start time (24h format)'),
('business_hours_end', '17:00', 'Business hours end time (24h format)'),
('max_call_retries', '3', 'Maximum number of call retry attempts'),
('call_retry_delay', '3600', 'Delay between call retries in seconds');

-- Sample merchants for testing (only insert if table is empty)
INSERT IGNORE INTO merchants (merchant_id, first_name, last_name, business_name, monthly_revenue, phone_number_1, application_date) VALUES 
(1, 'John', 'Smith', 'Smith Construction', 75000, '+13302895487', CURDATE()),
(2, 'Jane', 'Johnson', 'Johnson Consulting', 45000, '+15551234567', CURDATE() - INTERVAL 1 DAY),
(3, 'Mike', 'Wilson', 'Wilson Auto Repair', 85000, '+15559876543', CURDATE() - INTERVAL 2 DAY),
(4, 'Sarah', 'Davis', 'Davis Marketing', 65000, '+15551112222', CURDATE() - INTERVAL 3 DAY),
(5, 'Robert', 'Brown', 'Brown Plumbing', 55000, '+15553334444', CURDATE() - INTERVAL 4 DAY);

