import sqlite3
import sys
from contextlib import contextmanager
from datetime import datetime, timedelta

import config

if config.DB_TYPE == "mysql":
    _vendor = str(config.BASE_DIR / "vendor")
    if _vendor not in sys.path:
        sys.path.insert(0, _vendor)
    import pymysql
    from pymysql.cursors import DictCursor
elif config.DB_TYPE == "postgres":
    import psycopg2
    from psycopg2.extras import RealDictCursor

# mysql / postgres 都要显式建游标、占位符是 %s；sqlite 用连接自带的 execute 和 ?
_SERVER_BACKENDS = ("mysql", "postgres")

SCHEMA_SQLITE = """
CREATE TABLE IF NOT EXISTS category (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    name        TEXT NOT NULL UNIQUE,
    sort_order  INTEGER NOT NULL DEFAULT 0,
    visible     INTEGER NOT NULL DEFAULT 1,
    icon        TEXT NOT NULL DEFAULT 'box',
    color       TEXT NOT NULL DEFAULT '#4f6ef7',
    created_at  TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS software (
    id             INTEGER PRIMARY KEY AUTOINCREMENT,
    name           TEXT NOT NULL,
    version        TEXT NOT NULL DEFAULT '',
    category_id    INTEGER REFERENCES category(id) ON DELETE SET NULL,
    description    TEXT NOT NULL DEFAULT '',
    remark         TEXT NOT NULL DEFAULT '',
    pin_weight     INTEGER NOT NULL DEFAULT 0,
    view_count     INTEGER NOT NULL DEFAULT 0,
    recommended    INTEGER NOT NULL DEFAULT 0,
    download_count INTEGER NOT NULL DEFAULT 0,
    status         INTEGER NOT NULL DEFAULT 1,
    submitter_id   INTEGER,
    submitter_name TEXT NOT NULL DEFAULT '',
    developer      TEXT NOT NULL DEFAULT '',
    license_type   TEXT NOT NULL DEFAULT '',
    reviewed_at    TEXT NOT NULL DEFAULT '',
    review_note    TEXT NOT NULL DEFAULT '',
    created_at     TEXT NOT NULL,
    updated_at     TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS software_file (
    id             INTEGER PRIMARY KEY AUTOINCREMENT,
    software_id    INTEGER NOT NULL REFERENCES software(id) ON DELETE CASCADE,
    label          TEXT NOT NULL DEFAULT '安装包',
    original_name  TEXT NOT NULL,
    file_path      TEXT NOT NULL,
    file_size      INTEGER NOT NULL DEFAULT 0,
    source_type    TEXT NOT NULL DEFAULT 'upload',
    download_count INTEGER NOT NULL DEFAULT 0,
    sort_order     INTEGER NOT NULL DEFAULT 0,
    created_at     TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS tag (
    id   INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS software_tag (
    software_id INTEGER NOT NULL REFERENCES software(id) ON DELETE CASCADE,
    tag_id      INTEGER NOT NULL REFERENCES tag(id) ON DELETE CASCADE,
    PRIMARY KEY (software_id, tag_id)
);

CREATE TABLE IF NOT EXISTS admin (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    username        TEXT NOT NULL UNIQUE,
    password_hash   TEXT NOT NULL,
    role            TEXT NOT NULL DEFAULT 'admin',
    disabled        INTEGER NOT NULL DEFAULT 0,
    upload_token    TEXT NOT NULL DEFAULT '',
    id_card_hash    TEXT,
    id_card_mask    TEXT NOT NULL DEFAULT '',
    unit_dept       TEXT NOT NULL DEFAULT '',
    approval_status TEXT NOT NULL DEFAULT 'approved',
    approval_note   TEXT NOT NULL DEFAULT '',
    reviewed_at     TEXT NOT NULL DEFAULT '',
    created_at      TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS register_attempt (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    ip         TEXT NOT NULL,
    created_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS session (
    id          TEXT PRIMARY KEY,
    admin_id    INTEGER NOT NULL,
    created_at  TEXT NOT NULL,
    expires_at  TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS download_log (
    id            INTEGER PRIMARY KEY AUTOINCREMENT,
    software_id   INTEGER NOT NULL,
    software_name TEXT NOT NULL,
    file_id       INTEGER NOT NULL DEFAULT 0,
    file_label    TEXT NOT NULL DEFAULT '',
    client_ip     TEXT NOT NULL,
    user_agent    TEXT NOT NULL DEFAULT '',
    downloaded_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS comment (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    software_id INTEGER NOT NULL REFERENCES software(id) ON DELETE CASCADE,
    parent_id   INTEGER REFERENCES comment(id) ON DELETE CASCADE,
    nickname    TEXT NOT NULL DEFAULT '匿名网友',
    content     TEXT NOT NULL,
    client_ip   TEXT NOT NULL,
    is_admin    INTEGER NOT NULL DEFAULT 0,
    status      TEXT NOT NULL DEFAULT 'pending',
    created_at  TEXT NOT NULL,
    approved_at TEXT NOT NULL DEFAULT ''
);

CREATE TABLE IF NOT EXISTS rating_dimension (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    name        TEXT NOT NULL UNIQUE,
    sort_order  INTEGER NOT NULL DEFAULT 0,
    visible     INTEGER NOT NULL DEFAULT 1,
    created_at  TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS software_rating (
    id           INTEGER PRIMARY KEY AUTOINCREMENT,
    software_id  INTEGER NOT NULL REFERENCES software(id) ON DELETE CASCADE,
    dimension_id INTEGER NOT NULL REFERENCES rating_dimension(id) ON DELETE CASCADE,
    rater_key    TEXT NOT NULL,
    score        INTEGER NOT NULL DEFAULT 0,
    created_at   TEXT NOT NULL,
    UNIQUE (software_id, dimension_id, rater_key)
);

CREATE TABLE IF NOT EXISTS teach_video_category (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    name       TEXT NOT NULL,
    sort_order INTEGER NOT NULL DEFAULT 0,
    visible    INTEGER NOT NULL DEFAULT 1,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS teach_video (
    id            INTEGER PRIMARY KEY AUTOINCREMENT,
    title         TEXT NOT NULL,
    description   TEXT NOT NULL DEFAULT '',
    original_name TEXT NOT NULL DEFAULT '',
    file_path     TEXT NOT NULL,
    file_size     INTEGER NOT NULL DEFAULT 0,
    sort_order    INTEGER NOT NULL DEFAULT 0,
    visible       INTEGER NOT NULL DEFAULT 1,
    category_id   INTEGER NOT NULL DEFAULT 0,
    created_at    TEXT NOT NULL,
    updated_at    TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_video_visible ON teach_video(visible, sort_order);
CREATE INDEX IF NOT EXISTS idx_video_cat ON teach_video(category_id, sort_order);

CREATE TABLE IF NOT EXISTS photo_category (
    id                INTEGER PRIMARY KEY AUTOINCREMENT,
    name              TEXT NOT NULL,
    sort_order        INTEGER NOT NULL DEFAULT 0,
    visible           INTEGER NOT NULL DEFAULT 1,
    bgm_path          TEXT NOT NULL DEFAULT '',
    bgm_original_name TEXT NOT NULL DEFAULT '',
    bgm_file_size     INTEGER NOT NULL DEFAULT 0,
    created_at        TEXT NOT NULL,
    updated_at        TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS photo (
    id            INTEGER PRIMARY KEY AUTOINCREMENT,
    title         TEXT NOT NULL,
    description   TEXT NOT NULL DEFAULT '',
    original_name TEXT NOT NULL DEFAULT '',
    file_path     TEXT NOT NULL,
    file_size     INTEGER NOT NULL DEFAULT 0,
    sort_order    INTEGER NOT NULL DEFAULT 0,
    visible       INTEGER NOT NULL DEFAULT 1,
    category_id   INTEGER NOT NULL DEFAULT 0,
    created_at    TEXT NOT NULL,
    updated_at    TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_photo_visible ON photo(visible, sort_order);
CREATE INDEX IF NOT EXISTS idx_photo_cat ON photo(category_id, sort_order);

CREATE TABLE IF NOT EXISTS music_category (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    name       TEXT NOT NULL,
    sort_order INTEGER NOT NULL DEFAULT 0,
    visible    INTEGER NOT NULL DEFAULT 1,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS music (
    id            INTEGER PRIMARY KEY AUTOINCREMENT,
    title         TEXT NOT NULL,
    description   TEXT NOT NULL DEFAULT '',
    original_name TEXT NOT NULL DEFAULT '',
    file_path     TEXT NOT NULL,
    file_size     INTEGER NOT NULL DEFAULT 0,
    sort_order    INTEGER NOT NULL DEFAULT 0,
    visible       INTEGER NOT NULL DEFAULT 1,
    category_id   INTEGER NOT NULL DEFAULT 0,
    album_id      INTEGER NOT NULL DEFAULT 0,
    created_at    TEXT NOT NULL,
    updated_at    TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_music_visible ON music(visible, sort_order);
CREATE INDEX IF NOT EXISTS idx_music_cat ON music(category_id, sort_order);

CREATE TABLE IF NOT EXISTS music_album (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    category_id INTEGER NOT NULL DEFAULT 0,
    name        TEXT NOT NULL,
    sort_order  INTEGER NOT NULL DEFAULT 0,
    visible     INTEGER NOT NULL DEFAULT 1,
    created_at  TEXT NOT NULL,
    updated_at  TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_album_cat ON music_album(category_id, sort_order);
CREATE INDEX IF NOT EXISTS idx_music_album ON music(album_id, sort_order);

CREATE TABLE IF NOT EXISTS music_schedule (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    title       TEXT NOT NULL,
    category_id INTEGER NOT NULL DEFAULT 0,
    album_id    INTEGER NOT NULL DEFAULT 0,
    weekdays    TEXT NOT NULL DEFAULT '1,2,3,4,5',
    start_time  TEXT NOT NULL,
    end_time    TEXT NOT NULL,
    play_mode   TEXT NOT NULL DEFAULT 'loop',
    enabled     INTEGER NOT NULL DEFAULT 1,
    sort_order  INTEGER NOT NULL DEFAULT 0,
    created_at  TEXT NOT NULL,
    updated_at  TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_schedule_enabled ON music_schedule(enabled, start_time);

CREATE TABLE IF NOT EXISTS ebook_category (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    name       TEXT NOT NULL,
    sort_order INTEGER NOT NULL DEFAULT 0,
    visible    INTEGER NOT NULL DEFAULT 1,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS ebook (
    id            INTEGER PRIMARY KEY AUTOINCREMENT,
    title         TEXT NOT NULL,
    description   TEXT NOT NULL DEFAULT '',
    original_name TEXT NOT NULL DEFAULT '',
    file_path     TEXT NOT NULL,
    file_size     INTEGER NOT NULL DEFAULT 0,
    format        TEXT NOT NULL DEFAULT '',
    sort_order    INTEGER NOT NULL DEFAULT 0,
    visible       INTEGER NOT NULL DEFAULT 1,
    category_id   INTEGER NOT NULL DEFAULT 0,
    created_at    TEXT NOT NULL,
    updated_at    TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_ebook_visible ON ebook(visible, sort_order);
CREATE INDEX IF NOT EXISTS idx_ebook_cat ON ebook(category_id, sort_order);

CREATE TABLE IF NOT EXISTS board_message (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    nickname   TEXT NOT NULL DEFAULT '匿名网友',
    contact    TEXT NOT NULL DEFAULT '',
    kind       TEXT NOT NULL DEFAULT 'message',
    content    TEXT NOT NULL,
    reply      TEXT NOT NULL DEFAULT '',
    status     TEXT NOT NULL DEFAULT 'pending',
    client_ip  TEXT NOT NULL DEFAULT '',
    created_at TEXT NOT NULL,
    replied_at TEXT NOT NULL DEFAULT '',
    replied_by TEXT NOT NULL DEFAULT ''
);
CREATE INDEX IF NOT EXISTS idx_board_status ON board_message(status, created_at);

CREATE TABLE IF NOT EXISTS comment_image (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    client_ip  TEXT NOT NULL,
    created_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_rating_sw ON software_rating(software_id, dimension_id);
CREATE INDEX IF NOT EXISTS idx_comment_image_ip ON comment_image(client_ip, created_at);

CREATE TABLE IF NOT EXISTS announcement (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    title      TEXT NOT NULL,
    content    TEXT NOT NULL DEFAULT '',
    doc_path   TEXT NOT NULL DEFAULT '',
    doc_name   TEXT NOT NULL DEFAULT '',
    doc_size   INTEGER NOT NULL DEFAULT 0,
    doc_type   TEXT NOT NULL DEFAULT '',
    doc_html   TEXT NOT NULL DEFAULT '',
    visible    INTEGER NOT NULL DEFAULT 1,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_announcement_visible ON announcement(visible, created_at);

CREATE TABLE IF NOT EXISTS site_setting (
    skey   TEXT PRIMARY KEY,
    svalue TEXT NOT NULL DEFAULT ''
);

CREATE TABLE IF NOT EXISTS search_log (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    keyword    TEXT NOT NULL,
    client_ip  TEXT NOT NULL,
    created_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS admin_log (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    admin_name TEXT NOT NULL,
    action     TEXT NOT NULL,
    detail     TEXT NOT NULL DEFAULT '',
    client_ip  TEXT NOT NULL DEFAULT '',
    created_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS ip_blocklist (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    ip         TEXT NOT NULL UNIQUE,
    reason     TEXT NOT NULL DEFAULT '',
    created_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS dl_ip_rule (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    ip         TEXT NOT NULL UNIQUE,
    max_conns  INTEGER NOT NULL DEFAULT 0,
    rate_limit INTEGER NOT NULL DEFAULT 0,
    note       TEXT NOT NULL DEFAULT '',
    created_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS dl_lease (
    id      INTEGER PRIMARY KEY AUTOINCREMENT,
    ip      TEXT NOT NULL,
    label   TEXT NOT NULL DEFAULT '',
    expires INTEGER NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_lease_ip ON dl_lease(ip, expires);

CREATE TABLE IF NOT EXISTS vulnerability (
    id               INTEGER PRIMARY KEY AUTOINCREMENT,
    title            TEXT NOT NULL,
    cve_no           TEXT NOT NULL DEFAULT '',
    severity         TEXT NOT NULL DEFAULT 'medium',
    affected_product TEXT NOT NULL DEFAULT '',
    affected_version TEXT NOT NULL DEFAULT '',
    description      TEXT NOT NULL DEFAULT '',
    official_url     TEXT NOT NULL DEFAULT '',
    status           INTEGER NOT NULL DEFAULT 1,
    submitter_id     INTEGER,
    submitter_name   TEXT NOT NULL DEFAULT '',
    reviewed_at      TEXT NOT NULL DEFAULT '',
    review_note      TEXT NOT NULL DEFAULT '',
    view_count       INTEGER NOT NULL DEFAULT 0,
    created_at       TEXT NOT NULL,
    updated_at       TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS vulnerability_file (
    id               INTEGER PRIMARY KEY AUTOINCREMENT,
    vulnerability_id INTEGER NOT NULL REFERENCES vulnerability(id) ON DELETE CASCADE,
    label            TEXT NOT NULL DEFAULT '补丁包',
    original_name    TEXT NOT NULL,
    file_path        TEXT NOT NULL,
    file_size        INTEGER NOT NULL DEFAULT 0,
    source_type      TEXT NOT NULL DEFAULT 'upload',
    download_count   INTEGER NOT NULL DEFAULT 0,
    sort_order       INTEGER NOT NULL DEFAULT 0,
    created_at       TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS home_block (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    block_type  TEXT NOT NULL DEFAULT 'category',
    ref_id      INTEGER,
    title       TEXT NOT NULL DEFAULT '',
    config      TEXT NOT NULL DEFAULT '{}',
    sort_order  INTEGER NOT NULL DEFAULT 0,
    visible     INTEGER NOT NULL DEFAULT 1,
    created_at  TEXT NOT NULL,
    updated_at  TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS article (
    id             INTEGER PRIMARY KEY AUTOINCREMENT,
    title          TEXT NOT NULL,
    cover_url      TEXT NOT NULL DEFAULT '',
    summary        TEXT NOT NULL DEFAULT '',
    content        TEXT NOT NULL DEFAULT '',
    content_type   TEXT NOT NULL DEFAULT 'md',
    app_file_url   TEXT NOT NULL DEFAULT '',
    status         INTEGER NOT NULL DEFAULT 2,
    submitter_id   INTEGER,
    submitter_name TEXT NOT NULL DEFAULT '',
    reviewed_at    TEXT NOT NULL DEFAULT '',
    review_note    TEXT NOT NULL DEFAULT '',
    view_count     INTEGER NOT NULL DEFAULT 0,
    created_at     TEXT NOT NULL,
    updated_at     TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS article_part (
    id           INTEGER PRIMARY KEY AUTOINCREMENT,
    article_id   INTEGER NOT NULL,
    sort_order   INTEGER NOT NULL DEFAULT 0,
    title        TEXT NOT NULL DEFAULT '',
    content      TEXT NOT NULL DEFAULT '',
    content_type TEXT NOT NULL DEFAULT 'html'
);
CREATE INDEX IF NOT EXISTS idx_part_article ON article_part(article_id, sort_order);

CREATE TABLE IF NOT EXISTS article_attachment (
    id           INTEGER PRIMARY KEY AUTOINCREMENT,
    article_id   INTEGER NOT NULL,
    sort_order   INTEGER NOT NULL DEFAULT 0,
    kind         TEXT NOT NULL DEFAULT 'file',
    name         TEXT NOT NULL DEFAULT '',
    url          TEXT NOT NULL DEFAULT '',
    file_size    INTEGER NOT NULL DEFAULT 0
);
CREATE INDEX IF NOT EXISTS idx_att_article ON article_attachment(article_id, sort_order);

CREATE TABLE IF NOT EXISTS nav_category (
    id           INTEGER PRIMARY KEY AUTOINCREMENT,
    name         TEXT NOT NULL,
    sort_order   INTEGER NOT NULL DEFAULT 0,
    visible      INTEGER NOT NULL DEFAULT 1,
    created_at   TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS nav_link (
    id           INTEGER PRIMARY KEY AUTOINCREMENT,
    category_id  INTEGER NOT NULL DEFAULT 0,
    name         TEXT NOT NULL,
    url          TEXT NOT NULL,
    sort_order   INTEGER NOT NULL DEFAULT 0,
    visible      INTEGER NOT NULL DEFAULT 1,
    created_at   TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_nav_visible ON nav_link(visible, sort_order);
CREATE INDEX IF NOT EXISTS idx_nav_link_cat ON nav_link(category_id, sort_order);

CREATE INDEX IF NOT EXISTS idx_reg_attempt ON register_attempt(ip, created_at);
CREATE INDEX IF NOT EXISTS idx_log_software ON download_log(software_id);
CREATE INDEX IF NOT EXISTS idx_log_ip ON download_log(client_ip);
CREATE INDEX IF NOT EXISTS idx_log_time ON download_log(downloaded_at);
CREATE INDEX IF NOT EXISTS idx_file_sw ON software_file(software_id);
CREATE INDEX IF NOT EXISTS idx_comment_sw ON comment(software_id, status);
CREATE INDEX IF NOT EXISTS idx_comment_parent ON comment(parent_id, status);
CREATE INDEX IF NOT EXISTS idx_search_time ON search_log(created_at);
CREATE INDEX IF NOT EXISTS idx_vuln_status ON vulnerability(status, severity);
CREATE INDEX IF NOT EXISTS idx_vulnfile_vuln ON vulnerability_file(vulnerability_id);
CREATE INDEX IF NOT EXISTS idx_article_status ON article(status, created_at);
"""

