/**
 * 
 */
package stack.patterns.factory.method.parameterized;

/**
 * @author Damjan Vantur
 *
 */
public class OracleDatabaseTable extends DatabaseTable {

	public OracleDatabaseTable(String tableName, String idFieldName) {
		this.name = tableName;
		this.idFiledName = idFieldName;
		this.sequenceString = null;
		this.triggerString = null;
		this.tableString = null;
	}
	
	@Override
	void createTable() {
		
		/* Refactor so additional fields can be inserted here */
		
		StringBuilder builder = new StringBuilder();
		builder.append("CREATE TABLE ");
		builder.append(this.getName());
		builder.append("('");
		builder.append(this.getIdFiledName());
		builder.append("' NUMBER(*,0) NOT NULL, \n");
		builder.append("'POID_TYPE' VARCHAR2(255), \n");
		builder.append("'CREATED_T' NUMBER(*,0), \n");
		builder.append("'MODIFIED_T' NUMBER(*,0), \n");
		
		builder.append("CONSTRAINT ");
		builder.append(this.getIdFiledName());
		builder.append("_PK ");
		builder.append("PRIMARY KEY ('");
		builder.append(this.getIdFiledName());
		builder.append("') \n");
		builder.append(");");
		
		this.tableString = builder.toString();
		
	}
	
	@Override
	void prepareIdSequence() {
		
		StringBuilder builder = new StringBuilder();
		builder.append("CREATE SEQUENCE ");
		builder.append(this.getName());
		builder.append("_");
		builder.append(this.getIdFiledName());
		builder.append("_");
		builder.append("SQ");
		builder.append("\n");
		
		builder.append("INCREMENT BY 1 \n");
		builder.append("START WITH 1 \n");
		builder.append("MAXVALUE 999999999999 \n");
		builder.append("MINVALUE 1 \n");
		builder.append("CYCLE \n");
		builder.append("NOCACHE  \n");
		builder.append("ORDER;");
		
		this.sequenceString = builder.toString();
		
	}
	
	@Override
	void prepareAutoAssignIdTrigger() {

		StringBuilder builder = new StringBuilder();
		
		builder.append("CREATE OR REPLACE TRIGGER ");
		builder.append(this.getName());
		builder.append("_");
		builder.append(this.getIdFiledName());
		builder.append("_");
		builder.append("TG");
		builder.append("\n");
		
		builder.append("BEFORE INSERT ON ");
		builder.append(this.getName());
		builder.append("\n");
		
		builder.append("FOR EACH ROW \n");
		builder.append("DECLARE \n");
		builder.append("M_POID NUMBER; \n");
		builder.append("CUR_POID NUMBER; \n");
		builder.append("BEGIN \n");
		builder.append("IF inserting THEN \n");
		builder.append("IF :NEW.'");
		builder.append(this.idFiledName);
		builder.append("' IS NULL THEN \n");
		
		builder.append("SELECT ");
		builder.append(this.getName());
		builder.append("_");
		builder.append(this.getIdFiledName());
		builder.append("_");
		builder.append("SQ.nextval");
		builder.append("\n");
		
		builder.append("INTO :NEW.'");
		builder.append(this.getIdFiledName());
		builder.append("' FROM DUAL; \n");
		
		builder.append("ELSE \n");
		
		builder.append("SELECT greatest(MAX(");
		builder.append(this.getIdFiledName());
		builder.append("), :NEW.'");
		builder.append(this.getIdFiledName());
		builder.append("') \n");
		
		builder.append("INTO M_POID FROM ");
		builder.append(this.getName());
		builder.append("; \n");

		builder.append("SELECT ");
		builder.append(this.getName());
		builder.append("_");
		builder.append(this.getIdFiledName());
		builder.append("_");
		builder.append("SQ.nextval");
		builder.append(" INTO CUR_POID FROM DUAL; \n");
		
		builder.append("WHILE CUR_POID < M_POID LOOP \n");

		builder.append("SELECT ");
		builder.append(this.getName());
		builder.append("_");
		builder.append(this.getIdFiledName());
		builder.append("_");
		builder.append("SQ.nextval");
		builder.append(" INTO CUR_POID FROM DUAL; \n");

		builder.append("END LOOP \n");
		builder.append("END IF \n");
		builder.append("END IF \n");
		builder.append("END; \n");
		builder.append("/");
		
		this.triggerString = builder.toString();

	}
	
	@Override
	void commit() {
		super.commit();
	}
	
}
