---

-- 1. Создание базы данных
CREATE DATABASE corporate_security;
\c corporate_security;

-- 2. Создание 5 таблиц
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    role VARCHAR(50) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE access_control (
    id SERIAL PRIMARY KEY,
    role VARCHAR(50) NOT NULL,
    resource VARCHAR(100) NOT NULL,
    can_access BOOLEAN DEFAULT TRUE
);

CREATE TABLE audit_logs (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    action TEXT NOT NULL,
    action_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE login_attempts (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50),
    success BOOLEAN,
    attempt_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    ip_address INET
);

CREATE TABLE security_events (
    id SERIAL PRIMARY KEY,
    event TEXT NOT NULL,
    event_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    severity VARCHAR(20)
);

-- 3. Заполнение таблиц (по 10 записей)
INSERT INTO users (username, role) VALUES
('ivanov', 'employee'),
('petrova', 'hr'),
('sidorov', 'it'),
('admin1', 'admin'),
('kuznetsov', 'employee'),
('smirnova', 'finance'),
('popov', 'employee'),
('vorobiev', 'it'),
('makarov', 'finance'),
('fedotova', 'hr');

INSERT INTO access_control (role, resource, can_access) VALUES
('admin', 'all', TRUE),
('it', 'servers', TRUE),
('hr', 'personal_data', TRUE),
('finance', 'reports', TRUE),
('employee', 'docs', TRUE),
('employee', 'salary', FALSE),
('hr', 'salary', TRUE),
('it', 'logs', TRUE),
('finance', 'bank', TRUE),
('admin', 'audit', TRUE);

INSERT INTO audit_logs (user_id, action, action_time) VALUES
(1, 'login', CURRENT_TIMESTAMP),
(2, 'view_report', CURRENT_TIMESTAMP),
(3, 'change_password', CURRENT_TIMESTAMP),
(4, 'add_user', CURRENT_TIMESTAMP),
(5, 'logout', CURRENT_TIMESTAMP),
(1, 'failed_access', CURRENT_TIMESTAMP),
(6, 'download_file', CURRENT_TIMESTAMP),
(7, 'login', CURRENT_TIMESTAMP),
(8, 'restart_service', CURRENT_TIMESTAMP),
(9, 'view_salary', CURRENT_TIMESTAMP);

INSERT INTO login_attempts (username, success, ip_address) VALUES
('ivanov', TRUE, '192.168.1.10'),
('petrova', TRUE, '192.168.1.11'),
('sidorov', FALSE, '10.0.0.5'),
('unknown', FALSE, '203.0.113.45'),
('admin1', TRUE, '192.168.1.1'),
('kuznetsov', FALSE, '10.0.0.7'),
('smirnova', TRUE, '192.168.1.12'),
('popov', FALSE, '203.0.113.89'),
('vorobiev', TRUE, '192.168.1.13'),
('hacker', FALSE, '45.33.22.11');

INSERT INTO security_events (event, severity) VALUES
('multiple_failed_logins', 'HIGH'),
('privilege_change', 'MEDIUM'),
('login_from_new_ip', 'LOW'),
('data_export', 'MEDIUM'),
('user_locked', 'HIGH'),
('password_reset', 'LOW'),
('sensitive_file_access', 'HIGH'),
('role_changed', 'MEDIUM'),
('failed_audit', 'HIGH'),
('session_timeout', 'LOW');

-- 4. Реализация аудита (триггер)
CREATE OR REPLACE FUNCTION audit_user_changes()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO audit_logs(user_id, action, action_time)
    VALUES (OLD.id, 'UPDATE_USER', CURRENT_TIMESTAMP);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER user_audit_trigger
AFTER UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION audit_user_changes();

-- 5. Логирование попыток входа
CREATE OR REPLACE FUNCTION log_login_attempt()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO audit_logs(user_id, action, action_time)
    VALUES ((SELECT id FROM users WHERE username = NEW.username), 'LOGIN_ATTEMPT', CURRENT_TIMESTAMP);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER login_log_trigger
AFTER INSERT ON login_attempts
FOR EACH ROW EXECUTE FUNCTION log_login_attempt();

-- 6. Контроль доступа (функция проверки)
CREATE OR REPLACE FUNCTION check_access(p_username VARCHAR, p_resource VARCHAR)
RETURNS BOOLEAN AS $$
DECLARE
    user_role VARCHAR;
    has_access BOOLEAN;
BEGIN
    SELECT role INTO user_role FROM users WHERE username = p_username;
    SELECT can_access INTO has_access FROM access_control WHERE role = user_role AND resource = p_resource;
    RETURN COALESCE(has_access, FALSE);
END;
$$ LANGUAGE plpgsql;

-- 7. Анализ безопасности и выявление угроз
-- Неудачные попытки входа
SELECT username, COUNT(*) as failed_attempts
FROM login_attempts
WHERE success = FALSE
GROUP BY username
ORDER BY failed_attempts DESC;

-- Подозрительная активность (более 2 неудачных попыток)
SELECT username, COUNT(*) as attempts
FROM login_attempts
WHERE success = FALSE
GROUP BY username
HAVING COUNT(*) > 2;

-- События высокой серьёзности
SELECT * FROM security_events
WHERE severity = 'HIGH';

-- Аномалии: вход с необычных IP (единственный успешный вход)
SELECT username, ip_address, COUNT(*)
FROM login_attempts
WHERE success = TRUE
GROUP BY username, ip_address
HAVING COUNT(*) = 1;

-- 8. Мониторинг активных подключений
SELECT usename, state, query
FROM pg_stat_activity
WHERE state = 'active';

-- 9. Блокировка пользователя (при 3+ неудачных попытках)
CREATE TABLE locked_users (
    username VARCHAR(50) PRIMARY KEY,
    locked_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO locked_users (username)
SELECT username
FROM login_attempts
WHERE success = FALSE
GROUP BY username
HAVING COUNT(*) >= 3
ON CONFLICT (username) DO NOTHING;

-- 10. Отчёт безопасности (VIEW)
CREATE VIEW security_report AS
SELECT 
    la.username,
    COUNT(la.id) as total_logins,
    SUM(CASE WHEN la.success = FALSE THEN 1 ELSE 0 END) as failed_logins,
    COUNT(DISTINCT se.id) as high_security_events
FROM login_attempts la
LEFT JOIN security_events se ON se.severity = 'HIGH'
GROUP BY la.username;

-- 11. Автоматическое логирование событий безопасности
CREATE OR REPLACE FUNCTION auto_log_security_event()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.success = FALSE THEN
        INSERT INTO security_events(event, severity)
        VALUES ('failed_login_for_' || NEW.username, 'MEDIUM');
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER auto_security_log
AFTER INSERT ON login_attempts
FOR EACH ROW EXECUTE FUNCTION auto_log_security_event();

-- 12. Просмотр результатов
SELECT * FROM users;
SELECT * FROM access_control;
SELECT * FROM audit_logs;
SELECT * FROM login_attempts;
SELECT * FROM security_events;
SELECT * FROM security_report;
SELECT * FROM locked_users;