package railo.commons.io.res.type.datasource.core;

import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import railo.commons.db.DBUtil;
import railo.commons.io.res.type.datasource.Attr;
import railo.commons.lang.StringUtil;
import railo.runtime.db.DatasourceConnection;

public class MSSQL extends CoreSupport {

	private static final int DEFAULT_MODE = 0777;
	private static final int DEFAULT_ATTRS = 0;

	public MSSQL(DatasourceConnection dc, String prefix) throws SQLException {
		Connection conn = dc.getConnection();
		Statement stat1=null;
		ResultSet rs=null;
		boolean installAttrs=true;
		boolean installData=true;

		
		// check attr
		String sql="select count(*) as cnt from dbo.sysobjects where id = object_id(N'[dbo].[" + prefix + "attrs]') and OBJECTPROPERTY(id, N'IsUserTable') = 1";
		try{
			stat1=conn.createStatement();
			rs=stat1.executeQuery(sql);
			if(rs.next()){
				if(rs.getInt(1)>0) installAttrs=false;
			}
		}
		finally {
			DBUtil.closeEL(rs);
			DBUtil.closeEL(stat1);
		}

		// check data
		sql="select count(*) as CNT from dbo.sysobjects where id = object_id(N'[dbo].["+prefix+"data]') and OBJECTPROPERTY(id, N'IsUserTable') = 1";
		try{
			stat1=conn.createStatement();
			rs=stat1.executeQuery(sql);
			if(rs.next()){
				if(rs.getInt(1)>0) installData=false;
			}
		}
		finally {
			DBUtil.closeEL(rs);
			DBUtil.closeEL(stat1);
		}
		
				
		if(installAttrs){
			execute(conn,"CREATE TABLE [dbo].["+prefix+"attrs] (" +
				"[rdr_id] [int] IDENTITY (1, 1) NOT NULL ," +
				"[rdr_name] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ," +
				"[rdr_path_hash] [int] NULL ," +
				"[rdr_full_path_hash] [int] NULL ," +
				"[rdr_path] [varchar] (2048) COLLATE Latin1_General_CI_AS NULL ," +
				"[rdr_type] [int] NULL ," +
				"[rdr_last_modified] [datetime] NULL ," +
				"[rdr_mode] [int] NULL ," +
				"[rdr_attributes] [int] NULL ," +
				"[rdr_data] [int] NULL ," +
				"[rdr_length] [int] NULL" +
				") ON [PRIMARY]");
			
			execute(conn, "ALTER TABLE [dbo].["+prefix+"attrs] WITH NOCHECK ADD " +
				"CONSTRAINT [PK_"+prefix+"attrs] PRIMARY KEY  CLUSTERED " +
				"([rdr_id])  ON [PRIMARY] ");
	
			execute(conn, "ALTER TABLE [dbo].["+prefix+"attrs] ADD " +
				"CONSTRAINT [DF_"+prefix+"attrs_rdr_mode] DEFAULT (0) FOR [rdr_mode]," +
				"CONSTRAINT [DF_"+prefix+"attrs_rdr_attributes] DEFAULT (0) FOR [rdr_attributes]," +
				"CONSTRAINT [DF_"+prefix+"attrs_rdr_length] DEFAULT (0) FOR [rdr_length]");
	
			execute(conn, "CREATE  INDEX [IDX_name] ON [dbo].["+prefix+"attrs]([rdr_name]) ON [PRIMARY]");
			execute(conn, "CREATE  INDEX [IDX_id] ON [dbo].["+prefix+"attrs]([rdr_data]) ON [PRIMARY]");
			execute(conn, "CREATE  INDEX [idx_path] ON [dbo].["+prefix+"attrs]([rdr_path_hash]) ON [PRIMARY]");
			execute(conn, "CREATE  INDEX [idx_full_path] ON [dbo].["+prefix+"attrs]([rdr_full_path_hash]) ON [PRIMARY]");
		}
		
		if(installData){
			execute(conn, "CREATE TABLE [dbo].["+prefix+"data] (" +
				"[rdr_id] [int] IDENTITY (1, 1) NOT NULL ," +
				"[rdr_data] [image] NULL" +
				") ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]");

			execute(conn, "ALTER TABLE [dbo].["+prefix+"data] WITH NOCHECK ADD " +
				"CONSTRAINT [PK_"+prefix+"data] PRIMARY KEY  CLUSTERED " +
				"([rdr_id])  ON [PRIMARY] ");
		}
	}
	
	
	private void execute(Connection conn, String sql) throws SQLException {
		log(sql);
		Statement stat=null;
		try{
			stat=conn.createStatement();
			stat.executeUpdate(sql);
		}
		finally {
			DBUtil.closeEL(stat);
		}
	}


