package generated;

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


import common.BaseService;
import generated.MasterDetailVariantService;

import generated.MasterBean;
import generated.DetailBean;
import generated.Album;


public class MasterDetailVariantServiceImpl extends BaseService implements MasterDetailVariantService
{
    private List<MasterBean> getMasterList()
    throws SQLException
    {
        Connection connection = getConnection();
        PreparedStatement pstmt= connection.prepareCall(
            "select id, artist, album "+ 
            "from   t_master ");
        ResultSet resultSet=pstmt.executeQuery();
        List<MasterBean> c = new ArrayList<MasterBean>();
        while ( resultSet.next() )
        {
            c.add(resultSetToMasterBean( resultSet ) );
        }
        resultSet.close();
        pstmt.close();
        returnConnection(connection);
        return c;
    }

    private List<DetailBean> getDetailList(String masterId)
    throws SQLException
    {
        Connection connection = getConnection();
        PreparedStatement pstmt= connection.prepareCall(
            "select master_id, trackno, title "+ 
            "from   t_detail "+ 
            "where  master_id=? ");

        pstmt.setString(1,masterId); 
        ResultSet resultSet=pstmt.executeQuery();
        List<DetailBean> c = new ArrayList<DetailBean>();
        while ( resultSet.next() )
        {
            c.add(resultSetToDetailBean( resultSet ) );
        }
        resultSet.close();
        pstmt.close();
        returnConnection(connection);
        return c;
    }

    public List<Album> getAlbumList()
    throws SQLException
    {
        List<Album> albumList= new ArrayList<Album>();
        for ( MasterBean m : getMasterList() ) 
        {
            Album a=new Album();
            // set master details
            a.setArtist(m.getArtist());
            a.setAlbum(m.getAlbum());
            // set the detail details
            List<String> tList=new ArrayList<String>();
            for (DetailBean d : getDetailList( m.getId() ) )
            {
               tList.add( d.getTitle() ) ; 
            }
            a.setTrackList(tList);
            albumList.add(a);
        } 
        return albumList;
    } 



    private MasterBean resultSetToMasterBean(ResultSet resultSet)
    throws SQLException
    {
       MasterBean o = new MasterBean();
       o.setId( resultSet.getString(1)); 
       o.setArtist( resultSet.getString(2)); 
       o.setAlbum( resultSet.getString(3)); 
       return o;
    }

    private DetailBean resultSetToDetailBean(ResultSet resultSet)
    throws SQLException
    {
       DetailBean o = new DetailBean();
       o.setMaster_id( resultSet.getString(1)); 
       o.setTrackno( resultSet.getInt(2)); 
       o.setTitle( resultSet.getString(3)); 
       return o;
    }


 
}