-- ==========================================
-- ASTRO CALLING DATABASE SCHEMA
-- PostgreSQL
-- ==========================================

-- 1. Users Table (Clients, Astrologers, Staff, Admins, Superadmins)
CREATE TABLE IF NOT EXISTS users (
    id VARCHAR(100) PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255),
    role VARCHAR(50) NOT NULL DEFAULT 'client', -- 'superadmin', 'admin', 'staff', 'astrologer', 'client'
    status VARCHAR(50) NOT NULL DEFAULT 'active', -- 'active', 'inactive', 'blocked'
    avatar_url TEXT,
    wallet_balance NUMERIC(12, 2) DEFAULT 500.00,
    fcm_token TEXT,
    online_status BOOLEAN DEFAULT false,
    last_seen TIMESTAMP DEFAULT NOW(),
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- 2. Staff Permissions Table (Granular RBAC for Staff role)
CREATE TABLE IF NOT EXISTS staff_permissions (
    id SERIAL PRIMARY KEY,
    user_id VARCHAR(100) NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    permission_key VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    UNIQUE (user_id, permission_key)
);

-- 3. Astrologer Profiles Table
CREATE TABLE IF NOT EXISTS astrologer_profiles (
    user_id VARCHAR(100) PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
    audio_rate_per_min NUMERIC(10, 2) DEFAULT 15.00,
    video_rate_per_min NUMERIC(10, 2) DEFAULT 25.00,
    is_busy BOOLEAN DEFAULT false,
    is_available BOOLEAN DEFAULT true,
    rating NUMERIC(3, 2) DEFAULT 5.00,
    specialization TEXT DEFAULT 'Vedic Astrology & Kundali Expert',
    bio TEXT DEFAULT 'Experienced Vedic Astrologer providing personalized horoscope and life consultations.'
);

-- 4. Bookings Table
CREATE TABLE IF NOT EXISTS bookings (
    id VARCHAR(100) PRIMARY KEY,
    client_id VARCHAR(100) NOT NULL REFERENCES users(id),
    astrologer_id VARCHAR(100) NOT NULL REFERENCES users(id),
    status VARCHAR(50) DEFAULT 'confirmed', -- 'confirmed', 'completed', 'cancelled'
    scheduled_at TIMESTAMP DEFAULT NOW(),
    duration_minutes INTEGER DEFAULT 15,
    created_at TIMESTAMP DEFAULT NOW()
);

-- 5. Calls Table
CREATE TABLE IF NOT EXISTS calls (
    id SERIAL PRIMARY KEY,
    call_id VARCHAR(100) UNIQUE NOT NULL,
    caller_id VARCHAR(100) NOT NULL,
    receiver_id VARCHAR(100) NOT NULL,
    call_type VARCHAR(20) NOT NULL CHECK (call_type IN ('audio', 'video')),
    status VARCHAR(50) NOT NULL DEFAULT 'initiated', 
    -- 'initiated', 'ringing', 'accepted', 'rejected', 'missed', 'busy', 'connected', 'ended', 'failed'
    rate_per_minute NUMERIC(10, 2) DEFAULT 0.00,
    total_amount NUMERIC(10, 2) DEFAULT 0.00,
    created_at TIMESTAMP DEFAULT NOW(),
    ringing_at TIMESTAMP,
    answered_at TIMESTAMP,
    connected_at TIMESTAMP,
    ended_at TIMESTAMP,
    duration INTEGER DEFAULT 0, -- Duration in seconds
    end_reason VARCHAR(100),
    recording_status VARCHAR(50) DEFAULT 'none', -- 'none', 'recording', 'processing', 'completed', 'failed', 'deleted'
    recording_path TEXT
);

-- 6. Call Recordings Table
CREATE TABLE IF NOT EXISTS call_recordings (
    id SERIAL PRIMARY KEY,
    call_id VARCHAR(100) NOT NULL REFERENCES calls(call_id) ON DELETE CASCADE,
    caller_id VARCHAR(100) NOT NULL,
    receiver_id VARCHAR(100) NOT NULL,
    file_name VARCHAR(255) NOT NULL,
    file_path TEXT NOT NULL,
    file_size BIGINT DEFAULT 0,
    duration INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT NOW(),
    delete_after TIMESTAMP NOT NULL,
    status VARCHAR(50) NOT NULL DEFAULT 'recording'
);

-- 7. Call Quality Logs Table
CREATE TABLE IF NOT EXISTS call_quality_logs (
    id SERIAL PRIMARY KEY,
    call_id VARCHAR(100) NOT NULL,
    user_id VARCHAR(100) NOT NULL,
    timestamp TIMESTAMP DEFAULT NOW(),
    bitrate INTEGER DEFAULT 0,
    packet_loss NUMERIC(5, 2) DEFAULT 0.00,
    jitter NUMERIC(8, 2) DEFAULT 0.00,
    rtt NUMERIC(8, 2) DEFAULT 0.00,
    fps INTEGER DEFAULT 0,
    resolution VARCHAR(50) DEFAULT 'N/A',
    network_quality VARCHAR(50) DEFAULT 'Good'
);

