package industries.doublehelix.furrynotemanager;

import java.sql.Clob;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.Calendar;
import java.util.TreeSet;

import industries.doublehelix.conditionalfilter.filtertypes.container.MasterFilter;

public class Note implements Comparable<Note>{
	private Integer noteId = null;
	private int originalNoteId = -1;
	private User user = null;
	private String sender = null;
	private String receiver = null;
	private String title = "";
	private Calendar date = Calendar.getInstance();
	private String text = "";
	private String rawtext = "";
	private int priority = 0;
	private Folder folder = null;
	private boolean unread = false;
	
	public Note(User user) {
		this.user = user;
	}
	
	public Note(int originalNoteId, int userId, String sender, String receiver, String title,
			Calendar date, String text, String rawText, int priority, int folderId, boolean unread) throws SQLException {
		this(originalNoteId, User.getUser(userId), sender, receiver, title, date, text, rawText, priority,
				Folder.getFolder(folderId), unread);
	}
	
	public Note(int originalNoteId, User user, String sender, String receiver, String title,
			Calendar date, String text, String rawText, int priority, Folder folder, boolean unread) {
		setOriginalNoteId(originalNoteId);
		setUser(user);
		setSender(sender);
		setReceiver(receiver);
		setTitle(title);
		setDate(date);
		setText(text);
		setRawText(rawText);
		setPriority(priority);
		setFolder(folder);
		setUnread(unread);
	}
	
	public Note getNote(int noteId) throws SQLException {
		TreeSet<Note> notes = getNotesByExtendedStatement("NOTEID=" + noteId);
		return notes.first();
	}
	
	public static TreeSet<Note> getAllNotes(MasterFilter filter) throws SQLException {
		if (filter == null) return getNotesByExtendedStatement(null);
		return getNotesByExtendedStatement(filter.getSqlWhere());
	}
	
	public static TreeSet<Note> getAllNotesByUser(User user, MasterFilter filter) throws SQLException {
		if (filter == null) return getNotesByExtendedStatement("USERID=" + user.getUserId());
		return getNotesByExtendedStatement("USERID=" + user.getUserId() + " AND " + filter.getSqlWhere());
	}
	
	public static TreeSet<Note> getAllNotesByUserAndFolder(Folder folder, User user, MasterFilter filter) throws SQLException {
		if (filter == null) return getNotesByExtendedStatement("USERID=" + user.getUserId() + " AND FOLDERID=" + folder.getFolderId());
		return getNotesByExtendedStatement("USERID=" + user.getUserId() + " AND FOLDERID=" + folder.getFolderId() + " AND " + filter.getSqlWhere());
	}
	
	public static TreeSet<Note> getAllNotesByFolder(Folder folder, MasterFilter filter) throws SQLException {
		if (filter == null) return getNotesByExtendedStatement("FOLDERID=" + folder.getFolderId());
		return getNotesByExtendedStatement("FOLDERID=" + folder.getFolderId() + " AND " + filter.getSqlWhere());
	}
	
	public static TreeSet<Note> getNotesByExtendedStatement(String where) throws SQLException {
		String wherePart = "";
		if (where != null && !where.isEmpty()) wherePart = " WHERE " + where;
		
		TreeSet<Note> notes = new TreeSet<>();
		try (Statement s = DBTools.getInstance().getConnection().createStatement()) {
			try(ResultSet rs = s.executeQuery("SELECT * FROM NOTES" + wherePart)) {
				while(rs.next()) {
					Calendar cal = Calendar.getInstance();
					cal.setTimeInMillis(rs.getTimestamp("DATE").getTime());

					Clob clobText = rs.getClob("TEXT");
					Clob clobRawText = rs.getClob("RAWTEXT");
					// we can be sure the clobs are not longer than Integer.MAX_VALUE as strings are not and they
					// were strings before writing them in the DB
					String text = clobText.getSubString(1l, (int)clobText.length());
					String rawText = clobRawText.getSubString(1l, (int)clobRawText.length());
					clobText.free(); // is probably done by the garbage collector
					clobRawText.free();
					Note note = new Note(rs.getInt("ORIGINALNOTEID"), User.getUser(rs.getInt("USERID")),
							rs.getString("SENDER"), rs.getString("RECEIVER"), rs.getString("TITLE"), cal,
							text, rawText, rs.getInt("PRIORITY"), Folder.getFolder(rs.getInt("FOLDERID")),
							rs.getBoolean("UNREAD"));
					note.setNoteId(rs.getInt("NOTEID"));
					notes.add(note);
				}
			}
		}
		return notes;
	}
	
	public void removeFromDB() throws SQLException {
		try (Statement s = DBTools.getInstance().getConnection().createStatement()) {
			s.executeUpdate("DELETE FROM NOTES WHERE NOTEID=" + this.noteId);
		}
	}
	
