-- ============================================================
-- FreePBX SIP Integration Plugin - Database Migration
-- Version: 1.0.0
--
-- This migration creates all tables required for the FreePBX SIP
-- integration plugin including extensions, assignments, requests,
-- groups, calls, and audit logging.
-- ============================================================

-- Extension Groups for organizing SIP extensions
CREATE TABLE IF NOT EXISTS sip_extension_groups (
    id VARCHAR(36) PRIMARY KEY DEFAULT gen_random_uuid()::text,
    name VARCHAR(100) NOT NULL,
    name_ar VARCHAR(100),
    description TEXT,
    description_ar TEXT,
    color VARCHAR(7) DEFAULT '#3B82F6',
    icon VARCHAR(50) DEFAULT 'Phone',
    sort_order INTEGER DEFAULT 0,
    is_active BOOLEAN DEFAULT true,
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- FreePBX Server Connections (supports multiple PBX servers)
CREATE TABLE IF NOT EXISTS freepbx_connections (
    id VARCHAR(36) PRIMARY KEY DEFAULT gen_random_uuid()::text,
    name VARCHAR(100) NOT NULL,
    name_ar VARCHAR(100),
    description TEXT,

    -- Server connection details
    host VARCHAR(255) NOT NULL,
    sip_domain VARCHAR(255),

    -- AMI (Asterisk Manager Interface) credentials
    ami_port INTEGER DEFAULT 5038,
    ami_username VARCHAR(100),
    ami_secret_encrypted TEXT,
    ami_enabled BOOLEAN DEFAULT true,

    -- REST/GraphQL API credentials
    rest_api_url VARCHAR(500),
    rest_api_token_encrypted TEXT,
    rest_api_enabled BOOLEAN DEFAULT false,

    -- ARI (Asterisk REST Interface) credentials
    ari_url VARCHAR(500),
    ari_username VARCHAR(100),
    ari_password_encrypted TEXT,
    ari_enabled BOOLEAN DEFAULT false,

    -- WebSocket for real-time events
    websocket_url VARCHAR(500),
    websocket_enabled BOOLEAN DEFAULT false,

    -- Connection status
    is_active BOOLEAN DEFAULT true,
    is_primary BOOLEAN DEFAULT false,
    connection_status VARCHAR(20) DEFAULT 'disconnected',
    last_connected_at TIMESTAMP WITH TIME ZONE,
    last_error TEXT,

    -- Sync settings
    auto_sync_enabled BOOLEAN DEFAULT true,
    sync_interval_minutes INTEGER DEFAULT 60,
    last_sync_at TIMESTAMP WITH TIME ZONE,
    last_sync_status VARCHAR(20),
    last_sync_extensions_count INTEGER DEFAULT 0,

    -- TLS/Security
    use_tls BOOLEAN DEFAULT false,
    verify_certificate BOOLEAN DEFAULT true,
    ca_certificate TEXT,

    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- SIP Extensions synced from FreePBX
CREATE TABLE IF NOT EXISTS sip_extensions (
    id VARCHAR(36) PRIMARY KEY DEFAULT gen_random_uuid()::text,
    freepbx_connection_id VARCHAR(36) REFERENCES freepbx_connections(id) ON DELETE CASCADE,
    group_id VARCHAR(36) REFERENCES sip_extension_groups(id) ON DELETE SET NULL,

    -- Extension details
    extension VARCHAR(20) NOT NULL,
    display_name VARCHAR(255),
    display_name_ar VARCHAR(255),
    description TEXT,

    -- SIP credentials (encrypted)
    secret_encrypted TEXT,
    context VARCHAR(100) DEFAULT 'from-internal',

    -- Caller ID settings
    caller_id_name VARCHAR(100),
    caller_id_number VARCHAR(50),
    outbound_caller_id VARCHAR(50),

    -- FreePBX identifiers
    freepbx_device_id VARCHAR(100),
    freepbx_user_id VARCHAR(100),
    freepbx_extension_type VARCHAR(50),

    -- SIP server settings
    sip_server VARCHAR(255),
    sip_port INTEGER DEFAULT 5060,
    transport VARCHAR(10) DEFAULT 'udp',
    codecs TEXT[] DEFAULT ARRAY['ulaw', 'alaw'],

    -- WebRTC support
    webrtc_enabled BOOLEAN DEFAULT false,
    webrtc_port INTEGER DEFAULT 8089,

    -- Status tracking
    status VARCHAR(20) DEFAULT 'available',
    registration_status VARCHAR(20) DEFAULT 'unknown',
    last_registration_at TIMESTAMP WITH TIME ZONE,

    -- Rate limiting
    max_concurrent_calls INTEGER DEFAULT 2,
    current_active_calls INTEGER DEFAULT 0,

    -- Failover
    failover_extension_id VARCHAR(36),
    failover_enabled BOOLEAN DEFAULT false,

    -- Feature flags
    is_active BOOLEAN DEFAULT true,
    allow_outbound BOOLEAN DEFAULT true,
    allow_inbound BOOLEAN DEFAULT true,
    allow_transfer BOOLEAN DEFAULT true,
    record_calls BOOLEAN DEFAULT true,

    -- Sync tracking
    last_sync_at TIMESTAMP WITH TIME ZONE,
    sync_hash VARCHAR(64),

    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

    UNIQUE(freepbx_connection_id, extension)
);

-- Index for faster lookups
CREATE INDEX IF NOT EXISTS idx_sip_extensions_status ON sip_extensions(status);
CREATE INDEX IF NOT EXISTS idx_sip_extensions_extension ON sip_extensions(extension);
CREATE INDEX IF NOT EXISTS idx_sip_extensions_group ON sip_extensions(group_id);

-- User SIP Extension Assignments
CREATE TABLE IF NOT EXISTS sip_extension_assignments (
    id VARCHAR(36) PRIMARY KEY DEFAULT gen_random_uuid()::text,
    user_id VARCHAR(36) NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    sip_extension_id VARCHAR(36) NOT NULL REFERENCES sip_extensions(id) ON DELETE CASCADE,

    -- Connected agent (optional - user may assign later)
    agent_id VARCHAR(36) REFERENCES agents(id) ON DELETE SET NULL,

    -- Assignment status
    status VARCHAR(20) DEFAULT 'active',

    -- Assignment details
    assigned_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    assigned_by VARCHAR(36) REFERENCES users(id) ON DELETE SET NULL,

    -- Expiration (optional)
    expires_at TIMESTAMP WITH TIME ZONE,
    auto_renew BOOLEAN DEFAULT false,

    -- Usage limits (override plugin defaults)
    max_concurrent_calls INTEGER,
    max_daily_minutes INTEGER,
    max_monthly_minutes INTEGER,

    -- Usage tracking
    total_calls INTEGER DEFAULT 0,
    total_minutes INTEGER DEFAULT 0,
    current_month_minutes INTEGER DEFAULT 0,
    last_call_at TIMESTAMP WITH TIME ZONE,

    -- Notes
    admin_notes TEXT,
    user_notes TEXT,

    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

    -- One user per extension
    UNIQUE(sip_extension_id)
);

-- Index for faster lookups
CREATE INDEX IF NOT EXISTS idx_sip_assignments_user ON sip_extension_assignments(user_id);
CREATE INDEX IF NOT EXISTS idx_sip_assignments_status ON sip_extension_assignments(status);
CREATE INDEX IF NOT EXISTS idx_sip_assignments_agent ON sip_extension_assignments(agent_id);

-- User Requests for SIP Extensions (Approval Workflow)
CREATE TABLE IF NOT EXISTS sip_extension_requests (
    id VARCHAR(36) PRIMARY KEY DEFAULT gen_random_uuid()::text,
    user_id VARCHAR(36) NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    sip_extension_id VARCHAR(36) NOT NULL REFERENCES sip_extensions(id) ON DELETE CASCADE,

    -- Request status
    status VARCHAR(20) DEFAULT 'pending',
    priority VARCHAR(10) DEFAULT 'normal',

    -- Request details
    request_reason TEXT,
    intended_use TEXT,

    -- Review details
    reviewed_by VARCHAR(36) REFERENCES users(id) ON DELETE SET NULL,
    reviewed_at TIMESTAMP WITH TIME ZONE,
    admin_notes TEXT,
    rejection_reason TEXT,

    -- If approved, link to assignment
    assignment_id VARCHAR(36) REFERENCES sip_extension_assignments(id) ON DELETE SET NULL,

    -- Notification tracking
    user_notified BOOLEAN DEFAULT false,
    user_notified_at TIMESTAMP WITH TIME ZONE,

    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Index for faster lookups
CREATE INDEX IF NOT EXISTS idx_sip_requests_user ON sip_extension_requests(user_id);
CREATE INDEX IF NOT EXISTS idx_sip_requests_status ON sip_extension_requests(status);
CREATE INDEX IF NOT EXISTS idx_sip_requests_extension ON sip_extension_requests(sip_extension_id);

-- SIP Incoming Connections (links extension to agent for incoming calls)
CREATE TABLE IF NOT EXISTS sip_incoming_connections (
    id VARCHAR(36) PRIMARY KEY DEFAULT gen_random_uuid()::text,
    user_id VARCHAR(36) NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    assignment_id VARCHAR(36) NOT NULL REFERENCES sip_extension_assignments(id) ON DELETE CASCADE,
    agent_id VARCHAR(36) NOT NULL REFERENCES agents(id) ON DELETE CASCADE,

    -- Connection status
    is_active BOOLEAN DEFAULT true,

    -- Webhook configuration
    webhook_url TEXT,
    webhook_secret VARCHAR(64),

    -- Call routing settings
    ring_timeout_seconds INTEGER DEFAULT 30,
    voicemail_enabled BOOLEAN DEFAULT false,
    voicemail_email VARCHAR(255),

    -- Business hours (optional)
    business_hours_enabled BOOLEAN DEFAULT false,
    business_hours_config JSONB,
    after_hours_action VARCHAR(20) DEFAULT 'voicemail',
    after_hours_message TEXT,
    after_hours_message_ar TEXT,

    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

    UNIQUE(assignment_id)
);

-- FreePBX Call Records (renamed from sip_calls to avoid conflict with SIP trunk engine)
CREATE TABLE IF NOT EXISTS freepbx_calls (
    id VARCHAR(36) PRIMARY KEY DEFAULT gen_random_uuid()::text,
    user_id VARCHAR(36) REFERENCES users(id) ON DELETE SET NULL,
    assignment_id VARCHAR(36) REFERENCES sip_extension_assignments(id) ON DELETE SET NULL,
    agent_id VARCHAR(36) REFERENCES agents(id) ON DELETE SET NULL,
    campaign_id VARCHAR(36) REFERENCES campaigns(id) ON DELETE SET NULL,
    contact_id VARCHAR(36) REFERENCES contacts(id) ON DELETE SET NULL,
    incoming_connection_id VARCHAR(36) REFERENCES sip_incoming_connections(id) ON DELETE SET NULL,

    -- Extension details (denormalized for historical accuracy)
    sip_extension_id VARCHAR(36) REFERENCES sip_extensions(id) ON DELETE SET NULL,
    sip_extension VARCHAR(20) NOT NULL,
    freepbx_connection_id VARCHAR(36) REFERENCES freepbx_connections(id) ON DELETE SET NULL,

    -- Call details
    direction VARCHAR(10) NOT NULL,
    caller_id VARCHAR(100),
    caller_name VARCHAR(255),
    destination VARCHAR(100),
    destination_name VARCHAR(255),

    -- Call status tracking
    status VARCHAR(20) DEFAULT 'initiated',
    hangup_cause VARCHAR(50),
    hangup_cause_code INTEGER,

    -- Timing
    initiated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    ringing_at TIMESTAMP WITH TIME ZONE,
    answered_at TIMESTAMP WITH TIME ZONE,
    ended_at TIMESTAMP WITH TIME ZONE,
    duration_seconds INTEGER DEFAULT 0,
    billable_seconds INTEGER DEFAULT 0,

    -- Credits/billing
    credits_used NUMERIC(10, 2) DEFAULT 0,
    credit_rate NUMERIC(10, 4),

    -- Recording
    recording_enabled BOOLEAN DEFAULT false,
    recording_url TEXT,
    recording_duration_seconds INTEGER,
    recording_size_bytes BIGINT,

    -- AI Processing
    transcript TEXT,
    transcript_segments JSONB,
    ai_summary TEXT,
    ai_summary_ar TEXT,
    sentiment VARCHAR(20),
    sentiment_score NUMERIC(3, 2),
    keywords TEXT[],
    action_items JSONB,

    -- Lead/CRM data extracted
    lead_data JSONB,

    -- FreePBX/Asterisk identifiers
    freepbx_unique_id VARCHAR(100),
    freepbx_linked_id VARCHAR(100),
    asterisk_channel VARCHAR(255),

    -- Quality metrics
    mos_score NUMERIC(3, 2),
    jitter_ms INTEGER,
    packet_loss_percent NUMERIC(5, 2),

    -- Error tracking
    error_message TEXT,
    error_code VARCHAR(50),

    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Indexes for call lookups
CREATE INDEX IF NOT EXISTS idx_freepbx_calls_user ON freepbx_calls(user_id);
CREATE INDEX IF NOT EXISTS idx_freepbx_calls_extension ON freepbx_calls(sip_extension_id);
CREATE INDEX IF NOT EXISTS idx_freepbx_calls_agent ON freepbx_calls(agent_id);
CREATE INDEX IF NOT EXISTS idx_freepbx_calls_campaign ON freepbx_calls(campaign_id);
CREATE INDEX IF NOT EXISTS idx_freepbx_calls_status ON freepbx_calls(status);
CREATE INDEX IF NOT EXISTS idx_freepbx_calls_direction ON freepbx_calls(direction);
CREATE INDEX IF NOT EXISTS idx_freepbx_calls_initiated ON freepbx_calls(initiated_at);
CREATE INDEX IF NOT EXISTS idx_freepbx_calls_freepbx_uid ON freepbx_calls(freepbx_unique_id);

-- Audit Log for SIP operations
CREATE TABLE IF NOT EXISTS sip_audit_logs (
    id VARCHAR(36) PRIMARY KEY DEFAULT gen_random_uuid()::text,
    user_id VARCHAR(36) REFERENCES users(id) ON DELETE SET NULL,

    -- Action details
    action VARCHAR(50) NOT NULL,
    action_category VARCHAR(30) NOT NULL,

    -- Entity being acted upon
    entity_type VARCHAR(50) NOT NULL,
    entity_id VARCHAR(36) NOT NULL,
    entity_name VARCHAR(255),

    -- Change details
    old_values JSONB,
    new_values JSONB,
    changes_summary TEXT,

    -- Request context
    ip_address VARCHAR(45),
    user_agent TEXT,
    request_id VARCHAR(36),

    -- Result
    status VARCHAR(20) DEFAULT 'success',
    error_message TEXT,

    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Indexes for audit log
CREATE INDEX IF NOT EXISTS idx_sip_audit_user ON sip_audit_logs(user_id);
CREATE INDEX IF NOT EXISTS idx_sip_audit_action ON sip_audit_logs(action);
CREATE INDEX IF NOT EXISTS idx_sip_audit_entity ON sip_audit_logs(entity_type, entity_id);
CREATE INDEX IF NOT EXISTS idx_sip_audit_created ON sip_audit_logs(created_at);

-- SIP Usage Statistics (aggregated daily)
CREATE TABLE IF NOT EXISTS sip_usage_stats (
    id VARCHAR(36) PRIMARY KEY DEFAULT gen_random_uuid()::text,
    user_id VARCHAR(36) REFERENCES users(id) ON DELETE CASCADE,
    sip_extension_id VARCHAR(36) REFERENCES sip_extensions(id) ON DELETE CASCADE,

    -- Date for aggregation
    stat_date DATE NOT NULL,

    -- Call counts
    total_calls INTEGER DEFAULT 0,
    inbound_calls INTEGER DEFAULT 0,
    outbound_calls INTEGER DEFAULT 0,
    answered_calls INTEGER DEFAULT 0,
    missed_calls INTEGER DEFAULT 0,
    failed_calls INTEGER DEFAULT 0,

    -- Duration
    total_duration_seconds INTEGER DEFAULT 0,
    inbound_duration_seconds INTEGER DEFAULT 0,
    outbound_duration_seconds INTEGER DEFAULT 0,
    avg_call_duration_seconds INTEGER DEFAULT 0,

    -- Credits
    credits_used NUMERIC(10, 2) DEFAULT 0,

    -- Quality
    avg_sentiment_score NUMERIC(3, 2),
    avg_mos_score NUMERIC(3, 2),

    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

    UNIQUE(user_id, sip_extension_id, stat_date)
);

-- Index for stats lookups
CREATE INDEX IF NOT EXISTS idx_sip_stats_user_date ON sip_usage_stats(user_id, stat_date);
CREATE INDEX IF NOT EXISTS idx_sip_stats_extension_date ON sip_usage_stats(sip_extension_id, stat_date);

-- ============================================================
-- Trigger Functions
-- ============================================================

-- Update updated_at timestamp
CREATE OR REPLACE FUNCTION update_sip_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Apply triggers to all tables
DO $$
DECLARE
    t TEXT;
BEGIN
    FOR t IN
        SELECT unnest(ARRAY[
            'sip_extension_groups',
            'freepbx_connections',
            'sip_extensions',
            'sip_extension_assignments',
            'sip_extension_requests',
            'sip_incoming_connections',
            'freepbx_calls',
            'sip_usage_stats'
        ])
    LOOP
        EXECUTE format('
            DROP TRIGGER IF EXISTS trigger_update_%I_updated_at ON %I;
            CREATE TRIGGER trigger_update_%I_updated_at
            BEFORE UPDATE ON %I
            FOR EACH ROW
            EXECUTE FUNCTION update_sip_updated_at();
        ', t, t, t, t);
    END LOOP;
END;
$$;

-- Update extension status when assigned/unassigned
CREATE OR REPLACE FUNCTION update_extension_status_on_assignment()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        UPDATE sip_extensions
        SET status = 'assigned', updated_at = NOW()
        WHERE id = NEW.sip_extension_id;
    ELSIF TG_OP = 'DELETE' THEN
        UPDATE sip_extensions
        SET status = 'available', updated_at = NOW()
        WHERE id = OLD.sip_extension_id;
    END IF;
    RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_extension_status_on_assignment
AFTER INSERT OR DELETE ON sip_extension_assignments
FOR EACH ROW
EXECUTE FUNCTION update_extension_status_on_assignment();

-- ============================================================
-- Initial Data
-- ============================================================

-- Insert default extension group
INSERT INTO sip_extension_groups (id, name, name_ar, description, description_ar, color, icon, sort_order)
VALUES
    ('default-group', 'General', 'عام', 'Default extension group', 'مجموعة الامتدادات الافتراضية', '#3B82F6', 'Phone', 0)
ON CONFLICT (id) DO NOTHING;

-- ============================================================
-- Comments
-- ============================================================

COMMENT ON TABLE sip_extensions IS 'SIP extensions synced from FreePBX servers';
COMMENT ON TABLE sip_extension_assignments IS 'User assignments to SIP extensions (one extension per user)';
COMMENT ON TABLE sip_extension_requests IS 'User requests for SIP extensions pending admin approval';
COMMENT ON TABLE sip_extension_groups IS 'Groups for organizing SIP extensions';
COMMENT ON TABLE freepbx_connections IS 'FreePBX server connection configurations';
COMMENT ON TABLE freepbx_calls IS 'Call records for FreePBX SIP-based calls';
COMMENT ON TABLE sip_audit_logs IS 'Audit trail for all SIP-related operations';
COMMENT ON TABLE sip_usage_stats IS 'Aggregated daily usage statistics';
COMMENT ON TABLE sip_incoming_connections IS 'Links SIP extensions to AI agents for incoming call handling';