package com.hongshee.ejforum.data;

/**
 * <p>Title: SqlAdapter.java</p>
 * <p>Description: Standard sql stamement wrapper</p>
 * <p>Copyright: Hongshee Software (c) 2007</p>
 * @author jackie du
 * @version 1.0
 */

public abstract class SqlAdapter 
{
    protected SqlAdapter(){}

    /**
     * Do initialization according to different database
     * @param none
     * @return none
     * @throws Exception
     * @since 1.0
     */
    public abstract void init() throws Exception;

    /**
     * Append paging statement to query sql
     * @param
     *      sqlBuf - Original query sql
     *      pageNo - page number
     *      pageRows - each page size
     *      totalCount - total result count
     * @return 
     *      Paging query sql 
     * @throws none
     * @since 1.0
     */
    public abstract String getPageQuerySql(StringBuilder sqlBuf,
                                           int pageNo, int pageRows, int totalCount);

    /**
     * Get paging statement query parameters
     * @param
     *      pageNo - page number
     *      pageRows - each page size
     *      totalCount - total result count
     * @return 
     *      An int[2] result array which has following meaning:
     *      result[0] - 1st page parameter relative to getPageQuerySql function 
     *      result[1] - 2nd page parameter relative to getPageQuerySql function 
     * @throws none
     * @since 1.0
     */
    protected abstract int[] getPageQueryParams(int pageNo, int pageRows, int totalCount);
    
    // Group
    public String Group_Insert = 
        "insert into ejf_group(groupID,groupName,groupType,minCredits," +
        "stars,rights,createTime) values(?,?,?,?,?,?,NOW())";
    
    public String Group_Update = 
        "update ejf_group set groupName=?,minCredits=?,stars=?," +
        "updateTime=NOW() where groupID=?";
    
    public String Group_ModRights = 
        "update ejf_group set rights=?,updateTime=NOW() where groupID=?";
    
    public String Group_Delete = 
        "delete from ejf_group where groupID=?";
    
    public String Group_RemoveMembers = 
        "delete from ejf_group where groupType='M'";
    
    public String Group_SelectAll = 
        "select * from ejf_group order by groupType DESC,minCredits ASC,stars DESC";
    
    // User
    public String User_Insert = 
        "insert into ejf_user(userID,nickname,pwd,email,icq,webpage,gender,birth,city," +
        "remoteIP,brief,isMailPub,groupID,credits,state,lastVisited,createTime) " +
        "values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,NOW(),NOW())";
    
    public String User_Update = 
        "update ejf_user set nickname=?,email=?,icq=?,webpage=?,gender=?,birth=?," + 
        "city=?,isMailPub=?,updateTime=NOW() where userID=?";
    
    public String User_ModSpecInfo = 
        "update ejf_user set avatar=?,brief=?,updateTime=NOW() " + 
        "where userID=?";
    
    public String User_Login = 
        "select pwd,state,loginCount,updateTime," +
        "nickname,posts,unreadSMs,credits,groupID,lastVisited " +
        "from ejf_user where userID=?";
    
    public String User_QuickLogin = 
        "select loginExpire,nickname,posts,unreadSMs,credits,groupID,lastVisited,state " +
        "from ejf_user where userID=?";
    
    public String User_ModPasswd = 
        "update ejf_user set pwd=?,loginCount=0,updateTime=NOW() where userID=?";
    
    public String User_ModLastVisited = 
        "update ejf_user set visitCount=visitCount+1,lastVisited=NOW() " +
        "where userID=?";
    
    public String User_ModLoginCount = 
        "update ejf_user set loginCount=?,updateTime=NOW() where userID=?";
    
    public String User_ModLoginExpire = 
        "update ejf_user set loginCount=?,loginExpire=?,visitCount=visitCount+1," +
        "lastVisited=NOW(),updateTime=NOW() where userID=?";

    public String User_ClearLoginExpire = 
        "update ejf_user set loginExpire=?,updateTime=NOW() where userID=?";
    
    public String User_ModSetpwdExpire = 
        "update ejf_user set setpwdExpire=? where userID=?";
    
    public String User_IncPosts = 
        "update ejf_user set posts=posts+1 where userID=?";

    public String User_DecPostsAndCredits = 
        "update ejf_user set posts=posts-1,credits=credits-? where posts>0 and userID=?";

    public String User_IncCredits = 
        "update ejf_user set credits=credits+? where userID=?";

    public String User_DecCredits = 
        "update ejf_user set credits=credits-? where userID=?";
    
    public String User_IncUnreadSMs = 
        "update ejf_user set unreadSMs=unreadSMs+1 where userID=?";
    
    public String User_DecUnreadSMs = 
        "update ejf_user set unreadSMs=unreadSMs-1 where unreadSMs>0 and userID=?";
    
    public String User_StatUnreadSMs = 
        "update ejf_user set unreadSMs=? where userID=?";

    public String User_Delete = 
        "delete from ejf_user where userID=?";

    public String User_CleanExpired = 
        "delete from ejf_user where lastVisited < ? and posts = 0";
    
    public String User_Select = 
        "select * from ejf_user where userID=?";
    