	public boolean saveToDb() throws SQLException {
		if (user == null || originalNoteId == -1) {
			System.err.println("Attempt to save note without user set");
			return false;
		}
		if (originalNoteId == -1) {
			System.err.println("Attempt to save note without original note id set");
			return false;
		}
		if (folder == null) {
			System.err.println("Attempt to save note without folder set");
			return false;
		}
		
		// check if there is already a note in the db (same site (user), same originalid)
		TreeSet<Note> notes = getNotesByExtendedStatement("ORIGINALNOTEID=" + originalNoteId + 
				" AND USERID=" + user.getUserId());
		
		if (notes.size() == 0) {
			// new note
			StringBuffer sql = new StringBuffer();
			sql.append("INSERT INTO NOTES (");
			sql.append("ORIGINALNOTEID, USERID, SENDER, RECEIVER, TITLE, DATE, TEXT, RAWTEXT, PRIORITY, FOLDERID, UNREAD");
			sql.append(") VALUES (");
			sql.append("?,              ?,      ?,      ?,        ?,     ?,    ?,    ?,       ?,        ?,        ?");
			sql.append(")");
			
			try (PreparedStatement ps = DBTools.getInstance().getConnection().prepareStatement(sql.toString(),  Statement.RETURN_GENERATED_KEYS)) {
				ps.setInt(1, originalNoteId);
				ps.setInt(2, user.getUserId());
				ps.setString(3, sender); // can handle if sender is null
				ps.setString(4, receiver); // can handle if sender is null
				ps.setString(5, title);
				ps.setTimestamp(6, new Timestamp(date.getTimeInMillis()));
				ps.setString(7, text);
				ps.setString(8, rawtext);
				ps.setInt(9, priority);
				ps.setInt(10, folder.getFolderId());
				ps.setBoolean(11, unread);
				
				ps.executeUpdate();
			}

			// re-select the now inserted note to get the noteid
			notes = getNotesByExtendedStatement("ORIGINALNOTEID=" + originalNoteId + 
					" AND USERID=" + user.getUserId());
			this.noteId = notes.first().noteId;
		} else {
			this.noteId = notes.first().noteId;
			if (!this.equals(notes.first())) {
				// changed note
				// do not change by noteid, it exists just for linking by other tables, primary key are originalid + user
				StringBuffer sql = new StringBuffer();
				sql.append("UPDATE NOTES SET ");
				sql.append("SENDER=?, RECEIVER=?, TITLE=?, DATE=?, TEXT=?, RAWTEXT=?, PRIORITY=?, FOLDERID=?, UNREAD=? ");
				sql.append("WHERE ORIGINALNOTEID=? AND USERID=?");
				
				try (PreparedStatement ps = DBTools.getInstance().getConnection().prepareStatement(sql.toString())) {
					ps.setString(1, sender); // can handle if sender is null
					ps.setString(2, receiver); // can handle if sender is null
					ps.setString(3, title);
					ps.setTimestamp(4, new Timestamp(date.getTimeInMillis()));
					ps.setString(5, text);
					ps.setString(6, rawtext);
					ps.setInt(7, priority);
					ps.setInt(8, folder.getFolderId());
					ps.setBoolean(9, unread);
					ps.setInt(10, originalNoteId);
					ps.setInt(11, user.getUserId());
					
					ps.executeUpdate();
				}
			}
		}
		return true;
	}
	
	public int getNoteId() {
		return noteId;
	}
	public void setNoteId(int noteId) {
		this.noteId = noteId;
	}
	public int getOriginalNoteId() {
		return originalNoteId;
	}
	public void setOriginalNoteId(int originalNoteId) {
		this.originalNoteId = originalNoteId;
	}
	public User getUser() {
		return user;
	}
	public void setUser(User user) {
		this.user = user;
	}
	public String getSender() {
		return sender;
	}
	public void setSender(String sender) {
		this.sender = sender == null ? sender : sender.trim();;
	}
	public String getReceiver() {
		return receiver;
	}
	public void setReceiver(String receiver) {
		this.receiver = receiver == null ? receiver : receiver.trim();
	}
	public String getTitle() {
		return title;
	}
	public void setTitle(String title) {
		this.title = title == null ? title : title.trim();
	}
	public Calendar getDate() {
		return date;
	}
	public void setDate(Calendar date) {
		this.date = date;
		this.date.set(Calendar.MILLISECOND, 0); // timestamp has problems with ms
	}
	public String getText() {
		return text;
	}
	public void setText(String text) {
		this.text = text == null ? text : text.trim();
	}
	public String getRawText() {
		return rawtext;
	}
	public void setRawText(String text) {
		this.rawtext = text == null ? text : text.trim();
	}
	public int getPriority() {
		return priority;
	}
	public void setPriority(int priority) {
		this.priority = priority;
	}
	public Folder getFolder() {
		return folder;
	}
	public void setFolder(Folder folder) {
		this.folder = folder;
	}
	public boolean isUnread() {
		return unread;
	}
	public void setUnread(boolean unread) {
		this.unread = unread;
	}
	
	@Override
	public boolean equals(Object obj) {
		if (obj == null) return false;
		if (!(obj instanceof Note)) return false;
		
		Note note = (Note)obj;
		
		// not comparing noteid in case one side was not put into the DB
		return (originalNoteId == note.originalNoteId && 
				(user == null ? note.user == null : user.equals(note.user)) && // user can never be null
				(sender == null ? note.sender == null : sender.equals(note.sender)) &&
				(receiver == null ? note.receiver == null : receiver.equals(note.receiver)) &&
				title.equals(note.title) &&
				(date == null ? note.date == null : date.equals(note.date)) &&
				text.equals(note.text) && priority == note.priority &&
				(folder == null ? note.folder == null : folder.equals(note.folder)) &&
				unread == note.unread);
	}
	
	@Override
	public int compareTo(Note o) {
		int comp = Integer.compare(user.getSite().getSiteId(), o.user.getSite().getSiteId());
		if (comp == 0) {
			return Integer.compare(originalNoteId, o.originalNoteId);
		} else {
			return comp;
		}
	}
}
