TeamAAS-2.5/TeamAAS.Base/TeamAAS.Database/Services/SystemDatabase.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Security.Cryptography;
using System.Text;
using TeamAAS.Database.Interfaces;
using TeamAAS.Database.Models;
using TeamAAS.Database.Providers;

namespace TeamAAS.Database.Services
{
    /// <summary>
    /// 系统数据库（用户权限 + 操作审计）。
    /// 固定 SQLite 文件，首次运行自动建表并创建默认管理员。
    /// </summary>
    public class SystemDatabase : IDisposable
    {
        private readonly IDatabase _db;
        private readonly string _dbPath;

        /// <summary>
        /// 数据库实例
        /// </summary>
        public IDatabase Database => _db;

        /// <summary>
        /// 是否连接成功
        /// </summary>
        public bool IsConnected => _db?.IsConnected == true;

        /// <summary>
        /// 构造系统数据库
        /// </summary>
        /// <param name="configDir">配置目录（如 Config\），数据库文件放这里</param>
        public SystemDatabase(string configDir)
        {
            if (string.IsNullOrWhiteSpace(configDir))
                throw new ArgumentNullException(nameof(configDir));

            _dbPath = Path.Combine(configDir, "system.db");

            var config = new DatabaseConfig
            {
                Id = Guid.Parse("11111111-1111-1111-1111-111111111111"),
                Name = "SystemDB",
                ProviderType = "sqlite",
                Server = _dbPath
            };

            _db = new SqliteDatabase();
            _db.Configure(config);
        }

        /// <summary>
        /// 初始化：打开连接 + 自动建表 + 确保默认管理员存在
        /// </summary>
        public (bool Success, string Message) Initialize()
        {
            try
            {
                var dir = Path.GetDirectoryName(_dbPath);
                if (!string.IsNullOrWhiteSpace(dir) && !Directory.Exists(dir))
                    Directory.CreateDirectory(dir);

                if (!_db.Open())
                    return (false, "系统数据库打开失败");

                CreateTables();
                EnsureDefaultAdmin();

                return (true, "系统数据库初始化成功");
            }
            catch (Exception ex)
            {
                return (false, $"系统数据库初始化失败：{ex.Message}");
            }
        }

        #region 建表

        private void CreateTables()
        {
            var sqls = new[]
            {
                // 角色表
                @"CREATE TABLE IF NOT EXISTS sys_roles (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    role_name TEXT NOT NULL UNIQUE,
                    display_name TEXT,
                    description TEXT,
                    is_enabled INTEGER DEFAULT 1,
                    created_at TEXT DEFAULT (datetime('now','localtime')),
                    updated_at TEXT DEFAULT (datetime('now','localtime'))
                )",

                // 权限表
                @"CREATE TABLE IF NOT EXISTS sys_permissions (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    permission_key TEXT NOT NULL UNIQUE,
                    display_name TEXT,
                    category TEXT,
                    description TEXT,
                    created_at TEXT DEFAULT (datetime('now','localtime'))
                )",

                // 用户表
                @"CREATE TABLE IF NOT EXISTS sys_users (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    username TEXT NOT NULL UNIQUE,
                    password_hash TEXT NOT NULL,
                    salt TEXT NOT NULL,
                    display_name TEXT,
                    email TEXT,
                    is_enabled INTEGER DEFAULT 1,
                    is_locked INTEGER DEFAULT 0,
                    login_failed_count INTEGER DEFAULT 0,
                    last_login_at TEXT,
                    created_at TEXT DEFAULT (datetime('now','localtime')),
                    updated_at TEXT DEFAULT (datetime('now','localtime'))
                )",

