//---------------------------------------------------------------------------------------------------------------------------------
// File: params.js
// Contents: test suite for parameters
// 
// Copyright Microsoft Corporation and contributors
//
// 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.
//---------------------------------------------------------------------------------------------------------------------------------

var sql=require('../');
var assert=require('assert');
var async=require('async');
var config = require('./test-config');

var conn_str = config.conn_str;

function testBoilerPlate( tableName, tableFields, insertFunction, verifyFunction, doneFunction ) {

    var tableFieldsSql = " (id int identity, ";

    for( var field in tableFields ) {

        tableFieldsSql += field + ' ' + tableFields[field] + ',';
    }
    tableFieldsSql = tableFieldsSql.substr( 0, tableFieldsSql.length - 1 );
    tableFieldsSql += ')';

    async.series( [

        function( async_done ) {

            var dropQuery = "DROP TABLE " + tableName;
            sql.query( conn_str, dropQuery, function( e, r ) { async_done(); } );
        },
        function( async_done ) {

            var createQuery = "CREATE TABLE " + tableName + tableFieldsSql;
            sql.query( conn_str, createQuery, 
                       function( e, r ) { 
                           assert.ifError( e, "Error creating table" );
                           async_done();
                       });
        },
        function( async_done ) {

            var clusteredIndexSql = ["CREATE CLUSTERED INDEX IX_", tableName, " ON ", tableName, " (id)"].join("");
            sql.query( conn_str, clusteredIndexSql, 
                       function( e, r ) { 
                           assert.ifError( e, "Error creating index" );
                           async_done();
                       });
        },
        function( async_done ) {

            insertFunction( async_done );
        },
        function( async_done ) {

            verifyFunction( function() { async_done(); doneFunction(); } );
        }
    ]);
}

