# frozen-string-literal: true

Sequel::JDBC.load_driver('org.hsqldb.jdbcDriver', :HSQLDB)
require_relative 'transactions'

module Sequel
  module JDBC
    Sequel.synchronize do
      DATABASE_SETUP[:hsqldb] = proc do |db|
        db.extend(Sequel::JDBC::HSQLDB::DatabaseMethods)
        db.dataset_class = Sequel::JDBC::HSQLDB::Dataset
        org.hsqldb.jdbcDriver
      end
    end

    module HSQLDB
      module DatabaseMethods
        include ::Sequel::JDBC::Transactions

        def database_type
          :hsqldb
        end

        def freeze
          db_version
          super
        end

        # HSQLDB uses an IDENTITY sequence as the default value for primary
        # key columns.
        def serial_primary_key_options
          {:primary_key => true, :type => :integer, :identity=>true, :start_with=>1}
        end

        # The version of the database, as an integer (e.g 2.2.5 -> 20205)
        def db_version
          return @db_version if defined?(@db_version)
          v = get(Sequel.function(:DATABASE_VERSION))
          @db_version = if v =~ /(\d+)\.(\d+)\.(\d+)/
            $1.to_i * 10000 + $2.to_i * 100 + $3.to_i
          end
        end
        
        # HSQLDB supports DROP TABLE IF EXISTS
        def supports_drop_table_if_exists?
          true
        end

        private
        
        def alter_table_sql(table, op)
          case op[:op]
          when :add_column
            if op[:table]
              [super(table, op.merge(:table=>nil)),
               alter_table_sql(table, op.merge(:op=>:add_constraint, :type=>:foreign_key, :name=>op[:foreign_key_constraint_name], :columns=>[op[:name]], :table=>op[:table]))]
            else
              super
            end
          when :rename_column
            "ALTER TABLE #{quote_schema_table(table)} ALTER COLUMN #{quote_identifier(op[:name])} RENAME TO #{quote_identifier(op[:new_name])}"
          when :set_column_type
            "ALTER TABLE #{quote_schema_table(table)} ALTER COLUMN #{quote_identifier(op[:name])} SET DATA TYPE #{type_literal(op)}"
          when :set_column_null
            "ALTER TABLE #{quote_schema_table(table)} ALTER COLUMN #{quote_identifier(op[:name])} SET #{op[:null] ? 'NULL' : 'NOT NULL'}"
          else
            super
          end
        end

        # HSQLDB requires parens around the SELECT, and the WITH DATA syntax.
        def create_table_as_sql(name, sql, options)
          "#{create_table_prefix_sql(name, options)} AS (#{sql}) WITH DATA"
        end

        DATABASE_ERROR_REGEXPS = {
          /integrity constraint violation: unique constraint or index violation/ => UniqueConstraintViolation,
          /integrity constraint violation: foreign key/ => ForeignKeyConstraintViolation,
          /integrity constraint violation: check constraint/ => CheckConstraintViolation,
          /integrity constraint violation: NOT NULL check constraint/ => NotNullConstraintViolation,
          /serialization failure/ => SerializationFailure,
        }.freeze
        def database_error_regexps
          DATABASE_ERROR_REGEXPS
        end

        # IF EXISTS comes after table name on HSQLDB
        def drop_table_sql(name, options)
          "DROP TABLE #{quote_schema_table(name)}#{' IF EXISTS' if options[:if_exists]}#{' CASCADE' if options[:cascade]}"
        end
        
        # IF EXISTS comes after view name on HSQLDB
        def drop_view_sql(name, options)
          "DROP VIEW #{quote_schema_table(name)}#{' IF EXISTS' if options[:if_exists]}#{' CASCADE' if options[:cascade]}"
        end

        # Use IDENTITY() to get the last inserted id.
        def last_insert_id(conn, opts=OPTS)
          statement(conn) do |stmt|
            sql = 'CALL IDENTITY()'
            rs = log_connection_yield(sql, conn){stmt.executeQuery(sql)}
            rs.next
            rs.getLong(1)
          end
        end
        
        # Primary key indexes appear to start with sys_idx_sys_pk_ on HSQLDB
        def primary_key_index_re
          /\Asys_idx_sys_pk_/i
        end

        # If an :identity option is present in the column, add the necessary IDENTITY SQL.
        # It's possible to use an IDENTITY type, but that defaults the sequence to start
        # at 0 instead of 1, and we don't want that.
        def type_literal(column)
          if column[:identity]
            sql = "#{super} GENERATED BY DEFAULT AS IDENTITY"
            if sw = column[:start_with]
              sql += " (START WITH #{sw.to_i}"
              sql << " INCREMENT BY #{column[:increment_by].to_i}" if column[:increment_by]
              sql << ")"
            end
            sql
          else
            super
          end
        end

        # HSQLDB uses clob for text types.
        def uses_clob_for_text?
          true
        end

        # HSQLDB supports views with check option.
        def view_with_check_option_support
          :local
        end
      end
      
      class Dataset < JDBC::Dataset
        # Handle HSQLDB specific case insensitive LIKE and bitwise operator support.
        def complex_expression_sql_append(sql, op, args)
          case op
          when :ILIKE, :"NOT ILIKE"
            super(sql, (op == :ILIKE ? :LIKE : :"NOT LIKE"), args.map{|v| SQL::Function.new(:ucase, v)})
          when :&, :|, :^, :%, :<<, :>>, :'B~'
            complex_expression_emulate_append(sql, op, args)
          else
            super
          end
        end

        # HSQLDB requires recursive CTEs to have column aliases.
        def recursive_cte_requires_column_aliases?
          true
        end

        # HSQLDB requires SQL standard datetimes in some places.
        def requires_sql_standard_datetimes?
          true
        end

        # HSQLDB does support common table expressions, but the support is broken.
        # CTEs operate more like temprorary tables or views, lasting longer than the duration of the expression.
        # CTEs in earlier queries might take precedence over CTEs with the same name in later queries.
        # Also, if any CTE is recursive, all CTEs must be recursive.
        # If you want to use CTEs with HSQLDB, you'll have to manually modify the dataset to allow it.
        def supports_cte?(type=:select)
          false
        end

        # HSQLDB does not support IS TRUE.
        def supports_is_true?
          false
        end

        # HSQLDB supports lateral subqueries.
        def supports_lateral_subqueries?
          true
        end

        private

        def empty_from_sql
          " FROM (VALUES (0))"
        end
        
        # Use string in hex format for blob data.
        def literal_blob_append(sql, v)
          sql << "X'" << v.unpack("H*").first << "'"
        end

        # HSQLDB uses FALSE for false values.
        def literal_false
          'FALSE'
        end

        # HSQLDB handles fractional seconds in timestamps, but not in times
        def literal_sqltime(v)
          v.strftime("'%H:%M:%S'")
        end

        # HSQLDB uses TRUE for true values.
        def literal_true
          'TRUE'
        end

        # HSQLDB supports multiple rows in INSERT.
        def multi_insert_sql_strategy
          :values
        end

        # Use WITH RECURSIVE instead of WITH if any of the CTEs is recursive
        def select_with_sql_base
          opts[:with].any?{|w| w[:recursive]} ? "WITH RECURSIVE " : super
        end
      end
    end
  end
end