/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package put.io2.nerdlounge;

import org.joda.time.DateTime;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.MessageSource;
import org.springframework.context.annotation.Bean;
import org.springframework.context.support.ResourceBundleMessageSource;
import org.springframework.jdbc.core.JdbcTemplate;

import java.sql.Timestamp;
import java.util.Arrays;
import java.util.List;
import java.util.stream.Collectors;

/**
 * Contains the main method and database setup.
 * 
 * @author Maciej Olszowy
 */

@SpringBootApplication
public class NerdLounge implements CommandLineRunner
{
    private static final Logger log = LoggerFactory.getLogger(NerdLounge.class);

    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    //temp till we get login function
    @Autowired
    PlayerService playerService;

    /**
     * Tells spring where to look for messages used for example in reporting validation errors to the user.
     * @return 
     */
    @Bean
    public MessageSource messageSource() {
        ResourceBundleMessageSource source = new ResourceBundleMessageSource();
        source.setBasename("messages");
        return source;
    }
    
    public static void main(String[] args) {
        SpringApplication.run(NerdLounge.class, args);
    }
	
	private void createPlayersTable()
	{
		jdbcTemplate.execute("DROP TABLE players IF EXISTS");
        jdbcTemplate.execute("CREATE TABLE players(" +
                "id SERIAL, name VARCHAR(30), surname VARCHAR(30), "
		+ "nick VARCHAR(30), password VARCHAR(100), "
		+ "birth_date TIMESTAMP)");
	}
	
	private void createLocationsTable()
	{
		jdbcTemplate.execute("DROP TABLE locations IF EXISTS");
        jdbcTemplate.execute("CREATE TABLE locations( "
                + "loc_id SERIAL, name VARCHAR(50), city VARCHAR(30), "
                + "postal VARCHAR(6), addresscd VARCHAR(30), isPublic BOOLEAN, "
                + "owner_id INTEGER, "
                + "CONSTRAINT owner_id_fk FOREIGN KEY ( owner_id ) REFERENCES"
                + " players ( id ) ON DELETE CASCADE"
                + ")");
	}
	
        private void createAttendanceTable()
        {
            jdbcTemplate.execute("DROP TABLE attendance IF EXISTS");
            jdbcTemplate.execute("CREATE TABLE attendance ( " +
                    "id SERIAL, " +
                    "id_meeting INTEGER NOT NULL, " +
                    "id_player INTEGER NOT NULL, " +
                    "invitation VARCHAR(15) NOT NULL, " +
                    "is_accepted BOOLEAN, " +
                    "CONSTRAINT uczestnictwa_id_gracza_fk FOREIGN KEY ( id_player ) REFERENCES players ( id ) ON DELETE CASCADE, " +
                    "CONSTRAINT uczestnictwa_id_spotkania_fk FOREIGN KEY ( id_meeting ) REFERENCES meetings ( id ) ON DELETE CASCADE, " +
                    "CONSTRAINT uczestnictwa_zaproszenie_opcje CHECK ( invitation IN ( 'invite','rejected','attending','signup' ) ) " +
                    ");");
        }
//	this.organiserID = organiserID;
//		this.locationID = locationID;
//		this.availability = availability;
//		this.num_people = num_people;
//		this.age_restr = age_restr;
//		this.start = start;
//		this.end = end;
	private void createMeetingsTable()
	{
		jdbcTemplate.execute("DROP TABLE meetings IF EXISTS");
		jdbcTemplate.execute("CREATE TABLE meetings("
				+ "id SERIAL, organiser_id INTEGER, location_id INTEGER,"
				+ "availability VARCHAR(12), num_people INTEGER, age_restr INTEGER,"
				+ "start TIMESTAMP, end TIMESTAMP,"
				+ "CHECK( availability IN ('Prywatne','Publiczne','Potwierdzane') ),"
				+ "CONSTRAINT organiser_id_fk FOREIGN KEY(organiser_id) "
					+ "REFERENCES players(id) ON DELETE CASCADE,"
				+ "CONSTRAINT location_id_fk FOREIGN KEY(location_id) "
				+ "REFERENCES locations(loc_id) ON DELETE CASCADE"
				+ ")");
	}

    @Override
    public void run(String... strings) throws Exception {
	log.info("Creating tables");

        createPlayersTable();
        createLocationsTable();
	createMeetingsTable();
        createAttendanceTable();
		
        log.info("Tables created");
        log.info("Populating tables");

        // Populate players
        List<Object[]> splitUpData;
        
        splitUpData = Arrays.asList("Jan Kowalski Wololo $2a$10$zOxhxJ6w5gN2yWAeae0xMesydv1a9iV5Ui7e5xel0s//SFFpruOGO 1990-01-01",
                        "Lesław Staropolski 360NoScopeMaster $2a$10$zOxhxJ6w5gN2yWAeae0xMesydv1a9iV5Ui7e5xel0s//SFFpruOGO 1980-01-01",
                        "generic user user $2a$10$ZRnzmvQXcAO3pLrQTEJ8vuk.GXR7GV61P8axOVJM/IJLrCqLzmDwm 1111-11-11").stream()
        .map(name -> name.split(" "))
        .collect(Collectors.toList());

        jdbcTemplate.batchUpdate("INSERT INTO players(name, surname, nick, password, birth_date) "
                        + "VALUES (?,?,?,?,?)", splitUpData); 

        
		// Populate locations
        //get random player ID to create locations under that player
        List<Player> playerList = playerService.getPlayerList();
        Integer randomID =  playerList.get(0).getId();
        
        splitUpData = Arrays.asList("Placeholder Location Public,Ankh Morpork,66-666,Bakers 5,TRUE,"+randomID.toString(),
                "Placeholder Location Private,Ankh Morpork,33-333,Bakers 6,FALSE,"+randomID.toString()).stream()
                .map(name -> name.split(","))
                .collect(Collectors.toList());
        
        jdbcTemplate.batchUpdate("INSERT INTO locations(name, city, postal, addresscd,ispublic,owner_id) "
                        + "VALUES (?,?,?,?,?,?)", splitUpData);
		
        // Populate meetings
        for(int i=0; i<20; i++)
        {
        Meeting.Availability avb = Meeting.Availability.Publiczne;
        if(i>9)
            avb = Meeting.Availability.Potwierdzane;
        DateTime dt = new DateTime().hourOfDay().roundFloorCopy();
        Timestamp startDate = new Timestamp(dt.getMillis()+10800000*i);
        Timestamp endDate = new Timestamp(dt.getMillis()+10800000*(i+1));
                
	randomID = (i%3) +1;	
        
        splitUpData = Arrays.asList(randomID.toString()+",1,"+avb.name()+","
                        + "4,18,"+startDate+","+endDate).stream()
                        .map(name -> name.split(","))
                        .collect(Collectors.toList());

        jdbcTemplate.batchUpdate("INSERT INTO meetings(organiser_id, location_id,"
                        + "availability, num_people, age_restr, start, end) "
                        + "VALUES(?,?,?,?,?,?,?)", splitUpData);
        }
        log.info("Init finished");
    }
}
