package org.arkiwi.app.database;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;

import org.arkiwi.app.feed.FeedItem;
import org.arkiwi.app.feed.FeedItemFile;
import org.arkiwi.app.feed.FeedQuery;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteConstraintException;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

public class DbFeed {
	private static final String LOG_TAG = "DbFeed";
	private SQLiteDatabase db;
	private Db dbHelper;
	
    public static final String FILL_GRID = "SELECT * "+
			"FROM "+ Db.T_ITEM + " i " +
			"LEFT OUTER JOIN "+Db.T_ATTR+" a ON ( i."+Db.I_ID+"=a."+Db.A_IID+ ")"+ 
			" WHERE i."+Db.I_QID+" = ? AND i."+Db.I_STATUS+" <> "+Db.STATUS_DELETED;
    
	public DbFeed(Context context) {
		dbHelper = new Db(context);
	}

	public ArrayList<FeedQuery> fillQuerys(String order){
		ArrayList<FeedQuery> fq = new ArrayList<FeedQuery>();
		Cursor c = db.query(Db.T_QUERY, null, null, null, null, null, order);
		c.moveToFirst();
		while (!c.isAfterLast()) {
			FeedQuery q = cursorToFeedQuery(c);
			fq.add(q);
			c.moveToNext();
		}
		c.close();
		
		return fq;
	}
	
	public FeedQuery fillQuery(int qid){
		Cursor c = db.query(Db.T_QUERY, null, 
							Db.Q_ID + " = ?", new String[]{String.valueOf(qid)}, 
							null, null, null);
		c.moveToFirst();
		FeedQuery q = cursorToFeedQuery(c);
		c.close();
		
		return q;
	}

	private FeedQuery cursorToFeedQuery(Cursor c) {
		FeedQuery q = new FeedQuery();
		
		q.setQid(c.getInt(c.getColumnIndex(Db.Q_ID)));
		q.setRefresh(c.getString(c.getColumnIndex(Db.Q_REFRESHED)));
		q.setLast(c.getString(c.getColumnIndex(Db.Q_LAST)));
		q.setTitle(c.getString(c.getColumnIndex(Db.Q_TITLE)));
		q.setLink(c.getString(c.getColumnIndex(Db.Q_LINK)));
		q.setDesc(c.getString(c.getColumnIndex(Db.Q_DESC)));
		q.setStatus(c.getString(c.getColumnIndex(Db.Q_STATUS)));
		
		return q;
	}
	
	public void fillItem(FeedQuery q, int qid, String order){
		Cursor c = db.rawQuery(FILL_GRID + " ORDER BY " + order, new String[]{String.valueOf(qid)});

		//Log.d(LOG_TAG,"qid"+String.valueOf(qid));
		c.moveToFirst();
		while (!c.isAfterLast()) {
			cursorToFeedItem(c,q);
			c.moveToNext();
		}
		c.close();
	}
	
