Editing Data

' --- Validate inputs, return list of error messages ---
Private Function ValidateStudent() As List(Of String)
    Dim errors As New List(Of String)

    ' Name: required, 2–80 chars, letters/spaces only
    If txtName.Text.Trim().Length < 2 Then
        errors.Add("Name must be at least 2 characters.")
    ElseIf txtName.Text.Trim().Length > 80 Then
        errors.Add("Name must be 80 characters or fewer.")
    End If

    ' Grade: numeric, 0–100
    Dim grade As Double
    If Not Double.TryParse(txtGrade.Text, grade) Then
        errors.Add("Grade must be a number.")
    ElseIf grade < 0 Or grade > 100 Then
        errors.Add("Grade must be between 0 and 100.")
    End If

    ' Class: required selection
    If cboClass.SelectedIndex < 0 Then
        errors.Add("Please select a class.")
    End If

    Return errors
End Function

' --- Use in Save button ---
Private Sub btnSave_Click(...) Handles btnSave.Click
    Dim errors = ValidateStudent()
    If errors.Count > 0 Then
        MessageBox.Show(String.Join(Environment.NewLine, errors),
                        "Validation Errors", MessageBoxButtons.OK, MessageBoxIcon.Warning)
        Return   ' abort save
    End If
    SaveStudent()   ' validation passed — write to database
End Sub

' --- Live validation feedback (TextChanged) ---
Private Sub txtGrade_TextChanged(...) Handles txtGrade.TextChanged
    Dim g As Double
    If Double.TryParse(txtGrade.Text, g) And g >= 0 And g <= 100 Then
        txtGrade.BackColor = Color.Honeydew    ' green tint = valid
        lblGradeError.Text = ""
    Else
        txtGrade.BackColor = Color.MistyRose   ' red tint = invalid
        lblGradeError.Text = "⚠ Must be 0–100"
    End If
End Sub

' --- Duplicate name check (before INSERT) ---
Function NameExists(name As String) As Boolean
    Using conn As New SqliteConnection(CONN_STR)
        conn.Open()
        Using cmd As New SqliteCommand(
            "SELECT COUNT(*) FROM Students WHERE Name = @Name", conn)
            cmd.Parameters.AddWithValue("@Name", name.Trim())
            Return CInt(cmd.ExecuteScalar()) > 0
        End Using
    End Using
End Function

' --- CommandBuilder setup (in LoadData) ---
Private _da As SqliteDataAdapter
Private _dt As New DataTable()
Private _cb As SqliteCommandBuilder

Private Sub LoadData()
    Using conn As New SqliteConnection(CONN_STR)
        conn.Open()
        _da = New SqliteDataAdapter("SELECT * FROM Students ORDER BY Name", conn)
        _cb = New SqliteCommandBuilder(_da)   ' auto-generates INSERT/UPDATE/DELETE
        _da.Fill(_dt)
    End Using
    dgv.DataSource = _dt
End Sub

' --- Add a new row to the DataTable ---
Private Sub AddRow()
    Dim row = _dt.NewRow()
    row("Name")  = txtName.Text
    row("Grade") = CDbl(txtGrade.Text)
    row("Class") = cboClass.Text
    _dt.Rows.Add(row)   ' RowState = DataRowState.Added
End Sub

' --- Modify an existing row ---
Private Sub EditRow(rowIndex As Integer)
    _dt.Rows(rowIndex)("Grade") = CDbl(txtGrade.Text)
    ' RowState changes to DataRowState.Modified automatically
End Sub

' --- Mark a row for deletion ---
Private Sub DeleteRow(rowIndex As Integer)
    _dt.Rows(rowIndex).Delete()   ' RowState = DataRowState.Deleted
    ' Row not physically removed until da.Update() is called
End Sub

