<?php
namespace Models;

class User {
    private $db;
    
    public function __construct($db) {
        $this->db = $db;
    }
    
    public function findByEmail($email) {
        $result = $this->db->exec('SELECT u.*, r.name as role_name FROM users u LEFT JOIN roles r ON u.role_id = r.id WHERE u.email = $1', [$email]);
        return $result ? $result[0] : null;
    }
    
    public function findById($id) {
        $result = $this->db->exec('SELECT u.*, r.name as role_name FROM users u LEFT JOIN roles r ON u.role_id = r.id WHERE u.id = $1', [$id]);
        return $result ? $result[0] : null;
    }
    
    public function findByCompany($companyId) {
        return $this->db->exec('SELECT u.*, r.name as role_name FROM users u LEFT JOIN roles r ON u.role_id = r.id WHERE u.company_id = $1 ORDER BY u.name', [$companyId]);
    }
    
    public function findStaffByCompany($companyId) {
        return $this->db->exec(
            'SELECT u.*, r.name as role_name FROM users u 
             LEFT JOIN roles r ON u.role_id = r.id 
             WHERE u.company_id = $1 AND r.name = $2 
             ORDER BY u.name',
            [$companyId, 'staff']
        );
    }
    
    public function countStaffByCompany($companyId) {
        $result = $this->db->exec(
            'SELECT COUNT(*) as count FROM users u 
             INNER JOIN roles r ON u.role_id = r.id 
             WHERE u.company_id = $1 AND r.name = $2',
            [$companyId, 'staff']
        );
        return $result[0]['count'];
    }
    
    public function create($data) {
        $password = password_hash($data['password'], PASSWORD_DEFAULT);
        $roleId = $this->getRoleId($data['role'] ?? 'staff');
        
        $this->db->exec(
            'INSERT INTO users (name, email, password, phone, role_id, company_id, is_active) VALUES ($1, $2, $3, $4, $5, $6, $7)',
            [$data['name'], $data['email'], $password, $data['phone'] ?? null, $roleId, $data['company_id'] ?? null, true]
        );
        
        return $this->db->lastinsertid();
    }
    
    public function update($id, $data) {
        $sets = [];
        $params = [];
        $i = 1;
        
        foreach (['name', 'email', 'phone', 'is_active'] as $field) {
            if (isset($data[$field])) {
                $sets[] = "$field = \$$i";
                $params[] = $data[$field];
                $i++;
            }
        }
        
        if (isset($data['password']) && !empty($data['password'])) {
            $sets[] = "password = \$$i";
            $params[] = password_hash($data['password'], PASSWORD_DEFAULT);
            $i++;
        }
        
        $sets[] = "updated_at = CURRENT_TIMESTAMP";
        $params[] = $id;
        
        $this->db->exec('UPDATE users SET ' . implode(', ', $sets) . " WHERE id = \$$i", $params);
    }
    
    public function delete($id) {
        $this->db->exec('DELETE FROM users WHERE id = $1', [$id]);
    }
    
    public function verifyPassword($password, $hash) {
        return password_verify($password, $hash);
    }
    
    public function countAll() {
        $result = $this->db->exec('SELECT COUNT(*) as count FROM users');
        return $result[0]['count'];
    }
    
    public function countByCompany($companyId) {
        $result = $this->db->exec('SELECT COUNT(*) as count FROM users WHERE company_id = $1', [$companyId]);
        return $result[0]['count'];
    }
    
    private function getRoleId($roleName) {
        $result = $this->db->exec('SELECT id FROM roles WHERE name = $1', [$roleName]);
        return $result ? $result[0]['id'] : null;
    }
}
