DatabaseSetup.cs

using System;
using System.Data.SQLite;
using System.Security.Cryptography;
using System.Text;

namespace PreschoolManagement.Database
{
    public static class DatabaseSetup
    {
        public static void InitializeDatabase(DatabaseManager dbManager)
        {
            CreateTables(dbManager);
            InsertSampleData(dbManager);
        }

        private static void CreateTables(DatabaseManager dbManager)
        {
            // Create Users table
            string createUsersTable = @"
                CREATE TABLE IF NOT EXISTS Users (
                    UserId INTEGER PRIMARY KEY AUTOINCREMENT,
                    Username TEXT UNIQUE NOT NULL,
                    PasswordHash TEXT NOT NULL,
                    FirstName TEXT NOT NULL,
                    LastName TEXT NOT NULL,
                    Email TEXT,
                    Phone TEXT,
                    Role TEXT NOT NULL,
                    CreatedDate DATETIME DEFAULT CURRENT_TIMESTAMP
                );";
            dbManager.ExecuteNonQuery(createUsersTable);

            // Create Children table
            string createChildrenTable = @"
                CREATE TABLE IF NOT EXISTS Children (
                    ChildId INTEGER PRIMARY KEY AUTOINCREMENT,
                    FirstName TEXT NOT NULL,
                    LastName TEXT NOT NULL,
                    BirthDate DATETIME NOT NULL,
                    Gender TEXT,
                    SpecialNotes TEXT,
                    GroupId INTEGER,
                    CreatedDate DATETIME DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY (GroupId) REFERENCES Groups(GroupId)
                );";
            dbManager.ExecuteNonQuery(createChildrenTable);

            // Create Groups table
            string createGroupsTable = @"
                CREATE TABLE IF NOT EXISTS Groups (
                    GroupId INTEGER PRIMARY KEY AUTOINCREMENT,
                    GroupName TEXT NOT NULL,
                    TeacherId INTEGER,
                    Capacity INTEGER,
                    Description TEXT,
                    CreatedDate DATETIME DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY (TeacherId) REFERENCES Users(UserId)
                );";
            dbManager.ExecuteNonQuery(createGroupsTable);

            // Create ParentChild relationships table
            string createParentChildTable = @"
                CREATE TABLE IF NOT EXISTS ParentChild (
                    ParentId INTEGER NOT NULL,
                    ChildId INTEGER NOT NULL,
                    Relationship TEXT,
                    IsEmergencyContact INTEGER DEFAULT 0,
                    PRIMARY KEY (ParentId, ChildId),
                    FOREIGN KEY (ParentId) REFERENCES Users(UserId),
                    FOREIGN KEY (ChildId) REFERENCES Children(ChildId)
                );";
            dbManager.ExecuteNonQuery(createParentChildTable);
           

            // Create SystemLogs table
            string createLogsTable = @"
                CREATE TABLE IF NOT EXISTS SystemLogs (
                    LogId INTEGER PRIMARY KEY AUTOINCREMENT,
                    EventType TEXT NOT NULL,
                    UserId INTEGER,
                    Description TEXT NOT NULL,
                    LogDate DATETIME DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY (UserId) REFERENCES Users(UserId)
                );";
            dbManager.ExecuteNonQuery(createLogsTable);
        }