' --- Commit ALL pending changes to the database ---
Private Sub SaveAllChanges()
    Try
        Using conn As New SqliteConnection(CONN_STR)
            conn.Open()
            _da.SelectCommand.Connection = conn
            _da.Update(_dt)     ' pushes Added/Modified/Deleted rows
        End Using
        _dt.AcceptChanges()     ' reset all RowStates to Unchanged
        MessageBox.Show("All changes saved.")
    Catch ex As Exception
        MessageBox.Show(ex.Message, "Save Error")
    End Try
End Sub

' --- Discard all pending changes ---
Private Sub DiscardChanges()
    _dt.RejectChanges()   ' rolls back all Added/Modified/Deleted rows
End Sub

' --- Check if there are unsaved changes ---
Dim hasChanges = _dt.GetChanges() IsNot Nothing

' --- Transfer 10 marks from one student to another (atomic) ---
Private Sub TransferMarks(fromId As Integer, toId As Integer, amount As Integer)
    Using conn As New SqliteConnection(CONN_STR)
        conn.Open()
        Dim tx = conn.BeginTransaction()   ' START TRANSACTION
        Try
            ' Step 1 — deduct from source student
            Using cmd1 As New SqliteCommand(
                "UPDATE Students SET Grade = Grade - @Amt WHERE StudentID = @ID", conn, tx)
                cmd1.Parameters.AddWithValue("@Amt", amount)
                cmd1.Parameters.AddWithValue("@ID",  fromId)
                cmd1.ExecuteNonQuery()
            End Using

            ' Step 2 — add to destination student
            Using cmd2 As New SqliteCommand(
                "UPDATE Students SET Grade = Grade + @Amt WHERE StudentID = @ID", conn, tx)
                cmd2.Parameters.AddWithValue("@Amt", amount)
                cmd2.Parameters.AddWithValue("@ID",  toId)
                cmd2.ExecuteNonQuery()
            End Using

            ' Both steps succeeded — commit
            tx.Commit()
            MessageBox.Show("Transfer complete.")
        Catch ex As Exception
            tx.Rollback()   ' undo BOTH steps if either failed
            MessageBox.Show($"Transfer failed — rolled back: {ex.Message}")
        End Try
    End Using
End Sub

' --- Batch INSERT with transaction (much faster than individual inserts) ---
Private Sub BulkInsert(students As List(Of Student))
    Using conn As New SqliteConnection(CONN_STR)
        conn.Open()
        Dim tx = conn.BeginTransaction()
        Try
            Using cmd As New SqliteCommand(
                "INSERT INTO Students (Name, Grade, Class) VALUES (@N, @G, @C)", conn, tx)
                For Each s In students
                    cmd.Parameters.Clear()
                    cmd.Parameters.AddWithValue("@N", s.Name)
                    cmd.Parameters.AddWithValue("@G", s.Grade)
                    cmd.Parameters.AddWithValue("@C", s.Class)
                    cmd.ExecuteNonQuery()   ' reuse same command in loop
                Next
            End Using
            tx.Commit()
            MessageBox.Show($"Inserted {students.Count} records.")
        Catch ex As Exception
            tx.Rollback()
        End Try
    End Using
End Sub

' --- Allow inline editing ---
dgv.ReadOnly  = False
dgv.EditMode  = DataGridViewEditMode.EditOnDoubleClick  ' or EditOnKeystroke

' --- Validate immediately after cell edit ---
Private Sub dgv_CellValidating(...) Handles dgv.CellValidating
    If dgv.Columns(e.ColumnIndex).Name = "Grade" Then
        Dim g As Double
        If Not Double.TryParse(e.FormattedValue.ToString(), g) _
            Or g < 0 Or g > 100 Then
            e.Cancel = True   ' keep cell in edit mode
            MessageBox.Show("Grade must be 0–100.")
        End If
    End If
End Sub

