const sqlite3 = require('sqlite3').verbose();
const { Pool } = require('pg');
const path = require('path');
const fs = require('fs');
require('dotenv').config();

// Определяем, какую базу использовать
const USE_POSTGRES = process.env.POSTGRESQL === 'yes';
let db = null; // Основной объект базы данных
let postgresPool = null; // Пул соединений PostgreSQL
let isInitialized = false; // Флаг инициализации

const dataDir = path.join(__dirname, 'data');
const createDirIfNotExists = (dirPath) => {
    if (!fs.existsSync(dirPath)) {
        fs.mkdirSync(dirPath, { recursive: true });
    }
};

createDirIfNotExists(dataDir);

const dbPath = path.join(dataDir, 'school_crm.db');
const uploadsDir = path.join(dataDir, 'uploads');
const tasksDir = path.join(uploadsDir, 'tasks');
const logsDir = path.join(dataDir, 'logs');

createDirIfNotExists(uploadsDir);
createDirIfNotExists(tasksDir);
createDirIfNotExists(logsDir);

// Инициализация базы данных
async function initializeDatabase() {
    console.log(`🔧 Используется ${USE_POSTGRES ? 'PostgreSQL' : 'SQLite'}`);
    
    if (USE_POSTGRES) {
        // Используем PostgreSQL
        try {
            postgresPool = new Pool({
                host: process.env.DB_HOST,
                port: process.env.DB_PORT || 5432,
                database: process.env.DB_NAME || 'minicrm',
                user: process.env.DB_USER,
                password: process.env.DB_PASSWORD,
                max: 10,
                idleTimeoutMillis: 30000,
                connectionTimeoutMillis: 5000,
            });

            // Тестируем подключение
            const client = await postgresPool.connect();
            await client.query('SELECT 1');
            client.release();
            
            console.log('✅ Подключение к PostgreSQL установлено');
            
            // Создаем адаптер для PostgreSQL
            db = createPostgresAdapter(postgresPool);
            
            // Проверяем и создаем таблицы
            await createPostgresTables();
            
            isInitialized = true;
            
        } catch (error) {
            console.error('❌ Ошибка подключения к PostgreSQL:', error.message);
            console.log('🔄 Пытаемся использовать SQLite как запасной вариант...');
            await initializeSQLite();
        }
    } else {
        // Используем SQLite
        await initializeSQLite();
    }
    
    return db;
}

function initializeSQLite() {
    return new Promise((resolve, reject) => {
        db = new sqlite3.Database(dbPath, (err) => {
            if (err) {
                console.error('❌ Ошибка подключения к SQLite:', err.message);
                reject(err);
                return;
            } else {
                console.log('✅ Подключение к SQLite установлено');
                console.log('📁 База данных расположена:', dbPath);
                createSQLiteTables();
                isInitialized = true;
                resolve(db);
            }
        });
    });
}

