db_schema.sql

CREATE TABLE IF NOT EXISTS users (
    user_id    SERIAL PRIMARY KEY,
    name       VARCHAR(100) NOT NULL UNIQUE,
    password   CHAR(64) NOT NULL,           -- SHA-256 хэш пароля
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS administrators (
    admin_id  SERIAL PRIMARY KEY,
    name      VARCHAR(100) NOT NULL UNIQUE,
    password  CHAR(64) NOT NULL
);

CREATE TABLE IF NOT EXISTS security_system (
    record_id       SERIAL PRIMARY KEY,
    user_id         INTEGER REFERENCES users(user_id) ON DELETE SET NULL,
    ssid            VARCHAR(200),
    scan_time       TIMESTAMP NOT NULL DEFAULT NOW(),
    signal_strength INTEGER,
    encryption_type VARCHAR(100),
    auth_type       VARCHAR(100),
    security_status VARCHAR(50)
);

CREATE TABLE IF NOT EXISTS logs (
    log_id       SERIAL PRIMARY KEY,
    user_id      INTEGER REFERENCES users(user_id) ON DELETE SET NULL,
    admin_id     INTEGER REFERENCES administrators(admin_id) ON DELETE SET NULL,
    ssid         VARCHAR(200),
    scan_time    TIMESTAMP,
    description  TEXT,
    created_at   TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS bt_scan_results (
    id              SERIAL PRIMARY KEY,
    user_id         INTEGER REFERENCES users(user_id) ON DELETE SET NULL,
    device_name     VARCHAR(200),
    mac_address     VARCHAR(20),
    device_type     VARCHAR(50),
    rssi            INTEGER,
    signal_strength INTEGER,
    has_name        BOOLEAN DEFAULT FALSE,
    services_count  INTEGER DEFAULT 0,
    security_status VARCHAR(50),
    scan_time       TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS bt_logs (
    log_id       SERIAL PRIMARY KEY,
    user_id      INTEGER REFERENCES users(user_id) ON DELETE SET NULL,
    admin_id     INTEGER REFERENCES administrators(admin_id) ON DELETE SET NULL,
    device_name  VARCHAR(200),
    mac_address  VARCHAR(20),
    scan_time    TIMESTAMP DEFAULT NOW(),
    description  TEXT,
    created_at   TIMESTAMP DEFAULT NOW()
);

--тестовые данные
--пароль admin123

INSERT INTO administrators (name, password)
VALUES ('admin', '240be518fabd2724ddb6f04eeb1da5967448d7e831c08c8fa822809f74c720a9')
ON CONFLICT (name) DO NOTHING;