package org.mynonglam.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import javax.sql.DataSource;

public class ProfilePictureMod {
	private DataSource dataSource;
	 
	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}
	
 	public void changeAvatar(String email, String link) throws SQLException {
		Connection con = dataSource.getConnection();
		try {
			PreparedStatement ps = con.prepareStatement("UPDATE account SET avatar = ? WHERE email = ?");
			ps.setString(1, link);
			ps.setString(2, email);
			ps.executeUpdate();
			ps.close();
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
 	
 	public void changeCover(String email, String link) throws SQLException {
		Connection con = dataSource.getConnection();
		try {
			PreparedStatement ps = con.prepareStatement("UPDATE account SET coverphoto = ? WHERE email = ?");
			ps.setString(1, link);
			ps.setString(2, email);
			ps.executeUpdate();
			ps.close();
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
 	
	public ArrayList<String> getImages(String email, int offset, int limit) throws SQLException{

		ArrayList<String> list = new ArrayList<String>();
		Connection con = dataSource.getConnection();
   
		PreparedStatement ps = con.prepareStatement("SELECT link FROM linkphotos WHERE idalbum IN (SELECT idalbum FROM album WHERE email = ?) LIMIT ? OFFSET ?");
		ps.setString(1, email);
		ps.setInt(2, limit);
		ps.setInt(3, offset);
		ResultSet rs = ps.executeQuery();
		while (rs.next()) {
			list.add(rs.getString(1));
		}
		
		ps.close();
		con.close();
		return list;
	}
	
    public int getImagesRecordNumber(String email) throws SQLException {
    	int record = 0;
		Connection con = dataSource.getConnection();	
		PreparedStatement ps  = con.prepareStatement("SELECT COUNT(link) FROM linkphotos WHERE idalbum IN (SELECT idalbum FROM album WHERE email = ?)");
		ps.setString(1, email);
		ResultSet rs = ps.executeQuery();
		while (rs.next()) { 	        	
			record = rs.getInt(1);
		}
		ps.close();
		con.close();
		return record;
	}
 	
 	public static void main(String[] args) throws SQLException {
 		ArrayList<String> list = new ProfilePictureMod().getImages("lenhattung@gmail.com", 0, 3);
 		for (Object object : list) {
			System.out.println(object);
		}
		System.out.println(new ProfilePictureMod().getImagesRecordNumber("lenhattung@gmail.com"));
	}
}
