/**
 * Created by Ludde on 2014-10-30.
 */
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class Labb1 {

    public static String connectionUrl = "jdbc:sqlserver://LUDDE-HP\\MSSQLSERVER;" +
                           "database=Uppgift1;" +
                           "user=lodde;" +
                           "password=hemligt";

    public static Connection con;

    public static void main(String args[]){
        try{
            con = DriverManager.getConnection(connectionUrl);
            System.out.println("Connected.");
            List<Student> students = Labb1.getStudents();
            System.out.println(students.toString());
            List<Course> courses = Labb1.getCourses();
            System.out.println(courses.toString());
           // Labb1.addStudents();
            Labb1.deleteStudent();
            //Labb1.addCourses();
            Labb1.deleteCourse();
        }
        catch(Exception e){
            e.printStackTrace();
            System.out.println(e.getMessage());
            System.exit(0);
        }
    }

    public static List<Student> getStudents() {
        List<Student> students = new ArrayList<Student>();
        try {
            PreparedStatement statement = con.prepareStatement("SELECT id, name FROM student");
            ResultSet result = statement.executeQuery();

            while (result.next()) {
                students.add(new Student(result.getString(1), result.getString(2)));
            }
        }
        catch (SQLException e) {
            System.err.println("Kunde inte hitta namn på student " + e.getMessage());
            e.printStackTrace();
        }

        return students;
    }
    public static void addStudents(){
        try {
            PreparedStatement statement = con.prepareStatement("INSERT INTO Student VALUES ('ID6', 'Micke')");
            statement.executeUpdate();
            System.out.println("Student tillagd");
        }
        catch (SQLException e) {
            System.err.println("Kunde inte hitta namn på student " + e.getMessage());
            e.printStackTrace();
        }
    }

    public static void deleteStudent(){
        try {
            PreparedStatement statement = con.prepareStatement("DELETE FROM Student WHERE id='ID1'");
            statement.executeUpdate();
            System.out.println("Student borttagen");
        }
        catch (SQLException e) {
            System.err.println("Kunde inte hitta namn på student " + e.getMessage());
            e.printStackTrace();
        }
    }

    public static List<Course> getCourses() {
        List<Course> courses = new ArrayList<Course>();
        try {
            PreparedStatement statement = con.prepareStatement("SELECT code, name, points FROM course");
            ResultSet result = statement.executeQuery();

            while (result.next()) {
                courses.add(new Course(result.getString(1), result.getString(2), result.getInt(3)));
            }
        }
        catch (SQLException e) {
            System.err.println("Kunde inte hitta namn på student " + e.getMessage());
            e.printStackTrace();
        }

        return courses;
    }

    public static void addCourses(){
        try {
            PreparedStatement statement = con.prepareStatement("INSERT INTO Course VALUES ('CODE4', 'Swedish', 15)");
            statement.executeUpdate();
            System.out.println("Course added");
        }
        catch (SQLException e) {
            System.err.println("Kunde inte hitta namn på student " + e.getMessage());
            e.printStackTrace();
        }
    }
    public static void deleteCourse(){
        try {
            PreparedStatement statement = con.prepareStatement("DELETE FROM Course WHERE code='CODE4'");
            statement.executeUpdate();
            System.out.println("Course deleted");
        }
        catch (SQLException e) {
            System.err.println("Kunde inte hitta namn på student " + e.getMessage());
            e.printStackTrace();
        }
    }


}