        private static void InsertSampleData(DatabaseManager dbManager)
        {
            // Hash function for passwords
            string HashPassword(string password)
            {
                using (SHA256 sha256 = SHA256.Create())
                {
                    byte[] bytes = sha256.ComputeHash(Encoding.UTF8.GetBytes(password));
                    return Convert.ToBase64String(bytes);
                }
            }

            // Add sample users
            string insertUserSql = @"
                INSERT INTO Users (Username, PasswordHash, FirstName, LastName, Email, Phone, Role)
                VALUES (@Username, @PasswordHash, @FirstName, @LastName, @Email, @Phone, @Role);";

            // Admin user
            dbManager.ExecuteNonQuery(insertUserSql,
                new SQLiteParameter("@Username", "admin"),
                new SQLiteParameter("@PasswordHash", HashPassword("admin")),
                new SQLiteParameter("@FirstName", "Admin"),
                new SQLiteParameter("@LastName", "System"),
                new SQLiteParameter("@Email", "admin@preschool.com"),
                new SQLiteParameter("@Phone", "123456789"),
                new SQLiteParameter("@Role", "Administrator"));

            // Teacher user
            dbManager.ExecuteNonQuery(insertUserSql,
                new SQLiteParameter("@Username", "teacher"),
                new SQLiteParameter("@PasswordHash", HashPassword("teacher")),
                new SQLiteParameter("@FirstName", "Anna"),
                new SQLiteParameter("@LastName", "Kowalska"),
                new SQLiteParameter("@Email", "anna@preschool.com"),
                new SQLiteParameter("@Phone", "987654321"),
                new SQLiteParameter("@Role", "Teacher"));

            // Parent user
            dbManager.ExecuteNonQuery(insertUserSql,
                new SQLiteParameter("@Username", "parent"),
                new SQLiteParameter("@PasswordHash", HashPassword("parent")),
                new SQLiteParameter("@FirstName", "Jan"),
                new SQLiteParameter("@LastName", "Nowak"),
                new SQLiteParameter("@Email", "jan@example.com"),
                new SQLiteParameter("@Phone", "555666777"),
                new SQLiteParameter("@Role", "Parent"));

            // Add sample group
            string insertGroupSql = @"
                INSERT INTO Groups (GroupName, TeacherId, Capacity, Description)
                VALUES (@GroupName, @TeacherId, @Capacity, @Description);";

            int teacherId = Convert.ToInt32(dbManager.ExecuteScalar("SELECT UserId FROM Users WHERE Username = 'teacher'"));

            dbManager.ExecuteNonQuery(insertGroupSql,
                new SQLiteParameter("@GroupName", "Słoneczka"),
                new SQLiteParameter("@TeacherId", teacherId),
                new SQLiteParameter("@Capacity", 15),
                new SQLiteParameter("@Description", "Grupa dzieci 4-5 lat"));

            // Add sample child
            string insertChildSql = @"
                INSERT INTO Children (FirstName, LastName, BirthDate, Gender, SpecialNotes, GroupId)
                VALUES (@FirstName, @LastName, @BirthDate, @Gender, @SpecialNotes, @GroupId);";

            int groupId = Convert.ToInt32(dbManager.ExecuteScalar("SELECT GroupId FROM Groups WHERE GroupName = 'Słoneczka'"));

            dbManager.ExecuteNonQuery(insertChildSql,
                new SQLiteParameter("@FirstName", "Zosia"),
                new SQLiteParameter("@LastName", "Nowak"),
                new SQLiteParameter("@BirthDate", DateTime.Parse("2019-05-15")),
                new SQLiteParameter("@Gender", "Dziewczynka"),
                new SQLiteParameter("@SpecialNotes", "Lubi rysować"),
                new SQLiteParameter("@GroupId", groupId));

            // Link parent to child
            int parentId = Convert.ToInt32(dbManager.ExecuteScalar("SELECT UserId FROM Users WHERE Username = 'parent'"));
            int childId = Convert.ToInt32(dbManager.ExecuteScalar("SELECT ChildId FROM Children WHERE FirstName = 'Zosia' AND LastName = 'Nowak'"));

            string insertParentChildSql = @"
                INSERT INTO ParentChild (ParentId, ChildId, Relationship, IsEmergencyContact)
                VALUES (@ParentId, @ChildId, @Relationship, @IsEmergencyContact);";

            dbManager.ExecuteNonQuery(insertParentChildSql,
                new SQLiteParameter("@ParentId", parentId),
                new SQLiteParameter("@ChildId", childId),
                new SQLiteParameter("@Relationship", "Father"),
                new SQLiteParameter("@IsEmergencyContact", 1));
            
            
            // Add initial system log
            string insertLogSql = @"
                INSERT INTO SystemLogs (EventType, UserId, Description)
                VALUES (@EventType, @UserId, @Description);";

            int adminId = Convert.ToInt32(dbManager.ExecuteScalar("SELECT UserId FROM Users WHERE Username = 'admin'"));

            dbManager.ExecuteNonQuery(insertLogSql,
                new SQLiteParameter("@EventType", "System"),
                new SQLiteParameter("@UserId", adminId),
                new SQLiteParameter("@Description", "System initialized with sample data"));
        }
    }
}