database.sql

-- AdminLogPanel Database Schema
-- PostgreSQL 12+

-- Удаление существующих таблиц и функций
DROP TABLE IF EXISTS data_logs CASCADE;
DROP TABLE IF EXISTS accounts CASCADE;
DROP FUNCTION IF EXISTS generate_account_log_id() CASCADE;
DROP SEQUENCE IF EXISTS account_log_id_seq CASCADE;

-- Таблица аккаунтов
CREATE TABLE accounts (
    id SERIAL PRIMARY KEY,
    login VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    is_admin BOOLEAN DEFAULT FALSE,
    is_blocked BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Таблица логов с партиционированием
CREATE TABLE data_logs (
    id SERIAL,
    account_id INTEGER NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
    account_log_id INTEGER NOT NULL,
    log_id VARCHAR(255),
    ip_address INET NOT NULL,
    country VARCHAR(10) NOT NULL,
    country_true BOOLEAN DEFAULT FALSE,
    info_text TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id, account_id)
) PARTITION BY RANGE (id);

-- Создание партиций (по 100 id на партицию)
-- Создаём 20 партиций (достаточно для тестирования, до 2000 записей)
CREATE TABLE data_logs_p0 PARTITION OF data_logs FOR VALUES FROM (1) TO (100);
CREATE TABLE data_logs_p1 PARTITION OF data_logs FOR VALUES FROM (100) TO (200);
CREATE TABLE data_logs_p2 PARTITION OF data_logs FOR VALUES FROM (200) TO (300);
CREATE TABLE data_logs_p3 PARTITION OF data_logs FOR VALUES FROM (300) TO (400);
CREATE TABLE data_logs_p4 PARTITION OF data_logs FOR VALUES FROM (400) TO (500);
CREATE TABLE data_logs_p5 PARTITION OF data_logs FOR VALUES FROM (500) TO (600);
CREATE TABLE data_logs_p6 PARTITION OF data_logs FOR VALUES FROM (600) TO (700);
CREATE TABLE data_logs_p7 PARTITION OF data_logs FOR VALUES FROM (700) TO (800);
CREATE TABLE data_logs_p8 PARTITION OF data_logs FOR VALUES FROM (800) TO (900);
CREATE TABLE data_logs_p9 PARTITION OF data_logs FOR VALUES FROM (900) TO (1000);
CREATE TABLE data_logs_p10 PARTITION OF data_logs FOR VALUES FROM (1000) TO (1100);
CREATE TABLE data_logs_p11 PARTITION OF data_logs FOR VALUES FROM (1100) TO (1200);
CREATE TABLE data_logs_p12 PARTITION OF data_logs FOR VALUES FROM (1200) TO (1300);
CREATE TABLE data_logs_p13 PARTITION OF data_logs FOR VALUES FROM (1300) TO (1400);
CREATE TABLE data_logs_p14 PARTITION OF data_logs FOR VALUES FROM (1400) TO (1500);
CREATE TABLE data_logs_p15 PARTITION OF data_logs FOR VALUES FROM (1500) TO (1600);
CREATE TABLE data_logs_p16 PARTITION OF data_logs FOR VALUES FROM (1600) TO (1700);
CREATE TABLE data_logs_p17 PARTITION OF data_logs FOR VALUES FROM (1700) TO (1800);
CREATE TABLE data_logs_p18 PARTITION OF data_logs FOR VALUES FROM (1800) TO (1900);
CREATE TABLE data_logs_p19 PARTITION OF data_logs FOR VALUES FROM (1900) TO (2000);

-- Индексы для оптимизации поиска
CREATE INDEX idx_data_logs_account_id ON data_logs(account_id);
CREATE INDEX idx_data_logs_created_at ON data_logs(created_at);
CREATE INDEX idx_data_logs_country ON data_logs(country);
CREATE INDEX idx_data_logs_info_text ON data_logs USING gin(to_tsvector('english', info_text));

-- Функция для автоматической генерации account_log_id
CREATE OR REPLACE FUNCTION generate_account_log_id()
RETURNS TRIGGER AS $$
BEGIN
    -- Генерация уникального account_log_id в рамках account_id
    SELECT COALESCE(MAX(account_log_id), 0) + 1
    INTO NEW.account_log_id
    FROM data_logs
    WHERE account_id = NEW.account_id;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Триггер для автоматической генерации account_log_id
CREATE TRIGGER trigger_generate_account_log_id
BEFORE INSERT ON data_logs
FOR EACH ROW
EXECUTE FUNCTION generate_account_log_id();

-- Вставка тестовых аккаунтов
-- Пароль для обоих: "password" (хэш через password_hash())
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);

-- Примечание:
-- Для создания хэша пароля используйте PHP: password_hash('password', PASSWORD_DEFAULT)
-- Для проверки: password_verify('password', $hash)