	@Override
	public Attr getAttr(DatasourceConnection dc, String prefix, int fullPathHash,String path, String name) throws SQLException {
		// ROOT
		if(StringUtil.isEmpty(path))
			return ATTR_ROOT;
		
		
		String sql="select rdr_id,rdr_type,rdr_length,rdr_last_modified,rdr_mode,rdr_attributes,rdr_data from "+prefix+"attrs where rdr_full_path_hash=? and rdr_path=? and rdr_name=?";
		PreparedStatement stat = prepareStatement(dc, sql);//dc.getConnection().prepareStatement(sql);
		stat.setInt(1, fullPathHash);
		stat.setString(2, path);
		stat.setString(3, name);
		log(sql,fullPathHash+"",path,name);
		
			ResultSet rs = stat.executeQuery();
		try{
			if(!rs.next())return null;
			
			return new Attr(
						rs.getInt(1),
						name,path,true,
						rs.getInt(2),
						rs.getInt(3),
						rs.getTimestamp(4,getCalendar()).getTime(),
						rs.getShort(5),
						rs.getShort(6),
						rs.getInt(7));
		}
		finally {
			DBUtil.closeEL(rs);
			//DBUtil.closeEL(stat);
		}
	}


	@Override
	public List getAttrs(DatasourceConnection dc, String prefix, int pathHash,String path) throws SQLException {
		String sql="select rdr_id,rdr_name,rdr_type,rdr_length,rdr_last_modified,rdr_mode,rdr_attributes,rdr_data from "+prefix+"attrs where rdr_path_hash=? and rdr_path=? order by rdr_name";
		PreparedStatement stat =  prepareStatement(dc, sql);//dc.getConnection().prepareStatement(sql);
		stat.setInt(1, pathHash);
		stat.setString(2, path);
		log(sql,pathHash+"",path);
		
		ResultSet rs = stat.executeQuery();
		
		try{
			List attrs=new ArrayList();
			//hashCode=(path+name).hashCode();
			while(rs.next()) {
				attrs.add( new Attr(
						rs.getInt(1),
						rs.getString(2),
						path,
						true,
						rs.getInt(3),
						rs.getInt(4),
						rs.getTimestamp(5,getCalendar()).getTime(),
						rs.getShort(6),
						rs.getShort(7),
						rs.getInt(8)
					));
			}
			return attrs;
		}
		finally {
			DBUtil.closeEL(rs);
			//DBUtil.closeEL(stat);
		}
	}

	@Override
	public void create(DatasourceConnection dc, String prefix, int fullPatHash,int pathHash,String path, String name, int type) throws SQLException {
		String sql="insert into "+prefix+"attrs(rdr_type,rdr_path,rdr_name,rdr_full_path_hash,rdr_path_hash,rdr_last_modified,rdr_mode,rdr_attributes,rdr_data,rdr_length) " +
				"values(?,?,?,?,?,?,?,?,?,?)";
		PreparedStatement stat = prepareStatement(dc, sql);//dc.getConnection().prepareStatement(sql);
		log(sql);
		stat.setInt(1,type);
		stat.setString(2, path);
		stat.setString(3, name);
		stat.setInt(4, fullPatHash);
		stat.setInt(5, pathHash);
		stat.setTimestamp(6, new Timestamp(System.currentTimeMillis()),getCalendar());
		stat.setInt(7, DEFAULT_MODE);
		stat.setInt(8, DEFAULT_ATTRS);
		stat.setInt(9, 0);
		stat.setInt(10, 0);
		//try{
			stat.executeUpdate();
		/*}
		finally {
			//DBUtil.closeEL(stat);
		}*/
	}

