examples.sql

-- AdminLogPanel - SQL Examples
-- Полезные SQL запросы для работы с базой данных

-- ============================================
-- Управление аккаунтами
-- ============================================

-- Просмотр всех аккаунтов
SELECT id, login, is_admin, is_blocked, created_at FROM accounts;

-- Создание нового администратора
-- Сначала сгенерируйте хэш: php generate_password.php mypassword
INSERT INTO accounts (login, password_hash, is_admin, is_blocked)
VALUES ('newadmin', 'YOUR_HASH_HERE', TRUE, FALSE);

-- Разблокировка аккаунта
UPDATE accounts SET is_blocked = FALSE WHERE login = 'user';

-- Смена пароля
UPDATE accounts SET password_hash = 'NEW_HASH_HERE' WHERE login = 'admin';

-- Превращение пользователя в администратора
UPDATE accounts SET is_admin = TRUE WHERE login = 'user';

-- Удаление аккаунта (удалит все связанные логи из-за CASCADE)
DELETE FROM accounts WHERE id = 3;

-- ============================================
-- Работа с логами
-- ============================================

-- Просмотр всех логов с информацией об аккаунте
SELECT dl.*, a.login as account_login
FROM data_logs dl
JOIN accounts a ON dl.account_id = a.id
ORDER BY dl.created_at DESC
LIMIT 20;

-- Логи конкретного пользователя
SELECT * FROM data_logs
WHERE account_id = 2
ORDER BY created_at DESC;

-- Поиск по тексту
SELECT * FROM data_logs
WHERE info_text ILIKE '%authentication%'
ORDER BY created_at DESC;

-- Логи из конкретной страны
SELECT * FROM data_logs
WHERE country = 'RU'
ORDER BY created_at DESC;

-- Логи с невалидной страной
SELECT * FROM data_logs
WHERE country_true = FALSE
ORDER BY created_at DESC;

-- Логи за последний день
SELECT * FROM data_logs
WHERE created_at >= NOW() - INTERVAL '1 day'
ORDER BY created_at DESC;

-- Удаление всех логов пользователя
DELETE FROM data_logs WHERE account_id = 2;

-- Удаление старых логов (старше 30 дней)
DELETE FROM data_logs WHERE created_at < NOW() - INTERVAL '30 days';

-- ============================================
-- Статистика
-- ============================================

-- Количество логов по странам
SELECT country, COUNT(*) as count
FROM data_logs
GROUP BY country
ORDER BY count DESC;

-- Количество логов по пользователям
SELECT a.login, COUNT(dl.id) as count
FROM accounts a
LEFT JOIN data_logs dl ON a.id = dl.account_id
GROUP BY a.id, a.login
ORDER BY count DESC;

-- Количество логов по датам (последние 7 дней)
SELECT DATE(created_at) as date, COUNT(*) as count
FROM data_logs
WHERE created_at >= NOW() - INTERVAL '7 days'
GROUP BY DATE(created_at)
ORDER BY date DESC;

-- Процент валидных стран
SELECT
    COUNT(*) as total,
    SUM(CASE WHEN country_true THEN 1 ELSE 0 END) as valid,
    ROUND(100.0 * SUM(CASE WHEN country_true THEN 1 ELSE 0 END) / COUNT(*), 2) as valid_percent
FROM data_logs;

-- Топ IP адресов
SELECT ip_address, COUNT(*) as count
FROM data_logs
GROUP BY ip_address
ORDER BY count DESC
LIMIT 10;

-- Статистика по часам (когда больше всего логов)
SELECT EXTRACT(HOUR FROM created_at) as hour, COUNT(*) as count
FROM data_logs
GROUP BY hour
ORDER BY hour;

-- ============================================
-- Партиции
-- ============================================

-- Просмотр информации о партициях
SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size
FROM pg_tables
WHERE tablename LIKE 'data_logs%'
ORDER BY tablename;

-- Количество записей в каждой партиции
SELECT
    tableoid::regclass as partition_name,
    COUNT(*) as row_count
FROM data_logs
GROUP BY tableoid
ORDER BY partition_name;

-- Создание новой партиции (если нужно больше 2000 записей)
CREATE TABLE data_logs_p20 PARTITION OF data_logs
FOR VALUES FROM (2000) TO (2100);

-- ============================================
-- Обслуживание
-- ============================================

-- Размер таблиц
SELECT
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

-- Количество записей в таблицах
SELECT
    'accounts' as table_name,
    COUNT(*) as row_count
FROM accounts
UNION ALL
SELECT
    'data_logs' as table_name,
    COUNT(*) as row_count
FROM data_logs;

-- Полная очистка логов (сброс счетчиков)
TRUNCATE data_logs CASCADE;

-- Сброс всей базы (ОСТОРОЖНО! Удалит все данные)
-- TRUNCATE accounts CASCADE;

-- Пересоздание стандартных аккаунтов
-- TRUNCATE accounts CASCADE;
-- INSERT INTO accounts (login, password_hash, is_admin, is_blocked) VALUES
-- ('admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', TRUE, FALSE),
-- ('user', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', FALSE, FALSE);