import sqlite3
import sys
from contextlib import contextmanager
from datetime import datetime

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 '',
    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 '',
    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 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_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 '',
    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 '',
    created_at    VARCHAR(19) NOT NULL
) 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 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 '',
    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 '',
    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 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_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",
    "maintenance": "0",
    "dl_limit_enabled": "1",
    "dl_limit_conns": "8",
    "dl_limit_rate": "524288",
    "doc_max_mb": "100",
}


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 ''"),
    ("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"),
]

_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"
    )


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))
    get_settings(force=True)