function createSQLiteTables() {
    // SQLite таблицы
    db.run(`CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        login TEXT UNIQUE NOT NULL,
        password TEXT,
        name TEXT NOT NULL,
        email TEXT UNIQUE NOT NULL,
        role TEXT DEFAULT 'teacher',
        auth_type TEXT DEFAULT 'local',
        groups TEXT,
        description TEXT,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        last_login DATETIME,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )`);

    db.run(`CREATE TABLE IF NOT EXISTS tasks (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        title TEXT NOT NULL,
        description TEXT,
        status TEXT DEFAULT 'active',
        created_by INTEGER NOT NULL,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        deleted_at DATETIME,
        deleted_by INTEGER,
        original_task_id INTEGER,
        start_date DATETIME,
        due_date DATETIME,
        rework_comment TEXT,
        closed_at DATETIME,
        closed_by INTEGER,
        FOREIGN KEY (created_by) REFERENCES users (id),
        FOREIGN KEY (deleted_by) REFERENCES users (id),
        FOREIGN KEY (original_task_id) REFERENCES tasks (id),
        FOREIGN KEY (closed_by) REFERENCES users (id)
    )`);

    db.run(`CREATE TABLE IF NOT EXISTS task_assignments (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        task_id INTEGER NOT NULL,
        user_id INTEGER NOT NULL,
        status TEXT DEFAULT 'assigned',
        start_date DATETIME,
        due_date DATETIME,
        rework_comment TEXT,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (task_id) REFERENCES tasks (id),
        FOREIGN KEY (user_id) REFERENCES users (id)
    )`);

    db.run(`CREATE TABLE IF NOT EXISTS task_files (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        task_id INTEGER NOT NULL,
        user_id INTEGER NOT NULL,
        filename TEXT NOT NULL,
        original_name TEXT NOT NULL,
        file_path TEXT NOT NULL,
        file_size INTEGER NOT NULL,
        uploaded_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (task_id) REFERENCES tasks (id),
        FOREIGN KEY (user_id) REFERENCES users (id)
    )`);

    db.run(`CREATE TABLE IF NOT EXISTS activity_logs (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        task_id INTEGER NOT NULL,
        user_id INTEGER NOT NULL,
        action TEXT NOT NULL,
        details TEXT,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (task_id) REFERENCES tasks (id),
        FOREIGN KEY (user_id) REFERENCES users (id)
    )`);

    db.run(`CREATE TABLE IF NOT EXISTS notification_logs (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        notification_key TEXT NOT NULL,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )`);

    console.log('✅ База данных SQLite инициализирована');
    setTimeout(addMissingColumns, 1000);
}

function createPostgresAdapter(pool) {
    // Адаптер для PostgreSQL с совместимым API
    return {
        all: (sql, params = [], callback) => {
            if (!callback && typeof params === 'function') {
                callback = params;
                params = [];
            }
            
            // Адаптируем SQL для PostgreSQL
            const adaptedSql = adaptSQLForPostgres(sql);
            
            pool.query(adaptedSql, params)
                .then(result => callback(null, result.rows))
                .catch(err => {
                    console.error('PostgreSQL Error (all):', err.message, 'SQL:', adaptedSql);
                    callback(err);
                });
        },
        
        get: (sql, params = [], callback) => {
            if (!callback && typeof params === 'function') {
                callback = params;
                params = [];
            }
            
            // Адаптируем SQL для PostgreSQL
            const adaptedSql = adaptSQLForPostgres(sql);
            
            pool.query(adaptedSql, params)
                .then(result => callback(null, result.rows[0] || null))
                .catch(err => {
                    console.error('PostgreSQL Error (get):', err.message, 'SQL:', adaptedSql);
                    callback(err);
                });
        },
        
        run: (sql, params = [], callback) => {
            if (!callback && typeof params === 'function') {
                callback = params;
                params = [];
            }
            
            // Адаптируем SQL для PostgreSQL
            const adaptedSql = adaptSQLForPostgres(sql);
            
            pool.query(adaptedSql, params)
                .then(result => {
                    if (callback) {
                        const lastIdQuery = sql.toLowerCase().includes('insert into') ? 
                            "SELECT lastval() as last_id" : "SELECT 0 as last_id";
                        
                        if (sql.toLowerCase().includes('insert into')) {
                            pool.query("SELECT lastval() as last_id", [])
                                .then(lastIdResult => {
                                    callback(null, {
                                        lastID: lastIdResult.rows[0]?.last_id || null,
                                        changes: result.rowCount || 0
                                    });
                                })
                                .catch(err => callback(err));
                        } else {
                            callback(null, {
                                lastID: null,
                                changes: result.rowCount || 0
                            });
                        }
                    }
                })
                .catch(err => {
                    console.error('PostgreSQL Error (run):', err.message, 'SQL:', adaptedSql);
                    if (callback) callback(err);
                });
        },
        
        // Для транзакций - эмуляция
        serialize: (callback) => {
            // В PostgreSQL транзакции обрабатываются по-другому
            // Здесь просто выполняем колбэк
            try {
                callback();
            } catch (error) {
                console.error('Error in serialize:', error);
            }
        },
        
        // Закрытие соединения
        close: (callback) => {
            pool.end()
                .then(() => {
                    if (callback) callback(null);
                })
                .catch(err => {
                    if (callback) callback(err);
                });
        },
        
        // Дополнительные методы
        exec: (sql, callback) => {
            pool.query(sql)
                .then(() => {
                    if (callback) callback(null);
                })
                .catch(err => {
                    if (callback) callback(err);
                });
        }
    };
}

