-- Pamsika WhatsApp Bot Database Initialization Script
-- PostgreSQL Database Schema

-- Create database extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pg_trgm";

-- Create users table
CREATE TABLE IF NOT EXISTS users (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    phone VARCHAR(15) UNIQUE NOT NULL,
    name VARCHAR(100),
    language VARCHAR(5) DEFAULT 'en',
    is_active BOOLEAN DEFAULT true,
    message_count INTEGER DEFAULT 0,
    last_seen TIMESTAMP WITH TIME ZONE,
    join_date TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    session_data JSONB DEFAULT '{}',
    preferences JSONB DEFAULT '{}',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Create conversations table
CREATE TABLE IF NOT EXISTS conversations (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID REFERENCES users(id) ON DELETE CASCADE,
    message TEXT NOT NULL,
    direction VARCHAR(10) NOT NULL CHECK (direction IN ('incoming', 'outgoing')),
    message_type VARCHAR(20) DEFAULT 'text' CHECK (message_type IN ('text', 'media', 'document', 'image', 'video')),
    media_url TEXT,
    timestamp TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(20) DEFAULT 'sent' CHECK (status IN ('sent', 'delivered', 'read', 'failed')),
    metadata JSONB DEFAULT '{}'
);

-- Create orders table
CREATE TABLE IF NOT EXISTS orders (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    order_id VARCHAR(50) UNIQUE NOT NULL,
    user_id UUID REFERENCES users(id) ON DELETE SET NULL,
    phone VARCHAR(15) NOT NULL,
    customer_name VARCHAR(100) NOT NULL,
    product_id VARCHAR(50) NOT NULL,
    product_name VARCHAR(200) NOT NULL,
    quantity INTEGER NOT NULL DEFAULT 1,
    unit_price DECIMAL(10,2) NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    payment_status VARCHAR(20) DEFAULT 'pending' CHECK (payment_status IN ('pending', 'paid', 'failed', 'cancelled', 'refunded')),
    payment_method VARCHAR(20),
    order_data JSONB DEFAULT '{}',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Create admin_users table
CREATE TABLE IF NOT EXISTS admin_users (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    username VARCHAR(50) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    role VARCHAR(20) DEFAULT 'viewer' CHECK (role IN ('super_admin', 'admin', 'moderator', 'viewer')),
    permissions TEXT[] DEFAULT ARRAY[]::TEXT[],
    is_active BOOLEAN DEFAULT true,
    last_login TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    created_by UUID REFERENCES admin_users(id),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Create settings table
CREATE TABLE IF NOT EXISTS settings (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    key VARCHAR(100) UNIQUE NOT NULL,
    value TEXT,
    data_type VARCHAR(20) DEFAULT 'string' CHECK (data_type IN ('string', 'number', 'boolean', 'json')),
    description TEXT,
    is_system BOOLEAN DEFAULT false,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_by UUID REFERENCES admin_users(id)
);

-- Create sessions table for WhatsApp sessions
CREATE TABLE IF NOT EXISTS whatsapp_sessions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    session_id VARCHAR(100) UNIQUE NOT NULL,
    session_data JSONB NOT NULL,
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    last_active TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP WITH TIME ZONE
);

-- Create message_queue table for reliable message delivery
CREATE TABLE IF NOT EXISTS message_queue (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    phone VARCHAR(15) NOT NULL,
    message TEXT,
    media_url TEXT,
    message_type VARCHAR(20) DEFAULT 'text',
    priority INTEGER DEFAULT 5 CHECK (priority BETWEEN 1 AND 10),
    max_attempts INTEGER DEFAULT 3,
    current_attempts INTEGER DEFAULT 0,
    status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending', 'processing', 'sent', 'failed', 'cancelled')),
    scheduled_for TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    last_attempt TIMESTAMP WITH TIME ZONE,
    error_message TEXT,
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Create analytics table for tracking metrics
CREATE TABLE IF NOT EXISTS analytics (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    event_type VARCHAR(50) NOT NULL,
    event_name VARCHAR(100) NOT NULL,
    user_id UUID REFERENCES users(id) ON DELETE SET NULL,
    phone VARCHAR(15),
    event_data JSONB DEFAULT '{}',
    timestamp TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    session_id VARCHAR(100)
);

-- Create audit_log table for admin actions
CREATE TABLE IF NOT EXISTS audit_log (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    admin_id UUID REFERENCES admin_users(id) ON DELETE SET NULL,
    action VARCHAR(100) NOT NULL,
    resource_type VARCHAR(50),
    resource_id VARCHAR(100),
    old_values JSONB,
    new_values JSONB,
    ip_address INET,
    user_agent TEXT,
    timestamp TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Create indexes for performance
CREATE INDEX IF NOT EXISTS idx_users_phone ON users(phone);
CREATE INDEX IF NOT EXISTS idx_users_is_active ON users(is_active);
CREATE INDEX IF NOT EXISTS idx_users_last_seen ON users(last_seen);

CREATE INDEX IF NOT EXISTS idx_conversations_user_id ON conversations(user_id);
CREATE INDEX IF NOT EXISTS idx_conversations_timestamp ON conversations(timestamp);
CREATE INDEX IF NOT EXISTS idx_conversations_direction ON conversations(direction);

CREATE INDEX IF NOT EXISTS idx_orders_order_id ON orders(order_id);
CREATE INDEX IF NOT EXISTS idx_orders_user_id ON orders(user_id);
CREATE INDEX IF NOT EXISTS idx_orders_phone ON orders(phone);
CREATE INDEX IF NOT EXISTS idx_orders_payment_status ON orders(payment_status);
CREATE INDEX IF NOT EXISTS idx_orders_created_at ON orders(created_at);

CREATE INDEX IF NOT EXISTS idx_admin_users_username ON admin_users(username);
CREATE INDEX IF NOT EXISTS idx_admin_users_is_active ON admin_users(is_active);

CREATE INDEX IF NOT EXISTS idx_settings_key ON settings(key);
CREATE INDEX IF NOT EXISTS idx_settings_is_system ON settings(is_system);

CREATE INDEX IF NOT EXISTS idx_message_queue_status ON message_queue(status);
CREATE INDEX IF NOT EXISTS idx_message_queue_scheduled_for ON message_queue(scheduled_for);
CREATE INDEX IF NOT EXISTS idx_message_queue_phone ON message_queue(phone);

CREATE INDEX IF NOT EXISTS idx_analytics_event_type ON analytics(event_type);
CREATE INDEX IF NOT EXISTS idx_analytics_timestamp ON analytics(timestamp);
CREATE INDEX IF NOT EXISTS idx_analytics_user_id ON analytics(user_id);

CREATE INDEX IF NOT EXISTS idx_audit_log_admin_id ON audit_log(admin_id);
CREATE INDEX IF NOT EXISTS idx_audit_log_timestamp ON audit_log(timestamp);
CREATE INDEX IF NOT EXISTS idx_audit_log_action ON audit_log(action);

-- Create full-text search indexes
CREATE INDEX IF NOT EXISTS idx_users_name_search ON users USING gin(name gin_trgm_ops);
CREATE INDEX IF NOT EXISTS idx_conversations_message_search ON conversations USING gin(message gin_trgm_ops);

-- Create functions and triggers for updated_at timestamps
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

-- Apply updated_at triggers
CREATE TRIGGER update_users_updated_at BEFORE UPDATE ON users
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_orders_updated_at BEFORE UPDATE ON orders
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_admin_users_updated_at BEFORE UPDATE ON admin_users
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_settings_updated_at BEFORE UPDATE ON settings
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- Insert default admin user (password: admin123)
INSERT INTO admin_users (username, password_hash, role, permissions, is_active)
VALUES (
    'admin',
    '$2a$10$8K1p/a0dCZRHXHSEVgPF4eR7W8uBjsafOq6Kjn8rCPFQBP9sZ2JSe',
    'super_admin',
    ARRAY['users', 'messages', 'settings', 'stats', 'roles'],
    true
) ON CONFLICT (username) DO NOTHING;

-- Insert default settings
INSERT INTO settings (key, value, data_type, description, is_system) VALUES
    ('welcome_message', 'Welcome to Pamsika Ticketing Bot! How can I help you today?', 'string', 'Default welcome message for new users', true),
    ('session_timeout', '3600000', 'number', 'Session timeout in milliseconds', true),
    ('max_retries', '3', 'number', 'Maximum retry attempts for failed operations', true),
    ('support_phone', '+265 995 684 047', 'string', 'Support phone number', false),
    ('api_base_url', 'https://test.pamsikaonline.com/api', 'string', 'Base URL for Pamsika API', false),
    ('company_name', 'PamsikaOnline', 'string', 'Company name', false),
    ('company_email', 'inquiries@pamsikaonline.com', 'string', 'Company email', false),
    ('company_website', 'https://pamsikaonline.com', 'string', 'Company website', false),
    ('bot_enabled', 'true', 'boolean', 'Enable/disable bot functionality', true),
    ('maintenance_mode', 'false', 'boolean', 'Enable maintenance mode', true),
    ('rate_limit_window', '900000', 'number', 'Rate limit window in milliseconds', true),
    ('rate_limit_max_requests', '100', 'number', 'Maximum requests per rate limit window', true),
    ('message_retention_days', '90', 'number', 'Days to retain conversation messages', true),
    ('analytics_enabled', 'true', 'boolean', 'Enable analytics collection', true),
    ('auto_backup_enabled', 'true', 'boolean', 'Enable automatic backups', true)
ON CONFLICT (key) DO NOTHING;

-- Create views for common queries
CREATE OR REPLACE VIEW user_stats AS
SELECT
    u.id,
    u.phone,
    u.name,
    u.language,
    u.is_active,
    u.message_count,
    u.last_seen,
    u.join_date,
    COUNT(c.id) as conversation_count,
    COUNT(o.id) as order_count,
    COALESCE(SUM(o.total_amount), 0) as total_spent
FROM users u
LEFT JOIN conversations c ON u.id = c.user_id
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id;

CREATE OR REPLACE VIEW daily_stats AS
SELECT
    DATE(created_at) as date,
    'users' as metric,
    COUNT(*) as value
FROM users
GROUP BY DATE(created_at)
UNION ALL
SELECT
    DATE(timestamp) as date,
    'messages' as metric,
    COUNT(*) as value
FROM conversations
GROUP BY DATE(timestamp)
UNION ALL
SELECT
    DATE(created_at) as date,
    'orders' as metric,
    COUNT(*) as value
FROM orders
GROUP BY DATE(created_at);

-- Create function for cleaning old data
CREATE OR REPLACE FUNCTION cleanup_old_data()
RETURNS INTEGER AS $$
DECLARE
    retention_days INTEGER;
    deleted_count INTEGER := 0;
BEGIN
    -- Get retention period from settings
    SELECT value::INTEGER INTO retention_days
    FROM settings
    WHERE key = 'message_retention_days'
    LIMIT 1;

    IF retention_days IS NULL THEN
        retention_days := 90; -- Default to 90 days
    END IF;

    -- Delete old conversations
    DELETE FROM conversations
    WHERE timestamp < CURRENT_TIMESTAMP - INTERVAL '1 day' * retention_days;

    GET DIAGNOSTICS deleted_count = ROW_COUNT;

    -- Delete old analytics data (keep for 1 year)
    DELETE FROM analytics
    WHERE timestamp < CURRENT_TIMESTAMP - INTERVAL '365 days';

    -- Delete old audit logs (keep for 6 months)
    DELETE FROM audit_log
    WHERE timestamp < CURRENT_TIMESTAMP - INTERVAL '180 days';

    -- Delete processed message queue items older than 7 days
    DELETE FROM message_queue
    WHERE status IN ('sent', 'failed', 'cancelled')
    AND created_at < CURRENT_TIMESTAMP - INTERVAL '7 days';

    RETURN deleted_count;
END;
$$ LANGUAGE plpgsql;

-- Grant permissions (adjust as needed for your setup)
-- GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO pamsika_bot_user;
-- GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO pamsika_bot_user;
-- GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO pamsika_bot_user;

-- Create a scheduled cleanup job (requires pg_cron extension)
-- SELECT cron.schedule('cleanup-old-data', '0 2 * * *', 'SELECT cleanup_old_data();');

COMMENT ON DATABASE CURRENT_DATABASE() IS 'Pamsika WhatsApp Bot Database - Production Ready Schema';