find_package(Qt6 REQUIRED COMPONENTS Sql)
target_link_libraries(myapp PRIVATE Qt6::Sql)

// database.h
#ifndef DATABASE_H
#define DATABASE_H

#include <QSqlDatabase>
#include <QSqlQuery>
#include <QSqlError>
#include <QSqlRecord>
#include <QDebug>
#include <QStandardPaths>
#include <QDir>

class Database {
public:
    static bool initialize() {
        // 创建 SQLite 数据库
        QString dbPath = QStandardPaths::writableLocation(
            QStandardPaths::AppDataLocation) + "/myapp.db";
        QDir().mkpath(QFileInfo(dbPath).absolutePath());

        QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
        db.setDatabaseName(dbPath);

        if (!db.open()) {
            qCritical() << "Database error:" << db.lastError().text();
            return false;
        }

        qDebug() << "Database opened:" << dbPath;

        // 启用外键约束
        QSqlQuery query;
        query.exec("PRAGMA foreign_keys = ON");

        // 创建表
        createTables();
        return true;
    }

    static void createTables() {
        QSqlQuery query;

        // 用户表
        query.exec(R"(
            CREATE TABLE IF NOT EXISTS users (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL,
                email TEXT UNIQUE NOT NULL,
                age INTEGER DEFAULT 0,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP
            )
        )");

        // 文章表（外键关联用户）
        query.exec(R"(
            CREATE TABLE IF NOT EXISTS articles (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id INTEGER NOT NULL,
                title TEXT NOT NULL,
                content TEXT,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
            )
        )");

        // 索引
        query.exec("CREATE INDEX IF NOT EXISTS idx_articles_user "
                   "ON articles(user_id)");
    }
};

#endif // DATABASE_H

#!/usr/bin/env python3
"""数据库连接示例 - PySide6"""

import sys
from pathlib import Path
from PySide6.QtSql import QSqlDatabase, QSqlQuery
from PySide6.QtWidgets import QApplication


def init_database() -> QSqlDatabase:
    """初始化 SQLite 数据库"""
    db_path = Path.home() / ".myapp" / "data.db"
    db_path.parent.mkdir(parents=True, exist_ok=True)

    db = QSqlDatabase.addDatabase("QSQLITE")
    db.setDatabaseName(str(db_path))

    if not db.open():
        print(f"Error: {db.lastError().text()}")
        return db

    print(f"Database opened: {db_path}")

    query = QSqlQuery()
    query.exec("PRAGMA foreign_keys = ON")

    query.exec("""
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            email TEXT UNIQUE NOT NULL,
            age INTEGER DEFAULT 0,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )
    """)

    return db

// crud.h - 完整 CRUD 操作
#ifndef CRUD_H
#define CRUD_H

#include <QSqlQuery>
#include <QSqlError>
#include <QVariantList>
#include <QDebug>
#include <QJsonObject>

class UserCrud {
public:
    // CREATE - 创建
    static int create(const QString &name, const QString &email, int age) {
        QSqlQuery query;
        query.prepare("INSERT INTO users (name, email, age) VALUES (?, ?, ?)");
        query.addBindValue(name);
        query.addBindValue(email);
        query.addBindValue(age);

        if (!query.exec()) {
            qCritical() << "Insert error:" << query.lastError().text();
            return -1;
        }
        return query.lastInsertId().toInt();
    }

    // READ - 读取单个
    static QJsonObject read(int id) {
        QSqlQuery query;
        query.prepare("SELECT * FROM users WHERE id = ?");
        query.addBindValue(id);

        if (query.exec() && query.next()) {
            QJsonObject obj;
            obj["id"] = query.value("id").toInt();
            obj["name"] = query.value("name").toString();
            obj["email"] = query.value("email").toString();
            obj["age"] = query.value("age").toInt();
            obj["created_at"] = query.value("created_at").toString();
            return obj;
        }
        return {};
    }

    // READ ALL - 读取全部
    static QList<QJsonObject> readAll() {
        QList<QJsonObject> results;
        QSqlQuery query("SELECT * FROM users ORDER BY id DESC");

        while (query.next()) {
            QJsonObject obj;
            obj["id"] = query.value("id").toInt();
            obj["name"] = query.value("name").toString();
            obj["email"] = query.value("email").toString();
            obj["age"] = query.value("age").toInt();
            results.append(obj);
        }
        return results;
    }

    // UPDATE - 更新
    static bool update(int id, const QString &name,
                       const QString &email, int age) {
        QSqlQuery query;
        query.prepare("UPDATE users SET name=?, email=?, age=? WHERE id=?");
        query.addBindValue(name);
        query.addBindValue(email);
        query.addBindValue(age);
        query.addBindValue(id);

        if (!query.exec()) {
            qCritical() << "Update error:" << query.lastError().text();
            return false;
        }
        return query.numRowsAffected() > 0;
    }

