USE trainer_attendance;

CREATE TABLE IF NOT EXISTS employees (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_number VARCHAR(50) NOT NULL UNIQUE,
  full_name VARCHAR(255) NOT NULL,
  job_title VARCHAR(191) NULL,
  formation VARCHAR(255) NULL,
  department VARCHAR(255) NULL,
  education VARCHAR(191) NULL,
  position_title VARCHAR(255) NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_emp_name (full_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS permissions (
  perm_key VARCHAR(80) PRIMARY KEY,
  label VARCHAR(191) NOT NULL,
  group_name VARCHAR(80) NOT NULL DEFAULT 'عام'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS role_permissions (
  role ENUM('admin','trainer','trainee') NOT NULL,
  perm_key VARCHAR(80) NOT NULL,
  PRIMARY KEY (role, perm_key),
  CONSTRAINT fk_rp_perm FOREIGN KEY (perm_key) REFERENCES permissions(perm_key) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ربط المستخدم بالموظف
SET @col_exists := (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = 'trainer_attendance' AND TABLE_NAME = 'users' AND COLUMN_NAME = 'company_number'
);
SET @sql := IF(@col_exists = 0,
  'ALTER TABLE users ADD COLUMN company_number VARCHAR(50) NULL UNIQUE AFTER email',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;