SCHEMA_MYSQL = """
CREATE TABLE IF NOT EXISTS category (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(64) NOT NULL UNIQUE,
    sort_order  INT NOT NULL DEFAULT 0,
    visible     TINYINT NOT NULL DEFAULT 1,
    icon        VARCHAR(24) NOT NULL DEFAULT 'box',
    color       VARCHAR(16) NOT NULL DEFAULT '#4f6ef7',
    created_at  VARCHAR(19) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS software (
    id             INT AUTO_INCREMENT PRIMARY KEY,
    name           VARCHAR(128) NOT NULL,
    version        VARCHAR(64) NOT NULL DEFAULT '',
    category_id    INT NULL,
    description    MEDIUMTEXT NOT NULL,
    remark         VARCHAR(255) NOT NULL DEFAULT '',
    pin_weight     INT NOT NULL DEFAULT 0,
    view_count     INT NOT NULL DEFAULT 0,
    recommended    TINYINT NOT NULL DEFAULT 0,
    download_count INT NOT NULL DEFAULT 0,
    status         TINYINT NOT NULL DEFAULT 1,
    submitter_id   INT NULL,
    submitter_name VARCHAR(64) NOT NULL DEFAULT '',
    developer      VARCHAR(128) NOT NULL DEFAULT '',
    license_type   VARCHAR(32) NOT NULL DEFAULT '',
    reviewed_at    VARCHAR(19) NOT NULL DEFAULT '',
    review_note    VARCHAR(255) NOT NULL DEFAULT '',
    created_at     VARCHAR(19) NOT NULL,
    updated_at     VARCHAR(19) NOT NULL,
    CONSTRAINT fk_sw_cat FOREIGN KEY (category_id) REFERENCES category(id) ON DELETE SET NULL,
    INDEX idx_sw_status (status, pin_weight)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS software_file (
    id             INT AUTO_INCREMENT PRIMARY KEY,
    software_id    INT NOT NULL,
    label          VARCHAR(64) NOT NULL DEFAULT '安装包',
    original_name  VARCHAR(255) NOT NULL,
    file_path      VARCHAR(1024) NOT NULL,
    file_size      BIGINT NOT NULL DEFAULT 0,
    source_type    VARCHAR(16) NOT NULL DEFAULT 'upload',
    download_count INT NOT NULL DEFAULT 0,
    sort_order     INT NOT NULL DEFAULT 0,
    created_at     VARCHAR(19) NOT NULL,
    CONSTRAINT fk_file_sw FOREIGN KEY (software_id) REFERENCES software(id) ON DELETE CASCADE,
    INDEX idx_file_sw (software_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS tag (
    id   INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(32) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS software_tag (
    software_id INT NOT NULL,
    tag_id      INT NOT NULL,
    PRIMARY KEY (software_id, tag_id),
    CONSTRAINT fk_st_sw FOREIGN KEY (software_id) REFERENCES software(id) ON DELETE CASCADE,
    CONSTRAINT fk_st_tag FOREIGN KEY (tag_id) REFERENCES tag(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS admin (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    username        VARCHAR(64) NOT NULL UNIQUE,
    password_hash   VARCHAR(255) NOT NULL,
    role            VARCHAR(16) NOT NULL DEFAULT 'admin',
    disabled        TINYINT NOT NULL DEFAULT 0,
    upload_token    VARCHAR(64) NOT NULL DEFAULT '',
    id_card_hash    VARCHAR(64) NULL,
    id_card_mask    VARCHAR(32) NOT NULL DEFAULT '',
    unit_dept       VARCHAR(128) NOT NULL DEFAULT '',
    approval_status VARCHAR(16) NOT NULL DEFAULT 'approved',
    approval_note   VARCHAR(255) NOT NULL DEFAULT '',
    reviewed_at     VARCHAR(19) NOT NULL DEFAULT '',
    created_at      VARCHAR(19) NOT NULL,
    UNIQUE KEY uk_admin_idcard (id_card_hash)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS register_attempt (
    id         BIGINT AUTO_INCREMENT PRIMARY KEY,
    ip         VARCHAR(64) NOT NULL,
    created_at VARCHAR(19) NOT NULL,
    INDEX idx_reg_attempt (ip, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS session (
    id          VARCHAR(64) PRIMARY KEY,
    admin_id    INT NOT NULL,
    created_at  VARCHAR(19) NOT NULL,
    expires_at  VARCHAR(19) NOT NULL,
    INDEX idx_session_admin (admin_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS download_log (
    id            BIGINT AUTO_INCREMENT PRIMARY KEY,
    software_id   INT NOT NULL,
    software_name VARCHAR(128) NOT NULL,
    file_id       INT NOT NULL DEFAULT 0,
    file_label    VARCHAR(64) NOT NULL DEFAULT '',
    client_ip     VARCHAR(64) NOT NULL,
    user_agent    VARCHAR(300) NOT NULL DEFAULT '',
    downloaded_at VARCHAR(19) NOT NULL,
    INDEX idx_log_software (software_id),
    INDEX idx_log_ip (client_ip),
    INDEX idx_log_time (downloaded_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS comment (
    id          BIGINT AUTO_INCREMENT PRIMARY KEY,
    software_id INT NOT NULL,
    parent_id   BIGINT NULL,
    nickname    VARCHAR(32) NOT NULL DEFAULT '匿名网友',
    content     VARCHAR(1000) NOT NULL,
    client_ip   VARCHAR(64) NOT NULL,
    is_admin    TINYINT NOT NULL DEFAULT 0,
    status      VARCHAR(16) NOT NULL DEFAULT 'pending',
    created_at  VARCHAR(19) NOT NULL,
    approved_at VARCHAR(19) NOT NULL DEFAULT '',
    CONSTRAINT fk_comment_sw FOREIGN KEY (software_id) REFERENCES software(id) ON DELETE CASCADE,
    CONSTRAINT fk_comment_parent FOREIGN KEY (parent_id) REFERENCES comment(id) ON DELETE CASCADE,
    INDEX idx_comment_sw (software_id, status),
    INDEX idx_comment_parent (parent_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS rating_dimension (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    name       VARCHAR(32) NOT NULL UNIQUE,
    sort_order INT NOT NULL DEFAULT 0,
    visible    TINYINT NOT NULL DEFAULT 1,
    created_at VARCHAR(19) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS software_rating (
    id           BIGINT AUTO_INCREMENT PRIMARY KEY,
    software_id  INT NOT NULL,
    dimension_id INT NOT NULL,
    rater_key    VARCHAR(64) NOT NULL,
    score        TINYINT NOT NULL DEFAULT 0,
    created_at   VARCHAR(19) NOT NULL,
    CONSTRAINT fk_rating_sw FOREIGN KEY (software_id) REFERENCES software(id) ON DELETE CASCADE,
    CONSTRAINT fk_rating_dim FOREIGN KEY (dimension_id) REFERENCES rating_dimension(id) ON DELETE CASCADE,
    UNIQUE KEY uk_rating (software_id, dimension_id, rater_key),
    INDEX idx_rating_sw (software_id, dimension_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS teach_video_category (
    id         BIGINT AUTO_INCREMENT PRIMARY KEY,
    name       VARCHAR(60) NOT NULL,
    sort_order INT NOT NULL DEFAULT 0,
    visible    TINYINT NOT NULL DEFAULT 1,
    created_at VARCHAR(19) NOT NULL,
    updated_at VARCHAR(19) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS teach_video (
    id            BIGINT AUTO_INCREMENT PRIMARY KEY,
    title         VARCHAR(200) NOT NULL,
    description   TEXT NOT NULL,
    original_name VARCHAR(255) NOT NULL DEFAULT '',
    file_path     VARCHAR(500) NOT NULL,
    file_size     BIGINT NOT NULL DEFAULT 0,
    sort_order    INT NOT NULL DEFAULT 0,
    visible       TINYINT NOT NULL DEFAULT 1,
    category_id   BIGINT NOT NULL DEFAULT 0,
    created_at    VARCHAR(19) NOT NULL,
    updated_at    VARCHAR(19) NOT NULL,
    INDEX idx_video_visible (visible, sort_order),
    INDEX idx_video_cat (category_id, sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS photo_category (
    id                BIGINT AUTO_INCREMENT PRIMARY KEY,
    name              VARCHAR(60) NOT NULL,
    sort_order        INT NOT NULL DEFAULT 0,
    visible           TINYINT NOT NULL DEFAULT 1,
    bgm_path          VARCHAR(500) NOT NULL DEFAULT '',
    bgm_original_name VARCHAR(255) NOT NULL DEFAULT '',
    bgm_file_size     BIGINT NOT NULL DEFAULT 0,
    created_at        VARCHAR(19) NOT NULL,
    updated_at        VARCHAR(19) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS photo (
    id            BIGINT AUTO_INCREMENT PRIMARY KEY,
    title         VARCHAR(200) NOT NULL,
    description   TEXT NOT NULL,
    original_name VARCHAR(255) NOT NULL DEFAULT '',
    file_path     VARCHAR(500) NOT NULL,
    file_size     BIGINT NOT NULL DEFAULT 0,
    sort_order    INT NOT NULL DEFAULT 0,
    visible       TINYINT NOT NULL DEFAULT 1,
    category_id   BIGINT NOT NULL DEFAULT 0,
    created_at    VARCHAR(19) NOT NULL,
    updated_at    VARCHAR(19) NOT NULL,
    INDEX idx_photo_visible (visible, sort_order),
    INDEX idx_photo_cat (category_id, sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS music_category (
    id         BIGINT AUTO_INCREMENT PRIMARY KEY,
    name       VARCHAR(60) NOT NULL,
    sort_order INT NOT NULL DEFAULT 0,
    visible    TINYINT NOT NULL DEFAULT 1,
    created_at VARCHAR(19) NOT NULL,
    updated_at VARCHAR(19) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS music (
    id            BIGINT AUTO_INCREMENT PRIMARY KEY,
    title         VARCHAR(200) NOT NULL,
    description   TEXT NOT NULL,
    original_name VARCHAR(255) NOT NULL DEFAULT '',
    file_path     VARCHAR(500) NOT NULL,
    file_size     BIGINT NOT NULL DEFAULT 0,
    sort_order    INT NOT NULL DEFAULT 0,
    visible       TINYINT NOT NULL DEFAULT 1,
    category_id   BIGINT NOT NULL DEFAULT 0,
    album_id      BIGINT NOT NULL DEFAULT 0,
    created_at    VARCHAR(19) NOT NULL,
    updated_at    VARCHAR(19) NOT NULL,
    INDEX idx_music_visible (visible, sort_order),
    INDEX idx_music_cat (category_id, sort_order),
    INDEX idx_music_album (album_id, sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS music_album (
    id          BIGINT AUTO_INCREMENT PRIMARY KEY,
    category_id BIGINT NOT NULL DEFAULT 0,
    name        VARCHAR(80) NOT NULL,
    sort_order  INT NOT NULL DEFAULT 0,
    visible     TINYINT NOT NULL DEFAULT 1,
    created_at  VARCHAR(19) NOT NULL,
    updated_at  VARCHAR(19) NOT NULL,
    INDEX idx_album_cat (category_id, sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS music_schedule (
    id          BIGINT AUTO_INCREMENT PRIMARY KEY,
    title       VARCHAR(80) NOT NULL,
    category_id BIGINT NOT NULL DEFAULT 0,
    album_id    BIGINT NOT NULL DEFAULT 0,
    weekdays    VARCHAR(20) NOT NULL DEFAULT '1,2,3,4,5',
    start_time  VARCHAR(5) NOT NULL,
    end_time    VARCHAR(5) NOT NULL,
    play_mode   VARCHAR(10) NOT NULL DEFAULT 'loop',
    enabled     TINYINT NOT NULL DEFAULT 1,
    sort_order  INT NOT NULL DEFAULT 0,
    created_at  VARCHAR(19) NOT NULL,
    updated_at  VARCHAR(19) NOT NULL,
    INDEX idx_schedule_enabled (enabled, start_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ebook_category (
    id         BIGINT AUTO_INCREMENT PRIMARY KEY,
    name       VARCHAR(60) NOT NULL,
    sort_order INT NOT NULL DEFAULT 0,
    visible    TINYINT NOT NULL DEFAULT 1,
    created_at VARCHAR(19) NOT NULL,
    updated_at VARCHAR(19) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ebook (
    id            BIGINT AUTO_INCREMENT PRIMARY KEY,
    title         VARCHAR(200) NOT NULL,
    description   TEXT NOT NULL,
    original_name VARCHAR(255) NOT NULL DEFAULT '',
    file_path     VARCHAR(500) NOT NULL,
    file_size     BIGINT NOT NULL DEFAULT 0,
    format        VARCHAR(8) NOT NULL DEFAULT '',
    sort_order    INT NOT NULL DEFAULT 0,
    visible       TINYINT NOT NULL DEFAULT 1,
    category_id   BIGINT NOT NULL DEFAULT 0,
    created_at    VARCHAR(19) NOT NULL,
    updated_at    VARCHAR(19) NOT NULL,
    INDEX idx_ebook_visible (visible, sort_order),
    INDEX idx_ebook_cat (category_id, sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS board_message (
    id         BIGINT AUTO_INCREMENT PRIMARY KEY,
    nickname   VARCHAR(32) NOT NULL DEFAULT '匿名网友',
    contact    VARCHAR(64) NOT NULL DEFAULT '',
    kind       VARCHAR(16) NOT NULL DEFAULT 'message',
    content    TEXT NOT NULL,
    reply      TEXT NOT NULL,
    status     VARCHAR(16) NOT NULL DEFAULT 'pending',
    client_ip  VARCHAR(64) NOT NULL DEFAULT '',
    created_at VARCHAR(19) NOT NULL,
    replied_at VARCHAR(19) NOT NULL DEFAULT '',
    replied_by VARCHAR(64) NOT NULL DEFAULT '',
    INDEX idx_board_status (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS comment_image (
    id         BIGINT AUTO_INCREMENT PRIMARY KEY,
    client_ip  VARCHAR(64) NOT NULL,
    created_at VARCHAR(19) NOT NULL,
    INDEX idx_comment_image_ip (client_ip, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS announcement (
    id         BIGINT AUTO_INCREMENT PRIMARY KEY,
    title      VARCHAR(200) NOT NULL,
    content    MEDIUMTEXT NOT NULL,
    doc_path   VARCHAR(500) NOT NULL DEFAULT '',
    doc_name   VARCHAR(255) NOT NULL DEFAULT '',
    doc_size   BIGINT NOT NULL DEFAULT 0,
    doc_type   VARCHAR(10) NOT NULL DEFAULT '',
    doc_html   VARCHAR(500) NOT NULL DEFAULT '',
    visible    TINYINT NOT NULL DEFAULT 1,
    created_at VARCHAR(19) NOT NULL,
    updated_at VARCHAR(19) NOT NULL,
    INDEX idx_announcement_visible (visible, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS vulnerability (
    id               INT AUTO_INCREMENT PRIMARY KEY,
    title            VARCHAR(128) NOT NULL,
    cve_no           VARCHAR(32) NOT NULL DEFAULT '',
    severity         VARCHAR(16) NOT NULL DEFAULT 'medium',
    affected_product VARCHAR(128) NOT NULL DEFAULT '',
    affected_version VARCHAR(128) NOT NULL DEFAULT '',
    description      MEDIUMTEXT NOT NULL,
    official_url     VARCHAR(500) NOT NULL DEFAULT '',
    status           TINYINT NOT NULL DEFAULT 1,
    submitter_id     INT NULL,
    submitter_name   VARCHAR(64) NOT NULL DEFAULT '',
    reviewed_at      VARCHAR(19) NOT NULL DEFAULT '',
    review_note      VARCHAR(255) NOT NULL DEFAULT '',
    view_count       INT NOT NULL DEFAULT 0,
    created_at       VARCHAR(19) NOT NULL,
    updated_at       VARCHAR(19) NOT NULL,
    INDEX idx_vuln_status (status, severity)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS vulnerability_file (
    id               INT AUTO_INCREMENT PRIMARY KEY,
    vulnerability_id INT NOT NULL,
    label            VARCHAR(64) NOT NULL DEFAULT '补丁包',
    original_name    VARCHAR(255) NOT NULL,
    file_path        VARCHAR(1024) NOT NULL,
    file_size        BIGINT NOT NULL DEFAULT 0,
    source_type      VARCHAR(16) NOT NULL DEFAULT 'upload',
    download_count   INT NOT NULL DEFAULT 0,
    sort_order       INT NOT NULL DEFAULT 0,
    created_at       VARCHAR(19) NOT NULL,
    CONSTRAINT fk_vfile_vuln FOREIGN KEY (vulnerability_id) REFERENCES vulnerability(id) ON DELETE CASCADE,
    INDEX idx_vulnfile_vuln (vulnerability_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS home_block (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    block_type  VARCHAR(16) NOT NULL DEFAULT 'category',
    ref_id      INT NULL,
    title       VARCHAR(128) NOT NULL DEFAULT '',
    config      TEXT NOT NULL,
    sort_order  INT NOT NULL DEFAULT 0,
    visible     TINYINT NOT NULL DEFAULT 1,
    created_at  VARCHAR(19) NOT NULL,
    updated_at  VARCHAR(19) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS article (
    id             INT AUTO_INCREMENT PRIMARY KEY,
    title          VARCHAR(200) NOT NULL,
    cover_url      VARCHAR(1024) NOT NULL DEFAULT '',
    summary        VARCHAR(500) NOT NULL DEFAULT '',
    content        MEDIUMTEXT NOT NULL,
    content_type   VARCHAR(16) NOT NULL DEFAULT 'md',
    app_file_url   VARCHAR(1024) NOT NULL DEFAULT '',
    status         TINYINT NOT NULL DEFAULT 2,
    submitter_id   INT NULL,
    submitter_name VARCHAR(64) NOT NULL DEFAULT '',
    reviewed_at    VARCHAR(19) NOT NULL DEFAULT '',
    review_note    VARCHAR(255) NOT NULL DEFAULT '',
    view_count     INT NOT NULL DEFAULT 0,
    created_at     VARCHAR(19) NOT NULL,
    updated_at     VARCHAR(19) NOT NULL,
    INDEX idx_article_status (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS article_part (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    article_id   INT NOT NULL,
    sort_order   INT NOT NULL DEFAULT 0,
    title        VARCHAR(200) NOT NULL DEFAULT '',
    content      MEDIUMTEXT NOT NULL,
    content_type VARCHAR(8) NOT NULL DEFAULT 'html',
    INDEX idx_part_article (article_id, sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS article_attachment (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    article_id   INT NOT NULL,
    sort_order   INT NOT NULL DEFAULT 0,
    kind         VARCHAR(8) NOT NULL DEFAULT 'file',
    name         VARCHAR(255) NOT NULL DEFAULT '',
    url          VARCHAR(1024) NOT NULL DEFAULT '',
    file_size    BIGINT NOT NULL DEFAULT 0,
    INDEX idx_att_article (article_id, sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS site_setting (
    skey   VARCHAR(64) PRIMARY KEY,
    svalue TEXT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS search_log (
    id         BIGINT AUTO_INCREMENT PRIMARY KEY,
    keyword    VARCHAR(64) NOT NULL,
    client_ip  VARCHAR(64) NOT NULL,
    created_at VARCHAR(19) NOT NULL,
    INDEX idx_search_time (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS admin_log (
    id         BIGINT AUTO_INCREMENT PRIMARY KEY,
    admin_name VARCHAR(64) NOT NULL,
    action     VARCHAR(64) NOT NULL,
    detail     VARCHAR(500) NOT NULL DEFAULT '',
    client_ip  VARCHAR(64) NOT NULL DEFAULT '',
    created_at VARCHAR(19) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ip_blocklist (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    ip         VARCHAR(64) NOT NULL UNIQUE,
    reason     VARCHAR(255) NOT NULL DEFAULT '',
    created_at VARCHAR(19) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS dl_ip_rule (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    ip         VARCHAR(64) NOT NULL UNIQUE,
    max_conns  INT NOT NULL DEFAULT 0,
    rate_limit BIGINT NOT NULL DEFAULT 0,
    note       VARCHAR(255) NOT NULL DEFAULT '',
    created_at VARCHAR(19) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS dl_lease (
    id      INT AUTO_INCREMENT PRIMARY KEY,
    ip      VARCHAR(64) NOT NULL,
    label   VARCHAR(120) NOT NULL DEFAULT '',
    expires BIGINT NOT NULL,
    INDEX idx_lease_ip (ip, expires)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS nav_category (
    id         BIGINT AUTO_INCREMENT PRIMARY KEY,
    name       VARCHAR(60) NOT NULL,
    sort_order INT NOT NULL DEFAULT 0,
    visible    TINYINT NOT NULL DEFAULT 1,
    created_at VARCHAR(19) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS nav_link (
    id          BIGINT AUTO_INCREMENT PRIMARY KEY,
    category_id BIGINT NOT NULL DEFAULT 0,
    name        VARCHAR(120) NOT NULL,
    url         VARCHAR(500) NOT NULL,
    sort_order  INT NOT NULL DEFAULT 0,
    visible     TINYINT NOT NULL DEFAULT 1,
    created_at  VARCHAR(19) NOT NULL,
    INDEX idx_nav_visible (visible, sort_order),
    INDEX idx_nav_link_cat (category_id, sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
"""