    public String User_IsExistedID = 
        "select COUNT(*) from ejf_user where userID=?";
    
    public String User_IsExistedMail = 
        "select COUNT(*) from ejf_user where email=?";
    
    public String User_GetMailFromID = 
        "select email from ejf_user where userID=?";
    
    public String User_GetIDFromMail = 
        "select userID from ejf_user where email=?";
    
    public String User_GetSetpwdExpire = 
        "select setpwdExpire from ejf_user where userID=?";
    
    public String User_QueryInfo = 
        "select userID,nickname,posts,credits,groupID,state from ejf_user";

    public String User_GetList = 
        "select userID,nickname,gender,credits,posts,groupID,state,lastVisited," +
        "createTime from ejf_user where state<>'A' and state<>'S'";

    public String User_GetAuditHandler = 
        "select userID,email from ejf_user " +
        "where state<>'A' and state<>'S' and groupID in";

    public String User_GetReportHandler = 
        "select userID,email from ejf_user " +
        "where state<>'A' and state<>'S' and (groupID='A'";
    
    public String User_GetCount = 
        "select COUNT(*) from ejf_user where state<>'A' and state<>'S'";
    
    public String User_ModGroupID = 
        "update ejf_user set groupID=?,updateTime=NOW() where userID=?";

    public String User_ModState = 
        "update ejf_user set state=?,updateTime=NOW()";

    public String User_GetAuditing = 
        "select userID,remoteIP,email,createTime from ejf_user where state='A'";
    
    // Section
    public String Section_Insert = 
        "insert into ejf_section(sectionName,seqno,cols,createTime) values(?,?,?,NOW())";
    
    public String Section_Update = 
        "update ejf_section set sectionName=?,cols=?,updateTime=NOW() where sectionID=?";
    
    public String Section_ModSeqno = 
        "update ejf_section set seqno=?,updateTime=NOW() where sectionID=?";
    
    public String Section_ModModerator = 
        "update ejf_section set moderator=?,updateTime=NOW() where sectionID=?";
    
    public String Section_Delete = 
        "delete from ejf_section where sectionID=?";
    
    public String Section_SelectAll = 
        "select sectionID,sectionName,seqno,cols,moderator " + 
        "from ejf_section order by seqno ASC";
    
    public String Section_SelectBoards = 
        "select a.sectionID,a.seqno as s_seqno," + 
        "b.boardID,b.boardName,b.highColor,b.seqno,b.brief,b.keywords,b.moderator," +
        "b.viewStyle,b.sortField,b.isImageOK,b.isMediaOK,b.isGuestPostOK,b.allowGroups," +
        "b.acl,b.ruleCode,b.headAdCode,b.footAdCode,b.state from ejf_section a,ejf_board b " +
        "where b.sectionID = a.sectionID order by s_seqno ASC,b.seqno ASC";

    // Board
    public String Board_Insert = 
        "insert into ejf_board(sectionID,boardName,seqno,createTime) values(?,?,?,NOW())";
    
    public String Board_Update = 
        "update ejf_board set sectionID=?,boardName=?,highColor=?,brief=?,keywords=?," +
        "viewStyle=?,sortField=?,isImageOK=?,isMediaOK=?,isGuestPostOK=?,allowGroups=?," +
        "acl=?,ruleCode=?,headAdCode=?,footAdCode=?,state=?,updateTime=NOW() where boardID=?";

    public String Board_CopyInfo = 
        "update ejf_board set viewStyle=?,sortField=?,isImageOK=?,isMediaOK=?,isGuestPostOK=?," +
        "allowGroups=?,acl=?,ruleCode=?,headAdCode=?,footAdCode=?,updateTime=NOW() where boardID=?";
    
    public String Board_ModSeqno = 
        "update ejf_board set seqno=?,updateTime=NOW() where boardID=?";
    
    public String Board_ModModerator = 
        "update ejf_board set moderator=?,updateTime=NOW() where boardID=?";
    
    public String Board_Delete = 
        "delete from ejf_board where boardID=?";

    public String Board_StatTopics = 
        "select boardID,COUNT(topicID) as topics,SUM(replies) as posts," +
        "MAX(topicID) as lastTopicID from ejf_topic " +
        "where state <> 'R' group by boardID";

    public String Board_StatTodayTopics = 
        "select boardID,COUNT(topicID) as topics " +
        "from ejf_topic where createTime > ? and state<>'R' group by boardID";

    public String Board_StatTodayReplies = 
        "select a.boardID,COUNT(b.replyID) as replies from ejf_topic a, ejf_reply b " +
        "where a.topicID = b.topicID and b.createTime > ? " +
        "and a.state<>'R' and b.state<>'R' group by a.boardID";
    
    public String Board_GetLastTopic = 
        "select boardID,topicID,title,lastPostUser,lastNickname,lastPostTime from ejf_topic " +
        "where topicID in";
    
    public String Board_RemoveTopics = 
        "delete from ejf_topic where boardID=?";
    
    public String Board_RemoveTrashs = 
        "delete from ejf_trash_box where boardID=?";
    
    //public String Board_RemoveBookmarks = 
        //"delete from ejf_bookmark where boardID=?";