    // DELETE - 删除
    static bool remove(int id) {
        QSqlQuery query;
        query.prepare("DELETE FROM users WHERE id = ?");
        query.addBindValue(id);

        if (!query.exec()) {
            qCritical() << "Delete error:" << query.lastError().text();
            return false;
        }
        return query.numRowsAffected() > 0;
    }

    // 搜索
    static QList<QJsonObject> search(const QString &keyword) {
        QList<QJsonObject> results;
        QSqlQuery query;
        query.prepare("SELECT * FROM users WHERE name LIKE ? OR email LIKE ?");
        QString pattern = "%" + keyword + "%";
        query.addBindValue(pattern);
        query.addBindValue(pattern);

        while (query.exec() && query.next()) {
            QJsonObject obj;
            obj["id"] = query.value("id").toInt();
            obj["name"] = query.value("name").toString();
            obj["email"] = query.value("email").toString();
            results.append(obj);
        }
        return results;
    }

    // 事务操作
    static bool batchCreate(const QList<QJsonObject> &users) {
        QSqlDatabase::database().transaction();

        for (const auto &user : users) {
            int id = create(user["name"].toString(),
                          user["email"].toString(),
                          user["age"].toInt());
            if (id < 0) {
                QSqlDatabase::database().rollback();
                return false;
            }
        }

        return QSqlDatabase::database().commit();
    }
};

#endif // CRUD_H

// modelview.h
#ifndef MODELVIEW_H
#define MODELVIEW_H

#include <QWidget>
#include <QSqlTableModel>
#include <QTableView>
#include <QHeaderView>
#include <QVBoxLayout>
#include <QHBoxLayout>
#include <QPushButton>
#include <QLineEdit>
#include <QMessageBox>
#include <QSortFilterProxyModel>

class UserTableWidget : public QWidget {
    Q_OBJECT

public:
    explicit UserTableWidget(QWidget *parent = nullptr) : QWidget(parent) {
        auto *layout = new QVBoxLayout(this);

        // 工具栏
        auto *toolbar = new QHBoxLayout;
        m_searchEdit = new QLineEdit;
        m_searchEdit->setPlaceholderText("搜索 / Search...");
        auto *addBtn = new QPushButton("添加 / Add");
        auto *delBtn = new QPushButton("删除 / Delete");
        auto *refreshBtn = new QPushButton("刷新 / Refresh");

        toolbar->addWidget(m_searchEdit);
        toolbar->addWidget(addBtn);
        toolbar->addWidget(delBtn);
        toolbar->addWidget(refreshBtn);
        layout->addLayout(toolbar);

        // 模型
        m_model = new QSqlTableModel(this);
        m_model->setTable("users");
        m_model->setEditStrategy(QSqlTableModel::OnFieldChange);
        m_model->setHeaderData(0, Qt::Horizontal, "ID");
        m_model->setHeaderData(1, Qt::Horizontal, "姓名 / Name");
        m_model->setHeaderData(2, Qt::Horizontal, "邮箱 / Email");
        m_model->setHeaderData(3, Qt::Horizontal, "年龄 / Age");
        m_model->setHeaderData(4, Qt::Horizontal, "创建时间 / Created");
        m_model->select();

        // 排序过滤代理
        m_proxy = new QSortFilterProxyModel(this);
        m_proxy->setSourceModel(m_model);
        m_proxy->setFilterCaseSensitivity(Qt::CaseInsensitive);
        m_proxy->setFilterKeyColumn(-1);  // 搜索所有列

        // 视图
        m_view = new QTableView;
        m_view->setModel(m_proxy);
        m_view->setSortingEnabled(true);
        m_view->horizontalHeader()->setStretchLastSection(true);
        m_view->setSelectionBehavior(QAbstractItemView::SelectRows);
        m_view->setAlternatingRowColors(true);
        m_view->setColumnHidden(0, true);  // 隐藏 ID 列
        layout->addWidget(m_view);

        // 连接信号
        connect(addBtn, &QPushButton::clicked, this, &UserTableWidget::addUser);
        connect(delBtn, &QPushButton::clicked, this, &UserTableWidget::deleteUser);
        connect(refreshBtn, &QPushButton::clicked, m_model, &QSqlTableModel::select);
        connect(m_searchEdit, &QLineEdit::textChanged,
                m_proxy, &QSortFilterProxyModel::setFilterFixedString);
    }

private slots:
    void addUser() {
        int row = m_model->rowCount();
        m_model->insertRow(row);
        m_model->setData(m_model->index(row, 1), "新用户");
        m_model->setData(m_model->index(row, 2), "[email protected]");
        m_model->setData(m_model->index(row, 3), 0);
        m_model->submitAll();
    }

    void deleteUser() {
        QModelIndex index = m_view->currentIndex();
        if (!index.isValid()) {
            QMessageBox::warning(this, "提示", "请先选择一行 / Select a row first");
            return;
        }

        int sourceRow = m_proxy->mapToSource(index).row();
        if (QMessageBox::question(this, "确认",
                "确定要删除吗？/ Confirm delete?") == QMessageBox::Yes) {
            m_model->removeRow(sourceRow);
            m_model->submitAll();
            m_model->select();
        }
    }

private:
    QSqlTableModel *m_model;
    QSortFilterProxyModel *m_proxy;
    QTableView *m_view;
    QLineEdit *m_searchEdit;
};