	private void cursorToFeedItem(Cursor c, FeedQuery q) {
		String type = c.getString(c.getColumnIndex(Db.I_TYPE));
		String guid = c.getString(c.getColumnIndex(Db.I_GUID));
		//Log.d(LOG_TAG,"type:"+type+" title:"+c.getString(c.getColumnIndex(Db.I_TITLE)));
		q.addFeed(guid, type);
		FeedItem fi = q.getFeedItem(guid);
		fi.setIid(c.getInt(c.getColumnIndex(Db.I_ID)));
		fi.setStatus(c.getInt(c.getColumnIndex(Db.I_STATUS)));
		fi.setPubDate(c.getString(c.getColumnIndex(Db.I_PDATE)));
		fi.setTitle(c.getString(c.getColumnIndex(Db.I_TITLE)));
		fi.setLink(c.getString(c.getColumnIndex(Db.I_LINK)));
		fi.setQrcode(c.getString(c.getColumnIndex(Db.I_QR)));
		fi.setDescriprion(c.getString(c.getColumnIndex(Db.I_DESC)));
		
		if (!type.equalsIgnoreCase("dir")){
			((FeedItemFile) fi).setAid(c.getInt(c.getColumnIndex(Db.A_ID)));
			((FeedItemFile) fi).setChannels(c.getString(c.getColumnIndex(Db.A_CHANNELS)));
			((FeedItemFile) fi).setCreator(c.getString(c.getColumnIndex(Db.A_CREATOR)));
			((FeedItemFile) fi).setDownload(c.getString(c.getColumnIndex(Db.A_DOWNLOAD)));
			((FeedItemFile) fi).setDuration(c.getString(c.getColumnIndex(Db.A_DURATION)));
			//Log.d(LOG_TAG, "lic:"+c.getString(c.getColumnIndex(Db.A_LICENSE_URL)));
			((FeedItemFile) fi).setLicense(c.getString(c.getColumnIndex(Db.A_LICENSE_URL)));
			((FeedItemFile) fi).setRate(c.getString(c.getColumnIndex(Db.A_RATE)));
			((FeedItemFile) fi).setSize(c.getString(c.getColumnIndex(Db.A_SIZE)));
			//Log.d(LOG_TAG, "size:"+((FeedItemFile) fi).getSize());
			((FeedItemFile) fi).setTags(c.getString(c.getColumnIndex(Db.A_TAGS)));
			((FeedItemFile) fi).setTorrent(c.getString(c.getColumnIndex(Db.A_TORRENT)));
		}
	}
	
 	public void openW() throws SQLException {
		db = dbHelper.getWritableDatabase();
	}
	
	public void openR() throws SQLException {
		db = dbHelper.getReadableDatabase();
	}

	public void close() {
		dbHelper.close();
	}
    
	public int updateModifyQuery(String title, String desc, int qid){
		ContentValues values = new ContentValues();
		values.put(Db.Q_TITLE, title);
		values.put(Db.Q_DESC, desc);
		
		int n = db.update(Db.T_QUERY, values, Db.Q_ID + " = ?", new String[]{String.valueOf(qid)});
		
		return n;
	}
	
	public int updateAttrQuery(String attr, String newValue, int qid){
		ContentValues values = new ContentValues();
		values.put(attr, newValue);
		
		int n = db.update(Db.T_QUERY, values, Db.Q_ID + " = ?", new String[]{String.valueOf(qid)});
		
		return n;
	}
	
	public int updateLastQuery(String last, int qid){
		ContentValues values = new ContentValues();
		values.put(Db.Q_LAST, last);
		
		int n = db.update(Db.T_QUERY, values, Db.Q_ID + " = ?", new String[]{String.valueOf(qid)});
		
		return n;
	}
	
	public int updateStatus(String table, int id, int status){
		String wattr = Db.I_STATUS;
		String uattr = Db.I_ID;
		if (table.equalsIgnoreCase(Db.T_QUERY)){
			uattr = Db.Q_STATUS;
			wattr = Db.Q_ID;
		} 
		
		ContentValues values = new ContentValues();
		values.put(uattr, status);
		
		int n = db.update(table, values, wattr + " = ?", new String[]{String.valueOf(id)});
		
		return n;
	}
	
	private int deleteAttr(int iid){
		int n = db.delete(Db.T_ATTR, Db.A_IID+" = ?", new String[]{String.valueOf(iid)});
		
		Log.d(LOG_TAG, "deleteAttr() n:"+n+" iid:"+iid);
		return n;
	}
	
	public int deleteItem(FeedItem fi){
		int iid = fi.getIid();
		
		if(! fi.getMediaType().equalsIgnoreCase("dir")){
			deleteAttr(iid);
		}
		
		int n = db.delete(Db.T_ITEM, Db.I_ID+" = ?", new String[]{String.valueOf(iid)});
		Log.d(LOG_TAG, "deleteItem() n:"+n+" iid:"+iid);
		
		return n;
	}
	