function adaptSQLForPostgres(sql) {
    // Адаптируем SQL запросы для PostgreSQL
    let adaptedSql = sql;
    
    // Заменяем SQLite-специфичные синтаксисы
    adaptedSql = adaptedSql.replace(/AUTOINCREMENT/gi, 'SERIAL');
    adaptedSql = adaptedSql.replace(/DATETIME/gi, 'TIMESTAMP');
    adaptedSql = adaptedSql.replace(/INTEGER PRIMARY KEY/gi, 'SERIAL PRIMARY KEY');
    adaptedSql = adaptedSql.replace(/datetime\('now'\)/gi, 'CURRENT_TIMESTAMP');
    adaptedSql = adaptedSql.replace(/CURRENT_TIMESTAMP/gi, 'CURRENT_TIMESTAMP');
    
    // Исправляем INSERT с возвратом ID
    if (adaptedSql.includes('INSERT INTO') && adaptedSql.includes('RETURNING id')) {
        adaptedSql = adaptedSql.replace('RETURNING id', 'RETURNING id');
    }
    
    return adaptedSql;
}

async function createPostgresTables() {
    if (!USE_POSTGRES) return;
    
    try {
        const client = await postgresPool.connect();
        
        console.log('🔧 Проверяем/создаем таблицы в PostgreSQL...');
        
        // Создаем таблицы PostgreSQL
        await client.query(`
            CREATE TABLE IF NOT EXISTS users (
                id SERIAL PRIMARY KEY,
                login VARCHAR(100) UNIQUE NOT NULL,
                password TEXT,
                name VARCHAR(255) NOT NULL,
                email VARCHAR(255) UNIQUE NOT NULL,
                role VARCHAR(50) DEFAULT 'teacher',
                auth_type VARCHAR(50) DEFAULT 'local',
                groups TEXT DEFAULT '[]',
                description TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                last_login TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        `);
        
        await client.query(`
            CREATE TABLE IF NOT EXISTS tasks (
                id SERIAL PRIMARY KEY,
                title VARCHAR(500) NOT NULL,
                description TEXT,
                status VARCHAR(50) DEFAULT 'active',
                created_by INTEGER NOT NULL REFERENCES users(id),
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                deleted_at TIMESTAMP,
                deleted_by INTEGER REFERENCES users(id),
                original_task_id INTEGER REFERENCES tasks(id),
                start_date TIMESTAMP,
                due_date TIMESTAMP,
                rework_comment TEXT,
                closed_at TIMESTAMP,
                closed_by INTEGER REFERENCES users(id)
            )
        `);
        
        await client.query(`
            CREATE TABLE IF NOT EXISTS task_assignments (
                id SERIAL PRIMARY KEY,
                task_id INTEGER NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
                user_id INTEGER NOT NULL REFERENCES users(id),
                status VARCHAR(50) DEFAULT 'assigned',
                start_date TIMESTAMP,
                due_date TIMESTAMP,
                rework_comment TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        `);
        
        await client.query(`
            CREATE TABLE IF NOT EXISTS task_files (
                id SERIAL PRIMARY KEY,
                task_id INTEGER NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
                user_id INTEGER NOT NULL REFERENCES users(id),
                filename VARCHAR(255) NOT NULL,
                original_name VARCHAR(500) NOT NULL,
                file_path TEXT NOT NULL,
                file_size BIGINT NOT NULL,
                uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        `);
        
        await client.query(`
            CREATE TABLE IF NOT EXISTS activity_logs (
                id SERIAL PRIMARY KEY,
                task_id INTEGER NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
                user_id INTEGER NOT NULL REFERENCES users(id),
                action VARCHAR(100) NOT NULL,
                details TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        `);
        
        await client.query(`
            CREATE TABLE IF NOT EXISTS notification_logs (
                id SERIAL PRIMARY KEY,
                notification_key VARCHAR(500) NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        `);
        
        // Создаем индексы
        const indexes = [
            'CREATE INDEX IF NOT EXISTS idx_tasks_created_by ON tasks(created_by)',
            'CREATE INDEX IF NOT EXISTS idx_tasks_status ON tasks(status)',
            'CREATE INDEX IF NOT EXISTS idx_tasks_closed_at ON tasks(closed_at)',
            'CREATE INDEX IF NOT EXISTS idx_task_assignments_task_id ON task_assignments(task_id)',
            'CREATE INDEX IF NOT EXISTS idx_task_assignments_user_id ON task_assignments(user_id)',
            'CREATE INDEX IF NOT EXISTS idx_task_assignments_status ON task_assignments(status)',
            'CREATE INDEX IF NOT EXISTS idx_task_files_task_id ON task_files(task_id)',
            'CREATE INDEX IF NOT EXISTS idx_activity_logs_task_id ON activity_logs(task_id)',
            'CREATE INDEX IF NOT EXISTS idx_activity_logs_created_at ON activity_logs(created_at)'
        ];
        
        for (const indexQuery of indexes) {
            try {
                await client.query(indexQuery);
            } catch (err) {
                console.warn(`⚠️ Не удалось создать индекс: ${err.message}`);
            }
        }
        
        client.release();
        console.log('✅ Таблицы PostgreSQL проверены/созданы');
        
    } catch (error) {
        console.error('❌ Ошибка создания таблиц PostgreSQL:', error.message);
    }
}

