package org.openslx.imagemaster.db.mappers;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;
import org.openslx.bwlp.thrift.iface.Organization;
import org.openslx.imagemaster.db.Database;
import org.openslx.imagemaster.db.MysqlConnection;
import org.openslx.imagemaster.db.MysqlStatement;

/**
 * Represents an organization in the database.
 * Is used to authenticate the organization.
 */
public class DbOrganization
{
	private static final Logger LOGGER = Logger.getLogger( DbOrganization.class );

	private static final String organizationBaseSql = "SELECT"
			+ " o.organizationid, o.name, o.authmethod, o.publickey"
			+ " FROM organization o";

	private static final String suffixListFromOrgSql = "SELECT suffix FROM organization_suffix"
			+ " WHERE organizationid = :organizationid";

	private static Organization fromResultSet( MysqlConnection connection, ResultSet rs ) throws SQLException
	{
		String organizationId = rs.getString( "organizationid" );
		String ecpUrl = rs.getString( "authmethod" );
		if ( ecpUrl != null && !ecpUrl.startsWith( "http" ) ) {
			ecpUrl = null;
		}
		return new Organization( organizationId, rs.getString( "name" ), ecpUrl, getSuffixList( connection,
				organizationId ) );
	}

	/**
	 * Get organization by id. Returns null if not found.
	 * 
	 * @param organizationId
	 * @return
	 * @throws SQLException
	 */
	public static Organization fromOrganizationId( String organizationId ) throws SQLException
	{
		try ( MysqlConnection connection = Database.getConnection() ) {
			MysqlStatement stmt = connection.prepareStatement( organizationBaseSql + " WHERE o.organizationid = :organizationid" );
			stmt.setString( "organizationid", organizationId );
			ResultSet rs = stmt.executeQuery();
			if ( !rs.next() )
				return null;
			return fromResultSet( connection, rs );
		} catch ( SQLException e ) {
			LOGGER.error( "Query failed in DbOrganization.fromOrganizationId()", e );
			throw e;
		}
	}

	public static Organization fromSuffix( String suffix ) throws SQLException
	{
		try ( MysqlConnection connection = Database.getConnection() ) {
			MysqlStatement stmt = connection.prepareStatement( organizationBaseSql
					+ " INNER JOIN organization_suffix s USING (organizationid)"
					+ " WHERE s.suffix = :suffix" );
			stmt.setString( "suffix", suffix );
			ResultSet rs = stmt.executeQuery();
			if ( !rs.next() )
				return null;
			return fromResultSet( connection, rs );
		} catch ( SQLException e ) {
			LOGGER.error( "Query failed in DbOrganization.fromSuffix()", e );
			throw e;
		}
	}

	private static List<String> suffixForOrg( MysqlStatement stmt, String organizationId ) throws SQLException
	{
		stmt.setString( "organizationid", organizationId );
		ResultSet rs = stmt.executeQuery();
		List<String> list = new ArrayList<>();
		while ( rs.next() ) {
			list.add( rs.getString( "suffix" ) );
		}
		return list;
	}

	/**
	 * Return all known satellites/organizations as List of {@link OrganizationData}, which can be
	 * used directly by the thrift API.
	 * 
	 * @return list of all known organizations/satellites
	 * @throws SQLException
	 */
	public static List<Organization> getAll() throws SQLException
	{
		try ( MysqlConnection connection = Database.getConnection() ) {
			MysqlStatement stmt = connection.prepareStatement( organizationBaseSql );
			ResultSet rsOrg = stmt.executeQuery();
			MysqlStatement stmtSuffix = connection.prepareStatement( suffixListFromOrgSql );
			List<Organization> list = new ArrayList<>();
			while ( rsOrg.next() ) {
				String organizationId = rsOrg.getString( "organizationid" );
				String ecpUrl = rsOrg.getString( "authmethod" );
				if ( ecpUrl != null && !ecpUrl.startsWith( "http" ) ) {
					ecpUrl = null;
				}
				List<String> suffixList = suffixForOrg( stmtSuffix, organizationId );
				list.add( new Organization( organizationId, rsOrg.getString( "name" ), ecpUrl, suffixList ) );
			}
			return list;
		} catch ( SQLException e ) {
			LOGGER.error( "Query failed in DbOrganization.getAll()", e );
			throw e;
		}
	}

	public static DbOrganization fromPrefix( String prefix )
	{
		return null;
	}

	public static List<String> getSuffixList( MysqlConnection connection, String organizationId ) throws SQLException
	{
		List<String> list = new ArrayList<>();
		MysqlStatement stmt = connection.prepareStatement( suffixListFromOrgSql );
		stmt.setString( "organizationid", organizationId );
		ResultSet rs = stmt.executeQuery();
		while ( rs.next() ) {
			list.add( rs.getString( "suffix" ) );
		}
		return list;
	}

}