<?php
if ( ! defined( 'ABSPATH' ) ) exit;

/**
 * VMO Twilio Database Schema
 * 
 * Creates necessary database tables for Twilio phone management
 * 
 * @package VMO_AI_Website_Builder
 * @since 1.0.0
 */

if (!defined('ABSPATH')) {
    exit;
}

/**
 * Create VMO Twilio tables
 */
function vmo_create_twilio_tables() {
    global $wpdb;
    
    $charset_collate = $wpdb->get_charset_collate();
    
    // Phone numbers table
    $table_name = $wpdb->prefix . 'vmo_user_phone_numbers';
    
    $sql = "CREATE TABLE $table_name (
        id bigint(20) NOT NULL AUTO_INCREMENT,
        user_id bigint(20) NOT NULL,
        twilio_phone_sid varchar(100) NOT NULL,
        twilio_ai_agent_sid varchar(100) DEFAULT NULL,
        phone_number varchar(20) NOT NULL,
        status varchar(20) DEFAULT 'active',
        monthly_cost decimal(10,2) DEFAULT 29.00,
        provisioned_at datetime DEFAULT CURRENT_TIMESTAMP,
        next_billing_date datetime DEFAULT NULL,
        suspended_at datetime DEFAULT NULL,
        deleted_at datetime DEFAULT NULL,
        PRIMARY KEY (id),
        KEY user_id (user_id),
        KEY phone_number (phone_number),
        KEY status (status)
    ) $charset_collate;";
    
    require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
    dbDelta($sql);
    
    // Call logs table
    $table_name = $wpdb->prefix . 'vmo_call_logs';
    
    $sql = "CREATE TABLE $table_name (
        id bigint(20) NOT NULL AUTO_INCREMENT,
        user_id bigint(20) NOT NULL,
        phone_number_id bigint(20) NOT NULL,
        twilio_call_sid varchar(100) NOT NULL,
        from_number varchar(20) NOT NULL,
        to_number varchar(20) NOT NULL,
        direction varchar(20) DEFAULT 'inbound',
        status varchar(20) DEFAULT NULL,
        duration int(11) DEFAULT 0,
        recording_url varchar(500) DEFAULT NULL,
        transcription text DEFAULT NULL,
        ai_summary text DEFAULT NULL,
        created_at datetime DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (id),
        KEY user_id (user_id),
        KEY phone_number_id (phone_number_id),
        KEY twilio_call_sid (twilio_call_sid),
        KEY created_at (created_at)
    ) $charset_collate;";
    
    dbDelta($sql);
    
    // SMS logs table
    $table_name = $wpdb->prefix . 'vmo_sms_logs';
    
    $sql = "CREATE TABLE $table_name (
        id bigint(20) NOT NULL AUTO_INCREMENT,
        user_id bigint(20) NOT NULL,
        phone_number_id bigint(20) NOT NULL,
        twilio_message_sid varchar(100) NOT NULL,
        from_number varchar(20) NOT NULL,
        to_number varchar(20) NOT NULL,
        direction varchar(20) DEFAULT 'outbound',
        body text NOT NULL,
        status varchar(20) DEFAULT NULL,
        created_at datetime DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (id),
        KEY user_id (user_id),
        KEY phone_number_id (phone_number_id),
        KEY created_at (created_at)
    ) $charset_collate;";
    
    dbDelta($sql);
    
    // Bookings table
    $table_name = $wpdb->prefix . 'vmo_bookings';
    
    $sql = "CREATE TABLE $table_name (
        id bigint(20) NOT NULL AUTO_INCREMENT,
        user_id bigint(20) NOT NULL,
        customer_name varchar(255) NOT NULL,
        customer_phone varchar(20) NOT NULL,
        customer_email varchar(255) DEFAULT NULL,
        service varchar(255) NOT NULL,
        booking_date date NOT NULL,
        booking_time time NOT NULL,
        status varchar(20) DEFAULT 'confirmed',
        source varchar(50) DEFAULT 'phone_ai',
        notes text DEFAULT NULL,
        created_at datetime DEFAULT CURRENT_TIMESTAMP,
        updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (id),
        KEY user_id (user_id),
        KEY booking_date (booking_date),
        KEY status (status)
    ) $charset_collate;";
    
    dbDelta($sql);
    
    // Update version
    update_option('vmo_twilio_db_version', '1.0.0');
}

// Hook into plugin activation
if (defined('VMO_PLUGIN_FILE')) {
    register_activation_hook(VMO_PLUGIN_FILE, 'vmo_create_twilio_tables');
}

// Also create tables on plugin update if needed
add_action('admin_init', function() {
    $db_version = get_option('vmo_twilio_db_version', '0');
    if (version_compare($db_version, '1.0.0', '<')) {
        vmo_create_twilio_tables();
    }
});
