pdo = new PDO(
"mysql:host=" . DB_HOST . ";dbname=" . DB_NAME . ";charset=utf8mb4",
DB_USER,
DB_PASS,
[
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false
]
);
} catch (PDOException $e) {
throw new Exception("Database connection failed: " . $e->getMessage());
}
}
// User operations
public function getUser($userId) {
$stmt = $this->pdo->prepare("
SELECT user_id as userId, phone, telegram_id as telegramId,
first_name as firstName, last_name as lastName, position
FROM users
WHERE user_id = ?
");
$stmt->execute([$userId]);
return $stmt->fetch();
}
public function createUser($userData) {
$stmt = $this->pdo->prepare("
INSERT INTO users (user_id, phone, telegram_id, first_name, last_name, position)
VALUES (?, ?, ?, ?, ?, ?)
");
$stmt->execute([
$userData['userId'],
$userData['phone'],
$userData['telegramId'],
$userData['firstName'],
$userData['lastName'],
$userData['position']
]);
}
// Verification codes
public function storeVerificationCode($phone, $code) {
// Delete any existing codes for this phone
$this->pdo->prepare("DELETE FROM verification_codes WHERE phone = ?")->execute([$phone]);
// Insert new code with 5 minute expiry
$stmt = $this->pdo->prepare("
INSERT INTO verification_codes (phone, code, expires_at)
VALUES (?, ?, DATE_ADD(NOW(), INTERVAL 5 MINUTE))
");
$stmt->execute([$phone, $code]);
}
public function verifyCode($phone, $code) {
$stmt = $this->pdo->prepare("
SELECT 1 FROM verification_codes
WHERE phone = ? AND code = ? AND expires_at > NOW()
");
$stmt->execute([$phone, $code]);
return $stmt->rowCount() > 0;
}
public function clearVerificationCode($phone) {
$stmt = $this->pdo->prepare("DELETE FROM verification_codes WHERE phone = ?");
$stmt->execute([$phone]);
}
// Plans
public function getUserPlanWeeks($userId) {
$stmt = $this->pdo->prepare("
SELECT week_key FROM plans
WHERE user_id = ?
ORDER BY week_key DESC
");
$stmt->execute([$userId]);
return array_column($stmt->fetchAll(), 'week_key');
}
public function getPlan($userId, $weekKey) {
$stmt = $this->pdo->prepare("
SELECT tasks, created_at as date
FROM plans
WHERE user_id = ? AND week_key = ?
");
$stmt->execute([$userId, $weekKey]);
$result = $stmt->fetch();
if ($result) {
return [
'tasks' => json_decode($result['tasks'], true),
'date' => $result['date']
];
}
return null;
}
public function savePlan($userId, $weekKey, $tasks) {
$stmt = $this->pdo->prepare("
INSERT INTO plans (user_id, week_key, tasks)
VALUES (?, ?, ?)
ON DUPLICATE KEY UPDATE tasks = VALUES(tasks), created_at = NOW()
");
$stmt->execute([$userId, $weekKey, json_encode($tasks)]);
}
// Reports
public function getReport($userId, $weekKey) {
$stmt = $this->pdo->prepare("
SELECT results, summary, created_at as date
FROM reports
WHERE user_id = ? AND week_key = ?
");
$stmt->execute([$userId, $weekKey]);
$result = $stmt->fetch();
if ($result) {
return [
'results' => json_decode($result['results'], true),
'summary' => json_decode($result['summary'], true),
'date' => $result['date']
];
}
return null;
}
public function saveReport($userId, $weekKey, $results, $summary) {
$stmt = $this->pdo->prepare("
INSERT INTO reports (user_id, week_key, results, summary)
VALUES (?, ?, ?, ?)
ON DUPLICATE KEY UPDATE
results = VALUES(results),
summary = VALUES(summary),
created_at = NOW()
");
$stmt->execute([
$userId,
$weekKey,
json_encode($results),
json_encode($summary)
]);
}
// Clean up expired codes (run periodically)
public function cleanupExpiredCodes() {
$this->pdo->exec("DELETE FROM verification_codes WHERE expires_at < NOW()");
}
}
?>