	@Override
	public boolean delete(DatasourceConnection dc, String prefix, Attr attr) throws SQLException {
		boolean rst=false;
		if(attr!=null){
			String sql="delete from "+prefix+"attrs where rdr_id=?";
			log(sql,attr.getId()+"");
			PreparedStatement stat = prepareStatement(dc, sql);//dc.getConnection().prepareStatement(sql);
			stat.setInt(1,attr.getId());
			
			//try{
				rst = stat.executeUpdate()>0;
			/*}
			finally {
				//DBUtil.closeEL(stat);
			}*/
			
			if(attr.getData()>0) {
				sql="delete from "+prefix+"data where rdr_id=?";
				log(sql,attr.getData()+"");
				stat = prepareStatement(dc, sql);//dc.getConnection().prepareStatement(sql);
				stat.setInt(1,attr.getData());
				//try{
					stat.executeUpdate();
				/*}
				finally {
					//DBUtil.closeEL(stat);
				}*/
			}
		}
		return rst;
	}
	
	@Override
	public InputStream getInputStream(DatasourceConnection dc, String prefix, Attr attr) throws SQLException, IOException {
		if(attr==null || attr.getData()==0) return new ByteArrayInputStream(new byte[0]);
		
		String sql="select rdr_data from "+prefix+"data where rdr_id=?";
		log(sql,attr.getData()+"");
		PreparedStatement stat = prepareStatement(dc, sql);//dc.getConnection().prepareStatement(sql);
		stat.setInt(1,attr.getData());

		ResultSet rs=null;
		try{
			rs = stat.executeQuery();
			if(!rs.next()){
				throw new IOException("can't read data from "+attr.getParent()+attr.getName());
			}
			return rs.getBlob(1).getBinaryStream();
		}
		finally {
			DBUtil.closeEL(rs);
			//DBUtil.closeEL(stat);
		}
	}

	@Override
	public void write(DatasourceConnection dc, String prefix, Attr attr, InputStream is, boolean append) throws SQLException {
		if(attr.getData()==0) {
			writeInsert(dc, prefix, attr, is);
		}
		else writeUpdate(dc, prefix, attr, is,append);
	}
	
	private void writeUpdate(DatasourceConnection dc, String prefix, Attr attr, InputStream is, boolean append) throws SQLException {
		String sql;
		
		
		if(append) {
			sql="DECLARE @ptrval binary(16);" +
				"DECLARE @iLen int;" +
				"SELECT @ptrval = TEXTPTR(rdr_data), @iLen = dataLength(rdr_data)" +
				"FROM "+prefix+"data " +
				"WHERE rdr_id = ? " +
				"UPDATETEXT "+prefix+"data.rdr_data @ptrval @iLen 0 ?;";
		}
		else {
			sql="update "+prefix+"data set rdr_data=? where rdr_id=?";
		}
		
		
		
		log(sql);
		PreparedStatement stat1=null;
		PreparedStatement stat2=null;
		PreparedStatement stat3=null;
		ResultSet rs=null;
		//try{
			//Connection conn = dc.getConnection();
			stat1 = prepareStatement(dc, sql);//dc.getConnection().prepareStatement(sql);
			if(append) {
				stat1.setInt(1, attr.getData());
				stat1.setBinaryStream(2, is,-1);
			}
			else {
				stat1.setBinaryStream(1, is,-1);
				stat1.setInt(2, attr.getData());
			}
			stat1.executeUpdate();
			
			// select
			sql="select dataLength(rdr_data) as DataLen from "+prefix+"data where rdr_id=?";
			log(sql);
			stat2=prepareStatement(dc, sql);//dc.getConnection().prepareStatement(sql);
			stat2.setInt(1, attr.getData());
			rs=stat2.executeQuery();
			
			if(rs.next()){	
				sql="update "+prefix+"attrs set rdr_length=? where rdr_id=?";
				log(sql);
				stat3 = prepareStatement(dc, sql);//dc.getConnection().prepareStatement(sql);
				stat3.setInt(1, rs.getInt(1));
				stat3.setInt(2, attr.getId());
				stat3.executeUpdate();
			}
		/*}
		finally {
			//DBUtil.closeEL(stat1);
			//DBUtil.closeEL(stat2);
			//DBUtil.closeEL(stat3);
		}*/
	}

