<%@ page contentType="text/html" pageEncoding="UTF-8" %>
<%@ page import="java.io.*" %>
<%@ page import="java.sql.*" %>
<%@ page import="org.sqlite.*" %>
<%!
public class Database
{
    private Connection _Conn;
    private Statement stat;
    private String path_abs;
    public Database( String path )
    throws ClassNotFoundException, SQLException
    {
        this._Conn = null;

        File dbfile = new File( path );

        if( !dbfile.exists() || dbfile.isDirectory() )
            this.createDatabase();
        else
        {
            Class.forName( "org.sqlite.JDBC" );
            this._Conn = DriverManager.getConnection( "jdbc:sqlite:" + path );
        }
    }

    public void createDatabase()
    {
        this.path_abs = getServletContext().getRealPath("/");
        Class.forName("org.sqlite.JDBC");
	this._Conn = DriverManager.getConnection( "jdbc:sqlite:" + path_abs + "database.db" );
        stat = _Conn.createStatement();

        stat.executeUpdate(

                "CREATE TABLE `aktualnosci` (" +
                       " `id` INTEGER PRIMARY KEY AUTOINCREMENT," +
                        "`data` DATE NOT NULL," +
                        "`tresc` TEXT NOT NULL," +
                        "`id_klasa` INTEGER NOT NULL" +
                ");"
        );

        stat.executeUpdate(
                "CREATE TABLE `przedmiot` (" +
                        "`id` INTEGER PRIMARY KEY AUTOINCREMENT," +
                        "`nazwa` VARCHAR(50) NOT NULL UNIQUE," +
                        "`opis` TEXT DEFAULT NULL" +
                ");"
        );

        stat.executeUpdate(
                "CREATE TABLE `sala` (" +
                        "`id` INTEGER PRIMARY KEY AUTOINCREMENT," +
                        "`numer` VARCHAR(8)  NOT NULL," +
                        "`budynek` VARCHAR(45) DEFAULT NULL," +
                        "`opis` TEXT DEFAULT NULL" +
                ");"
        );

        stat.executeUpdate(
                "CREATE TABLE `miejscowosc` (" +
                        "`id` INTEGER PRIMARY KEY AUTOINCREMENT," +
                        "`nazwa` VARCHAR(60) NOT NULL," +
                        "`kod_pocztowy` CHAR(6) NOT NULL DEFAULT '00-000'," +
                        "`oddzial` VARCHAR(100) NOT NULL" +
                ");"
        );

        stat.executeUpdate(
                "CREATE TABLE `profil` (" +
                        "`id` INTEGER PRIMARY KEY AUTOINCREMENT," +
                        "`nazwa` VARCHAR(70) NOT NULL UNIQUE," +
                        "`opis` TEXT DEFAULT NULL" +
                ");"
        );

        stat.executeUpdate(
                "CREATE TABLE `uzytkownik` (" +
                        "`id` INTEGER PRIMARY KEY AUTOINCREMENT," +
                        "`imie` VARCHAR(50) NOT NULL," +
                        "`nazwisko` VARCHAR(60) NOT NULL," +
                        "`pesel` CHAR(11) NOT NULL," +
                        "`data_urodzenia` DATE NOT NULL," +
                        "`telefon` VARCHAR(20) DEFAULT NULL," +
                        "`email` VARCHAR(60) DEFAULT NULL," +
                        "`ulica` VARCHAR(60) DEFAULT NULL," +
                        "`numer` VARCHAR(10) NOT NULL," +
                        "`id_miejscowosc` INTEGER NOT NULL REFERENCES miejscowosc(id)," +
                        "`typ` SMALLINT NOT NULL," +
                        "`login` VARCHAR(255) NOT NULL," +
                        "`haslo` VARCHAR(255) NOT NULL" +
                ");"
        );
        stat.executeUpdate(
                "CREATE TABLE `klasa` (" +
                        "`id` INTEGER PRIMARY KEY AUTOINCREMENT," +
                        "`nr_klasy` VARCHAR(15) NOT NULL," +
                        "`rok_szkolny` INTEGER NOT NULL," +
                        "`id_sala` INTEGER NOT NULL REFERENCES sala(id)," +
                        "`id_wychowawca` INTEGER NOT NULL REFERENCES pracownik(id)," +
                        "`id_profil` INTEGER DEFAULT NULL REFERENCES profil(id)" +
                ");"

        );

        stat.executeUpdate(
                "CREATE TABLE `zajecia` (" +
                        "`id` INTEGER PRIMARY KEY AUTOINCREMENT," +
                        "`id_przedmiot` INTEGER NOT NULL REFERENCES przedmiot(id)," +
                        "`id_sala` INTEGER NOT NULL REFERENCES sala(id)," +
                        "`id_klasa` INTEGER NOT NULL REFERENCES klasa(id)," +
                        "`id_nauczyciel` INTEGER NOT NULL REFERENCES pracownik(id)," +
                        "`ilosc` SMALLINT NOT NULL DEFAULT 1" +
                        ");"
        );

        stat.executeUpdate(
                "CREATE TABLE `terminarz` (" +
                        "`id_zajecia` INTEGER NOT NULL REFERENCES zajecia(id)," +
                        "`data` DATE NOT NULL," +
                        "`typ` SMALLINT NOT NULL" +
                ");"
        );

        stat.executeUpdate(
                "CREATE TABLE `ocena` (" +
                        "`id_zajecia` INTEGER REFERENCES zajecia(id)," +
                        "`id_uczen` INTEGER REFERENCES uzytkownik(id)," +
                        "`ocena` NUMERIC(2,1) NOT NULL," +
                        "`data` DATE NOT NULL" +
                ");"
        );

        stat.executeUpdate(
                "CREATE TABLE `uwaga`(" +
                        "`id_zajecia` INTEGER PRIMARY KEY AUTOINCREMENT REFERENCES zajecia(id)," +
                        "`id_uczen` VARCHAR(50) NOT NULL REFERENCES uzytkownik(id)," +
                        "`tresc` TEXT" +
                ");"
        );

        stat.executeUpdate(
                "CREATE TABLE `obecnosci`(" +
                        "`id_zajecia` INTEGER NOT NULL REFERENCES zajecia(id)," +
                        "`id_uczen` INTEGER NOT NULL REFERENCES uzytkownik(id)," +
                        "`typ` SMALLINT NOT NULL," +
                        "`data` DATE NOT NULL" +
                ");"
        );
        stat.executeUpdate(
                "CREATE TABLE `logi`(" +
                        "`id` INTEGER PRIMARY KEY AUTOINCREMENT," +
                        "`id_user` INTEGER REFERENCES uzytkownik(id)," +
                        "`typ` TINYINT" +
                ");"
        );

        stat.close();
        _Conn.close();

    }

    public void close()
    throws SQLException
    {
        if( this._Conn != null )
            this._Conn.close();
    }




}
%>