import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import java.util.ArrayList;

/**
 * Created by 300079058 on 25/04/2016.
 */
public class DBHandler extends SQLiteOpenHelper {

    private final static String DATABASE_NAME ="GCCDatabase";
    private final String TABLE_LOGIN = "Login";
    private final String LOGIN_USERNAME= "Username";
    private final String LOGIN_PASSWORD= "Password";

    private final String TABLE_USER = "User";
    private final String USER_LOGINDETAILS= "Username";
    private final String USER_FIRSTNAME= "FirstName";
    private final String USER_LASTNAME= "LastName";
    private final String USER_EMAILADDRESS ="EmailAddress";

    private final String TABLE_ARTICLE = "Article";
    private final String ARTICLE_ARTICLENAME = "ArticleName";
    private final String ARTICLE_ARTICLECONTENTS = "ArticleContents";


    private final String TABLE_COMMENT = "Comment";
    private final String COMMENT_USERCOMMENT = "UserComment";
    private final String COMMENT_USERRATING = "UsersRating";
    private final String COMMENT_USERNAME = "Username";
    private final String COMMENT_ARTICLENAME = "ArticleName";






    public DBHandler(Context context)
    {

        super(context, DATABASE_NAME, null, 1);

    }


    @Override
    public void onCreate(SQLiteDatabase db)
    {

        //LOGIN
        String CREATE_LOGIN_TABLE = "CREATE TABLE " + TABLE_LOGIN + " (" +
                LOGIN_USERNAME + " TEXT PRIMARY KEY, " +
                LOGIN_PASSWORD + " TEXT)";
        db.execSQL(CREATE_LOGIN_TABLE);


        //SIGN UP
        String CREATE_USER_TABLE = "CREATE TABLE " + TABLE_USER + " (" +
                USER_LOGINDETAILS + " TEXT PRIMARY KEY, " +
                USER_FIRSTNAME + " TEXT, " +
                USER_LASTNAME + " TEXT, " +
                USER_EMAILADDRESS + " TEXT)";
        db.execSQL(CREATE_USER_TABLE);


        //ARTICLE
        String CREATE_ARTICLE_TABLE = "CREATE TABLE " + TABLE_ARTICLE + " (" +
                ARTICLE_ARTICLENAME + " TEXT PRIMARY KEY, " +
                ARTICLE_ARTICLECONTENTS  + " TEXT)";
        db.execSQL(CREATE_ARTICLE_TABLE);


        //COMMENT
        String CREATE_COMMENT_TABLE = "CREATE TABLE " + TABLE_COMMENT + " (" +
                COMMENT_USERCOMMENT + " TEXT, " +
                COMMENT_USERRATING  + " INTEGER, " +
                COMMENT_USERNAME + " TEXT, " +
                COMMENT_ARTICLENAME + " TEXT, " +
                "PRIMARY KEY (Username, ArticleName))";
        db.execSQL(CREATE_COMMENT_TABLE);



    }

    public void addLogin(Login login, SQLiteDatabase database)
    {
        ContentValues values = new ContentValues();
        values.put(LOGIN_USERNAME, login.getUsername());
        values.put(LOGIN_PASSWORD, login.getPassword());
        database.insert(TABLE_LOGIN, null, values);
    }


    public void addUser(User user, SQLiteDatabase database)
    {
        ContentValues values = new ContentValues();
        values.put(USER_LOGINDETAILS, user.getLoginDetails().getUsername());
        values.put(USER_FIRSTNAME, user.getFirstName());
        values.put(USER_LASTNAME, user.getLastName());
        values.put(USER_EMAILADDRESS, user.getEmailAddress());
        database.insert(TABLE_USER, null, values);
    }

    public void addComment(Comment comment, SQLiteDatabase database)
    {
        ContentValues values = new ContentValues();
        values.put(COMMENT_USERCOMMENT, comment.getUsersComment());
        values.put(COMMENT_USERRATING, comment.getUsersRating());
        values.put(COMMENT_USERNAME, comment.getUsername());
        values.put(COMMENT_ARTICLENAME, comment.getArticleName());

        database.insert(TABLE_COMMENT, null, values);
    }


    public ArrayList<Comment> getComments(String articleName){
        ArrayList<Comment> comments = new ArrayList<Comment>();
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery("SELECT * FROM Comment WHERE ArticleName='" + articleName + "'", null);

        if (cursor.getCount() !=0){
            if (cursor.moveToFirst()){
                do{
                    String usersComment = cursor.getString(cursor.getColumnIndex("UsersComment"));
                    Double usersRating = cursor.getDouble(cursor.getColumnIndex("UsersRating"));
                    String username = cursor.getString(cursor.getColumnIndex("Username"));
                    String article = cursor.getString(cursor.getColumnIndex("ArticleName"));

                    Comment foundcomment = new Comment(usersComment, usersRating, username, article);
                    comments.add(foundcomment);
                }while(cursor.moveToNext());
            }else{
                return comments;
            }
        }else{
            return comments;
        }
        return comments;
    }



    public boolean validateLogin(String username, String password)
    {
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery("SELECT * FROM Login WHERE Username='" + username + "' AND Password= '" + password + "'", null);
        if (cursor.getCount() != 0)
        {
            return true;
        }
        else
        {
            return false;
        }

    }



    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
    {
        String deleteTable = "DROP TABLE IF EXISTS " + TABLE_LOGIN;
        db.execSQL(deleteTable);
        onCreate(db);

    }





}