SCHEMA_POSTGRES = """
CREATE TABLE IF NOT EXISTS category (
    id          SERIAL PRIMARY KEY,
    name        VARCHAR(64) NOT NULL UNIQUE,
    sort_order  INT NOT NULL DEFAULT 0,
    visible     SMALLINT NOT NULL DEFAULT 1,
    icon        VARCHAR(24) NOT NULL DEFAULT 'box',
    color       VARCHAR(16) NOT NULL DEFAULT '#4f6ef7',
    created_at  VARCHAR(19) NOT NULL
);

CREATE TABLE IF NOT EXISTS software (
    id             SERIAL PRIMARY KEY,
    name           VARCHAR(128) NOT NULL,
    version        VARCHAR(64) NOT NULL DEFAULT '',
    category_id    INT NULL,
    description    TEXT NOT NULL,
    remark         VARCHAR(255) NOT NULL DEFAULT '',
    pin_weight     INT NOT NULL DEFAULT 0,
    view_count     INT NOT NULL DEFAULT 0,
    recommended    SMALLINT NOT NULL DEFAULT 0,
    download_count INT NOT NULL DEFAULT 0,
    status         SMALLINT NOT NULL DEFAULT 1,
    submitter_id   INT,
    submitter_name VARCHAR(64) NOT NULL DEFAULT '',
    developer      VARCHAR(128) NOT NULL DEFAULT '',
    license_type   VARCHAR(32) NOT NULL DEFAULT '',
    reviewed_at    VARCHAR(19) NOT NULL DEFAULT '',
    review_note    VARCHAR(255) NOT NULL DEFAULT '',
    created_at     VARCHAR(19) NOT NULL,
    updated_at     VARCHAR(19) NOT NULL,
    CONSTRAINT fk_sw_cat FOREIGN KEY (category_id) REFERENCES category(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS software_file (
    id             SERIAL PRIMARY KEY,
    software_id    INT NOT NULL,
    label          VARCHAR(64) NOT NULL DEFAULT '安装包',
    original_name  VARCHAR(255) NOT NULL,
    file_path      VARCHAR(1024) NOT NULL,
    file_size      BIGINT NOT NULL DEFAULT 0,
    source_type    VARCHAR(16) NOT NULL DEFAULT 'upload',
    download_count INT NOT NULL DEFAULT 0,
    sort_order     INT NOT NULL DEFAULT 0,
    created_at     VARCHAR(19) NOT NULL,
    CONSTRAINT fk_file_sw FOREIGN KEY (software_id) REFERENCES software(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS tag (
    id   SERIAL PRIMARY KEY,
    name VARCHAR(32) NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS software_tag (
    software_id INT NOT NULL,
    tag_id      INT NOT NULL,
    PRIMARY KEY (software_id, tag_id),
    CONSTRAINT fk_st_sw FOREIGN KEY (software_id) REFERENCES software(id) ON DELETE CASCADE,
    CONSTRAINT fk_st_tag FOREIGN KEY (tag_id) REFERENCES tag(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS admin (
    id              SERIAL PRIMARY KEY,
    username        VARCHAR(64) NOT NULL UNIQUE,
    password_hash   VARCHAR(255) NOT NULL,
    role            VARCHAR(16) NOT NULL DEFAULT 'admin',
    disabled        SMALLINT NOT NULL DEFAULT 0,
    upload_token    VARCHAR(64) NOT NULL DEFAULT '',
    id_card_hash    VARCHAR(64),
    id_card_mask    VARCHAR(32) NOT NULL DEFAULT '',
    unit_dept       VARCHAR(128) NOT NULL DEFAULT '',
    approval_status VARCHAR(16) NOT NULL DEFAULT 'approved',
    approval_note   VARCHAR(255) NOT NULL DEFAULT '',
    reviewed_at     VARCHAR(19) NOT NULL DEFAULT '',
    created_at      VARCHAR(19) NOT NULL
);

CREATE TABLE IF NOT EXISTS register_attempt (
    id         BIGSERIAL PRIMARY KEY,
    ip         VARCHAR(64) NOT NULL,
    created_at VARCHAR(19) NOT NULL
);

CREATE TABLE IF NOT EXISTS session (
    id          VARCHAR(64) PRIMARY KEY,
    admin_id    INT NOT NULL,
    created_at  VARCHAR(19) NOT NULL,
    expires_at  VARCHAR(19) NOT NULL
);

CREATE TABLE IF NOT EXISTS download_log (
    id            BIGSERIAL PRIMARY KEY,
    software_id   INT NOT NULL,
    software_name VARCHAR(128) NOT NULL,
    file_id       INT NOT NULL DEFAULT 0,
    file_label    VARCHAR(64) NOT NULL DEFAULT '',
    client_ip     VARCHAR(64) NOT NULL,
    user_agent    VARCHAR(300) NOT NULL DEFAULT '',
    downloaded_at VARCHAR(19) NOT NULL
);

CREATE TABLE IF NOT EXISTS comment (
    id          BIGSERIAL PRIMARY KEY,
    software_id INT NOT NULL,
    parent_id   BIGINT REFERENCES comment(id) ON DELETE CASCADE,
    nickname    VARCHAR(32) NOT NULL DEFAULT '匿名网友',
    content     VARCHAR(1000) NOT NULL,
    client_ip   VARCHAR(64) NOT NULL,
    is_admin    SMALLINT NOT NULL DEFAULT 0,
    status      VARCHAR(16) NOT NULL DEFAULT 'pending',
    created_at  VARCHAR(19) NOT NULL,
    approved_at VARCHAR(19) NOT NULL DEFAULT '',
    CONSTRAINT fk_comment_sw FOREIGN KEY (software_id) REFERENCES software(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS rating_dimension (
    id          SERIAL PRIMARY KEY,
    name        VARCHAR(32) NOT NULL UNIQUE,
    sort_order  INT NOT NULL DEFAULT 0,
    visible     SMALLINT NOT NULL DEFAULT 1,
    created_at  VARCHAR(19) NOT NULL
);

CREATE TABLE IF NOT EXISTS software_rating (
    id           BIGSERIAL PRIMARY KEY,
    software_id  INT NOT NULL,
    dimension_id INT NOT NULL,
    rater_key    VARCHAR(64) NOT NULL,
    score        SMALLINT NOT NULL DEFAULT 0,
    created_at   VARCHAR(19) NOT NULL,
    CONSTRAINT fk_rating_sw FOREIGN KEY (software_id) REFERENCES software(id) ON DELETE CASCADE,
    CONSTRAINT fk_rating_dim FOREIGN KEY (dimension_id) REFERENCES rating_dimension(id) ON DELETE CASCADE,
    UNIQUE (software_id, dimension_id, rater_key)
);

CREATE TABLE IF NOT EXISTS teach_video_category (
    id         SERIAL PRIMARY KEY,
    name       VARCHAR(60) NOT NULL,
    sort_order INT NOT NULL DEFAULT 0,
    visible    SMALLINT NOT NULL DEFAULT 1,
    created_at VARCHAR(19) NOT NULL,
    updated_at VARCHAR(19) NOT NULL
);

CREATE TABLE IF NOT EXISTS teach_video (
    id            SERIAL PRIMARY KEY,
    title         VARCHAR(200) NOT NULL,
    description   TEXT NOT NULL DEFAULT '',
    original_name VARCHAR(255) NOT NULL DEFAULT '',
    file_path     VARCHAR(500) NOT NULL,
    file_size     BIGINT NOT NULL DEFAULT 0,
    sort_order    INT NOT NULL DEFAULT 0,
    visible       SMALLINT NOT NULL DEFAULT 1,
    category_id   INT NOT NULL DEFAULT 0,
    created_at    VARCHAR(19) NOT NULL,
    updated_at    VARCHAR(19) NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_video_visible ON teach_video(visible, sort_order);
CREATE INDEX IF NOT EXISTS idx_video_cat ON teach_video(category_id, sort_order);

CREATE TABLE IF NOT EXISTS photo_category (
    id                SERIAL PRIMARY KEY,
    name              VARCHAR(60) NOT NULL,
    sort_order        INT NOT NULL DEFAULT 0,
    visible           SMALLINT NOT NULL DEFAULT 1,
    bgm_path          VARCHAR(500) NOT NULL DEFAULT '',
    bgm_original_name VARCHAR(255) NOT NULL DEFAULT '',
    bgm_file_size     BIGINT NOT NULL DEFAULT 0,
    created_at        VARCHAR(19) NOT NULL,
    updated_at        VARCHAR(19) NOT NULL
);

CREATE TABLE IF NOT EXISTS photo (
    id            SERIAL PRIMARY KEY,
    title         VARCHAR(200) NOT NULL,
    description   TEXT NOT NULL DEFAULT '',
    original_name VARCHAR(255) NOT NULL DEFAULT '',
    file_path     VARCHAR(500) NOT NULL,
    file_size     BIGINT NOT NULL DEFAULT 0,
    sort_order    INT NOT NULL DEFAULT 0,
    visible       SMALLINT NOT NULL DEFAULT 1,
    category_id   INT NOT NULL DEFAULT 0,
    created_at    VARCHAR(19) NOT NULL,
    updated_at    VARCHAR(19) NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_photo_visible ON photo(visible, sort_order);
CREATE INDEX IF NOT EXISTS idx_photo_cat ON photo(category_id, sort_order);

CREATE TABLE IF NOT EXISTS music_category (
    id         SERIAL PRIMARY KEY,
    name       VARCHAR(60) NOT NULL,
    sort_order INT NOT NULL DEFAULT 0,
    visible    SMALLINT NOT NULL DEFAULT 1,
    created_at VARCHAR(19) NOT NULL,
    updated_at VARCHAR(19) NOT NULL
);

CREATE TABLE IF NOT EXISTS music (
    id            SERIAL PRIMARY KEY,
    title         VARCHAR(200) NOT NULL,
    description   TEXT NOT NULL DEFAULT '',
    original_name VARCHAR(255) NOT NULL DEFAULT '',
    file_path     VARCHAR(500) NOT NULL,
    file_size     BIGINT NOT NULL DEFAULT 0,
    sort_order    INT NOT NULL DEFAULT 0,
    visible       SMALLINT NOT NULL DEFAULT 1,
    category_id   INT NOT NULL DEFAULT 0,
    album_id      INT NOT NULL DEFAULT 0,
    created_at    VARCHAR(19) NOT NULL,
    updated_at    VARCHAR(19) NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_music_visible ON music(visible, sort_order);
CREATE INDEX IF NOT EXISTS idx_music_cat ON music(category_id, sort_order);
CREATE INDEX IF NOT EXISTS idx_music_album ON music(album_id, sort_order);

CREATE TABLE IF NOT EXISTS music_album (
    id          SERIAL PRIMARY KEY,
    category_id INT NOT NULL DEFAULT 0,
    name        VARCHAR(80) NOT NULL,
    sort_order  INT NOT NULL DEFAULT 0,
    visible     SMALLINT NOT NULL DEFAULT 1,
    created_at  VARCHAR(19) NOT NULL,
    updated_at  VARCHAR(19) NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_album_cat ON music_album(category_id, sort_order);

CREATE TABLE IF NOT EXISTS music_schedule (
    id          SERIAL PRIMARY KEY,
    title       VARCHAR(80) NOT NULL,
    category_id INT NOT NULL DEFAULT 0,
    album_id    INT NOT NULL DEFAULT 0,
    weekdays    VARCHAR(20) NOT NULL DEFAULT '1,2,3,4,5',
    start_time  VARCHAR(5) NOT NULL,
    end_time    VARCHAR(5) NOT NULL,
    play_mode   VARCHAR(10) NOT NULL DEFAULT 'loop',
    enabled     SMALLINT NOT NULL DEFAULT 1,
    sort_order  INT NOT NULL DEFAULT 0,
    created_at  VARCHAR(19) NOT NULL,
    updated_at  VARCHAR(19) NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_schedule_enabled ON music_schedule(enabled, start_time);

CREATE TABLE IF NOT EXISTS ebook_category (
    id         SERIAL PRIMARY KEY,
    name       VARCHAR(60) NOT NULL,
    sort_order INT NOT NULL DEFAULT 0,
    visible    SMALLINT NOT NULL DEFAULT 1,
    created_at VARCHAR(19) NOT NULL,
    updated_at VARCHAR(19) NOT NULL
);

CREATE TABLE IF NOT EXISTS ebook (
    id            SERIAL PRIMARY KEY,
    title         VARCHAR(200) NOT NULL,
    description   TEXT NOT NULL DEFAULT '',
    original_name VARCHAR(255) NOT NULL DEFAULT '',
    file_path     VARCHAR(500) NOT NULL,
    file_size     BIGINT NOT NULL DEFAULT 0,
    format        VARCHAR(8) NOT NULL DEFAULT '',
    sort_order    INT NOT NULL DEFAULT 0,
    visible       SMALLINT NOT NULL DEFAULT 1,
    category_id   INT NOT NULL DEFAULT 0,
    created_at    VARCHAR(19) NOT NULL,
    updated_at    VARCHAR(19) NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_ebook_visible ON ebook(visible, sort_order);
CREATE INDEX IF NOT EXISTS idx_ebook_cat ON ebook(category_id, sort_order);

CREATE TABLE IF NOT EXISTS board_message (
    id         SERIAL PRIMARY KEY,
    nickname   VARCHAR(32) NOT NULL DEFAULT '匿名网友',
    contact    VARCHAR(64) NOT NULL DEFAULT '',
    kind       VARCHAR(16) NOT NULL DEFAULT 'message',
    content    TEXT NOT NULL,
    reply      TEXT NOT NULL DEFAULT '',
    status     VARCHAR(16) NOT NULL DEFAULT 'pending',
    client_ip  VARCHAR(64) NOT NULL DEFAULT '',
    created_at VARCHAR(19) NOT NULL,
    replied_at VARCHAR(19) NOT NULL DEFAULT '',
    replied_by VARCHAR(64) NOT NULL DEFAULT ''
);
CREATE INDEX IF NOT EXISTS idx_board_status ON board_message(status, created_at);

CREATE TABLE IF NOT EXISTS comment_image (
    id         BIGSERIAL PRIMARY KEY,
    client_ip  VARCHAR(64) NOT NULL,
    created_at VARCHAR(19) NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_rating_sw ON software_rating(software_id, dimension_id);
CREATE INDEX IF NOT EXISTS idx_comment_image_ip ON comment_image(client_ip, created_at);

CREATE TABLE IF NOT EXISTS announcement (
    id         SERIAL PRIMARY KEY,
    title      VARCHAR(200) NOT NULL,
    content    TEXT NOT NULL DEFAULT '',
    doc_path   VARCHAR(500) NOT NULL DEFAULT '',
    doc_name   VARCHAR(255) NOT NULL DEFAULT '',
    doc_size   BIGINT NOT NULL DEFAULT 0,
    doc_type   VARCHAR(10) NOT NULL DEFAULT '',
    doc_html   VARCHAR(500) NOT NULL DEFAULT '',
    visible    SMALLINT NOT NULL DEFAULT 1,
    created_at VARCHAR(19) NOT NULL,
    updated_at VARCHAR(19) NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_announcement_visible ON announcement(visible, created_at);

CREATE TABLE IF NOT EXISTS vulnerability (
    id               SERIAL PRIMARY KEY,
    title            VARCHAR(128) NOT NULL,
    cve_no           VARCHAR(32) NOT NULL DEFAULT '',
    severity         VARCHAR(16) NOT NULL DEFAULT 'medium',
    affected_product VARCHAR(128) NOT NULL DEFAULT '',
    affected_version VARCHAR(128) NOT NULL DEFAULT '',
    description      TEXT NOT NULL,
    official_url     VARCHAR(500) NOT NULL DEFAULT '',
    status           SMALLINT NOT NULL DEFAULT 1,
    submitter_id     INT,
    submitter_name   VARCHAR(64) NOT NULL DEFAULT '',
    reviewed_at      VARCHAR(19) NOT NULL DEFAULT '',
    review_note      VARCHAR(255) NOT NULL DEFAULT '',
    view_count       INT NOT NULL DEFAULT 0,
    created_at       VARCHAR(19) NOT NULL,
    updated_at       VARCHAR(19) NOT NULL
);

CREATE TABLE IF NOT EXISTS vulnerability_file (
    id               SERIAL PRIMARY KEY,
    vulnerability_id INT NOT NULL,
    label            VARCHAR(64) NOT NULL DEFAULT '补丁包',
    original_name    VARCHAR(255) NOT NULL,
    file_path        VARCHAR(1024) NOT NULL,
    file_size        BIGINT NOT NULL DEFAULT 0,
    source_type      VARCHAR(16) NOT NULL DEFAULT 'upload',
    download_count   INT NOT NULL DEFAULT 0,
    sort_order       INT NOT NULL DEFAULT 0,
    created_at       VARCHAR(19) NOT NULL,
    CONSTRAINT fk_vfile_vuln FOREIGN KEY (vulnerability_id) REFERENCES vulnerability(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS home_block (
    id          SERIAL PRIMARY KEY,
    block_type  VARCHAR(16) NOT NULL DEFAULT 'category',
    ref_id      INT,
    title       VARCHAR(128) NOT NULL DEFAULT '',
    config      TEXT NOT NULL DEFAULT '{}',
    sort_order  INT NOT NULL DEFAULT 0,
    visible     SMALLINT NOT NULL DEFAULT 1,
    created_at  VARCHAR(19) NOT NULL,
    updated_at  VARCHAR(19) NOT NULL
);

CREATE TABLE IF NOT EXISTS article (
    id             SERIAL PRIMARY KEY,
    title          VARCHAR(200) NOT NULL,
    cover_url      VARCHAR(1024) NOT NULL DEFAULT '',
    summary        VARCHAR(500) NOT NULL DEFAULT '',
    content        TEXT NOT NULL DEFAULT '',
    content_type   VARCHAR(16) NOT NULL DEFAULT 'md',
    app_file_url   VARCHAR(1024) NOT NULL DEFAULT '',
    status         SMALLINT NOT NULL DEFAULT 2,
    submitter_id   INT,
    submitter_name VARCHAR(64) NOT NULL DEFAULT '',
    reviewed_at    VARCHAR(19) NOT NULL DEFAULT '',
    review_note    VARCHAR(255) NOT NULL DEFAULT '',
    view_count     INT NOT NULL DEFAULT 0,
    created_at     VARCHAR(19) NOT NULL,
    updated_at     VARCHAR(19) NOT NULL
);

CREATE TABLE IF NOT EXISTS article_part (
    id           SERIAL PRIMARY KEY,
    article_id   INT NOT NULL,
    sort_order   INT NOT NULL DEFAULT 0,
    title        VARCHAR(200) NOT NULL DEFAULT '',
    content      TEXT NOT NULL DEFAULT '',
    content_type VARCHAR(8) NOT NULL DEFAULT 'html'
);
CREATE INDEX IF NOT EXISTS idx_part_article ON article_part(article_id, sort_order);

CREATE TABLE IF NOT EXISTS article_attachment (
    id           SERIAL PRIMARY KEY,
    article_id   INT NOT NULL,
    sort_order   INT NOT NULL DEFAULT 0,
    kind         VARCHAR(8) NOT NULL DEFAULT 'file',
    name         VARCHAR(255) NOT NULL DEFAULT '',
    url          VARCHAR(1024) NOT NULL DEFAULT '',
    file_size    BIGINT NOT NULL DEFAULT 0
);
CREATE INDEX IF NOT EXISTS idx_att_article ON article_attachment(article_id, sort_order);

CREATE TABLE IF NOT EXISTS site_setting (
    skey   VARCHAR(64) PRIMARY KEY,
    svalue TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS search_log (
    id         BIGSERIAL PRIMARY KEY,
    keyword    VARCHAR(64) NOT NULL,
    client_ip  VARCHAR(64) NOT NULL,
    created_at VARCHAR(19) NOT NULL
);

CREATE TABLE IF NOT EXISTS admin_log (
    id         BIGSERIAL PRIMARY KEY,
    admin_name VARCHAR(64) NOT NULL,
    action     VARCHAR(64) NOT NULL,
    detail     VARCHAR(500) NOT NULL DEFAULT '',
    client_ip  VARCHAR(64) NOT NULL DEFAULT '',
    created_at VARCHAR(19) NOT NULL
);

CREATE TABLE IF NOT EXISTS ip_blocklist (
    id         SERIAL PRIMARY KEY,
    ip         VARCHAR(64) NOT NULL UNIQUE,
    reason     VARCHAR(255) NOT NULL DEFAULT '',
    created_at VARCHAR(19) NOT NULL
);

CREATE TABLE IF NOT EXISTS dl_ip_rule (
    id         SERIAL PRIMARY KEY,
    ip         VARCHAR(64) NOT NULL UNIQUE,
    max_conns  INT NOT NULL DEFAULT 0,
    rate_limit BIGINT NOT NULL DEFAULT 0,
    note       VARCHAR(255) NOT NULL DEFAULT '',
    created_at VARCHAR(19) NOT NULL
);

CREATE TABLE IF NOT EXISTS dl_lease (
    id      SERIAL PRIMARY KEY,
    ip      VARCHAR(64) NOT NULL,
    label   VARCHAR(120) NOT NULL DEFAULT '',
    expires BIGINT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_lease_ip ON dl_lease(ip, expires);

CREATE TABLE IF NOT EXISTS nav_category (
    id         SERIAL PRIMARY KEY,
    name       VARCHAR(60) NOT NULL,
    sort_order INT NOT NULL DEFAULT 0,
    visible    SMALLINT NOT NULL DEFAULT 1,
    created_at VARCHAR(19) NOT NULL
);

CREATE TABLE IF NOT EXISTS nav_link (
    id          SERIAL PRIMARY KEY,
    category_id INT NOT NULL DEFAULT 0,
    name        VARCHAR(120) NOT NULL,
    url         VARCHAR(500) NOT NULL,
    sort_order  INT NOT NULL DEFAULT 0,
    visible     SMALLINT NOT NULL DEFAULT 1,
    created_at  VARCHAR(19) NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_nav_visible ON nav_link(visible, sort_order);
CREATE INDEX IF NOT EXISTS idx_nav_link_cat ON nav_link(category_id, sort_order);

CREATE INDEX IF NOT EXISTS idx_sw_status ON software(status, pin_weight);
CREATE INDEX IF NOT EXISTS idx_file_sw ON software_file(software_id);
CREATE INDEX IF NOT EXISTS idx_session_admin ON session(admin_id);
CREATE INDEX IF NOT EXISTS idx_reg_attempt ON register_attempt(ip, created_at);
CREATE INDEX IF NOT EXISTS idx_log_software ON download_log(software_id);
CREATE INDEX IF NOT EXISTS idx_log_ip ON download_log(client_ip);
CREATE INDEX IF NOT EXISTS idx_log_time ON download_log(downloaded_at);
CREATE INDEX IF NOT EXISTS idx_comment_sw ON comment(software_id, status);
CREATE INDEX IF NOT EXISTS idx_comment_parent ON comment(parent_id, status);
CREATE INDEX IF NOT EXISTS idx_search_time ON search_log(created_at);
CREATE INDEX IF NOT EXISTS idx_vuln_status ON vulnerability(status, severity);
CREATE INDEX IF NOT EXISTS idx_vulnfile_vuln ON vulnerability_file(vulnerability_id);
CREATE INDEX IF NOT EXISTS idx_article_status ON article(status, created_at);
"""

