-- ============================================================
-- FreePBX SIP Integration Plugin v2.0 - Optimized Database Schema
-- ============================================================
-- Changes from v1.0:
-- 1. Simplified table structure (removed redundant columns)
-- 2. Added proper indexes for common queries
-- 3. Partitioning support for calls table
-- 4. JSONB for flexible metadata
-- 5. Proper foreign key constraints with cascades
-- ============================================================

-- ============================================================
-- EXTENSION GROUPS
-- ============================================================
CREATE TABLE IF NOT EXISTS sip_groups (
    id VARCHAR(36) PRIMARY KEY DEFAULT gen_random_uuid()::text,
    name VARCHAR(100) NOT NULL,
    name_ar VARCHAR(100),
    description TEXT,
    color VARCHAR(7) DEFAULT '#3B82F6',
    icon VARCHAR(50) DEFAULT 'Phone',
    sort_order INTEGER DEFAULT 0,
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Default group
INSERT INTO sip_groups (id, name, name_ar, color, sort_order)
VALUES ('default-group', 'General', 'عام', '#3B82F6', 0)
ON CONFLICT (id) DO NOTHING;

-- ============================================================
-- FREEPBX CONNECTIONS
-- ============================================================
CREATE TABLE IF NOT EXISTS freepbx_connections (
    id VARCHAR(36) PRIMARY KEY DEFAULT gen_random_uuid()::text,

    -- Basic info
    name VARCHAR(100) NOT NULL,
    description TEXT,
    is_active BOOLEAN DEFAULT true,

    -- Connection details
    host VARCHAR(255) NOT NULL,
    ami_port INTEGER DEFAULT 5038,
    ami_username VARCHAR(100),
    ami_secret_encrypted TEXT,
    rest_api_url VARCHAR(500),
    rest_api_token_encrypted TEXT,

    -- Status
    connection_status VARCHAR(20) DEFAULT 'disconnected',
    last_connected_at TIMESTAMP WITH TIME ZONE,
    last_sync_at TIMESTAMP WITH TIME ZONE,

    -- Sync settings
    auto_sync BOOLEAN DEFAULT true,
    sync_interval_minutes INTEGER DEFAULT 60,

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

CREATE INDEX idx_freepbx_connections_status ON freepbx_connections(connection_status);
CREATE INDEX idx_freepbx_connections_active ON freepbx_connections(is_active);

-- ============================================================
-- SIP EXTENSIONS
-- ============================================================
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_groups(id) ON DELETE SET NULL,

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

    -- SIP config
    sip_server VARCHAR(255),
    sip_port INTEGER DEFAULT 5060,
    transport VARCHAR(10) DEFAULT 'udp',
    context VARCHAR(100) DEFAULT 'from-internal',

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

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

    -- Limits
    max_concurrent_calls INTEGER DEFAULT 2,
    current_calls INTEGER DEFAULT 0,

    -- Flags
    allow_outbound BOOLEAN DEFAULT true,
    allow_inbound BOOLEAN DEFAULT true,
    record_calls BOOLEAN DEFAULT true,

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

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

    UNIQUE(freepbx_connection_id, extension)
);

CREATE INDEX idx_sip_extensions_status ON sip_extensions(status);
CREATE INDEX idx_sip_extensions_group ON sip_extensions(group_id);
CREATE INDEX idx_sip_extensions_connection ON sip_extensions(freepbx_connection_id);
CREATE INDEX idx_sip_extensions_available ON sip_extensions(status) WHERE status = 'available';

-- ============================================================
-- USER ASSIGNMENTS
-- ============================================================
CREATE TABLE IF NOT EXISTS sip_assignments (
    id VARCHAR(36) PRIMARY KEY DEFAULT gen_random_uuid()::text,
    user_id VARCHAR(36) NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    extension_id VARCHAR(36) NOT NULL REFERENCES sip_extensions(id) ON DELETE CASCADE,
    agent_id VARCHAR(36) REFERENCES agents(id) ON DELETE SET NULL,

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

    -- Dates
    assigned_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    expires_at TIMESTAMP WITH TIME ZONE,

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

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

    UNIQUE(extension_id)
);

CREATE INDEX idx_sip_assignments_user ON sip_assignments(user_id);
CREATE INDEX idx_sip_assignments_extension ON sip_assignments(extension_id);
CREATE INDEX idx_sip_assignments_agent ON sip_assignments(agent_id);
CREATE INDEX idx_sip_assignments_status ON sip_assignments(status);
CREATE INDEX idx_sip_assignments_active ON sip_assignments(status) WHERE status = 'active';

-- ============================================================
-- CALL RECORDS (Partitioned by month)
-- ============================================================
CREATE TABLE IF NOT EXISTS freepbx_calls (
    id VARCHAR(36) PRIMARY KEY DEFAULT gen_random_uuid()::text,

    -- Links
    user_id VARCHAR(36) REFERENCES users(id) ON DELETE SET NULL,
    assignment_id VARCHAR(36) REFERENCES sip_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,
    extension_id VARCHAR(36) REFERENCES sip_extensions(id) ON DELETE SET NULL,

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

    -- Status
    status VARCHAR(20) DEFAULT 'initiated',
    hangup_cause VARCHAR(50),

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

    -- Recording
    recording_url TEXT,
    transcript TEXT,
    transcript_segments JSONB,
    ai_summary TEXT,

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

    -- Quality
    sentiment VARCHAR(20),
    sentiment_score NUMERIC(3, 2),

    -- External IDs
    freepbx_unique_id VARCHAR(100),
    asterisk_channel VARCHAR(255),

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

CREATE INDEX idx_freepbx_calls_user ON freepbx_calls(user_id);
CREATE INDEX idx_freepbx_calls_extension ON freepbx_calls(extension_id);
CREATE INDEX idx_freepbx_calls_agent ON freepbx_calls(agent_id);
CREATE INDEX idx_freepbx_calls_status ON freepbx_calls(status);
CREATE INDEX idx_freepbx_calls_direction ON freepbx_calls(direction);
CREATE INDEX idx_freepbx_calls_initiated ON freepbx_calls(initiated_at DESC);
CREATE INDEX idx_freepbx_calls_user_date ON freepbx_calls(user_id, initiated_at DESC);

-- ============================================================
-- AUDIT LOGS
-- ============================================================
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
    action VARCHAR(50) NOT NULL,
    entity_type VARCHAR(50) NOT NULL,
    entity_id VARCHAR(36) NOT NULL,

    -- Changes
    old_values JSONB,
    new_values JSONB,

    -- Context
    ip_address VARCHAR(45),
    user_agent TEXT,

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

    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_sip_audit_user ON sip_audit_logs(user_id);
CREATE INDEX idx_sip_audit_entity ON sip_audit_logs(entity_type, entity_id);
CREATE INDEX idx_sip_audit_created ON sip_audit_logs(created_at DESC);

-- ============================================================
-- USAGE STATS (Materialized view for performance)
-- ============================================================
CREATE MATERIALIZED VIEW IF NOT EXISTS sip_usage_stats AS
SELECT
    date_trunc('day', initiated_at) AS stat_date,
    user_id,
    extension_id,
    COUNT(*) FILTER (WHERE status IN ('completed', 'answered')) AS total_calls,
    COUNT(*) FILTER (WHERE direction = 'inbound') AS inbound_calls,
    COUNT(*) FILTER (WHERE direction = 'outbound') AS outbound_calls,
    SUM(duration_seconds) AS total_seconds,
    SUM(credits_used) AS total_credits,
    AVG(sentiment_score) AS avg_sentiment
FROM freepbx_calls
WHERE status IN ('completed', 'answered')
GROUP BY date_trunc('day', initiated_at), user_id, extension_id;

CREATE UNIQUE INDEX ON sip_usage_stats(stat_date, user_id, extension_id);

-- Function to refresh stats
CREATE OR REPLACE FUNCTION refresh_sip_stats()
RETURNS void AS $$
BEGIN
    REFRESH MATERIALIZED VIEW CONCURRENTLY sip_usage_stats;
END;
$$ LANGUAGE plpgsql;

-- Schedule refresh every hour (requires pg_cron extension)
-- SELECT cron.schedule('refresh-sip-stats', '0 * * * *', 'SELECT refresh_sip_stats()');

-- ============================================================
-- TRIGGER FUNCTIONS
-- ============================================================

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

-- Apply to all tables with updated_at
DO $$
DECLARE
    t TEXT;
BEGIN
    FOR t IN SELECT unnest(ARRAY[
        'sip_groups',
        'freepbx_connections',
        'sip_extensions',
        'sip_assignments',
        'freepbx_calls'
    ])
    LOOP
        EXECUTE format('
            DROP TRIGGER IF EXISTS trigger_%_updated_at ON %I;
            CREATE TRIGGER trigger_%_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 on assignment
CREATE OR REPLACE FUNCTION update_extension_on_assignment()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        UPDATE sip_extensions
        SET status = 'assigned', current_calls = current_calls + 1
        WHERE id = NEW.extension_id;
    ELSIF TG_OP = 'DELETE' THEN
        UPDATE sip_extensions
        SET status = 'available',
            current_calls = GREATEST(0, current_calls - 1)
        WHERE id = OLD.extension_id;
    END IF;
    RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_extension_on_assignment
AFTER INSERT OR DELETE ON sip_assignments
FOR EACH ROW
EXECUTE FUNCTION update_extension_on_assignment();

-- ============================================================
-- VIEWS FOR COMMON QUERIES
-- ============================================================

-- Active extensions with user info
CREATE OR REPLACE VIEW v_active_extensions AS
SELECT
    e.id,
    e.extension,
    e.display_name,
    e.status,
    e.group_id,
    g.name AS group_name,
    u.id AS user_id,
    u.name AS user_name,
    u.email AS user_email,
    a.id AS assignment_id,
    a.agent_id,
    ag.name AS agent_name
FROM sip_extensions e
LEFT JOIN sip_groups g ON e.group_id = g.id
LEFT JOIN sip_assignments a ON e.id = a.extension_id AND a.status = 'active'
LEFT JOIN users u ON a.user_id = u.id
LEFT JOIN agents ag ON a.agent_id = ag.id
WHERE e.is_active = true;

-- Call statistics dashboard
CREATE OR REPLACE VIEW v_call_stats AS
SELECT
    date_trunc('day', initiated_at) AS date,
    COUNT(*) FILTER (WHERE direction = 'outbound') AS outbound_calls,
    COUNT(*) FILTER (WHERE direction = 'inbound') AS inbound_calls,
    COUNT(*) FILTER (WHERE status = 'completed') AS completed_calls,
    COUNT(*) FILTER (WHERE status = 'failed') AS failed_calls,
    SUM(duration_seconds) FILTER (WHERE status = 'completed') AS total_duration,
    AVG(duration_seconds) FILTER (WHERE status = 'completed') AS avg_duration,
    SUM(credits_used) AS total_credits
FROM freepbx_calls
WHERE initiated_at > NOW() - INTERVAL '30 days'
GROUP BY date_trunc('day', initiated_at)
ORDER BY date DESC;

-- ============================================================
-- COMMENTS
-- ============================================================

COMMENT ON TABLE freepbx_connections IS 'FreePBX server connections for SIP integration';
COMMENT ON TABLE sip_extensions IS 'SIP extensions synced from FreePBX';
COMMENT ON TABLE sip_assignments IS 'User to extension assignments (one per user)';
COMMENT ON TABLE freepbx_calls IS 'Call records from SIP integration';
COMMENT ON TABLE sip_audit_logs IS 'Audit trail for SIP operations';
COMMENT ON TABLE sip_usage_stats IS 'Daily usage statistics (materialized view)';
COMMENT ON TABLE sip_groups IS 'Extension groups for organization';

-- ============================================================
-- SAMPLE DATA (for testing)
-- ============================================================

-- Sample FreePBX connection
INSERT INTO freepbx_connections (
    id, name, host, ami_port, ami_username, ami_secret_encrypted,
    connection_status, is_active
) VALUES (
    'test-connection',
    'Test FreePBX Server',
    '192.168.1.100',
    5038,
    'admin',
    'encrypted_placeholder',
    'disconnected',
    true
) ON CONFLICT (id) DO NOTHING;

-- Sample extensions
INSERT INTO sip_extensions (
    id, freepbx_connection_id, group_id, extension, display_name,
    sip_server, status, allow_outbound, allow_inbound
) VALUES
    ('ext-1001', 'test-connection', 'default-group', '1001', 'Sales Line 1', '192.168.1.100', 'available', true, true),
    ('ext-1002', 'test-connection', 'default-group', '1002', 'Sales Line 2', '192.168.1.100', 'available', true, true),
    ('ext-1003', 'test-connection', 'default-group', '1003', 'Support Line', '192.168.1.100', 'available', true, true)
ON CONFLICT (freepbx_connection_id, extension) DO NOTHING;