const sqlite3 = require('sqlite3').verbose();
const path = require('path');
const fs = require('fs');
const { Logger, handleDatabaseError } = require('../middleware/errorHandler');

class Database {
  constructor() {
    const dbDir = path.dirname(process.env.DB_PATH || './database/chat.db');
    if (!fs.existsSync(dbDir)) {
      fs.mkdirSync(dbDir, { recursive: true });
    }
    
    this.db = new sqlite3.Database(process.env.DB_PATH || './database/chat.db');
  }

  init() {
    return new Promise((resolve, reject) => {
      this.db.serialize(() => {
        // Create users table
        this.db.run(`
          CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            google_id TEXT UNIQUE NOT NULL,
            name TEXT NOT NULL,
            email TEXT NOT NULL,
            avatar TEXT,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP
          )
        `);

        // Create messages table
        this.db.run(`
          CREATE TABLE IF NOT EXISTS messages (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            content TEXT,
            message_type TEXT DEFAULT 'text',
            private_chat_id INTEGER NULL,
            status TEXT DEFAULT 'sent',
            read_at DATETIME NULL,
            timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users (id),
            FOREIGN KEY (private_chat_id) REFERENCES private_chats (id)
          )
        `);
        
        // Add status and read_at columns if they don't exist (for existing databases)
        this.db.run(`ALTER TABLE messages ADD COLUMN status TEXT DEFAULT 'sent'`, () => {});
        this.db.run(`ALTER TABLE messages ADD COLUMN read_at DATETIME NULL`, () => {});

        // Create files table for attachments
        this.db.run(`
          CREATE TABLE IF NOT EXISTS files (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            message_id INTEGER NOT NULL,
            filename TEXT NOT NULL,
            original_name TEXT NOT NULL,
            file_path TEXT NOT NULL,
            file_size INTEGER NOT NULL,
            mime_type TEXT NOT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (message_id) REFERENCES messages (id) ON DELETE CASCADE
          )
        `);

        // Create private_chats table for private conversations
        this.db.run(`
          CREATE TABLE IF NOT EXISTS private_chats (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user1_id INTEGER NOT NULL,
            user2_id INTEGER NOT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (user1_id) REFERENCES users (id),
            FOREIGN KEY (user2_id) REFERENCES users (id),
            UNIQUE(user1_id, user2_id)
          )
        `, (err) => {
          if (err) {
            Logger.error('Database initialization error', err);
            reject(err);
          } else {
            Logger.info('Database initialized successfully');
            resolve();
          }
        });
      });
    });
  }

  getUserByGoogleId(googleId) {
    return new Promise((resolve, reject) => {
      try {
        this.db.get(
          'SELECT * FROM users WHERE google_id = ?',
          [googleId],
          (err, row) => {
            if (err) {
              Logger.error('Error getting user by Google ID', err);
              handleDatabaseError(err, 'getUserByGoogleId');
            } else {
              resolve(row);
            }
          }
        );
      } catch (error) {
        Logger.error('Unexpected error in getUserByGoogleId', error);
        reject(error);
      }
    });
  }

  getUserById(id) {
    return new Promise((resolve, reject) => {
      try {
        this.db.get(
          'SELECT * FROM users WHERE id = ?',
          [id],
          (err, row) => {
            if (err) {
              Logger.error('Error getting user by ID', err);
              handleDatabaseError(err, 'getUserById');
            } else {
              resolve(row);
            }
          }
        );
      } catch (error) {
        Logger.error('Unexpected error in getUserById', error);
        reject(error);
      }
    });
  }

  createUser(userData) {
    return new Promise((resolve, reject) => {
      try {
        const { googleId, name, email, avatar } = userData;
        Logger.info(`Creating new user: ${name} (${email})`);
        
        this.db.run(
          'INSERT INTO users (google_id, name, email, avatar) VALUES (?, ?, ?, ?)',
          [googleId, name, email, avatar],
          function(err) {
            if (err) {
              Logger.error('Error creating user', err);
              handleDatabaseError(err, 'createUser');
            } else {
              Logger.info(`User created successfully with ID: ${this.lastID}`);
              resolve({ id: this.lastID, ...userData });
            }
          }
        );
      } catch (error) {
        Logger.error('Unexpected error in createUser', error);
        reject(error);
      }
    });
  }

  saveMessage(messageData) {
    return new Promise((resolve, reject) => {
      const { userId, content, timestamp, messageType = 'text', privateChatId = null } = messageData;
      this.db.run(
        'INSERT INTO messages (user_id, content, timestamp, message_type, private_chat_id) VALUES (?, ?, ?, ?, ?)',
        [userId, content, timestamp, messageType, privateChatId],
        function(err) {
          if (err) {
            reject(err);
          } else {
            resolve({
              id: this.lastID,
              user_id: userId,
              content,
              timestamp,
              message_type: messageType,
              private_chat_id: privateChatId
            });
          }
        }
      );
    });
  }

  saveFile(fileData) {
    return new Promise((resolve, reject) => {
      const { messageId, filename, originalName, filePath, fileSize, mimeType } = fileData;
      this.db.run(
        'INSERT INTO files (message_id, filename, original_name, file_path, file_size, mime_type) VALUES (?, ?, ?, ?, ?, ?)',
        [messageId, filename, originalName, filePath, fileSize, mimeType],
        function(err) {
          if (err) {
            reject(err);
          } else {
            resolve({
              id: this.lastID,
              message_id: messageId,
              filename,
              original_name: originalName,
              file_path: filePath,
              file_size: fileSize,
              mime_type: mimeType
            });
          }
        }
      );
    });
  }

