const sqlite3 = require('sqlite3').verbose();
const path = require('path');
const fs = require('fs');

const DB_PATH = path.join(__dirname, 'saas_platform.db');

// Создаем или открываем базу данных
const db = new sqlite3.Database(DB_PATH);

// Функция инициализации базы данных
function initDatabase() {
    return new Promise((resolve, reject) => {
        db.serialize(() => {
            // Создание таблиц
            db.run(`
                CREATE TABLE IF NOT EXISTS users (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    username TEXT UNIQUE NOT NULL,
                    email TEXT UNIQUE NOT NULL,
                    password_hash TEXT NOT NULL,
                    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                    is_active BOOLEAN DEFAULT 1,
                    subscription_type TEXT DEFAULT 'free'
                )
            `);

            db.run(`
                CREATE TABLE IF NOT EXISTS user_credentials (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    user_id INTEGER NOT NULL,
                    service_name TEXT NOT NULL,
                    credential_type TEXT NOT NULL,
                    encrypted_value TEXT NOT NULL,
                    description TEXT,
                    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                    is_active BOOLEAN DEFAULT 1,
                    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
                    UNIQUE(user_id, service_name, credential_type)
                )
            `);

            db.run(`
                CREATE TABLE IF NOT EXISTS workflow_templates (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    name TEXT NOT NULL,
                    description TEXT,
                    category TEXT,
                    template_data TEXT NOT NULL,
                    n8n_workflow_id TEXT,
                    required_credentials TEXT,
                    difficulty_level TEXT DEFAULT 'beginner',
                    estimated_setup_time INTEGER,
                    is_featured BOOLEAN DEFAULT 0,
                    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                    is_active BOOLEAN DEFAULT 1
                )
            `);

            db.run(`
                CREATE TABLE IF NOT EXISTS user_workflows (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    user_id INTEGER NOT NULL,
                    template_id INTEGER,
                    workflow_name TEXT NOT NULL,
                    n8n_workflow_id TEXT,
                    status TEXT DEFAULT 'draft',
                    configuration TEXT,
                    last_run_at DATETIME,
                    total_runs INTEGER DEFAULT 0,
                    successful_runs INTEGER DEFAULT 0,
                    failed_runs INTEGER DEFAULT 0,
                    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
                    FOREIGN KEY (template_id) REFERENCES workflow_templates(id) ON DELETE SET NULL
                )
            `);

            db.run(`
                CREATE TABLE IF NOT EXISTS usage_stats (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    user_id INTEGER NOT NULL,
                    workflow_id INTEGER,
                    action_type TEXT NOT NULL,
                    resource_usage INTEGER DEFAULT 1,
                    execution_time INTEGER,
                    status TEXT NOT NULL,
                    metadata TEXT,
                    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
                    FOREIGN KEY (workflow_id) REFERENCES user_workflows(id) ON DELETE SET NULL
                )
            `);

            // Создание индексов
            db.run('CREATE INDEX IF NOT EXISTS idx_user_credentials_user_id ON user_credentials(user_id)');
            db.run('CREATE INDEX IF NOT EXISTS idx_user_workflows_user_id ON user_workflows(user_id)');
            db.run('CREATE INDEX IF NOT EXISTS idx_usage_stats_user_id ON usage_stats(user_id)');
            db.run('CREATE INDEX IF NOT EXISTS idx_workflow_templates_category ON workflow_templates(category)');
            db.run('CREATE INDEX IF NOT EXISTS idx_workflow_templates_featured ON workflow_templates(is_featured)');

            // Вставка начальных данных
            insertInitialData(() => {
                console.log('✅ База данных инициализирована успешно');
                resolve(db);
            });
        });
    });
}