function addMissingColumns() {
    const columnsToAdd = [
        { table: 'tasks', column: 'rework_comment', type: 'TEXT' },
        { table: 'tasks', column: 'closed_at', type: 'DATETIME' },
        { table: 'tasks', column: 'closed_by', type: 'INTEGER' },
        { table: 'task_assignments', column: 'rework_comment', type: 'TEXT' }
    ];

    columnsToAdd.forEach(({ table, column, type }) => {
        db.all(`PRAGMA table_info(${table})`, (err, rows) => {
            if (err) {
                console.error(`Ошибка при проверке таблицы ${table}:`, err);
                return;
            }

            const columnExists = rows.some(row => row.name === column);
            if (!columnExists) {
                db.run(`ALTER TABLE ${table} ADD COLUMN ${column} ${type}`, (err) => {
                    if (err) {
                        console.error(`Ошибка при добавлении колонки ${column} в таблицу ${table}:`, err);
                    } else {
                        console.log(`✅ Добавлена колонка ${column} в таблицу ${table}`);
                    }
                });
            } else {
                console.log(`✅ Колонка ${column} уже существует в таблице ${table}`);
            }
        });
    });
}

function createTaskFolder(taskId) {
    const taskFolder = path.join(tasksDir, taskId.toString());
    createDirIfNotExists(taskFolder);
    return taskFolder;
}

function createUserTaskFolder(taskId, userLogin) {
    const taskFolder = path.join(tasksDir, taskId.toString());
    const userFolder = path.join(taskFolder, userLogin);
    createDirIfNotExists(userFolder);
    return userFolder;
}