  getRecentMessages(limit = 50) {
    return new Promise((resolve, reject) => {
      this.db.all(`
        SELECT m.*, u.name, u.avatar 
        FROM messages m 
        JOIN users u ON m.user_id = u.id 
        ORDER BY m.timestamp DESC 
        LIMIT ?
      `, [limit], async (err, rows) => {
        if (err) {
          reject(err);
        } else {
          // Get files for each message
          const messagesWithFiles = await Promise.all(
            rows.map(async (message) => {
              const files = await this.getMessageFiles(message.id);
              return { ...message, files };
            })
          );
          // Reverse to get chronological order
          resolve(messagesWithFiles.reverse());
        }
      });
    });
  }

  getMessageFiles(messageId) {
    return new Promise((resolve, reject) => {
      this.db.all(
        'SELECT * FROM files WHERE message_id = ?',
        [messageId],
        (err, rows) => {
          if (err) {
            reject(err);
          } else {
            resolve(rows || []);
          }
        }
      );
    });
  }

  // Private chat methods
  createOrGetPrivateChat(user1Id, user2Id) {
    return new Promise((resolve, reject) => {
      // Ensure consistent ordering (smaller ID first)
      const [smallerId, largerId] = user1Id < user2Id ? [user1Id, user2Id] : [user2Id, user1Id];
      
      // First try to get existing chat
      this.db.get(
        'SELECT * FROM private_chats WHERE user1_id = ? AND user2_id = ?',
        [smallerId, largerId],
        (err, row) => {
          if (err) {
            reject(err);
          } else if (row) {
            resolve(row);
          } else {
            // Create new private chat
            this.db.run(
              'INSERT INTO private_chats (user1_id, user2_id) VALUES (?, ?)',
              [smallerId, largerId],
              function(err) {
                if (err) {
                  reject(err);
                } else {
                  resolve({
                    id: this.lastID,
                    user1_id: smallerId,
                    user2_id: largerId
                  });
                }
              }
            );
          }
        }
      );
    });
  }

  getPrivateChatMessages(privateChatId, limit = 50) {
    return new Promise((resolve, reject) => {
      this.db.all(`
        SELECT m.*, u.name, u.avatar 
        FROM messages m 
        JOIN users u ON m.user_id = u.id 
        WHERE m.private_chat_id = ?
        ORDER BY m.timestamp DESC 
        LIMIT ?
      `, [privateChatId, limit], async (err, rows) => {
        if (err) {
          reject(err);
        } else {
          // Get files for each message
          const messagesWithFiles = await Promise.all(
            rows.map(async (message) => {
              const files = await this.getMessageFiles(message.id);
              return { ...message, files };
            })
          );
          // Reverse to get chronological order
          resolve(messagesWithFiles.reverse());
        }
      });
    });
  }

  getUserPrivateChats(userId) {
    return new Promise((resolve, reject) => {
      this.db.all(`
        SELECT pc.*, 
               CASE 
                 WHEN pc.user1_id = ? THEN u2.name 
                 ELSE u1.name 
               END as other_user_name,
               CASE 
                 WHEN pc.user1_id = ? THEN u2.avatar 
                 ELSE u1.avatar 
               END as other_user_avatar,
               CASE 
                 WHEN pc.user1_id = ? THEN pc.user2_id 
                 ELSE pc.user1_id 
               END as other_user_id,
               m.content as last_message,
               m.timestamp as last_message_time
        FROM private_chats pc
        JOIN users u1 ON pc.user1_id = u1.id
        JOIN users u2 ON pc.user2_id = u2.id
        LEFT JOIN messages m ON m.private_chat_id = pc.id
        WHERE pc.user1_id = ? OR pc.user2_id = ?
        GROUP BY pc.id
        ORDER BY m.timestamp DESC
      `, [userId, userId, userId, userId, userId], (err, rows) => {
        if (err) {
          reject(err);
        } else {
          resolve(rows || []);
        }
      });
    });
  }

  getAllUsers(excludeUserId = null) {
    return new Promise((resolve, reject) => {
      let query = 'SELECT id, name, email, avatar FROM users';
      let params = [];
      
      if (excludeUserId) {
        query += ' WHERE id != ?';
        params.push(excludeUserId);
      }
      
      query += ' ORDER BY name';
      
      this.db.all(query, params, (err, rows) => {
        if (err) {
          reject(err);
        } else {
          resolve(rows || []);
        }
      });
    });
  }
  
  markMessagesAsRead(privateChatId, userId) {
    return new Promise((resolve, reject) => {
      this.db.run(
        `UPDATE messages 
         SET read_at = CURRENT_TIMESTAMP 
         WHERE private_chat_id = ? AND user_id != ? AND read_at IS NULL`,
        [privateChatId, userId],
        function(err) {
          if (err) {
            reject(err);
          } else {
            resolve(this.changes);
          }
        }
      );
    });
  }
  
  updateMessageStatus(messageId, status) {
    return new Promise((resolve, reject) => {
      this.db.run(
        'UPDATE messages SET status = ? WHERE id = ?',
        [status, messageId],
        function(err) {
          if (err) {
            reject(err);
          } else {
            resolve(this.changes);
          }
        }
      );
    });
  }

  close() {
    return new Promise((resolve) => {
      this.db.close((err) => {
        if (err) {
          console.error('Error closing database:', err);
        } else {
          console.log('Database connection closed');
        }
        resolve();
      });
    });
  }
}

module.exports = Database;