function insertInitialData(callback) {
    // Вставка шаблонов рабочих процессов
    const templates = [
        {
            name: 'Уведомления в Telegram',
            description: 'Отправка уведомлений в Telegram при возникновении определенных событий',
            category: 'notifications',
            template_data: JSON.stringify({
                "nodes": [
                    {
                        "parameters": {
                            "httpMethod": "POST",
                            "path": "webhook",
                            "responseMode": "onReceived",
                            "options": {}
                        },
                        "id": "webhook",
                        "name": "Webhook",
                        "type": "n8n-nodes-base.webhook",
                        "typeVersion": 1,
                        "position": [240, 300]
                    },
                    {
                        "parameters": {
                            "chatId": "",
                            "text": "=Новое уведомление: {{$json[\"message\"]}}"
                        },
                        "id": "telegram",
                        "name": "Telegram",
                        "type": "n8n-nodes-base.telegram",
                        "typeVersion": 1,
                        "position": [460, 300]
                    }
                ],
                "connections": {
                    "Webhook": {
                        "main": [[{"node": "Telegram", "type": "main", "index": 0}]]
                    }
                }
            }),
            required_credentials: JSON.stringify(["telegram_bot_token"]),
            difficulty_level: 'beginner',
            estimated_setup_time: 15,
            is_featured: 1
        },
        {
            name: 'Email в Slack интеграция',
            description: 'Пересылка важных писем в канал Slack',
            category: 'communication',
            template_data: JSON.stringify({
                "nodes": [
                    {
                        "parameters": {
                            "pollTimes": {"item": [{"mode": "everyMinute", "value": 5}]}
                        },
                        "id": "email",
                        "name": "Email (IMAP)",
                        "type": "n8n-nodes-base.emailReadImap",
                        "typeVersion": 1,
                        "position": [240, 300]
                    },
                    {
                        "parameters": {
                            "channel": "#general",
                            "text": "=Новое письмо от {{$json[\"from\"][\"text\"]}}: {{$json[\"subject\"]}}"
                        },
                        "id": "slack",
                        "name": "Slack",
                        "type": "n8n-nodes-base.slack",
                        "typeVersion": 1,
                        "position": [460, 300]
                    }
                ],
                "connections": {
                    "Email (IMAP)": {
                        "main": [[{"node": "Slack", "type": "main", "index": 0}]]
                    }
                }
            }),
            required_credentials: JSON.stringify(["imap_credentials", "slack_oauth_token"]),
            difficulty_level: 'intermediate',
            estimated_setup_time: 25,
            is_featured: 1
        },
        {
            name: 'Резервное копирование в Google Drive',
            description: 'Автоматическое резервное копирование важных данных в Google Drive',
            category: 'backup',
            template_data: JSON.stringify({
                "nodes": [
                    {
                        "parameters": {
                            "rule": {"interval": [{"field": "hours", "value": 24}]}
                        },
                        "id": "schedule",
                        "name": "Schedule Trigger",
                        "type": "n8n-nodes-base.scheduleTrigger",
                        "typeVersion": 1,
                        "position": [240, 300]
                    },
                    {
                        "parameters": {
                            "operation": "upload",
                            "fileId": "={{$json[\"file_id\"]}}"
                        },
                        "id": "googledrive",
                        "name": "Google Drive",
                        "type": "n8n-nodes-base.googleDrive",
                        "typeVersion": 1,
                        "position": [460, 300]
                    }
                ],
                "connections": {
                    "Schedule Trigger": {
                        "main": [[{"node": "Google Drive", "type": "main", "index": 0}]]
                    }
                }
            }),
            required_credentials: JSON.stringify(["google_drive_oauth"]),
            difficulty_level: 'intermediate',
            estimated_setup_time: 30,
            is_featured: 0
        },
        {
            name: 'Автоматизация скоринга лидов',
            description: 'Автоматическая оценка и маршрутизация лидов по критериям',
            category: 'sales',
            template_data: JSON.stringify({
                "nodes": [
                    {
                        "parameters": {
                            "httpMethod": "POST",
                            "path": "lead",
                            "responseMode": "onReceived"
                        },
                        "id": "webhook",
                        "name": "Webhook",
                        "type": "n8n-nodes-base.webhook",
                        "typeVersion": 1,
                        "position": [240, 300]
                    },
                    {
                        "parameters": {
                            "conditions": {
                                "number": [{
                                    "value1": "={{$json[\"budget\"]}}",
                                    "operation": "larger",
                                    "value2": 10000
                                }]
                            }
                        },
                        "id": "if",
                        "name": "If",
                        "type": "n8n-nodes-base.if",
                        "typeVersion": 1,
                        "position": [460, 300]
                    },
                    {
                        "parameters": {
                            "subject": "Горячий лид!",
                            "text": "=Высокобюджетный лид: {{$json[\"name\"]}} - Бюджет: ${{$json[\"budget\"]}}"
                        },
                        "id": "email",
                        "name": "Email",
                        "type": "n8n-nodes-base.emailSend",
                        "typeVersion": 1,
                        "position": [680, 200]
                    }
                ],
                "connections": {
                    "Webhook": {
                        "main": [[{"node": "If", "type": "main", "index": 0}]]
                    },
                    "If": {
                        "main": [[{"node": "Email", "type": "main", "index": 0}]]
                    }
                }
            }),
            required_credentials: JSON.stringify(["smtp_credentials"]),
            difficulty_level: 'advanced',
            estimated_setup_time: 45,
            is_featured: 1
        }
    ];

    // Вставляем шаблоны
    const templateStmt = db.prepare(`
        INSERT OR REPLACE INTO workflow_templates (
            name, description, category, template_data, required_credentials,
            difficulty_level, estimated_setup_time, is_featured
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
    `);

    templates.forEach(template => {
        templateStmt.run([
            template.name,
            template.description,
            template.category,
            template.template_data,
            template.required_credentials,
            template.difficulty_level,
            template.estimated_setup_time,
            template.is_featured
        ]);
    });

    templateStmt.finalize();

    // Создание демо пользователя
    const bcrypt = require('bcrypt');
    const demoPasswordHash = bcrypt.hashSync('demo123', 10);

    db.run(`
        INSERT OR REPLACE INTO users (id, username, email, password_hash, subscription_type)
        VALUES (1, 'demo', 'demo@example.com', ?, 'pro')
    `, [demoPasswordHash], function(err) {
        if (err) {
            console.error('Ошибка создания демо пользователя:', err);
        } else {
            console.log('✅ Демо пользователь создан: demo@example.com / demo123');
        }
        callback();
    });
}

// Экспорт базы данных и функций
module.exports = {
    db,
    initDatabase,
    DB_PATH
};