function saveTaskMetadata(taskId, title, description, createdBy, originalTaskId = null, startDate = null, dueDate = null) {
    const taskFolder = createTaskFolder(taskId);
    const metadata = {
        id: taskId,
        title: title,
        description: description,
        status: 'active',
        created_by: createdBy,
        original_task_id: originalTaskId,
        start_date: startDate,
        due_date: dueDate,
        created_at: new Date().toISOString(),
        updated_at: new Date().toISOString(),
        files: []
    };

    const metadataPath = path.join(taskFolder, 'task.json');
    fs.writeFileSync(metadataPath, JSON.stringify(metadata, null, 2));
}

function updateTaskMetadata(taskId, updates) {
    const metadataPath = path.join(tasksDir, taskId.toString(), 'task.json');
    if (fs.existsSync(metadataPath)) {
        const metadata = JSON.parse(fs.readFileSync(metadataPath, 'utf8'));
        const updatedMetadata = { ...metadata, ...updates, updated_at: new Date().toISOString() };
        fs.writeFileSync(metadataPath, JSON.stringify(updatedMetadata, null, 2));
    }
}

function logActivity(taskId, userId, action, details = '') {
    db.run(
        "INSERT INTO activity_logs (task_id, user_id, action, details) VALUES (?, ?, ?, ?)",
        [taskId, userId, action, details]
    );

    const logEntry = `${new Date().toISOString()} - User ${userId}: ${action} - Task ${taskId} - ${details}\n`;
    fs.appendFileSync(path.join(logsDir, 'activity.log'), logEntry);
}

function checkTaskAccess(userId, taskId, callback) {
    db.get("SELECT role FROM users WHERE id = ?", [userId], (err, user) => {
        if (err) {
            callback(err, false);
            return;
        }

        if (user && user.role === 'admin') {
            callback(null, true);
            return;
        }

        db.get("SELECT status, created_by, closed_at FROM tasks WHERE id = ?", [taskId], (err, task) => {
            if (err || !task) {
                callback(err, false);
                return;
            }

            if (task.closed_at && task.created_by !== userId && user.role !== 'admin') {
                callback(null, false);
                return;
            }

            const query = `
                SELECT 1 FROM tasks t
                WHERE t.id = ? AND (
                    t.created_by = ? 
                    OR EXISTS (SELECT 1 FROM task_assignments WHERE task_id = t.id AND user_id = ?)
                )
            `;

            db.get(query, [taskId, userId, userId], (err, row) => {
                callback(err, !!row);
            });
        });
    });
}

function checkOverdueTasks() {
    const now = new Date().toISOString();
    
    const query = `
        SELECT ta.id, ta.task_id, ta.user_id, ta.status, ta.due_date
        FROM task_assignments ta
        JOIN tasks t ON ta.task_id = t.id
        WHERE ta.due_date IS NOT NULL 
        AND ta.due_date < ? 
        AND ta.status NOT IN ('completed', 'overdue')
        AND t.status = 'active'
        AND t.closed_at IS NULL
    `;

    db.all(query, [now], (err, assignments) => {
        if (err) {
            console.error('Ошибка при проверке просроченных задач:', err);
            return;
        }

        assignments.forEach(assignment => {
            db.run(
                "UPDATE task_assignments SET status = 'overdue' WHERE id = ?",
                [assignment.id]
            );
            logActivity(assignment.task_id, assignment.user_id, 'STATUS_CHANGED', 'Задача просрочена');
        });
    });
}

setInterval(checkOverdueTasks, 60000);

module.exports = { 
    initializeDatabase, // Экспортируем функцию инициализации
    getDb: () => {
        if (!isInitialized) {
            throw new Error('База данных не инициализирована');
        }
        return db;
    },
    isInitialized: () => isInitialized,
    logActivity, 
    createTaskFolder, 
    createUserTaskFolder, 
    saveTaskMetadata, 
    updateTaskMetadata,
    checkTaskAccess,
    USE_POSTGRES,
    getDatabaseType: () => USE_POSTGRES ? 'PostgreSQL' : 'SQLite'
};

// Запускаем инициализацию при экспорте (но она завершится позже)
initializeDatabase().catch(err => {
    console.error('❌ Ошибка инициализации базы данных:', err.message);
});