package kadai1;

import java.sql.*;
import java.util.ArrayList;

public class Database {

    private static Connection getDatabaseConnection() throws Exception {
        Class.forName("org.hsqldb.jdbcDriver");
        String url = "jdbc:hsqldb:file:c:\\mydb\\kadai_a2\\hsqldb;create=true";
        System.out.println("Connecting to " + url);
        Connection con = DriverManager.getConnection(url, "sa", "");
        System.out.println("Connected");
        return con;
    }

    private static void createtable() throws Exception {
        try {
            Connection con = getDatabaseConnection();

            System.out.println("Create table");
            Statement st = con.createStatement();
            st.executeUpdate(
                "create table userdata (" +
                    "id varchar(255) PRIMARY KEY," +
                    "lastname varchar(32) NOT NULL," +
                    "firstname varchar(32) NOT NULL," +
                    "lastnamekana varchar(64) NOT NULL," +
                    "firstnamekana varchar(64) NOT NULL," +
                    "sex int NOT NULL," +
                    "birthyear int NOT NULL," +
                    "birthmonth int NOT NULL," +
                    "birthdate int NOT NULL," +
                    "mailaddress varchar(255) UNIQUE NOT NULL," +
                    "area int NOT NULL," +
                    "prefecture int NOT NULL," +
                    "schoolname varchar(128)," +
                    "grade int NOT NULL," +
                    "password varchar(12) NOT NULL" +
                ")");
            System.out.println("Created");

            st.close();
            con.close();

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void insertdata(ModelUserData userdata) throws Exception {
        createtable();
        try {
            Connection con = getDatabaseConnection();

            System.out.println("Insert data");
            PreparedStatement ps = con.prepareStatement("insert into userdata values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
            int paramcount = 1;
            ps.setString(paramcount++, userdata.mailaddress);//userdata.id);
            ps.setString(paramcount++, userdata.lastname);
            ps.setString(paramcount++, userdata.firstname);
            ps.setString(paramcount++, userdata.lastnamekana);
            ps.setString(paramcount++, userdata.firstnamekana);
            ps.setInt(paramcount++, userdata.sex);
            ps.setInt(paramcount++, userdata.birthyear);
            ps.setInt(paramcount++, userdata.birthmonth);
            ps.setInt(paramcount++, userdata.birthdate);
            ps.setString(paramcount++, userdata.mailaddress);
            ps.setInt(paramcount++, userdata.area);
            ps.setInt(paramcount++, userdata.prefecture);
            ps.setString(paramcount++, userdata.schoolname);
            ps.setInt(paramcount++, userdata.grade);
            ps.setString(paramcount++, userdata.password);
            System.out.println(ps.toString());

            int result = ps.executeUpdate();
            System.out.println("Inserted " + result);

            ps.close();
            con.close();

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static ArrayList<ModelUserData> selectdata(ModelUserData userdata) throws Exception {
        ArrayList<ModelUserData> result = new ArrayList<ModelUserData>();

        try {
            Connection con = getDatabaseConnection();

            boolean isadd = false;
            String querystring = "select * from userdata where";
            if( !( (userdata.id == null) || userdata.id.isEmpty()) ) {
            	querystring += " id like ?";
            	isadd = true;
            }
            if(userdata.grade > 1) {
            	if(isadd) querystring += " AND";
            	querystring += " grade=?";
            	isadd = true;
            }
            if( !( (userdata.mailaddress == null) || userdata.mailaddress.isEmpty()) ) {
            	if(isadd) querystring += " AND";
            	querystring += " mailaddress like ?";
            	isadd = true;
            }
            if(userdata.area > 1) {
            	if(isadd) querystring += " AND";
            	querystring += " area=?";
            	isadd = true;
            }
            System.out.println(querystring);

            int paramcount = 1;
            PreparedStatement ps = con.prepareStatement(querystring);
            if( !( (userdata.id == null) || userdata.id.isEmpty()) ) {
                ps.setString(paramcount, userdata.id +"%");
                paramcount++;
            }
            if(userdata.grade > 1) {
                ps.setInt(paramcount, userdata.grade);
                paramcount++;
            }
            if( !( (userdata.mailaddress == null) || userdata.mailaddress.isEmpty()) ) {
                ps.setString(paramcount, userdata.mailaddress +"%");
                paramcount++;
            }
            if(userdata.area > 1) {
                ps.setInt(paramcount, userdata.area);
                paramcount++;
            }

            System.out.println(ps.toString());
            ResultSet rs = ps.executeQuery();
            System.out.println("selected");

            while ( rs.next() )
            {
            	ModelUserData resultdata = new ModelUserData();
            	resultdata.id = rs.getString( "id" );
            	resultdata.lastname = rs.getString( "lastname" );
            	resultdata.firstname = rs.getString( "firstname" );
            	resultdata.lastnamekana = rs.getString( "lastnamekana" );
            	resultdata.firstnamekana = rs.getString( "firstnamekana" );
            	resultdata.sex = rs.getInt( "sex" );
            	resultdata.birthyear = rs.getInt( "birthyear" );
                resultdata.birthmonth = rs.getInt( "birthmonth" );
                resultdata.birthdate = rs.getInt( "birthdate" );
                resultdata.mailaddress = rs.getString( "mailaddress" );
                resultdata.mailaddressconfirm = resultdata.mailaddress;
            	resultdata.area = rs.getInt( "area" );
                resultdata.prefecture = rs.getInt( "prefecture" );
                resultdata.schoolname = rs.getString( "schoolname" );
                resultdata.grade = rs.getInt( "grade" );
                resultdata.password = rs.getString( "password" );
                resultdata.passwordconfirm = resultdata.password;
                System.out.println(resultdata.toString());
                result.add( resultdata );
            }

            con.close();

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return result;
    }

}