import ca.tecreations.File;
import ca.tecreations.ProjectPath;
import ca.tecreations.db.mysql.MySQL;

import org.springframework.security.crypto.bcrypt.BCryptPasswordEncoder;
import org.springframework.security.crypto.password.PasswordEncoder;

import org.springframework.security.oauth2.core.oidc.OidcScopes; 
/**
 *
 * @author tim
 */
public class DBSetup {
    static public String dbName = "sitesec_dev";
    static public String user = "sitesec_user";
    static public String pass = "eMZx06__xi9]O2q";
    static MySQL mysql = new MySQL("tecreations.ca",3306,dbName,user,pass.toCharArray());
    
    static PasswordEncoder passwordEncoder = new BCryptPasswordEncoder(12);

    public static void main(String[] args) {
        if (ProjectPath.PROJECTS_HOME == null) {
            ProjectPath.PROJECTS_HOME = new File(".").getUnwrapped();
        }
        ProjectPath.PROJECT_DIR = "Login2";
        mysql.debug = true;
        String dbName = "sitesec_dev";
        //mysql.issue("DROP DATABASE IF EXISTS " + dbName);
        //mysql.issue("CREATE DATABASE " + dbName);
        mysql.issue("USE " + dbName);
        
        String sqlDirectory = ProjectPath.getProjectPath() + "sql" + File.separator;
//        mysql.parseAndExecute(sqlDirectory + "oauth2-authorization-consent-schema.sql");
//        mysql.parseAndExecute(sqlDirectory + "oauth2-authorization-schema.sql");
//        mysql.parseAndExecute(sqlDirectory + "oauth2-registered-client-schema.sql");
         System.out.println("sqlDirectory: " + sqlDirectory);
        mysql.issue("DROP TABLE IF EXISTS SPRING_SESSION_ATTRIBUTES");
        mysql.issue("DROP TABLE IF EXISTS SPRING_SESSION");
        mysql.issue("DROP TABLE IF EXISTS users");
        
        mysql.parseAndExecute(sqlDirectory + "session-schema-spring-session.sql",true);
        mysql.parseAndExecute(sqlDirectory + "users.schema.sql",true);
         
        mysql.issue("INSERT INTO authorities (authority) VALUES('" + OidcScopes.OPENID + "')");
        mysql.issue("INSERT INTO authorities (authority) VALUES('" + OidcScopes.PROFILE + "')");
        mysql.issue("INSERT INTO authorities (authority) VALUES('ROLE_ADMIN')");
        mysql.issue("INSERT INTO authorities (authority) VALUES('ROLE_SYSOP')");
        mysql.issue("INSERT INTO authorities (authority) VALUES('ROLE_USER')");
        
        String password = passwordEncoder.encode("pass");
        replaceOrInsert("tim",password,true );
        replaceOrInsert("admin",password,true );
        replaceOrInsert("user",password,true );
        String timsId = mysql.getValue("SELECT uid FROM users WHERE email='tim'",true);
        System.out.println("TIMS_ID: " + timsId);
        //mysql.issue("INSERT INTO emails (uid,email) VALUES ('" + timsId + "','tim.devries@yahoo.ca')");
        //mysql.issue("INSERT INTO emails (uid,email) VALUES ('" + timsId + "','tecreator@gmail.com')");
        //mysql.issue("INSERT INTO emails (uid,email) VALUES ('" + timsId + "','tim@tecreations.ca')");
        
        
        
        // do this
        //mysql.parseAndExecute(pp.getProjectPath() + "initial.setup.sql");

        //or this
        addAuthority(OidcScopes.OPENID,"tim");
        addAuthority(OidcScopes.OPENID,"admin");
        addAuthority(OidcScopes.OPENID,"user");
        addAuthority("ROLE_SYSOP","tim");
        addAuthority("ROLE_ADMIN","tim");
        addAuthority("ROLE_ADMIN","admin");
        addAuthority("ROLE_USER","user");
        
        createAccountOpsTable(true);
        createUserOpsTable(true);
        
        mysql.issue("DROP TABLE IF EXISTS users_details");
        String sql = "";
        sql += "CREATE TABLE users_details (";
        sql += "    uid INTEGER(11) AUTO_INCREMENT PRIMARY KEY,";
        sql += "    prefer VARCHAR(128) NOT NULL DEFAULT '',";
        sql += "    prefix VARCHAR(128) NOT NULL DEFAULT '',";
        sql += "    first VARCHAR(128) NOT NULL DEFAULT '',";
        sql += "    middles VARCHAR(256) NOT NULL DEFAULT '',";
        sql += "    last VARCHAR(256) NOT NULL DEFAULT '',";
        sql += "    postfix VARCHAR(128) NOT NULL DEFAULT '',";
        sql += "    birthdate VARCHAR(12) NOT NULL DEFAULT ''";
        sql += ")";
        mysql.issue(sql);
    }
    
    public static void addAuthority(String scope, String email) {
        String uid = mysql.getValue("SELECT uid FROM users WHERE email='" + email + "'",true);
        System.out.println("UID: " + uid);
        Integer userId = null;
        if (uid != null) {
            userId = Integer.valueOf(uid);
            String csv = mysql.getValue("SELECT authorities FROM users WHERE uid='" + userId + "'",true);
            String auth_id = mysql.getValue("SELECT id FROM authorities WHERE authority='" + scope + "'",true);
            if (csv.length() > 0) {
                csv += "," + auth_id;
            } else {
                csv = auth_id;
            }
            mysql.issue("UPDATE users SET authorities = '" + csv + "' WHERE uid='" + userId + "'",true);
        } else {
            throw new IllegalArgumentException("addAuthority: must be an existing user: " + email);
        }
    }
    
    public static void replaceOrInsert(String email, String password, boolean enabled) {
        String sql = "SELECT uid FROM users WHERE email='" + email + "'";
        int count = mysql.countRows(sql,true);
        if (count == 0) {
            sql = "INSERT ";
        } else {
            sql = "REPLACE ";
        }
        sql += "INTO users (email,password,enabled) ";
        sql += "VALUES('" + email + "','" + password + "','" + (enabled ? "1" : "0") + "')";
        mysql.issue(sql);
    }
    
    public static void createAccountOpsTable(boolean dropFirst) {
        if (dropFirst) {
            mysql.issue("DROP TABLE account_ops");
        }
        String sql = "";
        sql = "CREATE TABLE account_ops (";
        sql += "    id INT(11) AUTO_INCREMENT PRIMARY KEY,";
        sql += "    uid INT(11) NOT NULL,";
        sql += "    op VARCHAR(256) NOT NULL DEFAULT '',";
        sql += "    txt VARCHAR(1024) NOT NULL DEFAULT '',";
        sql += "    expiry VARCHAR(33) NOT NULL";
        sql += ")";
        mysql.issue(sql);
    }

    public static void createUserOpsTable(boolean dropFirst) {
        if (dropFirst) {
            mysql.issue("DROP TABLE IF EXISTS user_ops");
        }
        String sql = "";
        sql += "CREATE TABLE user_ops (";
        sql += "    id INT(11) AUTO_INCREMENT PRIMARY KEY, ";
        sql += "    uid INT(11) NOT NULL, ";
        sql += "    op VARCHAR(256) NOT NULL, ";
        sql += "    txt VARCHAR(1024) NOT NULL DEFAULT '')";
        mysql.issue(sql);
    }
}