package org.nutz.dao.impl.jdbc.sqlserver2005;

import java.util.ArrayList;
import java.util.List;

import org.nutz.dao.DB;
import org.nutz.dao.Dao;
import org.nutz.dao.Sqls;
import org.nutz.dao.entity.Entity;
import org.nutz.dao.entity.MappingField;
import org.nutz.dao.entity.PkType;
import org.nutz.dao.impl.entity.macro.SqlFieldMacro;
import org.nutz.dao.impl.jdbc.AbstractJdbcExpert;
import org.nutz.dao.jdbc.JdbcExpertConfigFile;
import org.nutz.dao.pager.Pager;
import org.nutz.dao.sql.PItem;
import org.nutz.dao.sql.Pojo;
import org.nutz.dao.sql.Sql;
import org.nutz.dao.util.Pojos;

/**
 * 
 * @author wendal
 */
public class Sqlserver2005JdbcExpert extends AbstractJdbcExpert {

    // private static String COMMENT_TABLE =
    // "EXECUTE sp_updateextendedproperty N'Description', '$tableComment', N'user', N'dbo', N'table', N'$table', NULL, NULL";

    private static String COMMENT_COLUMN = "EXECUTE sp_addextendedproperty N'Description', '$columnComment', N'user', N'dbo', N'table', N'$table', N'column', N'$column'";

    public Sqlserver2005JdbcExpert(JdbcExpertConfigFile conf) {
        super(conf);
    }

    public String getDatabaseType() {
        return DB.SQLSERVER.name();
    }

    public boolean createEntity(Dao dao, Entity<?> en) {
        StringBuilder sb = new StringBuilder("CREATE TABLE " + en.getTableName() + "(");
        // 创建字段
        for (MappingField mf : en.getMappingFields()) {
            sb.append('\n').append(mf.getColumnName());
            sb.append(' ').append(evalFieldType(mf));
            // 非主键的 @Name，应该加入唯一性约束
            if (mf.isName() && en.getPkType() != PkType.NAME) {
                sb.append(" UNIQUE NOT NULL");
            }
            // 普通字段
            else {
                if (mf.isUnsigned())
                    sb.append(" UNSIGNED");
                if (mf.isNotNull())
                    sb.append(" NOT NULL");
                if (mf.isAutoIncreasement())
                    sb.append(" IDENTITY");
                if (mf.hasDefaultValue())
                    sb.append(" DEFAULT '").append(getDefaultValue(mf)).append('\'');
            }
            sb.append(',');
        }
        // 创建主键
        List<MappingField> pks = en.getPks();
        if (!pks.isEmpty()) {
            sb.append('\n');
            sb.append("PRIMARY KEY (");
            for (MappingField pk : pks) {
                sb.append(pk.getColumnName()).append(',');
            }
            sb.setCharAt(sb.length() - 1, ')');
            sb.append("\n ");
        }

        // 结束表字段设置
        sb.setCharAt(sb.length() - 1, ')');

        // 执行创建语句
        dao.execute(Sqls.create(sb.toString()));
        // 创建索引
        dao.execute(createIndexs(en).toArray(new Sql[0]));
        // 创建关联表
        createRelation(dao, en);
        // 添加注释(表注释与字段注释)
        addComment(dao, en, COMMENT_COLUMN);

        return true;
    }

    private void addComment(Dao dao, Entity<?> en, String commentColumn) {
        // TODO 表注释 SQLServer2005中貌似不行
        // 字段注释
        if (en.hasColumnComment()) {
            List<Sql> sqls = new ArrayList<Sql>();
            for (MappingField mf : en.getMappingFields()) {
                if (mf.hasColumnComment()) {
                    Sql columnCommentSQL = Sqls.create(commentColumn);
                    columnCommentSQL.vars()
                                    .set("table", en.getTableName())
                                    .set("column", mf.getColumnName())
                                    .set("columnComment", mf.getColumnComment());
                    sqls.add(columnCommentSQL);
                }
            }
            // 执行创建语句
            dao.execute(sqls.toArray(new Sql[sqls.size()]));
        }
    }

    @Override
    protected String evalFieldType(MappingField mf) {
        if (mf.getCustomDbType() != null)
            return mf.getCustomDbType();
        switch (mf.getColumnType()) {
        case BOOLEAN:
            return "BIT";

        case TIMESTAMP:
            return "DATETIME";// TODO 值得讨论

        case DATETIME:
        case DATE:
        case TIME:
            return "DATETIME";
        case INT:
            // 用户自定义了宽度
            if (mf.getWidth() > 0)
                return "NUMERIC(" + mf.getWidth() + ")";
            // 用数据库的默认宽度
            return "INT";

        case FLOAT:
            // 用户自定义了精度
            if (mf.getWidth() > 0 && mf.getPrecision() > 0) {
                return "decimal(" + mf.getWidth() + "," + mf.getPrecision() + ")";
            }
            // 用默认精度
            if (mf.getTypeMirror().isDouble())
                return "decimal(15,10)";
            return "float";
        case BINARY:
            return "BINARY";
        default :
            break;
        }
        return super.evalFieldType(mf);
    }

    public void formatQuery(Pojo pojo) {
        Pager pager = pojo.getContext().getPager();
        if (null != pager && pager.getPageNumber() > 0) {
            // -----------------------------------------------------
            // TODO XXX 这个写法灰常暴力!!But , it works!!!! 期待更好的写法
            PItem pi = pojo.getItem(0);
            StringBuilder sb = new StringBuilder();
            pi.joinSql(pojo.getEntity(), sb);
            String str = sb.toString();
            if (str.trim().toLowerCase().startsWith("select")) {
                pojo.setItem(0, Pojos.Items.wrap(str.substring(6)));
            } else
                return;// 以免出错.
            pojo.insertFirst(Pojos.Items.wrapf(    "select * from(select row_number()over(order by __tc__)__rn__,* from(select top %d 0 __tc__, ",
                                                pager.getOffset() + pager.getPageSize()));
            pojo.append(Pojos.Items.wrapf(")t)tt where __rn__ > %d", pager.getOffset()));
        }
    }
    
    @Override
    public void formatQuery(Sql sql) {
        Pager pager = sql.getContext().getPager();
        // 需要进行分页
        if (null != pager && pager.getPageNumber() > 0) {
            // -----------------------------------------------------
            // TODO XXX 这个写法灰常暴力!!But , it works!!!! 期待更好的写法
            if (!sql.getSourceSql().toUpperCase().startsWith("SELECT "))
                return;// 以免出错.
            String xSql = sql.getSourceSql().substring(6);
            String pre = String.format(    "select * from(select row_number()over(order by __tc__)__rn__,* from(select top %d 0 __tc__, ",
                                                            pager.getOffset() + pager.getPageSize());
            String last = String.format(")t)tt where __rn__ > %d", pager.getOffset());
            sql.setSourceSql(pre + xSql + last);
        }
    }

    protected String createResultSetMetaSql(Entity<?> en) {
        return "SELECT top 1 * FROM " + en.getViewName();
    }
    
    public Pojo fetchPojoId(Entity<?> en, MappingField idField) {
        String autoSql = "SELECT @@@@IDENTITY as $field";
        Pojo autoInfo = new SqlFieldMacro(idField, autoSql);
        autoInfo.setEntity(en);
        return autoInfo;
    }
}

@@ -5888,32 +5888,48 @@
 here __rn__ %3E %25d
+ order by __rn__
 %22, pager.getOffs

fix issue #434