require 'spec_helper'
require 'flydata-core/table_def'
require 'timecop'

module FlydataCore
module TableDef

describe RedshiftTableDef do
  let(:subject_object) { described_class }
  let(:table_name) { 'test_table' }

  describe '.from_flydata_tabledef' do
    subject { subject_object.from_flydata_tabledef(flydata_tabledef, option) }

    let(:value_column_hash) { { column: "value", type: "text" } }
    let(:value_column) { value_column_hash }
    let(:value_type_body) { 'text' }
    let(:value_type) { value_type_body }
    let(:flydata_tabledef) { {
        table_name: "test_table",
        columns: [
          { column: "id", type: "int4(11)", not_null: true, primary_key: true },
          { column: "age", type: "int4(11) unsigned" },
          value_column,
        ],
        default_charset: "UTF_8",
        src_ddl: "dummy_src_ddl"} }
    let(:option) { { flydata_ctl_table: false } }

    let(:schema_prefix) { "" }

    let(:drop_table_subquery) { <<EOT }
DROP TABLE IF EXISTS #{schema_prefix}"test_table_flydata20160125232857";
CREATE TABLE IF NOT EXISTS #{schema_prefix}"test_table" (
  "id" int4,
  "age" int8,
  "value" varchar(max),
  PRIMARY KEY ("id")
) DISTKEY("id") SORTKEY("id");
ALTER TABLE #{schema_prefix}"test_table" RENAME TO "test_table_flydata20160125232857";
EOT

    let(:drop_backup_table_subquery) { <<EOT.strip }
DROP TABLE IF EXISTS #{schema_prefix}"test_table_flydata20160125232857";
EOT

    let(:create_table_queries) { <<EOT.strip }
#{drop_table_subquery}CREATE TABLE #{schema_prefix}"test_table" (
  "id" int4,
  "age" int8,
  "value" varchar(max),
  PRIMARY KEY ("id")
) DISTKEY("id") SORTKEY("id");
EOT
    let(:flydata_ctl_update) { <<EOT.strip }
DELETE FROM #{schema_prefix}"flydata_ctl_columns" WHERE table_name = 'test_table';
INSERT INTO #{schema_prefix}"flydata_ctl_columns" (table_name, column_name, src_data_type, ordinal_position) VALUES
('test_table', 'id', 'int4(11)', 1),
('test_table', 'age', 'int4(11) unsigned', 2),
('test_table', 'value', '#{value_type}', 3);
DELETE FROM #{schema_prefix}"flydata_ctl_tables" WHERE table_name = 'test_table';
INSERT INTO #{schema_prefix}"flydata_ctl_tables" (table_name, attribute, value) VALUES
('test_table', 'cs', 'UTF_8'),
('test_table', 'revision', 1),
('test_table', 'src_ddl', 'dummy_src_ddl');
EOT

    let(:flydata_ctl_create) { <<EOT.strip }
CREATE TABLE IF NOT EXISTS #{schema_prefix}"flydata_ctl_columns"(
  id integer NOT NULL IDENTITY(1,1),
  table_name varchar(128) NOT NULL,
  column_name varchar(128) NOT NULL,
  src_data_type varchar(1024) NOT NULL,
  revision int NOT NULL DEFAULT 1,
  ordinal_position int NOT NULL,
  PRIMARY KEY(id)
) DISTKEY(table_name) SORTKEY(table_name);
CREATE TABLE IF NOT EXISTS #{schema_prefix}"flydata_ctl_tables"(
  id integer NOT NULL IDENTITY(1,1),
  table_name varchar(128) NOT NULL,
  attribute varchar(128) NOT NULL,
  value varchar(max),
  created_at timestamp DEFAULT SYSDATE,
  PRIMARY KEY(id)
) DISTKEY(table_name) SORTKEY(table_name);
EOT

    let(:comment_subquery) { <<EOT.strip }
COMMENT ON COLUMN #{schema_prefix}"test_table"."value"
  IS 'helloworld';
EOT

    let(:create_schema_query) { <<EOT.strip }
CREATE SCHEMA IF NOT EXISTS "test_schema";
EOT

    before do
      Timecop.freeze(Time.local(2016, 1, 25, 23, 28, 57))
    end

    context 'with simple flydatadef' do
      it 'should return ddl' do
        expect(subject).to eq "BEGIN;\n#{create_table_queries}\n#{flydata_ctl_update}\nCOMMIT;\n#{drop_backup_table_subquery}\n"
      end
    end

    context 'with flydata_ctl_columns option true' do
      let(:option) { { flydata_ctl_columns: true } }

      it 'should return ddl including flydata_ctl_columns creation' do
        expect(subject).to eq "#{flydata_ctl_create}\nBEGIN;\n#{create_table_queries}\n#{flydata_ctl_update}\nCOMMIT;\n#{drop_backup_table_subquery}\n"
      end
    end

    context 'when skip_drop_table option is true' do
      before do
        option.merge!(skip_drop_table: true)
      end

      let(:drop_table_subquery) { ""}
      it 'should return ddl' do
        expect(subject).to eq "BEGIN;\n#{create_table_queries}\n#{flydata_ctl_update}\nCOMMIT;\n"
      end
    end

    context 'with flydatadef with comment' do
      let(:value_column) { value_column_hash.merge(comment: "helloworld") }

      it 'should add comment creation ddl' do
        expect(subject).to eq "BEGIN;\n#{create_table_queries}\n#{comment_subquery}\n#{flydata_ctl_update}\nCOMMIT;\n#{drop_backup_table_subquery}\n"
      end
    end

    context 'with schema_name' do
      let(:value_column) { value_column_hash.merge(comment: "helloworld") }
      let(:option) { { flydata_ctl_table: true, schema_name: 'test_schema' } }
      let(:schema_prefix) { '"test_schema".' }

      it 'should preappend schema name to table name' do
        expect(subject).to eq <<EOT