-- 8. Wallet Transactions Table
CREATE TABLE IF NOT EXISTS wallet_transactions (
    id SERIAL PRIMARY KEY,
    user_id VARCHAR(100) NOT NULL REFERENCES users(id),
    call_id VARCHAR(100),
    amount NUMERIC(12, 2) NOT NULL,
    type VARCHAR(20) NOT NULL CHECK (type IN ('debit', 'credit')),
    description TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);

-- 9. System Settings Table (Superadmin controlled)
-- 9. System Settings Table (Superadmin controlled)
CREATE TABLE IF NOT EXISTS system_settings (
    key VARCHAR(100) PRIMARY KEY,
    value TEXT NOT NULL,
    category VARCHAR(50) DEFAULT 'general',
    description TEXT,
    updated_at TIMESTAMP DEFAULT NOW()
);

-- 10. Device Tokens Table (Multi-device FCM support for Clients & Astrologers)
CREATE TABLE IF NOT EXISTS device_tokens (
    id SERIAL PRIMARY KEY,
    user_id VARCHAR(100) NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    user_role VARCHAR(50) NOT NULL, -- 'client', 'astrologer', 'staff', 'admin', 'superadmin'
    device_id VARCHAR(255) NOT NULL,
    fcm_token TEXT NOT NULL,
    app_type VARCHAR(50) NOT NULL, -- 'client', 'astrologer'
    platform VARCHAR(50) DEFAULT 'android', -- 'android', 'ios', 'web'
    is_active BOOLEAN DEFAULT true,
    last_used_at TIMESTAMP DEFAULT NOW(),
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),
    CONSTRAINT uq_device_app UNIQUE (device_id, app_type)
);

-- 11. Push Notification Logs Table
CREATE TABLE IF NOT EXISTS notification_logs (
    id SERIAL PRIMARY KEY,
    user_id VARCHAR(100),
    user_role VARCHAR(50),
    notification_type VARCHAR(100) NOT NULL,
    title VARCHAR(255) NOT NULL,
    body TEXT NOT NULL,
    reference_id VARCHAR(100),
    fcm_token TEXT,
    status VARCHAR(50) DEFAULT 'pending', -- 'pending', 'sent', 'delivered_if_available', 'failed'
    firebase_message_id VARCHAR(255),
    error_message TEXT,
    data_payload JSONB,
    created_at TIMESTAMP DEFAULT NOW()
);

-- 12. Settings Audit Logs Table
CREATE TABLE IF NOT EXISTS setting_audit_logs (
    id SERIAL PRIMARY KEY,
    user_id VARCHAR(100) NOT NULL,
    role VARCHAR(50) NOT NULL,
    setting_section VARCHAR(100) NOT NULL, -- 'firebase', 'msg91', 'email', 'payment_razorpay', 'payment_cashfree', 'database', 'system_update'
    action VARCHAR(50) NOT NULL, -- 'update', 'test', 'upload', 'rollback'
    old_value_summary TEXT,
    new_value_summary TEXT,
    ip_address VARCHAR(100),
    created_at TIMESTAMP DEFAULT NOW()
);

-- 13. Encrypted Settings Table (For sensitive credentials)
CREATE TABLE IF NOT EXISTS encrypted_settings (
    key VARCHAR(100) PRIMARY KEY,
    section VARCHAR(50) NOT NULL, -- 'firebase', 'msg91', 'email', 'payment_razorpay', 'payment_cashfree', 'database', 'general'
    is_secret BOOLEAN DEFAULT false,
    value TEXT NOT NULL,
    updated_by VARCHAR(100),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- Indexes for performance
CREATE INDEX IF NOT EXISTS idx_users_role ON users(role);
CREATE INDEX IF NOT EXISTS idx_users_status ON users(status);
CREATE INDEX IF NOT EXISTS idx_staff_permissions_user ON staff_permissions(user_id);
CREATE INDEX IF NOT EXISTS idx_calls_call_id ON calls(call_id);
CREATE INDEX IF NOT EXISTS idx_calls_caller_id ON calls(caller_id);
CREATE INDEX IF NOT EXISTS idx_calls_receiver_id ON calls(receiver_id);
CREATE INDEX IF NOT EXISTS idx_calls_status ON calls(status);
CREATE INDEX IF NOT EXISTS idx_calls_created_at ON calls(created_at);
CREATE INDEX IF NOT EXISTS idx_recordings_call_id ON call_recordings(call_id);
CREATE INDEX IF NOT EXISTS idx_recordings_delete_after ON call_recordings(delete_after);
CREATE INDEX IF NOT EXISTS idx_quality_call_id ON call_quality_logs(call_id);
CREATE INDEX IF NOT EXISTS idx_device_tokens_user ON device_tokens(user_id);
CREATE INDEX IF NOT EXISTS idx_device_tokens_active ON device_tokens(is_active);
CREATE INDEX IF NOT EXISTS idx_notification_logs_user ON notification_logs(user_id);
CREATE INDEX IF NOT EXISTS idx_notification_logs_type ON notification_logs(notification_type);
CREATE INDEX IF NOT EXISTS idx_setting_audit_section ON setting_audit_logs(setting_section);