
package org.arkiwi.app.database;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class Db extends SQLiteOpenHelper {
	private static final String LOG_TAG = "Db";
    private static final int DB_VERSION = 5;
    private static final String DB_NAME = "aarkiwi.db";
    
    public static final int DB_OK = 1;
    public static final int DB_ERR_UNDEF = -1;
    public static final int DB_ERR_GENERIC = -2;
    public static final int DB_ERR_CONSTRAINT_ADD = -3;
 
    // Table name
    public static final String T_ITEM = "items";
    public static final String T_ATTR = "attributes";
    public static final String T_QUERY = "querys";
    // Table Attributes names
    // T_QUERY
    public static final String Q_ID = "qid";    
    public static final String Q_REFRESHED = "refresh"; 
    public static final String Q_LAST = "last"; 
    public static final String Q_TITLE = "title";
    public static final String Q_LINK = "link";
    public static final String Q_DESC = "desc";
    public static final String Q_STATUS = "status";
    
    private static final String C_QUERY_T = 
			"CREATE TABLE \""+T_QUERY+"\" ("+
		    "\""+Q_ID+"\" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,"+
		    "\""+Q_REFRESHED+"\" TEXT NOT NULL,"+
		    "\""+Q_LAST+"\" TEXT NOT NULL,"+
		    "\""+Q_TITLE+"\" TEXT NOT NULL,"+
		    "\""+Q_LINK+"\" TEXT NOT NULL UNIQUE,"+
		    "\""+Q_DESC+"\" TEXT,"+
		    "\""+Q_STATUS+"\" INTEGER NOT NULL"+
		    ");";

    // T_ITEM
    public static final int STATUS_DELETED = 0;
    public static final int STATUS_NEW = 1;
    public static final int STATUS_MOD = 2;
    public static final int STATUS_OLD = 3;
    
    public static final String I_ID = "iid";
    public static final String I_QID = "qid";
    public static final String I_GUID = "guid";
    public static final String I_STATUS = "status";
    public static final String I_PDATE = "pub_date";
    public static final String I_TYPE = "tipo";
    public static final String I_TITLE = "titolo";
    public static final String I_LINK = "link";
    public static final String I_QR = "qrcode";
    public static final String I_DESC = "descrizione";

    private static final String C_ITEM_T = 
			"CREATE TABLE \""+T_ITEM+"\" ("+
		    "\""+I_ID+"\" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,"+
		    "\""+I_QID+"\" INTEGER NOT NULL REFERENCES "+T_QUERY+"("+Q_ID+") ,"+
		    "\""+I_GUID+"\" TEXT UNIQUE NOT NULL,"+
		    "\""+I_STATUS+"\" INTEGER NOT NULL,"+
		    "\""+I_PDATE+"\" TEXT NOT NULL,"+
		    "\""+I_TYPE+"\" TEXT NOT NULL,"+
		    "\""+I_TITLE+"\" TEXT NOT NULL,"+
		    "\""+I_LINK+"\" TEXT UNIQUE NOT NULL,"+
		    "\""+I_QR+"\" TEXT UNIQUE NOT NULL,"+
		    "\""+I_DESC+"\" TEXT"+
		    ");";
    //T_ATTR
    public static final String A_ID = "aid";
    public static final String A_IID = "iid";
    public static final String A_SIZE = "grandezza";
    public static final String A_DURATION = "durata";
    public static final String A_CHANNELS = "canali";
    public static final String A_RATE = "sampleRate";
    public static final String A_TORRENT = "torrent";
    public static final String A_DOWNLOAD = "download";
    public static final String A_LICENSE_URL = "licenza_url";
    public static final String A_TAGS = "tags";
    public static final String A_CREATOR = "creatore";

    private static final String C_ATTR_T = 
			"CREATE TABLE \""+T_ATTR+"\" ("+
		    "\""+A_ID+"\" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,"+
		    "\""+A_IID+"\" INTEGER NOT NULL REFERENCES "+T_ITEM+"("+I_ID+") ,"+
		    "\""+A_SIZE+"\" LONG NOT NULL,"+
		    "\""+A_DURATION+"\" LONG NOT NULL,"+
		    "\""+A_CHANNELS+"\" TEXT NOT NULL,"+
		    "\""+A_RATE+"\" TEXT NOT NULL,"+
		    "\""+A_TORRENT+"\" TEXT NOT NULL,"+
		    "\""+A_DOWNLOAD+"\" TEXT NOT NULL,"+
		    "\""+A_LICENSE_URL+"\"TEXT,"+
		    "\""+A_TAGS+"\" TEXT,"+
		    "\""+A_CREATOR+"\" TEXT,"+
		    "UNIQUE ("+A_ID+","+A_IID+")"+
		    ");";

    public Db(Context context) {
        super(context, DB_NAME, null, DB_VERSION);
    }
 
	@Override
	public void onCreate(SQLiteDatabase db) {
		Log.i(LOG_TAG,"onCreate()");
		
		db.execSQL(C_ITEM_T);
		db.execSQL(C_QUERY_T);
		db.execSQL(C_ATTR_T);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		Log.i(LOG_TAG,"onUpgrade()");
		db.execSQL("DROP TABLE IF EXISTS " + T_ATTR);
		db.execSQL("DROP TABLE IF EXISTS " + T_ITEM);
		db.execSQL("DROP TABLE IF EXISTS " + T_QUERY);
		onCreate(db);
	}
}