-- ============================================================
-- 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;

-- ============================================================
-- 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);

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;

CREATE TRIGGER trigger_extension_on_assignment
AFTER INSERT OR DELETE ON sip_assignments
FOR EACH ROW
EXECUTE FUNCTION update_extension_on_assignment();
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';