#endif // MODELVIEW_H

// custommodel.h - 自定义 QAbstractTableModel
#ifndef CUSTOMMODEL_H
#define CUSTOMMODEL_H

#include <QAbstractTableModel>
#include <QJsonObject>
#include <QJsonArray>

class UserModel : public QAbstractTableModel {
    Q_OBJECT

public:
    enum Column { ColId = 0, ColName, ColEmail, ColAge, ColCount };

    explicit UserModel(QObject *parent = nullptr)
        : QAbstractTableModel(parent) {}

    // 必须重写的虚函数
    int rowCount(const QModelIndex & = QModelIndex()) const override {
        return m_data.size();
    }

    int columnCount(const QModelIndex & = QModelIndex()) const override {
        return ColCount;
    }

    QVariant data(const QModelIndex &index, int role) const override {
        if (!index.isValid() || index.row() >= m_data.size())
            return {};

        const auto &user = m_data[index.row()];

        if (role == Qt::DisplayRole || role == Qt::EditRole) {
            switch (index.column()) {
                case ColId:    return user["id"].toInt();
                case ColName:  return user["name"].toString();
                case ColEmail: return user["email"].toString();
                case ColAge:   return user["age"].toInt();
            }
        }

        if (role == Qt::TextAlignmentRole) {
            if (index.column() == ColAge)
                return Qt::AlignCenter;
        }

        return {};
    }

    QVariant headerData(int section, Qt::Orientation orientation,
                        int role) const override {
        if (role != Qt::DisplayRole || orientation != Qt::Horizontal)
            return {};

        switch (section) {
            case ColId:    return "ID";
            case ColName:  return "姓名 / Name";
            case ColEmail: return "邮箱 / Email";
            case ColAge:   return "年龄 / Age";
        }
        return {};
    }

    // 编辑支持
    Qt::ItemFlags flags(const QModelIndex &index) const override {
        Qt::ItemFlags f = QAbstractTableModel::flags(index);
        if (index.column() != ColId)
            f |= Qt::ItemIsEditable;
        return f;
    }

    bool setData(const QModelIndex &index, const QVariant &value,
                 int role = Qt::EditRole) override {
        if (!index.isValid() || role != Qt::EditRole)
            return false;

        auto &user = m_data[index.row()];
        switch (index.column()) {
            case ColName:  user["name"] = value.toString(); break;
            case ColEmail: user["email"] = value.toString(); break;
            case ColAge:   user["age"] = value.toInt(); break;
            default: return false;
        }

        emit dataChanged(index, index, {role});
        return true;
    }

    // 添加/删除行
    void addUser(const QJsonObject &user) {
        beginInsertRows(QModelIndex(), m_data.size(), m_data.size());
        m_data.append(user);
        endInsertRows();
    }

    void removeRow(int row) {
        if (row < 0 || row >= m_data.size()) return;
        beginRemoveRows(QModelIndex(), row, row);
        m_data.removeAt(row);
        endRemoveRows();
    }

    void setUsers(const QJsonArray &users) {
        beginResetModel();
        m_data.clear();
        for (const auto &u : users)
            m_data.append(u.toObject());
        endResetModel();
    }

private:
    QList<QJsonObject> m_data;
};

#endif // CUSTOMMODEL_H

// spinboxdelegate.h - 自定义年龄编辑委托
#ifndef SPINBOXDELEGATE_H
#define SPINBOXDELEGATE_H

#include <QStyledItemDelegate>
#include <QSpinBox>

class SpinBoxDelegate : public QStyledItemDelegate {
    Q_OBJECT

public:
    explicit SpinBoxDelegate(int min = 0, int max = 150, QObject *parent = nullptr)
        : QStyledItemDelegate(parent), m_min(min), m_max(max) {}

    QWidget *createEditor(QWidget *parent, const QStyleOptionViewItem &,
                          const QModelIndex &) const override {
        auto *editor = new QSpinBox(parent);
        editor->setRange(m_min, m_max);
        editor->setFrame(false);
        return editor;
    }

    void setEditorData(QWidget *editor, const QModelIndex &index) const override {
        int value = index.data(Qt::EditRole).toInt();
        auto *spinBox = qobject_cast<QSpinBox*>(editor);
        if (spinBox) spinBox->setValue(value);
    }

    void setModelData(QWidget *editor, QAbstractItemModel *model,
                      const QModelIndex &index) const override {
        auto *spinBox = qobject_cast<QSpinBox*>(editor);
        if (spinBox) {
            model->setData(index, spinBox->value(), Qt::EditRole);
        }
    }

private:
    int m_min, m_max;
};

#endif // SPINBOXDELEGATE_H

// 使用自定义委托
m_view->setItemDelegateForColumn(UserModel::ColAge, new SpinBoxDelegate(0, 150, this));