DEFAULT_SETTINGS = {
    "hero_title": "找软件，来这里就够了",
    "hero_subtitle": "无需注册 · 直接下载 · 内网极速传输",
    "announcement": "",
    "footer_text": "局域网软件分享平台 · 无需注册，点击下载即用",
    "contact": "",
    "allow_comment": "1",
    # 前台列表展示风格：card（卡片式）/ list（列表式），由管理员在站点设置切换
    "front_view_mode": "card",
    # 访客打开站点根路径时默认进入的页面：software / announcements / articles / nav / ...
    "default_home": "software",
    "maintenance": "0",
    "dl_limit_enabled": "1",
    "dl_limit_conns": "8",
    "dl_limit_rate": "524288",
    "doc_max_mb": "100",
    # 投稿员上传限额（仅对 role != admin 生效）
    "submitter_max_file_mb": "100",
    "submitter_total_mb": "500",
    # 允许投稿员选择的授权方式（逗号分隔，默认不包含 paid）
    "submitter_license_allowed": "open_free,free_activation,no_activation_closed,cracked",
}


def now_str() -> str:
    return datetime.now().strftime("%Y-%m-%d %H:%M:%S")


class Conn:
    """统一 sqlite3 / pymysql / psycopg2 的最小连接包装：execute 返回可 fetch 的游标，行按列名取值"""

    def __init__(self, raw, kind):
        self._raw = raw
        self._kind = kind
        self._cur = None

    def _sql(self, sql):
        # 三种后端的占位符：sqlite 用 ?，mysql/postgres 用 %s
        return sql.replace("?", "%s") if self._kind in _SERVER_BACKENDS else sql

    def _cursor(self):
        if self._cur is None:
            self._cur = self._raw.cursor()
        return self._cur

    def execute(self, sql, params=()):
        if self._kind in _SERVER_BACKENDS:
            cur = self._cursor()
            # 参数为空时必须传 None：传 () 会让驱动仍去做 %s 插值，SQL 里的字面 % 会报错
            cur.execute(self._sql(sql), params or None)
            return cur
        return self._raw.execute(sql, params)

    def executemany(self, sql, seq):
        if self._kind in _SERVER_BACKENDS:
            cur = self._cursor()
            cur.executemany(self._sql(sql), seq)
            return cur
        return self._raw.executemany(sql, seq)

    def insert_ignore(self, sql, params=()):
        """“唯一键冲突就跳过”的插入。三种数据库语法不同，且 PostgreSQL 一旦报错整个事务就作废，
        不能靠 try/except IntegrityError 兜底，必须用各自的忽略语法。"""
        if self._kind == "postgres":
            return self.execute(sql + " ON CONFLICT DO NOTHING", params)
        if self._kind == "mysql":
            return self.execute(sql.replace("INSERT INTO", "INSERT IGNORE INTO", 1), params)
        return self.execute(sql.replace("INSERT INTO", "INSERT OR IGNORE INTO", 1), params)

    def insert_returning_id(self, sql, params=()):
        """插入一行并返回自增主键（PostgreSQL 没有 lastrowid，只能靠 RETURNING）"""
        if self._kind == "postgres":
            return self.execute(sql + " RETURNING id", params).fetchone()["id"]
        return self.execute(sql, params).lastrowid

    def commit(self):
        self._raw.commit()

    def close(self):
        self._raw.close()