	public int deleteQuery(FeedQuery q){
		int qid = q.getQid();
		fillItem(q, qid, Db.I_PDATE + " DESC");
		
		HashMap<String, FeedItem> feeds = q.getFeedItems();
		Iterator<String> iterator = feeds.keySet().iterator();
		while (iterator.hasNext()) {
			String key = (String) iterator.next();
			deleteItem(q.getFeedItem(key));
		}
		
		int n = db.delete(Db.T_QUERY, Db.Q_ID+" = ?", new String[]{String.valueOf(qid)}); 
		//Log.d(LOG_TAG, "deleteQuery() num:"+q.getNumFeedItem()+" n:"+n+" qid:"+qid);
		
		return n;
	}
	
 	private void addAttr(FeedItemFile item, int iid){
		ContentValues values = new ContentValues();
		values.put(Db.A_IID, iid);
		values.put(Db.A_SIZE, item.getSize());
		values.put(Db.A_DURATION, item.getDuration());
		values.put(Db.A_CHANNELS, item.getChannels());
		values.put(Db.A_RATE, item.getRate());
		values.put(Db.A_TORRENT, item.getTorrent());
		values.put(Db.A_DOWNLOAD, item.getDownload());
		values.put(Db.A_LICENSE_URL, item.getLicenseCC());
		values.put(Db.A_TAGS, item.getTags());
		values.put(Db.A_CREATOR, item.getCreator());
		
		db.insertOrThrow(Db.T_ATTR, null, values);
		//Log.d(LOG_TAG,"addAttr");
	}
	
	public int addItem(FeedItem item, int qid, int status){
		int ret = Db.DB_OK;
		
		Log.d(LOG_TAG,"addItem:"+item.getTitle()+" qid:"+qid+" status:"+status);
		
		ContentValues values = new ContentValues();
		values.put(Db.I_QID, qid);
		values.put(Db.I_GUID, item.getGuid());
		values.put(Db.I_STATUS, status);
		values.put(Db.I_PDATE, item.getPubDate());
		String type = item.getMediaType();
		values.put(Db.I_TYPE, type);
		values.put(Db.I_TITLE, item.getTitle());
		values.put(Db.I_LINK, item.getLink());
		values.put(Db.I_QR, item.getQrcode());
		values.put(Db.I_DESC, item.getDescriprion());

		try {
			long iid = db.insertOrThrow(Db.T_ITEM, null, values);
			if(! type.equalsIgnoreCase("dir"))
				addAttr((FeedItemFile) item, (int) iid);
		} catch (SQLiteConstraintException e) {
			ret = Db.DB_ERR_CONSTRAINT_ADD;
		} catch (SQLException e) {
			ret = Db.DB_ERR_GENERIC;
		}
		return ret;
	}
	
	public int addQuery(FeedQuery q){
		int ret = Db.DB_OK;

		ContentValues values = new ContentValues();
		
		values.put(Db.Q_DESC, q.getDesc());
		values.put(Db.Q_LINK, q.getLink());
		values.put(Db.Q_TITLE, q.getTitle());
		values.put(Db.Q_LAST, q.getLast());
		values.put(Db.Q_REFRESHED, q.getRefresh());
		values.put(Db.Q_STATUS, Db.STATUS_NEW);
		
		try {
			long qid = db.insertOrThrow(Db.T_QUERY, null, values);
			HashMap<String, FeedItem> feeds = q.getFeedItems();
			Iterator<String> iterator = feeds.keySet().iterator();
			while (iterator.hasNext()) {
				String key = (String) iterator.next();
				addItem(q.getFeedItem(key), (int) qid, Db.STATUS_NEW);
				if(ret!=Db.DB_OK)
					break;
			}
		} catch (SQLiteConstraintException e) {
			ret = Db.DB_ERR_CONSTRAINT_ADD;
		} catch (SQLException e) {
			ret = Db.DB_ERR_GENERIC;
		}
		//Log.d(LOG_TAG,"addQuery");
		
		return ret;
	}
}
