const mysql = require('mysql2/promise');
require('dotenv').config();

const dbConfig = {
  host: process.env.DB_HOST || 'localhost',
  port: process.env.DB_PORT || 3306,
  user: process.env.DB_USER || 'root',
  password: process.env.DB_PASSWORD || '',
  multipleStatements: true
};

const createDatabase = async () => {
  const connection = await mysql.createConnection(dbConfig);
  
  try {
    // Create database if it doesn't exist
    await connection.query(`CREATE DATABASE IF NOT EXISTS ${process.env.DB_NAME || 'project_management'}`);
    console.log('✅ Database created/verified');
    
    // Use the database
    await connection.query(`USE ${process.env.DB_NAME || 'project_management'}`);
    
    // Create tables
    const createTablesSQL = `
      -- Companies table for hierarchical organization
      CREATE TABLE IF NOT EXISTS companies (
        id INT PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(255) UNIQUE NOT NULL,
        description TEXT,
        admin_phone VARCHAR(20) NOT NULL,
        status ENUM('active', 'inactive') DEFAULT 'active',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
      );

      -- Users table with simplified role model
      CREATE TABLE IF NOT EXISTS users (
        id INT PRIMARY KEY AUTO_INCREMENT,
        username VARCHAR(255) UNIQUE NOT NULL,
        phone_number VARCHAR(20) UNIQUE NOT NULL,
        first_name VARCHAR(100),
        last_name VARCHAR(100),
        role ENUM('main_admin', 'admin', 'user') DEFAULT 'user',
        permission_level INT DEFAULT 1,
        company_id INT,
        status ENUM('active', 'inactive', 'suspended') DEFAULT 'active',
        profile_image VARCHAR(500),
        created_by INT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE SET NULL,
        FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
      );

      -- Company users relationship table
      CREATE TABLE IF NOT EXISTS company_users (
        id INT PRIMARY KEY AUTO_INCREMENT,
        company_id INT NOT NULL,
        user_id INT NOT NULL,
        role ENUM('admin', 'user') DEFAULT 'user',
        permissions TEXT, -- JSON string for flexible permissions
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        UNIQUE KEY unique_company_user (company_id, user_id)
      );

      -- OTP table for authentication
      CREATE TABLE IF NOT EXISTS otps (
        id INT PRIMARY KEY AUTO_INCREMENT,
        phone_number VARCHAR(20) NOT NULL,
        otp_code VARCHAR(10) NOT NULL,
        expires_at TIMESTAMP NOT NULL,
        is_used BOOLEAN DEFAULT FALSE,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_phone_number (phone_number),
        INDEX idx_expires_at (expires_at)
      );

      -- Refresh tokens table
      CREATE TABLE IF NOT EXISTS refresh_tokens (
        id INT PRIMARY KEY AUTO_INCREMENT,
        user_id INT NOT NULL,
        token VARCHAR(500) NOT NULL,
        expires_at TIMESTAMP NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        INDEX idx_user_id (user_id),
        INDEX idx_token (token(255))
      );

      -- Projects table
      CREATE TABLE IF NOT EXISTS projects (
        id INT PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(255) UNIQUE NOT NULL,
        description TEXT,
        company_id INT,
        status ENUM('planning', 'active', 'on_hold', 'completed', 'cancelled') DEFAULT 'planning',
        priority ENUM('low', 'medium', 'high', 'critical') DEFAULT 'medium',
        start_date DATE,
        end_date DATE,
        budget DECIMAL(15,2),
        owner_id INT NOT NULL,
        created_by INT NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE SET NULL,
        FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE CASCADE,
        FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE CASCADE
      );

      -- Project members table
      CREATE TABLE IF NOT EXISTS project_members (
        id INT PRIMARY KEY AUTO_INCREMENT,
        project_id INT NOT NULL,
        user_id INT NOT NULL,
        role ENUM('main_admin', 'admin', 'user') DEFAULT 'user',
        permissions TEXT, -- JSON string for project-specific permissions
        joined_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        assigned_by INT,
        FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        FOREIGN KEY (assigned_by) REFERENCES users(id) ON DELETE SET NULL,
        UNIQUE KEY unique_project_user (project_id, user_id)
      );

      -- Project permissions table for granular access control
      CREATE TABLE IF NOT EXISTS project_permissions (
        id INT PRIMARY KEY AUTO_INCREMENT,
        user_id INT NOT NULL,
        project_id INT NOT NULL,
        can_view BOOLEAN DEFAULT TRUE,
        can_create_tasks BOOLEAN DEFAULT FALSE,
        can_edit_tasks BOOLEAN DEFAULT FALSE,
        can_delete_tasks BOOLEAN DEFAULT FALSE,
        can_assign_tasks BOOLEAN DEFAULT FALSE,
        can_manage_members BOOLEAN DEFAULT FALSE,
        created_by INT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
        FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
        UNIQUE KEY unique_user_project (user_id, project_id)
      );

      -- Tasks table with comprehensive fields
      CREATE TABLE IF NOT EXISTS tasks (
        id INT PRIMARY KEY AUTO_INCREMENT,
        project_id INT NOT NULL,
        sprint_id INT,
        title VARCHAR(255) NOT NULL,
        description TEXT,
        status ENUM('todo', 'in_progress', 'review', 'testing', 'done') DEFAULT 'todo',
        priority ENUM('low', 'medium', 'high', 'critical') DEFAULT 'medium',
        type ENUM('feature', 'bug', 'improvement', 'documentation') DEFAULT 'feature',
        story_points INT,
        estimated_hours DECIMAL(5,2),
        actual_hours DECIMAL(5,2),
        assigned_to INT,
        created_by INT NOT NULL,
        due_date DATE,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        completed_at TIMESTAMP NULL,
        FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
        FOREIGN KEY (sprint_id) REFERENCES sprints(id) ON DELETE SET NULL,
        FOREIGN KEY (assigned_to) REFERENCES users(id) ON DELETE SET NULL,
        FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE CASCADE
      );

      -- Task assignments table for multiple assignees
      CREATE TABLE IF NOT EXISTS task_assignments (
        id INT PRIMARY KEY AUTO_INCREMENT,
        task_id INT NOT NULL,
        user_id INT NOT NULL,
        assigned_by INT NOT NULL,
        assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        FOREIGN KEY (assigned_by) REFERENCES users(id) ON DELETE CASCADE,
        UNIQUE KEY unique_task_user (task_id, user_id)
      );

      -- User permissions table for system-wide permissions
      CREATE TABLE IF NOT EXISTS user_permissions (
        id INT PRIMARY KEY AUTO_INCREMENT,
        user_id INT NOT NULL,
        permission_name VARCHAR(255) NOT NULL,
        permission_value BOOLEAN DEFAULT FALSE,
        granted_by INT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        FOREIGN KEY (granted_by) REFERENCES users(id) ON DELETE SET NULL,
        UNIQUE KEY unique_user_permission (user_id, permission_name)
      );

      -- Activity logs table
      CREATE TABLE IF NOT EXISTS activity_logs (
        id INT PRIMARY KEY AUTO_INCREMENT,
        user_id INT NOT NULL,
        action VARCHAR(255) NOT NULL,
        entity_type VARCHAR(50) NOT NULL,
        entity_id INT NOT NULL,
        details TEXT,
        ip_address VARCHAR(45),
        user_agent TEXT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        INDEX idx_activity_logs_user (user_id),
        INDEX idx_activity_logs_entity (entity_type, entity_id),
        INDEX idx_activity_logs_created_at (created_at)
      );

      -- Sprints table
      CREATE TABLE IF NOT EXISTS sprints (
        id INT PRIMARY KEY AUTO_INCREMENT,
        project_id INT NOT NULL,
        name VARCHAR(255) NOT NULL,
        description TEXT,
        start_date DATE NOT NULL,
        end_date DATE NOT NULL,
        status ENUM('planning', 'active', 'completed', 'cancelled') DEFAULT 'planning',
        goal TEXT,
        created_by INT NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
        FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE CASCADE
      );
    `;

    await connection.query(createTablesSQL);
    console.log('✅ All tables created successfully');

    // Insert default companies
    await connection.query("INSERT IGNORE INTO companies (id, name, description, admin_phone) VALUES (1, 'Main Company', 'Main administrative company', '1234567890')");
    await connection.query("INSERT IGNORE INTO companies (id, name, description, admin_phone) VALUES (2, 'CompanyA', 'Company A for testing', '1234512345')");
    console.log('✅ Default companies inserted');

    // Insert default users
    await connection.query("INSERT IGNORE INTO users (id, username, phone_number, first_name, last_name, role, permission_level, company_id, status) VALUES (1, 'main_admin', '1234567890', 'Main', 'Admin', 'main_admin', 3, 1, 'active')");
    await connection.query("INSERT IGNORE INTO users (id, username, phone_number, first_name, last_name, role, permission_level, company_id, status, created_by) VALUES (2, 'primary_admin', '1234512345', 'Primary', 'Admin', 'admin', 2, 1, 'active', 1)");
    await connection.query("INSERT IGNORE INTO users (id, username, phone_number, first_name, last_name, role, permission_level, company_id, status, created_by) VALUES (3, 'standard_user', '1234567899', 'Sample', 'User', 'user', 1, 1, 'active', 2)");
    console.log('✅ Default users inserted');

    // Link users to companies
    await connection.query("INSERT IGNORE INTO company_users (company_id, user_id, role) VALUES (1, 1, 'admin')");
    await connection.query("INSERT IGNORE INTO company_users (company_id, user_id, role) VALUES (1, 2, 'admin')");
    await connection.query("INSERT IGNORE INTO company_users (company_id, user_id, role) VALUES (1, 3, 'user')");
    console.log('✅ User-company relationships created');

  } catch (error) {
    console.error('❌ Migration failed:', error);
    throw error;
  } finally {
    await connection.end();
  }
};

// Run migration
if (require.main === module) {
  createDatabase()
    .then(() => {
      console.log('🎉 Database migration completed successfully');
      process.exit(0);
    })
    .catch((error) => {
      console.error('💥 Migration failed:', error);
      process.exit(1);
    });
}

module.exports = { createDatabase };