def connect():
    if config.DB_TYPE == "mysql":
        kwargs = dict(
            user=config.MYSQL_USER,
            password=config.MYSQL_PASSWORD,
            database=config.MYSQL_DB,
            charset="utf8mb4",
            cursorclass=DictCursor,
            autocommit=False,
            connect_timeout=10,
        )
        if config.MYSQL_SOCKET:
            kwargs["unix_socket"] = config.MYSQL_SOCKET
        else:
            kwargs["host"] = config.MYSQL_HOST
            kwargs["port"] = config.MYSQL_PORT
        return Conn(pymysql.connect(**kwargs), "mysql")
    if config.DB_TYPE == "postgres":
        raw = psycopg2.connect(
            host=config.PG_HOST,
            port=config.PG_PORT,
            user=config.PG_USER,
            password=config.PG_PASSWORD,
            dbname=config.PG_DB,
            connect_timeout=10,
            cursor_factory=RealDictCursor,
        )
        return Conn(raw, "postgres")
    raw = sqlite3.connect(config.DB_PATH, timeout=30)
    raw.row_factory = sqlite3.Row
    raw.execute("PRAGMA foreign_keys = ON")
    raw.execute("PRAGMA journal_mode = WAL")
    return Conn(raw, "sqlite")


@contextmanager
def get_db():
    conn = connect()
    try:
        yield conn
        conn.commit()
    finally:
        conn.close()


