/**
    *    Copyright 2009-2015 the original author or authors.
    *
    *    Licensed under the Apache License, Version 2.0 (the "License");
    *    you may not use this file except in compliance with the License.
    *    You may obtain a copy of the License at
    *
    *       http://www.apache.org/licenses/LICENSE-2.0
    *
   *    Unless required by applicable law or agreed to in writing, software
   *    distributed under the License is distributed on an "AS IS" BASIS,
   *    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   *    See the License for the specific language governing permissions and
   *    limitations under the License.
   */
  package org.apache.ibatis.jdbc;
  
  import org.junit.Test;
  
  import static org.junit.Assert.assertEquals;
  
  public class SQLTest {
  
    @Test
    public void shouldDemonstrateProvidedStringBuilder() {
      //You can pass in your own StringBuilder
      final StringBuilder sb = new StringBuilder();
      //From the tutorial
      final String sql = new SQL() {{
        SELECT("P.ID, P.USERNAME, P.PASSWORD, P.FULL_NAME");
        SELECT("P.LAST_NAME, P.CREATED_ON, P.UPDATED_ON");
        FROM("PERSON P");
        FROM("ACCOUNT A");
        INNER_JOIN("DEPARTMENT D on D.ID = P.DEPARTMENT_ID");
        INNER_JOIN("COMPANY C on D.COMPANY_ID = C.ID");
        WHERE("P.ID = A.ID");
        WHERE("P.FIRST_NAME like ?");
        OR();
        WHERE("P.LAST_NAME like ?");
        GROUP_BY("P.ID");
        HAVING("P.LAST_NAME like ?");
        OR();
        HAVING("P.FIRST_NAME like ?");
        ORDER_BY("P.ID");
        ORDER_BY("P.FULL_NAME");
      }}.usingAppender(sb).toString();
  
      assertEquals("SELECT P.ID, P.USERNAME, P.PASSWORD, P.FULL_NAME, P.LAST_NAME, P.CREATED_ON, P.UPDATED_ON\n" +
          "FROM PERSON P, ACCOUNT A\n" +
          "INNER JOIN DEPARTMENT D on D.ID = P.DEPARTMENT_ID\n" +
          "INNER JOIN COMPANY C on D.COMPANY_ID = C.ID\n" +
          "WHERE (P.ID = A.ID AND P.FIRST_NAME like ?) \n" +
          "OR (P.LAST_NAME like ?)\n" +
          "GROUP BY P.ID\n" +
          "HAVING (P.LAST_NAME like ?) \n" +
          "OR (P.FIRST_NAME like ?)\n" +
          "ORDER BY P.ID, P.FULL_NAME", sql);
    }
  
    @Test
    public void shouldDemonstrateMixedStyle() {
      //Mixed
      final String sql = new SQL() {{
        SELECT("id, name");
        FROM("PERSON A");
        WHERE("name like ?").WHERE("id = ?");
      }}.toString();
  
      assertEquals("" +
          "SELECT id, name\n" +
          "FROM PERSON A\n" +
          "WHERE (name like ? AND id = ?)", sql);
    }
  
    @Test
    public void shouldDemonstrateFluentStyle() {
      //Fluent Style
      final String sql = new SQL()
          .SELECT("id, name").FROM("PERSON A")
          .WHERE("name like ?")
          .WHERE("id = ?").toString();
  
      assertEquals("" +
          "SELECT id, name\n" +
          "FROM PERSON A\n" +
          "WHERE (name like ? AND id = ?)", sql);
    }
  
    @Test
    public void shouldProduceExpectedSimpleSelectStatement() {
      final String expected =
          "SELECT P.ID, P.USERNAME, P.PASSWORD, P.FIRST_NAME, P.LAST_NAME\n" +
              "FROM PERSON P\n" +
              "WHERE (P.ID like #id# AND P.FIRST_NAME like #firstName# AND P.LAST_NAME like #lastName#)\n" +
              "ORDER BY P.LAST_NAME";
      assertEquals(expected, example2("a", "b", "c"));
    }
  
    @Test
   public void shouldProduceExpectedSimpleSelectStatementMissingFirstParam() {
     final String expected =
         "SELECT P.ID, P.USERNAME, P.PASSWORD, P.FIRST_NAME, P.LAST_NAME\n" +
             "FROM PERSON P\n" +
             "WHERE (P.FIRST_NAME like #firstName# AND P.LAST_NAME like #lastName#)\n" +
             "ORDER BY P.LAST_NAME";
     assertEquals(expected, example2(null, "b", "c"));
   }
 
   @Test
   public void shouldProduceExpectedSimpleSelectStatementMissingFirstTwoParams() {
     final String expected =
         "SELECT P.ID, P.USERNAME, P.PASSWORD, P.FIRST_NAME, P.LAST_NAME\n" +
             "FROM PERSON P\n" +
             "WHERE (P.LAST_NAME like #lastName#)\n" +
             "ORDER BY P.LAST_NAME";
     assertEquals(expected, example2(null, null, "c"));
   }
 
   @Test
   public void shouldProduceExpectedSimpleSelectStatementMissingAllParams() {
     final String expected =
         "SELECT P.ID, P.USERNAME, P.PASSWORD, P.FIRST_NAME, P.LAST_NAME\n" +
             "FROM PERSON P\n" +
             "ORDER BY P.LAST_NAME";
     assertEquals(expected, example2(null, null, null));
   }
 
   @Test
   public void shouldProduceExpectedComplexSelectStatement() {
     final String expected =
         "SELECT P.ID, P.USERNAME, P.PASSWORD, P.FULL_NAME, P.LAST_NAME, P.CREATED_ON, P.UPDATED_ON\n" +
             "FROM PERSON P, ACCOUNT A\n" +
             "INNER JOIN DEPARTMENT D on D.ID = P.DEPARTMENT_ID\n" +
             "INNER JOIN COMPANY C on D.COMPANY_ID = C.ID\n" +
             "WHERE (P.ID = A.ID AND P.FIRST_NAME like ?) \n" +
             "OR (P.LAST_NAME like ?)\n" +
             "GROUP BY P.ID\n" +
             "HAVING (P.LAST_NAME like ?) \n" +
             "OR (P.FIRST_NAME like ?)\n" +
             "ORDER BY P.ID, P.FULL_NAME";
     assertEquals(expected, example1());
   }
 
   private static String example1() {
     return new SQL() {{
       SELECT("P.ID, P.USERNAME, P.PASSWORD, P.FULL_NAME");
       SELECT("P.LAST_NAME, P.CREATED_ON, P.UPDATED_ON");
       FROM("PERSON P");
       FROM("ACCOUNT A");
       INNER_JOIN("DEPARTMENT D on D.ID = P.DEPARTMENT_ID");
       INNER_JOIN("COMPANY C on D.COMPANY_ID = C.ID");
       WHERE("P.ID = A.ID");
       WHERE("P.FIRST_NAME like ?");
       OR();
       WHERE("P.LAST_NAME like ?");
       GROUP_BY("P.ID");
       HAVING("P.LAST_NAME like ?");
       OR();
       HAVING("P.FIRST_NAME like ?");
       ORDER_BY("P.ID");
       ORDER_BY("P.FULL_NAME");
     }}.toString();
   }
 
   private static String example2(final String id, final String firstName, final String lastName) {
     return new SQL() {{
       SELECT("P.ID, P.USERNAME, P.PASSWORD, P.FIRST_NAME, P.LAST_NAME");
       FROM("PERSON P");
       if (id != null) {
         WHERE("P.ID like #id#");
       }
       if (firstName != null) {
         WHERE("P.FIRST_NAME like #firstName#");
       }
       if (lastName != null) {
         WHERE("P.LAST_NAME like #lastName#");
       }
       ORDER_BY("P.LAST_NAME");
     }}.toString();
   }
 
 }