-- V11: 시스템관리 (로그인로그/API로그/변경이력/파일/알림/시스템설정)

CREATE TABLE login_log (
    log_id        SERIAL PRIMARY KEY,
    user_id       BIGINT,
    login_id      VARCHAR(50),
    login_type    VARCHAR(20),                       -- SUCCESS / FAIL / LOGOUT
    fail_reason   VARCHAR(200),
    ip_address    VARCHAR(50),
    user_agent    VARCHAR(500),
    login_at      TIMESTAMP NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_ll_user ON login_log(user_id, login_at);

CREATE TABLE api_access_log (
    log_id          SERIAL PRIMARY KEY,
    user_id         BIGINT,
    method          VARCHAR(10),
    url             VARCHAR(500),
    request_param   TEXT,
    response_code   VARCHAR(10),
    response_time   INT,
    ip_address      VARCHAR(50),
    error_message   TEXT,
    created_at      TIMESTAMP NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_aal_user ON api_access_log(user_id, created_at);

CREATE TABLE data_change_log (
    log_id        SERIAL PRIMARY KEY,
    user_id       BIGINT,
    menu_code     VARCHAR(50),
    table_name    VARCHAR(50),
    record_id     VARCHAR(100),
    action_type   VARCHAR(20),                       -- INSERT / UPDATE / DELETE
    before_data   JSONB,
    after_data    JSONB,
    changed_keys  VARCHAR(1000),
    ip_address    VARCHAR(50),
    created_at    TIMESTAMP NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_dcl_table ON data_change_log(table_name, record_id, created_at);

CREATE TABLE file_storage (
    file_id        SERIAL PRIMARY KEY,
    file_group     VARCHAR(50),
    original_name  VARCHAR(300) NOT NULL,
    stored_name    VARCHAR(300) NOT NULL,
    file_path      VARCHAR(500) NOT NULL,
    file_size      BIGINT,
    content_type   VARCHAR(100),
    extension      VARCHAR(20),
    download_count INT NOT NULL DEFAULT 0,
    is_deleted     CHAR(1) NOT NULL DEFAULT 'N',
    created_at     TIMESTAMP NOT NULL DEFAULT NOW(),
    created_by     BIGINT
);
CREATE INDEX idx_fs_group ON file_storage(file_group, is_deleted);

CREATE TABLE notification (
    noti_id       SERIAL PRIMARY KEY,
    user_id       BIGINT       NOT NULL,
    noti_type     VARCHAR(30),
    title         VARCHAR(200) NOT NULL,
    content       VARCHAR(1000),
    link_url      VARCHAR(300),
    is_read       CHAR(1)      NOT NULL DEFAULT 'N',
    read_at       TIMESTAMP,
    created_at    TIMESTAMP    NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_noti_user ON notification(user_id, is_read, created_at);

CREATE TABLE system_config (
    config_key      VARCHAR(100) PRIMARY KEY,
    config_value    VARCHAR(2000),
    config_group    VARCHAR(50),
    value_type      VARCHAR(20),                    -- STRING / NUMBER / BOOLEAN / JSON
    is_encrypted    CHAR(1) NOT NULL DEFAULT 'N',
    is_editable     CHAR(1) NOT NULL DEFAULT 'Y',
    description     VARCHAR(300),
    updated_at      TIMESTAMP,
    updated_by      BIGINT
);