# ---------- 站点设置 ----------

# 缓存只用来合并同一次页面渲染里的重复读取（模板会多次调用 site_settings）。
# TTL 必须很短：多 worker 各自持有独立缓存，TTL 越长管理员改完设置后访客看到旧内容的窗口就越大。
_SETTINGS_TTL = 1.0
_settings_cache = {"ts": 0.0, "data": None}


def get_settings(force=False):
    import time
    if not force and _settings_cache["data"] is not None and time.time() - _settings_cache["ts"] < _SETTINGS_TTL:
        return _settings_cache["data"]
    with get_db() as conn:
        rows = conn.execute("SELECT skey, svalue FROM site_setting").fetchall()
    data = dict(DEFAULT_SETTINGS)
    data.update({r["skey"]: r["svalue"] for r in rows})
    _settings_cache.update(ts=time.time(), data=data)
    return data


def set_setting(key, value):
    with get_db() as conn:
        conn.insert_ignore("INSERT INTO site_setting (skey, svalue) VALUES (?, ?)", (key, value))
        conn.execute("UPDATE site_setting SET svalue = ? WHERE skey = ?", (value, key))
    get_settings(force=True)


# ---------- 审计与黑名单 ----------

def log_action(conn, admin_name, action, detail="", client_ip=""):
    conn.execute(
        "INSERT INTO admin_log (admin_name, action, detail, client_ip, created_at) VALUES (?,?,?,?,?)",
        (admin_name, action, detail[:500], client_ip, now_str()),
    )