#{create_schema_query}
#{flydata_ctl_create}
BEGIN;
#{create_table_queries}
#{comment_subquery}
#{flydata_ctl_update}
COMMIT;
#{drop_backup_table_subquery}
EOT
      end
    end

    context 'with column charset' do
      let(:value_column) { value_column_hash.merge(charset: "ISO_8859_1") }
      let(:value_type) { "#{value_type_body} cs:ISO_8859_1" }
      it 'should return ddl' do
        expect(subject).to eq "BEGIN;\n#{create_table_queries}\n#{flydata_ctl_update}\nCOMMIT;\n#{drop_backup_table_subquery}\n"
      end
    end
  end

  describe '.column_def_sql' do
    subject { subject_object.column_def_sql(column, opt) }
    let(:column) { {} }
    let(:opt) { {} }

    shared_examples "test generated Redshift SQL for column" do
      it do
         expect(subject).to eq(expected_query)
      end
    end

    context "test different column names" do
      before do
        column[:type] = "varchar(12)"
      end
      let(:expected_query) { %Q|  "#{expected_column_name}" varchar(12)| }

      context "normal column name" do
        before do
          column[:column] = "all_lower_case_not_reserved"
        end
        let(:expected_column_name) { "all_lower_case_not_reserved" }

        it_behaves_like "test generated Redshift SQL for column"
      end

      context "capitalized column name" do
        before do
          column[:column] = "CapitalizedNotReserved"
        end
        let(:expected_column_name) { "capitalizednotreserved" }

        it_behaves_like "test generated Redshift SQL for column"
      end

      context "reserved column name" do
        before do
          column[:column] = "oid"
        end
        let(:expected_column_name) { "_oid" }

        it_behaves_like "test generated Redshift SQL for column"
      end

      context "captalized reserved column name" do
        before do
          column[:column] = "OID"
        end
        let(:expected_column_name) { "_oid" }

        it_behaves_like "test generated Redshift SQL for column"
      end
    end

    let(:no_default_sql) { "" }
    shared_examples "default values" do |*examples|
      context "default nil" do
        before do
          column[:default] = nil
        end
        let(:default_sql) { " DEFAULT NULL" }
        it_behaves_like *examples
      end
      context "no default" do
        let(:default_sql) { no_default_sql }
        it_behaves_like *examples
      end
      context "defalut value" do
        before do
          column[:default] = default_value
        end
        let(:default_sql) { " DEFAULT #{default_value_sql}" }
        it_behaves_like *examples
      end
      context "replaced nil default" do
        before do
          column[:default] = default_value
          allow(subject_object).to receive(:replace_default_value).
            and_return(nil)
        end
        let(:default_sql) { no_default_sql }
        it_behaves_like *examples
      end
    end

    shared_examples "not null values" do |*examples|
      context "without not null" do
        let(:not_null_sql) { "" }
        it_behaves_like *examples
      end
      context "with not null false" do
        before do
          column[:not_null] = false
        end
        let(:not_null_sql) { "" }
        it_behaves_like *examples
      end
      context "with not null true" do
        before do
          column[:not_null] = true
        end
        let(:not_null_sql) { "" }
        it_behaves_like *examples
      end
    end

    shared_examples "test column types" do |*examples|
      context 'with varchar column def' do
        let(:default_value) { "something" }
        let(:default_value_sql) { "'#{default_value}'" }
        let(:not_null_default_sql) { " DEFAULT ''" }
        before do
          column[:column] = "col_char"
            column[:type] = "varchar(255)"
        end
        let(:type_sql) { %Q|"col_char" varchar(255)| }

        context 'when size is smaller than max size' do
          before do
            column[:type] = "varchar(18)"
          end
          let(:type_sql) { %Q|"col_char" varchar(18)| }

          it_behaves_like *examples
        end

        context 'when size exceeds varchar max size' do
          before do
            column[:type] = "varchar(1000000000)"
          end
          let(:type_sql) { %Q|"col_char" varchar(65535)| }

          it_behaves_like *examples
        end

        context 'when size is max size' do
          before do
            column[:type] = "varchar(max)"
          end
          let(:type_sql) { %Q|"col_char" varchar(max)| }

          it_behaves_like *examples
        end

        context 'when default_value has PostgreSQL-style type cast' do
          let(:default_value) { "'2014-10-17'::text" }
          let(:default_value_sql) { "'2014-10-17'" }

          it_behaves_like *examples
        end

      end

      context 'with medium blob column def' do
        context 'when size exceeds max size' do
          let(:default_value) { "something" }
          let(:default_value_sql) { "'#{default_value}'" }
          let(:not_null_default_sql) { " DEFAULT ''" }
          before do
            column[:column] = "col_medium_blob"
            column[:type] = "varbinary(16777215)"
          end
          let(:type_sql) { %Q|"col_medium_blob" varchar(65535)| }

          it_behaves_like *examples
        end
      end

      context 'with unsigned column def' do
        let(:default_value) { 4 }
        let(:default_value_sql) { "'#{default_value}'" }
        let(:not_null_default_sql) { " DEFAULT '0'" }
        before do
          column[:column] = "value_small_int"
          column[:type] = "int2(5) unsigned"
        end
        let(:type_sql) { %Q|"value_small_int" int4| }

        it_behaves_like *examples
      end

      context 'with boolean column def' do
        context 'with true as column_default' do
          let(:default_value) { true }
          let(:default_value_sql) { "#{default_value.to_s.upcase}" }
          let(:not_null_default_sql) { " DEFAULT FALSE" }
          before do
            column[:column] = "value_boolean"
            column[:type] = "boolean"
          end
          let(:type_sql) { %Q|"value_boolean" boolean| }

          it_behaves_like *examples
        end

        context 'with false as column_default' do
          let(:default_value) { false }
          let(:default_value_sql) { "#{default_value.to_s.upcase}" }
          let(:not_null_default_sql) { " DEFAULT FALSE" }
          before do
            column[:column] = "value_boolean"
            column[:type] = "boolean"
          end
          let(:type_sql) { %Q|"value_boolean" boolean| }

          it_behaves_like *examples
        end
      end

      context 'with date column def' do
        before do
          column[:column] = "value_date"
          column[:type] = "date"
        end
        let(:type_sql) { %Q|"value_date" date| }
        let(:not_null_default_sql) { " DEFAULT '0000-01-01'" }

        context 'when default_value is normal' do
          let(:default_value) { "'2014-10-17'" }
          let(:default_value_sql) { default_value }
          it_behaves_like *examples
        end

        context 'when default_value is 0000-00-00' do
          let(:default_value) { "'0000-00-00'" }
          let(:default_value_sql) { "'0001-01-01'" }
          it_behaves_like *examples
        end
      end

      context 'with datetime column def' do
        before do
          column[:column] = "value_datetime"
          column[:type] = "datetime"
        end
        let(:type_sql) { %Q|"value_datetime" timestamp| }
        let(:not_null_default_sql) { " DEFAULT '0000-01-01'" }

        context 'when default_value is normal' do
          let(:default_value) { "'2014-10-17 22:22:22'" }
          let(:default_value_sql) { "'2014-10-17 22:22:22.000000'" }
          it_behaves_like *examples
        end

        context 'when default_value is CURRENT_TIMESTAMP' do
          let(:default_value) { 'CURRENT_TIMESTAMP' }
          let(:default_value_sql) { "SYSDATE" }
          it_behaves_like *examples
        end

        context 'when default_value is CURRENT_TIMESTAMP(6)' do
          let(:default_value) { 'CURRENT_TIMESTAMP(6)' }
          let(:default_value_sql) { "SYSDATE" }
          it_behaves_like *examples
        end

        context 'when default_value is 0000-00-00 00:00:00' do
          let(:default_value) { "'0000-00-00 00:00:00'" }
          let(:default_value_sql) { "'0001-01-01 00:00:00.000000'" }
          it_behaves_like *examples
        end
      end

      context 'with datetimetz column def' do
        before do
          column[:column] = "value_datetimetz"
          column[:type] = "datetimetz"
        end
        let(:type_sql) { %Q|"value_datetimetz" timestamp| }
        let(:not_null_default_sql) { " DEFAULT '0000-01-01'" }

        context 'when default_value is normal' do
          let(:default_value) { "'2014-10-17 22:22:22+00'::timestamp with time zone" }
          let(:default_value_sql) { "'2014-10-17 22:22:22.000000'" }
          it_behaves_like *examples
        end

        context 'when default_value is current_timestamp' do
          let(:default_value) { 'current_timestamp' }
          let(:default_value_sql) { "SYSDATE" }
          it_behaves_like *examples
        end

        context 'when default_value is now()' do
          let(:default_value) { 'now()' }
          let(:default_value_sql) { "SYSDATE" }
          it_behaves_like *examples
        end

        context 'when default_value is 0000-00-00 00:00:00-07' do
          let(:default_value) { "'0000-00-00 00:00:00-07'" }
          let(:default_value_sql) { "'0001-01-01 07:00:00.000000'" }
          it_behaves_like *examples
        end
      end

      context 'with decimal column def' do
        context 'when precision is exactly max allowed' do
          let(:default_value) { "'4'" }
          let(:default_value_sql) { default_value }
          let(:not_null_default_sql) { " DEFAULT '0'" }
          before do
            column[:column] = "value"
            column[:type] = "numeric(38,4)" #34+4 digits
          end
          let(:type_sql) { %Q|"value" numeric(38,4)| }

          it_behaves_like *examples
        end

        context 'when precision exceeds max allowed' do
          let(:default_value) { "'4'" }
          let(:default_value_sql) { default_value }
          let(:not_null_default_sql) { " DEFAULT '0'" }
          before do
            column[:column] = "value"
            column[:type] = "numeric(42,37)" #5+37 (precision 42) => 5+33 digits (precision 38)
          end
          # Preserve digits over decimal as much as possible,
          # and truncate scale.
          let(:type_sql) { %Q|"value" numeric(38,33)| }

          it_behaves_like *examples
        end

        context 'when both precision and scale exceed max allowed' do #possible in postgresql
          let(:default_value) { "'4'" }
          let(:default_value_sql) { default_value }
          let(:not_null_default_sql) { " DEFAULT '0'" }
          before do
            column[:column] = "value"
            column[:type] = "numeric(1000,44)" #956+44 => 38+0 digits
          end
          let(:type_sql) { %Q|"value" numeric(38,0)| }

          it_behaves_like *examples
        end

        context 'when only scale exceed max allowed' do
          let(:default_value) { "'4'" }
          let(:default_value_sql) { default_value }
          let(:not_null_default_sql) { " DEFAULT '0'" }
          before do
            column[:column] = "value"
            column[:type] = "numeric(38,38)" #0+38 => 0+37 digits
          end
          let(:type_sql) { %Q|"value" numeric(37,37)| }

          it_behaves_like *examples
        end

        context 'when precision and scale are not given' do #possible in postgresql
          let(:default_value) { "'4'" }
          let(:default_value_sql) { default_value }
          let(:not_null_default_sql) { " DEFAULT '0'" }
          before do
            column[:column] = "value"
            column[:type] = "numeric"
          end
          let(:type_sql) { %Q|"value" numeric(18,8)| }

         it_behaves_like *examples
        end

        context 'when precision and scale exceeds max allowed (for unsigned)' do
          let(:default_value) { "'4'" }
          let(:default_value_sql) { default_value }
          let(:not_null_default_sql) { " DEFAULT '0'" }
          before do
            column[:column] = "value"
            column[:type] = "numeric(65,44) unsigned"
          end
          let(:type_sql) {%Q|"value" numeric(38,17)| }

          it_behaves_like *examples
        end

        context 'when precision and scale are within limit (for unsigned)' do
          let(:default_value) { "'4'" }
          let(:default_value_sql) { default_value }
          let(:not_null_default_sql) { " DEFAULT '0'" }
          before do
            column[:column] = "value"
            column[:type] = "numeric(37,29) unsigned"
          end
          let(:type_sql) { %Q|"value" numeric(37,29)| }

          it_behaves_like *examples
        end
      end

      context 'with bit column def' do
        before do
          column[:column] = "value_bit"
          column[:type] = "bit(1)"
        end
        let(:type_sql) { %Q|"value_bit" bigint| }
        let(:not_null_default_sql) { " DEFAULT '0'" }

        context 'when default_value is small bit' do
          let(:default_value) { "b'1'" }
          let(:default_value_sql) { "1" }
          it_behaves_like *examples
        end

        context 'when default_value is large bit' do
          let(:default_value) { "b'10110'" }
          let(:default_value_sql) { "22" }
          it_behaves_like *examples
        end

        context 'when default_value uses the capital B prefix' do
          let(:default_value) { "B'1'" }
          let(:default_value_sql) { "1" }
          it_behaves_like *examples
        end

        context 'when default_value has a type cast' do
          let(:default_value) { %Q|B'1'::"bit"| }
          let(:default_value_sql) { "1" }
          it_behaves_like *examples
        end

        # it's not real case
        context "when default_value is x'' format" do
          let(:default_value) { "x'12'" }
          let(:default_value_sql) { "18" }
          it_behaves_like *examples
        end

        context "when default_value is X'' format" do
          let(:default_value) { "X'2a'" }
          let(:default_value_sql) { "42" }
          it_behaves_like *examples
        end

        context 'when default_value is 0x format' do
          let(:default_value) { "0x0013" }
          let(:default_value_sql) { "19" }
          it_behaves_like *examples
        end

        context 'when default_value is 0b format' do
          let(:default_value) { "0b1011" }
          let(:default_value_sql) { "11" }
          it_behaves_like *examples
        end
      end

      context 'with timetz column def' do
        before do
          column[:column] = "value_timetz"
          column[:type] = "timetz"
        end
        let(:type_sql) { %Q|"value_timetz" timestamp| }
        let(:not_null_default_sql) { " DEFAULT '0000-01-01'" }

        context 'when default_value is normal' do
          let(:default_value) { "'22:22:22+00'::time with time zone" }
          let(:default_value_sql) { "'0001-01-01 22:22:22.000000'" }
          it_behaves_like *examples
        end

        context 'when default_value is current_time' do
          let(:default_value) { 'current_time' }
          let(:default_value_sql) { "SYSDATE" }
          it_behaves_like *examples
        end

        context 'when default_value is 00:00:00-07' do
          let(:default_value) { "'00:00:00-07'" }
          let(:default_value_sql) { "'0001-01-01 07:00:00.000000'" }
          it_behaves_like *examples
        end

        context 'when default_value is 00:00:00+07' do
          let(:default_value) { "'00:00:00+07'" }
          # Per ISO 8601, year 0000 is year 1 BC
          let(:default_value_sql) { "'0000-12-31 17:00:00.000000'" }
          it_behaves_like *examples
        end
      end

      context 'with year column def' do
        before do
          column[:column] = "value_year"
          column[:type] = "year(4)"
        end
        let(:type_sql) { %Q|"value_year" date| }
        let(:not_null_default_sql) { " DEFAULT '0001-01-01'" }

        context 'when default_value is normal' do
          let(:default_value) { "'2014'" }
          let(:default_value_sql) { "'2014-01-01'" }
          it_behaves_like *examples
        end

        context 'when default_value is 69' do
          let(:default_value) { "'69'" }
          let(:default_value_sql) { "'2069-01-01'" }
          it_behaves_like *examples
        end

        context 'when default_value is 70' do
          let(:default_value) { "'70'" }
          let(:default_value_sql) { "'1970-01-01'" }
          it_behaves_like *examples
        end

        context 'when default_value is 0' do
          let(:default_value) { "'0'" }
          let(:default_value_sql) { "'0001-01-01'" }
          it_behaves_like *examples
        end

        context 'when the type has no width' do
          before do
            column[:type] = "year"
          end
          context 'when default_value is normal' do
            let(:default_value) { "'2014'" }
            let(:default_value_sql) { "'2014-01-01'" }
            it_behaves_like *examples
          end
        end
      end
      context 'with money column def' do
        before do
          column[:column] = "value_money"
          column[:type] = "money(19,2)"
        end
        let(:type_sql) { %Q|"value_money" numeric(19,2)| }
        let(:not_null_default_sql) { " DEFAULT '0'" }

        context 'when default_value is normal' do
          let(:default_value) { "'10.40'" }
          let(:default_value_sql) { "'10.40'" }
          it_behaves_like *examples
        end

        context 'when default_value is in a monetary format' do
          let(:default_value) { "'$1,430.00'" }
          let(:default_value_sql) { "'1430.00'" }
          it_behaves_like *examples
        end

        context 'when default_value is in Japanese yen' do
          let(:default_value) { "'143,000 yen'" }
          let(:default_value_sql) { "'143000'" }
          it_behaves_like *examples
        end
      end
      context 'with unsupported column def' do
        before do
          column[:column] = "value_unsupported"
          column[:type] = "_unsupported"
        end
        let(:type_sql) { %Q|"value_unsupported" varchar(max)| }
        let(:not_null_default_sql) { " DEFAULT ''" }

        context 'when default_value is normal' do
          let(:default_value) { "'happy'" }
          let(:default_value_sql) { "'happy'" }
          it_behaves_like *examples
        end

        context 'when default value has a PostgreSQL-style type cast' do
          let(:default_value) { "'ok'::mood" }
          let(:default_value_sql) { "'ok'" }
          it_behaves_like *examples
        end
      end
    end

    context 'for create table' do
      let(:opt) { {} }
      let(:expected_query) { %Q|  #{type_sql}#{not_null_sql}#{default_sql}| }
      let(:no_default_sql) { "" }

      it_behaves_like "test column types", "not null values", "default values", "test generated Redshift SQL for column"
    end

    context 'for alter table' do
      let(:opt) { { for: :alter_table } }

      context "without not null" do
        let(:expected_query) { %Q|  #{type_sql}#{default_sql}| }
        let(:no_default_sql) { "" }

        it_behaves_like "test column types", "default values", "test generated Redshift SQL for column"
      end
      context "with not null false" do
        let(:expected_query) { %Q|  #{type_sql}#{default_sql}| }
        before do
          column[:not_null] = false
        end
        let(:no_default_sql) { "" }

        it_behaves_like "test column types", "default values", "test generated Redshift SQL for column"
      end

      context "with not null true" do
        let(:expected_query) { %Q|  #{type_sql}#{default_sql}| } # Redshift supports NOT NULL but we do not respect it in order to accept NULL values which may come in future.
        before do
          column[:not_null] = true
        end
        let(:no_default_sql) { "" }

        it_behaves_like "test column types", "default values", "test generated Redshift SQL for column"
      end
    end
  end

  describe '.flydata_ctl_columns_sql' do
    subject { subject_object.flydata_ctl_columns_sql(flydata_tabledef, schema_name) }
    let(:flydata_tabledef) { {
        table_name: table_name,
        columns: [
          { column: column_name, type: "varchar(12)" },
        ],
        default_charset: "UTF_8"} }
    let(:schema_name) { nil }

    let(:expected_sql) { <<EOS }
DELETE FROM "flydata_ctl_columns" WHERE table_name = '#{table_name}';
INSERT INTO "flydata_ctl_columns" (table_name, column_name, src_data_type, ordinal_position) VALUES
('#{table_name}', '#{expected_column_name}', 'varchar(12)', 1);
EOS

    context "test different column names" do
      context "normal column name" do
        let(:column_name) { "all_lower_case_not_reserved" }
        let(:expected_column_name) { column_name }

        it { is_expected.to eq expected_sql }
      end

      context "capitalized column name" do
        let(:column_name) { "CapitalizedNotReserved" }
        let(:expected_column_name) { column_name }

        it { is_expected.to eq expected_sql }
      end

      context "reserved column name" do
        let(:column_name) { "oid" }
        let(:expected_column_name) { column_name }

        it { is_expected.to eq expected_sql }
      end

      context "captalized reserved column name" do
        let(:column_name) { "OID" }
        let(:expected_column_name) { column_name }

        it { is_expected.to eq expected_sql }
      end

      context "column name with a single quote" do
        let(:column_name) { "90's hits" }
        let(:expected_column_name) { "90\\'s hits" }

        it { is_expected.to eq expected_sql }
      end

      context "column name with a backslash" do
        let(:column_name) { "\\^_^/" }
        let(:expected_column_name) { "\\\\^_^/" }

        it { is_expected.to eq expected_sql }
      end
    end
  end

  describe '.parse_timestamp' do
    let(:value) { nil }
    subject { subject_object.parse_timestamp(value) }

    context 'with w3c format value' do
      context 'with sec' do
        let(:value) { '1997-07-16T19:20:01+01:00' }
        it { is_expected.to eq('1997-07-16 18:20:01.000000') }
      end
      context 'with millisec' do
        let(:value) { '1997-07-16T19:20:01+01:00' }
        it { is_expected.to eq('1997-07-16 18:20:01.000000') }
      end
    end

    context 'with twitter format value' do
      let(:value) { 'Sat Sep 17 14:48:57 +0900 2011' }
      it { is_expected.to eq('2011-09-17 05:48:57.000000') }
    end

    context 'with redshift support format value' do
      context 'with sec' do
        let(:value) { '2012-01-23 19:20:15' }
        it { is_expected.to eq('2012-01-23 19:20:15.000000') }
      end

      context 'with millisec' do
        let(:value) { '2012-01-23 19:20:15.243' }
        it { is_expected.to eq('2012-01-23 19:20:15.243000') }
      end
      context 'with nanosec' do
        let(:value) { '2012-1-23 10:40:5.243312' }
        it { is_expected.to eq('2012-01-23 10:40:05.243312') }
      end
    end

    context 'with apache format value' do
      let(:value) { '[13/Sep/2006:07:01:53 -0700]' }
      it { is_expected.to eq('2006-09-13 14:01:53.000000') }
    end

    context 'with timezone value' do
      context 'with sec' do
        let(:value) { '2012-01-23 19:20:15 +0100' }
        it { is_expected.to eq('2012-01-23 18:20:15.000000') }
      end

      context 'with millisec' do
        let(:value) { '2012-1-23 10:40:5.243 +0230' }
        it { is_expected.to eq('2012-01-23 08:10:05.243000') }
      end

      context 'with colon separated timezone value' do
        let(:value) { '2012-01-23 19:20:15 +01:00' }
        it { is_expected.to eq('2012-01-23 18:20:15.000000') }
      end

      context 'without space before imezone value' do
        let(:value) { '2012-01-23 19:20:15+01:00' }
        it { is_expected.to eq('2012-01-23 18:20:15.000000') }
      end
    end

    context 'with unix epoch time' do
      context 'with string type' do
        let(:value) { '1400705967' }
        it { is_expected.to eq('2014-05-21 20:59:27.000000') }
      end

      context 'with integer type' do
        let(:value) { 1400705967 }
        it { is_expected.to eq('2014-05-21 20:59:27.000000') }
      end

      context 'with millisec' do
        let(:value) { 1400705967.283928 }
        it { is_expected.to eq('2014-05-21 20:59:27.283928') }
      end
    end

    context 'with all zero string' do
      context 'without fraction' do
        let(:value) { '0000-00-00 00:00:00' }
        it { is_expected.to eq('0001-01-01 00:00:00.000000') }
      end

      context 'with fraction' do
        let(:value) { '0000-00-00 00:00:00.000000' }
        it { is_expected.to eq('0001-01-01 00:00:00.000000') }
      end
    end

    context 'with zero date' do
      let(:value) { '0000-00-00 10:24:28' }
      it { is_expected.to eq('0001-01-01 10:24:28.000000') }
    end

    context 'with zero month' do
      let(:value) { '2005-00-19 10:19:25' }
      it { is_expected.to eq('2005-01-19 10:19:25.000000') }
    end

    context 'with zero day and month' do
      let(:value) { '2010-00-00 00:00:00' }
      it { is_expected.to eq('2010-01-01 00:00:00.000000') }
    end

    context 'with time value' do
      context 'when value is positive' do
        context 'with sec' do
          let(:value) { '23:01:24' }
          it { is_expected.to eq('0001-01-01 23:01:24.000000') }
        end

        context 'with msec' do
          let(:value) { '23:01:24.123456' }
          it { is_expected.to eq('0001-01-01 23:01:24.123456') }
        end
      end

      context 'when value is bigger than 24 hour' do
        let(:value) { '28:01:24' }
        it { is_expected.to eq('0001-01-02 04:01:24.000000') }
      end

      context 'when value is mysql max value' do
        let(:value) { '838:59:59' }
        it { is_expected.to eq('0001-02-04 22:59:59.000000') }
      end

      context 'when value is mysql min value' do
        let(:value) { '-838:59:59' }
        it { is_expected.to eq('0000-11-27 01:00:01.000000') }
      end

      context 'when value is zero time' do
        let(:value) { '00:00:00' }
        it { is_expected.to eq('0001-01-01 00:00:00.000000') }
      end

      context 'when value is negative' do
        context 'with sec' do
          let(:value) { '-03:01:24' }
          it { is_expected.to eq('0000-12-31 20:58:36.000000') }
        end

        context 'with msec' do
          let(:value) { '-03:01:24.123' }
          it { is_expected.to eq('0000-12-31 20:58:35.876999') } # floating-point arithmetic(only negative value)
        end

        context 'with nsec' do
          let(:value) { '-03:01:24.123456' }
          it { is_expected.to eq('0000-12-31 20:58:35.876543') } # floating-point arithmetic(only negative value)
        end
      end

      context 'when value has timezone' do
        context 'with no-colon timezone value' do
          let(:value) { '13:01:24 +0800' }
          it { is_expected.to eq('0001-01-01 05:01:24.000000') }
        end
        context 'with colon separated timezone value' do
          let(:value) { '13:01:24 -08:00' }
          it { is_expected.to eq('0001-01-01 21:01:24.000000') }
        end
        context 'with a timezone whose corresponding UTC goes negative' do
          let(:value) { '03:01:24 +0800' }
          it { is_expected.to eq('0000-12-31 19:01:24.000000') }
        end
      end
    end

    context 'with nil value' do
      let(:value){ nil }
      it { is_expected.to be_nil }
    end

    context 'with empty string' do
      let(:value){ '' }
      it { is_expected.to be_nil }
    end

    context 'with invalid value' do
      let(:value) { 'abcd' }
      it { expect{subject}.to raise_error(ArgumentError) }
    end

    context 'with boolean value' do
      let(:value){ false }
      it { expect{subject}.to raise_error(ArgumentError) }
    end
  end

  describe '.parse_date' do
    let(:value) { nil }
    subject { subject_object.parse_date(value) }

    context 'with year values' do
      context 'with value string 0' do
        let(:value){ '0' }
        it { is_expected.to eq('0001-01-01') }
      end

      context 'with value int 0' do
        let(:value){ 0 }
        it { is_expected.to eq('0001-01-01') }
      end

      context 'with value 0000' do
        let(:value){ '0000' }
        it { is_expected.to eq('0001-01-01') }
      end

      context 'with year(4) >= 1970' do
        let(:value){ '2000' }
        it { is_expected.to eq('2000-01-01') }
      end

      context 'with year(4) < 1970' do
        let(:value){ '1969' }
        it { is_expected.to eq('1969-01-01') }
      end

      context 'with year(2) >= 70' do
        let(:value){ '07' }
        it { is_expected.to eq('2007-01-01') }
      end

      context 'with year(2) < 70' do
        let(:value){ '69' }
        it { is_expected.to eq('2069-01-01') }
      end

      context 'with value 259392-00-00' do
        let(:value) { '259392-00-00' }
        it { is_expected.to eq('259392-01-01') }
      end

      context 'with value 259392-00-04' do
        let(:value) { '259392-00-04' }
        it { is_expected.to eq('259392-01-04') }
      end

      context 'with value 0000-01-04' do
        let(:value) { '0000-01-04' }
        it { is_expected.to eq('0001-01-04') }
      end

      context 'with value 259392-40-04' do
        let(:value) { '259392-40-04' }
        it { expect{subject}.to raise_error(ArgumentError) }
      end
    end

    context 'with date values' do
      shared_examples "returning the input value as is" do
        it { is_expected.to eq(value) }
      end
      context 'with valid date' do
        let(:value){ '1920-01-01' }
        it_behaves_like "returning the input value as is"
      end

      context 'with zero date' do
        let(:value){ '0000-00-00' }
        it { is_expected.to eq('0001-01-01') }
      end
    end

    context 'with nil' do
      it { is_expected.to eq(nil) }
    end
  end

  shared_examples "expecting no underscore in front of numbers" do
    # Redshift accepts a table/column name starting with numbers (e.g. 20grams) or even a number-only name (e.g. 12345)
    context "with a key including numbers" do
      let(:key) { "year2000" }
      let(:expected_value) { key }
      it { is_expected.to eq expected_value }
    end

    context "with a key only including numbers" do
      let(:key) { "2000" }
      let(:expected_value) { key }
      it { is_expected.to eq expected_value }
    end

    context "with a key starting with numbers" do
      let(:key) { "2000stars" }
      let(:expected_value) { key }
      it { is_expected.to eq expected_value }
    end
  end

  describe ".convert_to_valid_name" do
    subject { subject_object.convert_to_valid_name(key) }

    it_behaves_like "expecting no underscore in front of numbers"

    context "with a key that has spaces" do
      let(:key) { "space test" }
      let(:expected_value) { key }
      it { is_expected.to eq expected_value }
    end

    context "with a key that has non-ASCII chars" do
      let(:key) { "行列123" }
      let(:expected_value) { "__123" }
      it "Redshift does not support non-ASCII table/column name.  Those characters will be replaced with '_'" do
        is_expected.to eq expected_value
      end
    end
    context "with a key that has special chars" do
      let(:key) { %q| !"#$%&'()*+,-/:;<=>?@[\\]^_{\|}~`| }
      let(:expected_value) { %q| !_#$%&'()*+,-/:;<=>?@[\\]^_{\|}~`| }
      it "Redshift supports all these special characters but double quote (\") which is replaced with underscore (_)" do
        is_expected.to eq expected_value
      end
    end
    context "with a key that has a period in it" do
      let(:key) { %q|my.table| }
      let(:expected_value) { key }
      it "Redshift supports period (.) in a table/column name" do
        is_expected.to eq expected_value
      end
    end
  end

  describe ".convert_to_valid_column_name" do
    subject { subject_object.convert_to_valid_name(key) }

    it_behaves_like "expecting no underscore in front of numbers"
  end

  describe ".convert_to_valid_table_name" do
    subject { subject_object.convert_to_valid_name(key) }

    it_behaves_like "expecting no underscore in front of numbers"
  end

  describe ".fq_table_name" do
    subject { subject_object.fq_table_name(table_name, schema_name) }
    context "with no schema name" do
      let(:schema_name) { nil }
      context "with a table name with no schema prefix" do
        let(:table_name) { "test_table" }
        it { is_expected.to eq "test_table" }
      end
      context "with a table name with a schema prefix" do
        let(:table_name) { "some_schema.test_table" }
        it { is_expected.to eq "test_table" }
      end
      context "with a mixed-case table name" do
        let(:table_name) { "Test_Table" }
        it { is_expected.to eq "test_table" }
      end
    end
    context "with a schema name" do
      let(:schema_name) { "test_schema" }
      context "with a table name with no schema prefix" do
        let(:table_name) { "test_table" }
        it { is_expected.to eq "test_schema.test_table" }
      end
      context "with a table name with a schema prefix" do
        let(:table_name) { "some_schema.test_table" }
        it { is_expected.to eq "test_schema.test_table" }
      end
    end
    context "with a mixed-case schema name" do
      let(:schema_name) { "Test_Schema" }
      context "with a table name with no schema prefix" do
        let(:table_name) { "test_table" }
        it { is_expected.to eq "test_schema.test_table" }
      end
    end
  end
  describe '.replace_default_value' do
    subject { subject_object.replace_default_value(flydata_type, redshift_type,
              default_value) }

    context 'with cast expression from PostgreSQL' do
      context 'with multiple-lines in a default value' do
        let(:flydata_type) { "text" }
        let(:redshift_type) { "varchar(max)" }
        let(:default_value) { "'--- {} \nthe 2nd line'::text" }
        it { is_expected.to eq "'--- {} \nthe 2nd line'" }
      end

      context 'with :: in a default value' do
        let(:flydata_type) { "varchar" }
        let(:redshift_type) { "varchar" }
        let(:default_value) { "'FlyDataCore::TableDef'::character varying" }
        it { is_expected.to eq "'FlyDataCore::TableDef'" }
      end

      context 'with a single-quote in a default value' do
        let(:flydata_type) { "varchar" }
        let(:redshift_type) { "varchar" }
        let(:default_value) { "'That''s it!'::character varying" }
        it { is_expected.to eq "'That''s it!'" }
      end
    end

    context 'with integer type' do
      let(:flydata_type) { "int8" }
      let(:redshift_type) { "int8" }

      context "nextval() as the value" do
        let(:default_value) { "nextval('test_id_seq'::regclass)" }
        it 'returns nil because Redshift does not support nextval()' do
          is_expected.to be_nil
        end
      end
    end
    context 'with moeny type' do
      let(:flydata_type) { "money" }
      let(:redshift_type) { "money" }

      context 'in dollar format' do
        let(:default_value) { "'$2,392.40'" }
        it { is_expected.to eq "'2392.40'" }
      end

      context 'in JPY format' do
        let(:default_value) { "'JPY2,382,929'" }
        it { is_expected.to eq "'2382929'" }
      end

      context 'in INR format' do
        let(:default_value) { "'12,23,382.29'" }
        it { is_expected.to eq "'1223382.29'" }
      end

      context 'in DKK format' do
        let(:default_value) { "'10.382,29'" }
        it { is_expected.to eq "'10382.29'" }
      end
    end
  end
end

end
end