' --- Save the cell edit to the database ---
Private Sub dgv_CellEndEdit(...) Handles dgv.CellEndEdit
    Dim col = dgv.Columns(e.ColumnIndex).Name
    Dim val = dgv.Rows(e.RowIndex).Cells(e.ColumnIndex).Value
    Dim id  = CInt(dgv.Rows(e.RowIndex).Cells("StudentID").Value)

    Using conn As New SqliteConnection(CONN_STR)
        conn.Open()
        Dim sql = $"UPDATE Students SET {col} = @Val WHERE StudentID = @ID"
        Using cmd As New SqliteCommand(sql, conn)
            cmd.Parameters.AddWithValue("@Val", val)
            cmd.Parameters.AddWithValue("@ID",  id)
            cmd.ExecuteNonQuery()
        End Using
    End Using
End Sub

' --- Prevent editing of primary-key column ---
Private Sub dgv_CellBeginEdit(...) Handles dgv.CellBeginEdit
    If dgv.Columns(e.ColumnIndex).Name = "StudentID" Then
        e.Cancel = True   ' block edit of primary key
    End If
End Sub

' --- Undo last cell edit (Ctrl+Z) ---
Private Sub Form_KeyDown(...) Handles MyBase.KeyDown
    If e.Control And e.KeyCode = Keys.Z Then UndoLastEdit()
End Sub

' --- Undo action record ---
Enum UndoType : Added : Edited : Deleted : End Enum

Structure UndoAction
    Dim Kind        As UndoType
    Dim StudentID   As Integer
    Dim OldName     As String
    Dim OldGrade    As Double
    Dim OldClass    As String
End Structure

Private _undo As New Stack(Of UndoAction)()

' --- Push BEFORE making any change ---
Private Sub PushUndo(kind As UndoType, row As DataRow)
    _undo.Push(New UndoAction With {
        .Kind      = kind,
        .StudentID = CInt(row("StudentID")),
        .OldName   = row("Name").ToString(),
        .OldGrade  = CDbl(row("Grade")),
        .OldClass  = row("Class").ToString()
    })
    btnUndo.Enabled = True
End Sub

' --- Undo last action ---
Private Sub UndoLastEdit()
    If _undo.Count = 0 Then Return
    Dim action = _undo.Pop()

    Using conn As New SqliteConnection(CONN_STR)
        conn.Open()
        Select Case action.Kind
            Case UndoType.Added
                ' Undo add = delete the inserted row
                Using cmd As New SqliteCommand(
                    "DELETE FROM Students WHERE StudentID = @ID", conn)
                    cmd.Parameters.AddWithValue("@ID", action.StudentID)
                    cmd.ExecuteNonQuery()
                End Using
            Case UndoType.Edited
                ' Undo edit = restore old values
                Using cmd As New SqliteCommand(
                    "UPDATE Students SET Name=@N, Grade=@G, Class=@C WHERE StudentID=@ID", conn)
                    cmd.Parameters.AddWithValue("@N",  action.OldName)
                    cmd.Parameters.AddWithValue("@G",  action.OldGrade)
                    cmd.Parameters.AddWithValue("@C",  action.OldClass)
                    cmd.Parameters.AddWithValue("@ID", action.StudentID)
                    cmd.ExecuteNonQuery()
                End Using
            Case UndoType.Deleted
                ' Undo delete = re-insert the row
                Using cmd As New SqliteCommand(
                    "INSERT INTO Students (StudentID, Name, Grade, Class) VALUES (@ID, @N, @G, @C)", conn)
                    cmd.Parameters.AddWithValue("@ID", action.StudentID)
                    cmd.Parameters.AddWithValue("@N",  action.OldName)
                    cmd.Parameters.AddWithValue("@G",  action.OldGrade)
                    cmd.Parameters.AddWithValue("@C",  action.OldClass)
                    cmd.ExecuteNonQuery()
                End Using
        End Select
    End Using

    If _undo.Count = 0 Then btnUndo.Enabled = False
    LoadData()   ' refresh DataGridView
End Sub