def is_ip_blocked(conn, ip):
    row = conn.execute("SELECT 1 AS x FROM ip_blocklist WHERE ip = ?", (ip,)).fetchone()
    if row:
        return True
    rows = conn.execute("SELECT ip FROM ip_blocklist WHERE ip LIKE ?", ("%.",)).fetchall()
    return any(ip.startswith(r["ip"]) for r in rows)


# ---------- 老库增量迁移 ----------

# CREATE TABLE IF NOT EXISTS 不会给已存在的表补列，这里幂等地补齐历史库缺的列。
# (表, 列, sqlite类型, mysql类型, postgres类型)
COLUMN_MIGRATIONS = [
    ("comment", "parent_id", "INTEGER", "BIGINT NULL", "BIGINT"),
    ("comment", "is_admin", "INTEGER NOT NULL DEFAULT 0", "TINYINT NOT NULL DEFAULT 0", "SMALLINT NOT NULL DEFAULT 0"),
    ("admin", "role", "TEXT NOT NULL DEFAULT 'admin'", "VARCHAR(16) NOT NULL DEFAULT 'admin'", "VARCHAR(16) NOT NULL DEFAULT 'admin'"),
    ("admin", "disabled", "INTEGER NOT NULL DEFAULT 0", "TINYINT NOT NULL DEFAULT 0", "SMALLINT NOT NULL DEFAULT 0"),
    ("software", "submitter_id", "INTEGER", "INT NULL", "INT"),
    ("software", "submitter_name", "TEXT NOT NULL DEFAULT ''", "VARCHAR(64) NOT NULL DEFAULT ''", "VARCHAR(64) NOT NULL DEFAULT ''"),
    ("software", "reviewed_at", "TEXT NOT NULL DEFAULT ''", "VARCHAR(19) NOT NULL DEFAULT ''", "VARCHAR(19) NOT NULL DEFAULT ''"),
    ("software", "review_note", "TEXT NOT NULL DEFAULT ''", "VARCHAR(255) NOT NULL DEFAULT ''", "VARCHAR(255) NOT NULL DEFAULT ''"),
    ("software", "developer", "TEXT NOT NULL DEFAULT ''", "VARCHAR(128) NOT NULL DEFAULT ''", "VARCHAR(128) NOT NULL DEFAULT ''"),
    ("software", "license_type", "TEXT NOT NULL DEFAULT ''", "VARCHAR(32) NOT NULL DEFAULT ''", "VARCHAR(32) NOT NULL DEFAULT ''"),
    ("admin", "upload_token", "TEXT NOT NULL DEFAULT ''", "VARCHAR(64) NOT NULL DEFAULT ''", "VARCHAR(64) NOT NULL DEFAULT ''"),
    ("article", "content_type", "TEXT NOT NULL DEFAULT 'md'", "VARCHAR(16) NOT NULL DEFAULT 'md'", "VARCHAR(16) NOT NULL DEFAULT 'md'"),
    ("article", "app_file_url", "TEXT NOT NULL DEFAULT ''", "VARCHAR(1024) NOT NULL DEFAULT ''", "VARCHAR(1024) NOT NULL DEFAULT ''"),
    ("nav_link", "category_id", "INTEGER", "BIGINT NULL", "INT"),
    ("admin", "id_card_hash", "TEXT", "VARCHAR(64) NULL", "VARCHAR(64)"),
    ("admin", "id_card_mask", "TEXT NOT NULL DEFAULT ''", "VARCHAR(32) NOT NULL DEFAULT ''", "VARCHAR(32) NOT NULL DEFAULT ''"),
    ("admin", "unit_dept", "TEXT NOT NULL DEFAULT ''", "VARCHAR(128) NOT NULL DEFAULT ''", "VARCHAR(128) NOT NULL DEFAULT ''"),
    ("admin", "approval_status", "TEXT NOT NULL DEFAULT 'approved'", "VARCHAR(16) NOT NULL DEFAULT 'approved'", "VARCHAR(16) NOT NULL DEFAULT 'approved'"),
    ("admin", "approval_note", "TEXT NOT NULL DEFAULT ''", "VARCHAR(255) NOT NULL DEFAULT ''", "VARCHAR(255) NOT NULL DEFAULT ''"),
    ("admin", "reviewed_at", "TEXT NOT NULL DEFAULT ''", "VARCHAR(19) NOT NULL DEFAULT ''", "VARCHAR(19) NOT NULL DEFAULT ''"),
    ("teach_video", "category_id", "INTEGER NOT NULL DEFAULT 0", "BIGINT NOT NULL DEFAULT 0", "INT NOT NULL DEFAULT 0"),
    ("photo_category", "bgm_path", "TEXT NOT NULL DEFAULT ''", "VARCHAR(500) NOT NULL DEFAULT ''", "VARCHAR(500) NOT NULL DEFAULT ''"),
    ("photo_category", "bgm_original_name", "TEXT NOT NULL DEFAULT ''", "VARCHAR(255) NOT NULL DEFAULT ''", "VARCHAR(255) NOT NULL DEFAULT ''"),
    ("photo_category", "bgm_file_size", "INTEGER NOT NULL DEFAULT 0", "BIGINT NOT NULL DEFAULT 0", "BIGINT NOT NULL DEFAULT 0"),
    ("music", "album_id", "INTEGER NOT NULL DEFAULT 0", "BIGINT NOT NULL DEFAULT 0", "INT NOT NULL DEFAULT 0"),
]