suite( 'params', function() {

    var c;

    var localDate = new Date();
    var utcDate = new Date( Date.UTC( localDate.getUTCFullYear(), 
                                      localDate.getUTCMonth(),
                                      localDate.getUTCDate(),
                                      localDate.getUTCHours(),
                                      localDate.getUTCMinutes(),
                                      localDate.getUTCSeconds(),
                                      localDate.getUTCMilliseconds() ));

    setup( function( test_done ) {

        sql.open( conn_str, function( err, new_conn ) {

            assert.ifError( err );

            c = new_conn;

            test_done();
        });
    });

    teardown( function( done ) {

        c.close( function( err ) { assert.ifError( err ); done(); });
    });

    test('insert null as parameter', function( test_done ) {

        testBoilerPlate( "null_param_test", { "null_test" : "varchar(1)" },
            function( done ) {

                c.queryRaw( "INSERT INTO null_param_test (null_test) VALUES (?)", [null], function( e, r ) {
                    assert.ifError( e );
                    done();
                });
            },
            function( done ) {

                c.queryRaw( "SELECT null_test FROM null_param_test", function( e, r ) {

                    assert.ifError( e );
                    var expected = { meta: [ { name: 'null_test', size: 1, nullable: true, type: 'text', sqlType: 'varchar' } ],
                                     rows: [[null]] };
                    assert.deepEqual( expected, r );
                    done();
                });
            },
            test_done);
    });

    test('insert string as parameter', function( test_done ) {

        testBoilerPlate( "string_param_test", { "string_test" : "nvarchar(100)" },
            function( done ) {

                c.queryRaw( "INSERT INTO string_param_test (string_test) VALUES (?)", ['This is a test'], function( e, r ) {
                    assert.ifError( e );
                    done();
                });
            },
            function( done ) {

                c.queryRaw( "SELECT string_test FROM string_param_test", function( e, r ) {

                    assert.ifError( e );
                    var expected = { meta: [ { name: 'string_test', size: 100, nullable: true, type: 'text', sqlType: 'nvarchar' } ],
                                     rows: [ [ 'This is a test' ] ] };
                    assert.deepEqual( expected, r );
                    done();
                });
            },
            test_done);
    });

    test( 'insert a bool as a parameter', function( test_done ) {

        testBoilerPlate( "bool_param_test", { "bool_test" : "bit" },
            function( done ) {
                c.queryRaw( "INSERT INTO bool_param_test (bool_test) VALUES (?)", [true], function( e, r ) {
                    assert.ifError( e );
                    done();
                });
            },
            function( done ) {

                c.queryRaw( "SELECT bool_test FROM bool_param_test", function( e, r ) {

                    assert.ifError( e );
                    var expected = { meta: [ { name: 'bool_test', size: 1, nullable: true, type: 'boolean', sqlType: 'bit' } ],
                                     rows: [ [ true ] ] };
                    assert.deepEqual( expected, r );
                    done();
                });
            },
            test_done);            
    });

    test( 'insert largest positive int as parameter', function( test_done ) {

        testBoilerPlate( "int_param_test", { "int_test" : "int" },
            function( done ) {
                c.queryRaw( "INSERT INTO int_param_test (int_test) VALUES (?)", [0x7fffffff], function( e, r ) {
                    assert.ifError( e );
                    done();
                });
            },
            function( done ) {

                c.queryRaw( "SELECT int_test FROM int_param_test", function( e, r ) {

                    assert.ifError( e );
                    var expected = { meta: [ { name: 'int_test', size: 10, nullable: true, type: 'number', sqlType: 'int' } ],
                                    rows: [ [ 2147483647 ] ] };
                    assert.deepEqual( expected, r );
                    done();
                });
            },
            test_done);            
    });

    test( 'insert largest negative int as parameter', function( test_done ) {

        testBoilerPlate( "int_param_test", { "int_test" : "int" },
            function( done ) {
                c.queryRaw( "INSERT INTO int_param_test (int_test) VALUES (?)", [-0x80000000], function( e, r ) {
                    assert.ifError( e );
                    done();
                });
            },
            function( done ) {

                c.queryRaw( "SELECT int_test FROM int_param_test", function( e, r ) {

                    assert.ifError( e );
                    var expected = { meta: [ { name: 'int_test', size: 10, nullable: true, type: 'number', sqlType: 'int' } ],
                                     rows: [ [ -2147483648 ] ] };
                    assert.deepEqual( expected, r );
                    done();
                });
            },  
            test_done);            
    });

    test( 'insert bigint as parameter', function( test_done ) {

        testBoilerPlate( "bigint_param_test", { "bigint_test" : "bigint" },
            function( done ) {
                c.queryRaw( "INSERT INTO bigint_param_test (bigint_test) VALUES (?)", [0x80000000], function( e, r ) {
                    assert.ifError( e );
                    done();
                });
            },
            function( done ) {

                c.queryRaw( "SELECT bigint_test FROM bigint_param_test", function( e, r ) {

                    assert.ifError( e );
                    var expected = { meta: [ { name: 'bigint_test', size: 19, nullable: true, type: 'number', sqlType: 'bigint' } ],
                                     rows: [ [ 0x80000000 ] ] };
                    assert.deepEqual( expected, r );
                    done();
                });
            },  
            test_done);                        
    });

    test( 'insert largest bigint as parameter', function( test_done ) {

        testBoilerPlate( "bigint_param_test", { "bigint_test" : "bigint" },
            function( done ) {
                c.queryRaw( "INSERT INTO bigint_param_test (bigint_test) VALUES (?)", [0x4fffffffffffffff], function( e, r ) {
                    assert.ifError( e );
                    done();
                });
            },
            function( done ) {

                c.queryRaw( "SELECT bigint_test FROM bigint_param_test", function( e, r ) {

                    assert.ifError( e );
                    var expected = { meta: [ { name: 'bigint_test', size: 19, nullable: true, type: 'number', sqlType: 'bigint' } ],
                                     rows: [ [ 0x4fffffffffffffff ] ] };
                    assert.deepEqual( expected, r );
                    done();
                });
            },  
            test_done);                        
    });

    test( 'insert decimal as parameter', function( test_done ) {

        testBoilerPlate( "decimal_param_test", { "decimal_test" : "decimal(18,7)" },
            function( done ) {
                c.queryRaw( "INSERT INTO decimal_param_test (decimal_test) VALUES (?)", [3.141593],
                    function( e, r ) {
                        assert.ifError( e );
                        done();
                });
            },
            function( done ) {

                c.queryRaw( "SELECT decimal_test FROM decimal_param_test", function( e, r ) {

                    assert.ifError( e );
                    var expected = { meta: [ { name: 'decimal_test', size: 18, nullable: true, type: 'number', sqlType: 'decimal' } ],
                                     rows: [ [ 3.141593 ] ] };
                    assert.deepEqual( expected, r );
                    done();
                });
            },  
            test_done);                        
    });

    test( 'insert decimal as bigint parameter', function( test_done ) {

        testBoilerPlate( "decimal_as_bigint_param_test", { "decimal_bigint" : "bigint" },
            function( done ) {
                c.queryRaw( "INSERT INTO decimal_as_bigint_param_test (decimal_bigint) VALUES (?)", [123456789.0],
                    function( e, r ) {
                        assert.ifError( e );
                        done();
                });
            },
            function( done ) {

                c.queryRaw( "SELECT decimal_bigint FROM decimal_as_bigint_param_test", function( e, r ) {

                    assert.ifError( e );
                    var expected = { meta:
                                       [ { name: 'decimal_bigint',
                                           size: 19,
                                           nullable: true,
                                           type: 'number', sqlType: 'bigint' } ],
                                      rows: [ [ 123456789 ] ] };
                    assert.deepEqual( expected, r );
                    done();
                });
            },  
            test_done);                        
    });

    test( 'insert date as parameter', function( test_done ) {

        testBoilerPlate( "date_param_test", { "date_test" : "datetimeoffset" },
            function( done ) {
                c.queryRaw( "INSERT INTO date_param_test (date_test) VALUES (?)", [utcDate],
                    function( e, r ) {
                        assert.ifError( e );
                        done();
                });
            },
            function( done ) {

                c.queryRaw( "SELECT date_test FROM date_param_test", function( e, r ) {

                    assert.ifError( e );

                    assert.equal( utcDate.toISOString(), r.rows[0][0].toISOString(), "dates are not equal" );
                    assert.equal( r.rows[0][0].nanosecondsDelta, 0, "nanoseconds not 0" );
                    done();
                });
            },  
            test_done);                        
    });

    test( 'verify js date inserted into datetime field', function( test_done ) {

        var localDate = new Date();
        var utcDate = new Date( Date.UTC( localDate.getUTCFullYear(), 
                                          localDate.getUTCMonth(),
                                          localDate.getUTCDate(),
                                          localDate.getUTCHours(),
                                          localDate.getUTCMinutes(),
                                          localDate.getUTCSeconds(),
                                          localDate.getUTCMilliseconds() ));

        testBoilerPlate( "datetime_test", { "datetime_test" : "datetime" },
            function( done ) {

                c.queryRaw( "INSERT INTO datetime_test (datetime_test) VALUES (?)", [ utcDate ], function( e, r ) {

                    assert.ifError( e );
                    assert( r.rowcount == 1 );

                    done();
                }); 
            },
            function( done ) {


                c.queryRaw( "SELECT * FROM datetime_test", function( e, r ) {

                    assert.ifError( e );
                    assert( r.rows[0][0], utcDate );

                    done();
                }); 
            },
            test_done );

    });

    test( 'verify empty string inserted into nvarchar field', function( test_done ) {

        testBoilerPlate( "emptystring_test", { "emptystring_test" : "nvarchar(1)" },
            function( done ) {

                c.queryRaw( "INSERT INTO emptystring_test (emptystring_test) VALUES (?)", [ '' ], function( e, r ) {

                    assert.ifError( e );
                    assert( r.rowcount == 1 );

                    done();
                }); 
            },
            function( done ) {

                c.queryRaw( "SELECT * FROM emptystring_test", function( e, r ) {

                    assert.ifError( e );
                    assert( r.rows[0][0], '' );

                    done();
                });
            },
            test_done );
    });

    test( 'insert min and max number values', function( test_done ) {

        testBoilerPlate( "minmax_test", { "f" : "float" },
            function( done ) {
                async.series([

                    function( async_done ) {

                        c.queryRaw( "INSERT INTO minmax_test (f) VALUES (?)", [ Number.MAX_VALUE ], 
                            function( e, r ) {

                                assert.ifError( e );

                                async_done();
                        });
                    },
                    function( async_done ) {

                        c.queryRaw( "INSERT INTO minmax_test (f) VALUES (?)", [ -Number.MAX_VALUE ], 
                            function( e, r ) {

                                assert.ifError( e );

                                async_done();
                                done();
                        });
                    },
                ]);
            },
            function( done ) {

                c.queryRaw( "SELECT f FROM minmax_test", function( e, r ) {

                    assert.ifError( e );

                    var expected =  { meta: [ { name: 'f', size: 53, nullable: true, type: 'number', sqlType: 'float' } ],
                                      rows: [ [ 1.7976931348623157e+308 ], [ -1.7976931348623157e+308 ] ] };

                    assert.deepEqual( r, expected, "minmax results don't match" );

                    done();
                });
            },            
            test_done );
    });

    test( 'invalid numbers cause errors', function( test_done ) {

        testBoilerPlate( "invalid_numbers_test", { "f" : "float" },

            function( done ) {
                async.series([
                    function( async_done ) {

                        c.queryRaw( "INSERT INTO invalid_numbers_test (f) VALUES (?)", [ Number.POSITIVE_INFINITY ], function( e, r ) {

                            assert( e == "Error: IMNOD: [msnodesql] Parameter 1: Invalid number parameter" );

                            async_done();
                        });
                    },

                    function( async_done ) {

                        c.queryRaw( "INSERT INTO invalid_numbers_test (f) VALUES (?)", [ Number.NEGATIVE_INFINITY ], function( e, r ) {

                            assert( e == "Error: IMNOD: [msnodesql] Parameter 1: Invalid number parameter" );

                            async_done();
                            done();
                        });
                    }
                ]);
            },
            function( done ) {
                done();
            },
            test_done );
    });

    test( 'insert large string into max column', function( test_done ) {

        String.prototype.repeat = function( num )
        {
            return new Array( num + 1 ).join( this );
        }

        testBoilerPlate( "test_large_insert", { "large_insert" : "nvarchar(max) " },
            function( done ) {

                var large_text = "A".repeat( 10000 );

                c.query( "INSERT INTO test_large_insert (large_insert) VALUES (?)", [ large_text ], function( e, r ) {

                    assert.ifError( e, "Error inserting large string" );

                    done();
                })
            },
            function( done ) {

                c.query( "SELECT large_insert FROM test_large_insert", function( e, r ) {

                    assert.ifError( e );

                    assert( r[0].large_insert.length == 10000, "Incorrect length for large insert" );

                    done();
                });
            },
            test_done );
    });

    test( 'verify js date inserted into datetime field', function( test_done ) {

        var localDate = new Date();
        var utcDate = new Date( Date.UTC( localDate.getUTCFullYear(), 
                                          localDate.getUTCMonth(),
                                          localDate.getUTCDate(),
                                          localDate.getUTCHours(),
                                          localDate.getUTCMinutes(),
                                          localDate.getUTCSeconds(),
                                          localDate.getUTCMilliseconds() ));

        testBoilerPlate( "datetime_test", { "datetime_test" : "datetime" },

            function( done ) {

                c.queryRaw( "INSERT INTO datetime_test (datetime_test) VALUES (?)", [ utcDate ], function( e, r ) {

                    assert.ifError( e );
                    assert( r.rowcount == 1 );

                    done();
                }); 
            },
            function( done ) {

                c.queryRaw( "SELECT * FROM datetime_test", function( e, r ) {

                    assert.ifError( e );
                    assert( r.rows[0][0], utcDate );

                    done();
                }); 
            },
            test_done );
    });

    test( 'verify js date before 1970 inserted into datetime field', function( test_done ) {

        var ancientDate = new Date( 1492, 10, 11, 6, 32, 46, 578 );
        var utcDate = new Date( Date.UTC( ancientDate.getUTCFullYear(), 
                                          ancientDate.getUTCMonth(),
                                          ancientDate.getUTCDate(),
                                          ancientDate.getUTCHours(),
                                          ancientDate.getUTCMinutes(),
                                          ancientDate.getUTCSeconds(),
                                          ancientDate.getUTCMilliseconds() ));

        testBoilerPlate( 'datetime_test', { 'datetime_test' : 'datetimeoffset(3)' },

            function( done ) {

                c.queryRaw( "INSERT INTO datetime_test (datetime_test) VALUES (?)", [ utcDate ], function( e, r ) {

                    assert.ifError( e );
                    assert( r.rowcount == 1 );

                    done();
                }); 
            },
            function( done ) {

                c.queryRaw( "SELECT datetime_test FROM datetime_test", function( e, r ) {

                    assert.ifError( e );
                    assert.equal( r.rows[0][0].valueOf(), utcDate.valueOf() );

                    done();
                }); 
            },
            test_done );
    });

  // verify fix for a bug that would return the wrong day when a datetimeoffset was inserted where the date
  // was before 1/1/1970 and the time was midnight.
  test( 'verify dates with midnight time', function( test_done ) {
    sql.open( conn_str, function( err, conn ) {

        assert.ifError( err );

        var midnightDate = new Date( Date.parse( "2030-08-13T00:00:00.000Z" ));

        testBoilerPlate( 'midnight_date_test', { 'midnight_date_test': 'datetimeoffset(3)' },

            function (done) {

                var insertQuery = "INSERT INTO midnight_date_test (midnight_date_test) VALUES (?);";
                conn.queryRaw( insertQuery, [ midnightDate ], function( e ) {

                    assert.ifError( e );
                    done();
                });
            },
            // test valid dates
            function( done ) {

                conn.queryRaw( "SELECT midnight_date_test FROM midnight_date_test", function( e, r ) {

                    assert.ifError( e );

                    var expectedDates = [];
                    var expectedDate = midnightDate
                    expectedDates.push( [ expectedDate ]);
                    var expectedResults = { meta: [ { name: 'midnight_date_test', size: 30, nullable: true, type: 'date', sqlType: 'datetimeoffset' } ],
                        rows: expectedDates 
                    };
                    assert.deepEqual( expectedResults.meta, r.meta );
                    assert( r.rows.length == 1 );
                    for( var row in r.rows ) {
                        for( var d in row ) {
                            assert.deepEqual( expectedResults.rows[row][d], r.rows[row][d] );
                        }
                    }
                    done();
                })
            },
            test_done );
      });
  });

  test( 'verify bug fix for last day of the year error', function( test_done ) {
    sql.open( conn_str, function( err, conn ) {

        assert.ifError( err );

        var eoyDate = new Date( Date.parse( "1960-12-31T11:12:13.000Z" ));

        testBoilerPlate( 'eoy_date_test', { 'eoy_date_test': 'datetimeoffset(3)' },

            function (done) {

                var insertQuery = "INSERT INTO eoy_date_test (eoy_date_test) VALUES (?);";
                conn.queryRaw( insertQuery, [ eoyDate ], function( e ) {

                    assert.ifError( e );
                    done();
                });
            },
            // test valid dates
            function( done ) {

                conn.queryRaw( "SELECT eoy_date_test FROM eoy_date_test", function( e, r ) {

                    assert.ifError( e );

                    var expectedDates = [];
                    var expectedDate = eoyDate;
                    expectedDates.push( [ expectedDate ]);
                    var expectedResults = { meta: [ { name: 'eoy_date_test', size: 30, nullable: true, type: 'date', sqlType: 'datetimeoffset' } ],
                        rows: expectedDates 
                    };
                    assert.deepEqual( expectedResults.meta, r.meta );
                    assert( r.rows.length == 1 );
                    for( var row in r.rows ) {
                        for( var d in row ) {
                            assert.deepEqual( expectedResults.rows[row][d], r.rows[row][d] );
                        }
                    }
                    done();
                })
            },
            test_done );
      });
  });

  test( 'verify Buffer objects as input parameters', function( test_done ) {

    sql.open( conn_str, function( err, conn ) {
        
        assert.ifError( err );
        var b = new Buffer( '0102030405060708090a', 'hex' );

        testBoilerPlate( 'buffer_param_test', { 'buffer_param' : 'varbinary(100)' },

            function( done ) {
                conn.queryRaw( "INSERT INTO buffer_param_test (buffer_param) VALUES (?)", [ b ], function( e, r ) {
                    assert.ifError( e );
                    done();
                });
            },
            function( done ) {
                conn.queryRaw( "SELECT buffer_param FROM buffer_param_test WHERE buffer_param = ?", [ b ], function( e, r ) {
                    assert.ifError( e );
                    assert( r.rows.length = 1 );
                    assert.deepEqual( r.rows[0][0], b );
                    done();
                });
            },
            test_done );
    });
  });

  test( 'verify buffer longer than column causes error', function( test_done ) {

    sql.open( conn_str, function( err, conn ) {

        assert.ifError( err );
        var b = new Buffer( '0102030405060708090a', 'hex' );

        testBoilerPlate( 'buffer_param_test', { 'buffer_param' : 'varbinary(5)' },

            function( done ) {
                conn.queryRaw( "INSERT INTO buffer_param_test (buffer_param) VALUES (?)", [ b ], function( e, r ) {
                    var expectedError = new Error('[Microsoft][SQL Server Native Client 11.0][SQL Server]String or binary data would be truncated.');
                    expectedError.sqlstate = "22001";
                    expectedError.code = 8152;
                    assert.deepEqual( e, expectedError );
                    done();
                });
            },
            function( done ) {
                done();
            },
            test_done );
    });
  });

  test( 'verify that non-Buffer object parameter returns an error', function( test_done ) {

    sql.open( conn_str, function( err, conn ) {

        assert.ifError( err );
        var o = { field1: 'value1', field2: -1 };

        testBoilerPlate( 'non_buffer_object', { 'object_col': 'varbinary(100)'},
            
            function( done ) {
                conn.queryRaw( "INSERT INTO non_buffer_object (object_col) VALUES (?)", [ o ], function( e, r ) {
                    assert( e == "Error: IMNOD: [msnodesql] Parameter 1: Invalid parameter type" );
                    done();
                });
            },
            function( done ) {
                done();
            },
            test_done );
    });
  });
});