                // 用户-角色关联
                @"CREATE TABLE IF NOT EXISTS sys_user_roles (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    user_id INTEGER NOT NULL,
                    role_id INTEGER NOT NULL,
                    created_at TEXT DEFAULT (datetime('now','localtime')),
                    UNIQUE(user_id, role_id)
                )",

                // 角色-权限关联
                @"CREATE TABLE IF NOT EXISTS sys_role_permissions (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    role_id INTEGER NOT NULL,
                    permission_id INTEGER NOT NULL,
                    created_at TEXT DEFAULT (datetime('now','localtime')),
                    UNIQUE(role_id, permission_id)
                )",

                // 审计日志
                @"CREATE TABLE IF NOT EXISTS sys_audit_logs (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    user_id INTEGER,
                    username TEXT,
                    action_type TEXT NOT NULL,
                    action_detail TEXT,
                    module TEXT,
                    ip_address TEXT,
                    result INTEGER DEFAULT 1,
                    error_message TEXT,
                    created_at TEXT DEFAULT (datetime('now','localtime'))
                )",

                // 索引
                "CREATE INDEX IF NOT EXISTS idx_audit_user ON sys_audit_logs(username)",
                "CREATE INDEX IF NOT EXISTS idx_audit_action ON sys_audit_logs(action_type)",
                "CREATE INDEX IF NOT EXISTS idx_audit_time ON sys_audit_logs(created_at)",
                "CREATE INDEX IF NOT EXISTS idx_user_roles_uid ON sys_user_roles(user_id)",
                "CREATE INDEX IF NOT EXISTS idx_role_perm_rid ON sys_role_permissions(role_id)"
            };

            _db.ExecuteTransaction(sqls);
        }

        #endregion

        #region 默认数据

        /// <summary>
        /// 确保默认管理员账号存在（admin / admin123）
        /// </summary>
        private void EnsureDefaultAdmin()
        {
            var count = Convert.ToInt32(_db.ExecuteScalar(
                "SELECT COUNT(*) FROM sys_users WHERE username = 'admin'"));

            if (count > 0) return;

            // 默认角色
            var roleId = Convert.ToInt32(_db.ExecuteScalar(
                "SELECT id FROM sys_roles WHERE role_name = 'admin'"));
            if (roleId == 0)
            {
                _db.ExecuteNonQuery(
                    "INSERT INTO sys_roles (role_name, display_name, description) VALUES ('admin', '系统管理员', '拥有全部权限')");
                roleId = Convert.ToInt32(_db.ExecuteScalar("SELECT last_insert_rowid()"));
            }

            // 默认管理员
            var salt = GenerateSalt();
            var hash = HashPassword("admin123", salt);

            var userParams = new Dictionary<string, object>
            {
                {"@username", "admin"},
                {"@password_hash", hash},
                {"@salt", salt},
                {"@display_name", "系统管理员"}
            };
            _db.ExecuteNonQuery(
                "INSERT INTO sys_users (username, password_hash, salt, display_name) VALUES (@username, @password_hash, @salt, @display_name)",
                userParams);

            var userId = Convert.ToInt32(_db.ExecuteScalar("SELECT last_insert_rowid()"));

            // 关联角色
            var relateParams = new Dictionary<string, object>
            {
                {"@user_id", userId},
                {"@role_id", roleId}
            };
            _db.ExecuteNonQuery(
                "INSERT OR IGNORE INTO sys_user_roles (user_id, role_id) VALUES (@user_id, @role_id)",
                relateParams);

            // 默认权限（种子数据）
            SeedPermissions();
        }

        private void SeedPermissions()
        {
            var perms = new (string Key, string Name, string Category)[]
            {
                ("system.user.view", "查看用户", "用户管理"),
                ("system.user.manage", "管理用户", "用户管理"),
                ("system.role.view", "查看角色", "角色权限"),
                ("system.role.manage", "管理角色", "角色权限"),
                ("system.audit.view", "查看审计日志", "系统审计"),
                ("product.view", "查看产品", "产品管理"),
                ("product.manage", "管理产品", "产品管理"),
                ("recipe.view", "查看配方", "配方管理"),
                ("recipe.manage", "管理配方", "配方管理"),
                ("device.control", "设备控制", "设备操作"),
                ("flow.edit", "编辑流程", "流程编辑"),
                ("flow.run", "运行流程", "流程运行"),
                ("system.config", "系统配置", "系统设置")
            };

            // 管理员角色ID
            var roleId = Convert.ToInt32(_db.ExecuteScalar(
                "SELECT id FROM sys_roles WHERE role_name = 'admin'"));

            foreach (var p in perms)
            {
                var pms = new Dictionary<string, object>
                {
                    {"@k", p.Key}, {"@n", p.Name}, {"@c", p.Category}
                };
                _db.ExecuteNonQuery(
                    "INSERT OR IGNORE INTO sys_permissions (permission_key, display_name, category) VALUES (@k, @n, @c)",
                    pms);

                var permId = Convert.ToInt32(_db.ExecuteScalar(
                    "SELECT id FROM sys_permissions WHERE permission_key = @k",
                    new Dictionary<string, object> {{"@k", p.Key}}));

                var rpm = new Dictionary<string, object>
                {
                    {"@rid", roleId}, {"@pid", permId}
                };
                _db.ExecuteNonQuery(
                    "INSERT OR IGNORE INTO sys_role_permissions (role_id, permission_id) VALUES (@rid, @pid)",
                    rpm);
            }
        }

        #endregion

        #region 用户认证

        /// <summary>
        /// 验证用户名密码
        /// </summary>
        public (bool Success, int UserId, string Message) ValidateUser(string username, string password)
        {
            if (string.IsNullOrWhiteSpace(username) || string.IsNullOrWhiteSpace(password))
                return (false, 0, "用户名或密码不能为空");

            var dt = _db.ExecuteQuery(
                "SELECT id, password_hash, salt, is_enabled, is_locked FROM sys_users WHERE username = @u",
                new Dictionary<string, object> {{"@u", username}});

            if (dt.Rows.Count == 0)
                return (false, 0, "用户不存在");

            var row = dt.Rows[0];
            var userId = Convert.ToInt32(row["id"]);
            var hash = row["password_hash"].ToString();
            var salt = row["salt"].ToString();
            var enabled = Convert.ToInt32(row["is_enabled"]) == 1;
            var locked = Convert.ToInt32(row["is_locked"]) == 1;

            if (!enabled) return (false, userId, "账号已禁用");
            if (locked) return (false, userId, "账号已锁定");

            var inputHash = HashPassword(password, salt);
            if (inputHash != hash)
            {
                // 失败次数+1
                _db.ExecuteNonQuery(
                    "UPDATE sys_users SET login_failed_count = login_failed_count + 1 WHERE id = @id",
                    new Dictionary<string, object> {{"@id", userId}});
                return (false, userId, "密码错误");
            }

            // 登录成功，清失败计数，写登录时间
            var up = new Dictionary<string, object> {{"@id", userId}};
            _db.ExecuteNonQuery(
                "UPDATE sys_users SET login_failed_count = 0, last_login_at = datetime('now','localtime') WHERE id = @id",
                up);

            return (true, userId, "登录成功");
        }

        /// <summary>
        /// 获取用户的所有权限 key
        /// </summary>
        public List<string> GetUserPermissions(int userId)
        {
            var result = new List<string>();
            var sql = @"
                SELECT DISTINCT p.permission_key
                FROM sys_permissions p
                INNER JOIN sys_role_permissions rp ON p.id = rp.permission_id
                INNER JOIN sys_user_roles ur ON rp.role_id = ur.role_id
                WHERE ur.user_id = @uid";

            var dt = _db.ExecuteQuery(sql, new Dictionary<string, object> {{"@uid", userId}});
            foreach (DataRow row in dt.Rows)
                result.Add(row["permission_key"].ToString());

            return result;
        }

        /// <summary>
        /// 检查用户是否有指定权限
        /// </summary>
        public bool HasPermission(int userId, string permissionKey)
        {
            var sql = @"
                SELECT COUNT(*)
                FROM sys_permissions p
                INNER JOIN sys_role_permissions rp ON p.id = rp.permission_id
                INNER JOIN sys_user_roles ur ON rp.role_id = ur.role_id
                WHERE ur.user_id = @uid AND p.permission_key = @pk";

            var count = Convert.ToInt32(_db.ExecuteQuery(sql,
                new Dictionary<string, object> {{"@uid", userId}, {"@pk", permissionKey}}).Rows[0][0]);

            return count > 0;
        }

        #endregion

        #region 审计日志

        /// <summary>
        /// 写入审计日志
        /// </summary>
        public void WriteAuditLog(string username, string actionType, string actionDetail,
            string module = null, string ipAddress = null, bool success = true, string errorMessage = null)
        {
            try
            {
                var sql = @"INSERT INTO sys_audit_logs
                    (username, action_type, action_detail, module, ip_address, result, error_message)
                    VALUES (@u, @a, @d, @m, @ip, @r, @e)";

                var p = new Dictionary<string, object>
                {
                    {"@u", (object)username ?? DBNull.Value},
                    {"@a", actionType},
                    {"@d", (object)actionDetail ?? DBNull.Value},
                    {"@m", (object)module ?? DBNull.Value},
                    {"@ip", (object)ipAddress ?? DBNull.Value},
                    {"@r", success ? 1 : 0},
                    {"@e", (object)errorMessage ?? DBNull.Value}
                };

                _db.ExecuteNonQuery(sql, p);
            }
            catch
            {
                // 审计日志写入失败不影响主流程
            }
        }

        /// <summary>
        /// 查询审计日志（分页）
        /// </summary>
        public (DataTable Rows, int TotalCount) QueryAuditLogs(
            string username = null, string actionType = null, string module = null,
            DateTime? startTime = null, DateTime? endTime = null,
            int pageIndex = 1, int pageSize = 50)
        {
            var conditions = new List<string>();
            var parameters = new Dictionary<string, object>();

            if (!string.IsNullOrWhiteSpace(username))
            {
                conditions.Add("username LIKE @u");
                parameters["@u"] = $"%{username}%";
            }
            if (!string.IsNullOrWhiteSpace(actionType))
            {
                conditions.Add("action_type = @a");
                parameters["@a"] = actionType;
            }
            if (!string.IsNullOrWhiteSpace(module))
            {
                conditions.Add("module = @m");
                parameters["@m"] = module;
            }
            if (startTime.HasValue)
            {
                conditions.Add("created_at >= @st");
                parameters["@st"] = startTime.Value.ToString("yyyy-MM-dd HH:mm:ss");
            }
            if (endTime.HasValue)
            {
                conditions.Add("created_at <= @et");
                parameters["@et"] = endTime.Value.ToString("yyyy-MM-dd HH:mm:ss");
            }

            var where = conditions.Count > 0 ? "WHERE " + string.Join(" AND ", conditions) : "";

            var totalCount = Convert.ToInt32(
                _db.ExecuteScalar($"SELECT COUNT(*) FROM sys_audit_logs {where}", parameters));

            var offset = (pageIndex - 1) * pageSize;
            var dataSql = $@"
                SELECT * FROM sys_audit_logs {where}
                ORDER BY id DESC
                LIMIT @limit OFFSET @offset";

            parameters["@limit"] = pageSize;
            parameters["@offset"] = offset;

            var dt = _db.ExecuteQuery(dataSql, parameters);
            return (dt, totalCount);
        }

        #endregion

        #region 密码工具

        private static string GenerateSalt()
        {
            using (var rng = new RNGCryptoServiceProvider())
            {
                var bytes = new byte[16];
                rng.GetBytes(bytes);
                return Convert.ToBase64String(bytes);
            }
        }

        private static string HashPassword(string password, string salt)
        {
            using (var sha256 = SHA256.Create())
            {
                var combined = Encoding.UTF8.GetBytes(password + salt);
                var hash = sha256.ComputeHash(combined);
                var sb = new StringBuilder();
                foreach (var b in hash)
                    sb.Append(b.ToString("x2"));
                return sb.ToString();
            }
        }

        #endregion

        #region IDisposable

        private bool _disposed;

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        protected virtual void Dispose(bool disposing)
        {
            if (_disposed) return;
            if (disposing)
            {
                try { _db?.Dispose(); } catch { }
            }
            _disposed = true;
        }

        ~SystemDatabase()
        {
            Dispose(false);
        }

        #endregion
    }
}