_TYPE_IDX = {"sqlite": 2, "mysql": 3, "postgres": 4}


def _existing_columns(conn, table):
    if config.DB_TYPE == "sqlite":
        return {r["name"] for r in conn.execute(f"PRAGMA table_info({table})").fetchall()}
    if config.DB_TYPE == "postgres":
        rows = conn.execute(
            "SELECT column_name AS name FROM information_schema.columns "
            "WHERE table_schema = 'public' AND table_name = ?", (table,),
        ).fetchall()
    else:
        rows = conn.execute(
            "SELECT column_name AS name FROM information_schema.columns "
            "WHERE table_schema = DATABASE() AND table_name = ?", (table,),
        ).fetchall()
    return {r["name"] for r in rows}


def migrate(conn):
    """给历史数据库补齐新列（幂等）。新表靠 schema 里的 IF NOT EXISTS 自动创建。"""
    kind = _TYPE_IDX[config.DB_TYPE]
    cache = {}
    for table, column, *ddl in COLUMN_MIGRATIONS:
        if table not in cache:
            cache[table] = _existing_columns(conn, table)
        if column not in cache[table]:
            conn.execute(f"ALTER TABLE {table} ADD COLUMN {column} {ddl[kind - 2]}")

    # content_type 需要容纳 "collection"（10 字符），老库可能是 VARCHAR(8)，幂等扩到 VARCHAR(16)
    if config.DB_TYPE == "postgres":
        conn.execute("ALTER TABLE article ALTER COLUMN content_type TYPE VARCHAR(16)")
    elif config.DB_TYPE == "mysql":
        conn.execute("ALTER TABLE article MODIFY COLUMN content_type VARCHAR(16) NOT NULL DEFAULT 'md'")

    # 网址导航升级为分类板块：没有任何分类时补一个默认分类，老链接全部挂入（幂等）
    conn.execute(
        "INSERT INTO nav_category (name, sort_order, visible, created_at) "
        "SELECT '常用网站', 0, 1, ? WHERE NOT EXISTS (SELECT 1 FROM nav_category)",
        (now_str(),),
    )
    conn.execute(
        "UPDATE nav_link SET category_id = (SELECT MIN(id) FROM nav_category) "
        "WHERE category_id IS NULL OR category_id = 0"
    )

    # 身份证哈希唯一索引：存量账号与后台手工建的账号该列为 NULL，不参与唯一约束，保持原有行为。
    # MySQL 的 CREATE INDEX 不支持 IF NOT EXISTS，先查 information_schema.statistics 再建。
    if config.DB_TYPE == "mysql":
        idx = conn.execute(
            "SELECT 1 AS x FROM information_schema.statistics "
            "WHERE table_schema = DATABASE() AND table_name = 'admin' AND index_name = 'uk_admin_idcard'"
        ).fetchone()
        if not idx:
            conn.execute("CREATE UNIQUE INDEX uk_admin_idcard ON admin (id_card_hash)")
    else:
        conn.execute("CREATE UNIQUE INDEX IF NOT EXISTS uk_admin_idcard ON admin (id_card_hash)")

    # 注册限流记录只保留最近 24 小时，避免表无限增长
    conn.execute(
        "DELETE FROM register_attempt WHERE created_at < ?",
        ((datetime.now() - timedelta(hours=24)).strftime("%Y-%m-%d %H:%M:%S"),),
    )


def init_db():
    from auth import hash_password

    config.ensure_dirs()
    import warnings
    with get_db() as dbconn, warnings.catch_warnings():
        warnings.simplefilter("ignore")  # 抑制 "Table already exists" 之类的提示
        if config.DB_TYPE == "postgres":
            # 多个 worker 同时执行 CREATE TABLE IF NOT EXISTS 会撞 pg_type 唯一索引（PG 不像
            # MySQL/SQLite 那样容忍），用事务级咨询锁把建表串行化；锁随本事务提交自动释放，
            # 后到的进程拿到锁时表已存在，IF NOT EXISTS 直接跳过
            dbconn.execute("SELECT pg_advisory_xact_lock(742024)")
        schema = {"sqlite": SCHEMA_SQLITE, "postgres": SCHEMA_POSTGRES}.get(config.DB_TYPE, SCHEMA_MYSQL)
        # 两阶段执行：先建表 → migrate() 给老库补列 → 再建索引。
        # 否则老库上索引引用的新列（如 comment.parent_id）还不存在，CREATE INDEX 直接报错
        tables, indexes = [], []
        for stmt in schema.strip().split(";\n"):
            stmt = stmt.strip()
            if stmt:
                (indexes if stmt.upper().startswith("CREATE INDEX") else tables).append(stmt)
        for stmt in tables:
            dbconn.execute(stmt)
        migrate(dbconn)  # 老库补列（幂等）
        for stmt in indexes:
            dbconn.execute(stmt)
        now = now_str()
        # 多个 uvicorn worker 会同时跑 init_db，种子数据一律用 insert_ignore 靠唯一键去重：
        # PostgreSQL 中任何语句报错都会让整个事务作废，不能像以前那样 try/except 后继续
        if dbconn.execute("SELECT COUNT(*) AS n FROM admin").fetchone()["n"] == 0:
            cur = dbconn.insert_ignore(
                "INSERT INTO admin (username, password_hash, created_at) VALUES (?, ?, ?)",
                (config.DEFAULT_ADMIN_USER, hash_password(config.DEFAULT_ADMIN_PASSWORD), now),
            )
            if cur.rowcount:
                print(f"[init] 已创建默认管理员: {config.DEFAULT_ADMIN_USER} / "
                      f"{config.DEFAULT_ADMIN_PASSWORD}（请登录后尽快修改密码）")
        if dbconn.execute("SELECT COUNT(*) AS n FROM category").fetchone()["n"] == 0:
            defaults = [
                ("办公应用", "doc", "#2f9e63"), ("开发工具", "tool", "#4f6ef7"),
                ("图形图像", "image", "#e8853d"), ("系统工具", "box", "#7a5af8"),
                ("网络应用", "network", "#0ea5e9"), ("其他", "other", "#64748b"),
            ]
            for i, (name, icon, color) in enumerate(defaults):
                dbconn.insert_ignore(
                    "INSERT INTO category (name, sort_order, visible, icon, color, created_at) VALUES (?,?,1,?,?,?)",
                    (name, i, icon, color, now),
                )
        if dbconn.execute("SELECT COUNT(*) AS n FROM site_setting").fetchone()["n"] == 0:
            for k, v in DEFAULT_SETTINGS.items():
                dbconn.insert_ignore("INSERT INTO site_setting (skey, svalue) VALUES (?, ?)", (k, v))
        if dbconn.execute("SELECT COUNT(*) AS n FROM rating_dimension").fetchone()["n"] == 0:
            for i, name in enumerate(("最受欢迎", "最有创意", "开源免费")):
                dbconn.insert_ignore(
                    "INSERT INTO rating_dimension (name, sort_order, visible, created_at) VALUES (?,?,1,?)",
                    (name, i, now),
                )
    get_settings(force=True)