	private void writeInsert(DatasourceConnection dc, String prefix, Attr attr, InputStream is) throws SQLException {
		PreparedStatement stat1=null;
		PreparedStatement stat2=null;
		PreparedStatement stat3=null;
		ResultSet rs=null;
		try{
			// Insert
			String sql="insert into "+prefix+"data (rdr_data) values(?)";
			log(sql);
			//Connection conn = dc.getConnection();
			stat1 = prepareStatement(dc, sql);//dc.getConnection().prepareStatement(sql);
			stat1.setBinaryStream(1, is,-1);
			stat1.execute();
			
			// select
			sql="select TOP 1 rdr_id,dataLength(rdr_data) as DataLen from "+prefix+"data order by rdr_id desc";
			log(sql);
			stat2=prepareStatement(dc, sql);//conn.createStatement();
			rs=stat2.executeQuery();
			
			// update
			if(rs.next()){
				sql="update "+prefix+"attrs set rdr_data=?,rdr_length=? where rdr_id=?";
				log(sql);
				stat3 = prepareStatement(dc, sql);//dc.getConnection().prepareStatement(sql);
				stat3.setInt(1, rs.getInt(1));
				stat3.setInt(2,rs.getInt(2));
				stat3.setInt(3, attr.getId());
				stat3.executeUpdate();
			}
			 
		}
		finally {
			DBUtil.closeEL(rs);
			//DBUtil.closeEL(stat1);
			//DBUtil.closeEL(stat2);
			//DBUtil.closeEL(stat3);
		}
	}
	



	@Override
	public void setLastModified(DatasourceConnection dc, String prefix, Attr attr, long time) throws SQLException {
		String sql="update "+prefix+"attrs set rdr_last_modified=? where rdr_id=?";
		log(sql);
		PreparedStatement stat=null;
		//try{
			stat = prepareStatement(dc, sql);//dc.getConnection().prepareStatement(sql);
			stat.setTimestamp(1, new Timestamp(time),getCalendar());
			stat.setInt(2, attr.getId());
			stat.executeUpdate();
		/*}
		finally {
			//DBUtil.closeEL(stat);
		}*/
	}


	@Override
	public void setMode(DatasourceConnection dc,String prefix, Attr attr, int mode) throws SQLException {
		String sql="update "+prefix+"attrs set rdr_mode=? where rdr_id=?";
		log(sql);
		PreparedStatement stat=null;
		//try{
			stat = prepareStatement(dc, sql);//dc.getConnection().prepareStatement(sql);
			stat.setInt(1, mode);
			stat.setInt(2, attr.getId());
			stat.executeUpdate();
		/*}
		finally {
			//DBUtil.closeEL(stat);
		}*/
	}


	@Override
	public void setAttributes(DatasourceConnection dc,String prefix, Attr attr, int attributes) throws SQLException {
		String sql="update "+prefix+"attrs set rdr_attributes=? where rdr_id=?";
		log(sql);
		PreparedStatement stat=null;
		//try{
			stat = prepareStatement(dc, sql);//dc.getConnection().prepareStatement(sql);
			stat.setInt(1, attributes);
			stat.setInt(2, attr.getId());
			stat.executeUpdate();
		/*}
		finally {
			//DBUtil.closeEL(stat);
		}*/
	}

	@Override
	public boolean concatSupported() {
		return true;
	}
}
