### Copyright 2018 Pixar
###
###    Licensed under the Apache License, Version 2.0 (the "Apache License")
###    with the following modification; you may not use this file except in
###    compliance with the Apache License and the following modification to it:
###    Section 6. Trademarks. is deleted and replaced with:
###
###    6. Trademarks. This License does not grant permission to use the trade
###       names, trademarks, service marks, or product names of the Licensor
###       and its affiliates, except as required to comply with Section 4(c) of
###       the License and to reproduce the content of the NOTICE file.
###
###    You may obtain a copy of the Apache License at
###
###        http://www.apache.org/licenses/LICENSE-2.0
###
###    Unless required by applicable law or agreed to in writing, software
###    distributed under the Apache License with the above modification is
###    distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
###    KIND, either express or implied. See the Apache License for the specific
###    language governing permissions and limitations under the Apache License.
###
###

###
module D3

  ###
  module Database

    ################# Module Constants #################

    ### Booleans are stored as 1's and 0's in the db.
    TRUE_VAL = 1
    FALSE_VAL = 0

    # This table has info about the JSS schema
    SCHEMA_TABLE = 'db_schema_information'.freeze

    # the minimum JSS schema version allowed
    MIN_SCHEMA_VERSION = '9.4'.freeze

    # the max JSS schema version allowed
    MAX_SCHEMA_VERSION = "10.999.0"

    ### these Proc objects allow us to encapsulate and pass around various
    ### blocks of code more easily for converting data between their mysql
    ### representation and the Ruby classses we use internally.

    ### Ruby Time objects are stored as JSS epochs (unix epoch plus milliseconds)
    EPOCH_TO_TIME = proc { |v| v.nil? || v.to_s.empty? ? nil : JSS.epoch_to_time(v) }

    ### JSS epochs (unix epoch plus milliseconds) as used as Ruby Time objects
    TIME_TO_EPOCH = proc { |v| v.nil? || v.to_s.empty? ? nil : v.to_jss_epoch }

    ### Integers come from the database as strings, but empty ones should be nil, not zero, as #to_i would do
    STRING_TO_INT = proc { |v| v.nil? || v.to_s.empty? ? nil : v.to_i }

    ### Some values are stored as comma-separated strings, but used as Arrays
    COMMA_STRING_TO_ARRAY = proc { |v| JSS.to_s_and_a(v)[:arrayform] }

    ### Some values are stored as comma-separated strings, but used as Arrays of Pathnames
    COMMA_STRING_TO_ARRAY_OF_PATHNAMES = proc { |v| JSS.to_s_and_a(v)[:arrayform].map { |p| Pathname.new(p) } }
    ARRAY_OF_PATHNAMES_TO_COMMA_STRING = proc { |v| v.is_a?(Array) ? v.join(', ') : '' }

    ### Some values are used as Arrays but stored as comma-separated strings
    ARRAY_TO_COMMA_STRING = proc { |v| JSS.to_s_and_a(v)[:stringform] }

    ### Some values are stored in the DB as YAML dumps
    RUBY_TO_YAML = proc { |v| YAML.dump v }

    YAML_TO_RUBY = proc { |v| YAML.load v.to_s }

    ### Booleans are stored as zero and one
    BOOL_TO_INT = proc { |v| v == true ? TRUE_VAL : FALSE_VAL }

    ### Booleans are stored as zero and one
    INT_TO_BOOL = proc { |v| v.to_i == FALSE_VAL ? false : true }

    ### Regexps are stored as strings
    STRING_TO_REGEXP = proc { |v| v.to_s.empty? ? nil : Regexp.new(v.to_s) }

    ### Regexps are stored as strings
    REGEXP_TO_STRING = proc { |v| v.to_s }

    ### Status values are stored as strings, but used as symbols
    STATUS_TO_STRING = proc { |v| v.to_s }
    STRING_TO_STATUS = proc { |v| v.to_sym }

    ### Expiration paths are stored as strings, but used as Pathnames
    STRING_TO_PATHNAME = proc { |v| Pathname.new v.to_s }
    PATHNAME_TO_STRING = proc { |v| v.to_s }

    ### The MySQL table that defines which JSS Packages are a part of d3
    ###
    ### This complex Hash contains all the data needed to create and work with the
    ### d3 Packages table.
    ###
    ### The Hash contains these keys & values:
    ###
    ### - :table_name [String] the name of the table in the database
    ###
    ### - :other_indexes [Array<String>] SQL clauses for defining multi-field indexes
    ###   in the CREATE TABLE statement. Single-field indexes are defined in the field definitions.
    ###
    ### - :field_definitions [Hash<Hash>] The definitions of the fields in the table, used throughout the D3 module to
    ###   refer to the data from the database. The keys are also used as the attribute names of {DD3::Package} objects
    ###   Each field definition is a subHash with these keys:
    ### - - :field_name [String] the name of the field in the table
    ### - - :sql_type [String] The SQL data type clause and options for creating the field in the table
    ### - - :index [Boolean,Symbol] How should the field be indexed in the table? One of: true, :primary, :unique or nil/false
    ### - - :to_sql [Proc] the Proc to call with a Ruby object, to convert it to the storable format for the field.
    ###     Integers and Strings don't need conversion. Mysql.encode will be called on all values automatically.
    ### - - :to_ruby [Proc] The Proc to call with a MySQL return value, to convert it to the Ruby class
    ###     used by this module. nil if the value should be a String in Ruby.
    ###
    ### The fields in the table, their Ruby classes, and their meanings are:
    ###
    ### - :id [Integer] the JSS::Package id of this package
    ###
    ### - :basename [String] the basename to which this pkg belongs
    ###
    ### - :version [String] the version number for this basename, installed by this package
    ###
    ### - :revision [Integer] the d3 pkg-revision number of this version. I.e. how many times has this basename-version
    ###   been added to d3?
    ###
    ### - :apple_receipt_data [Array<Hash>] the apple package data for this pkg and all it's sub-pkgs
    ###   Each Hash contains these keys for each pkg installed
    ###     - :apple_pkg_id  The identifier for the item, e.g. com.avid.edlmanager.pkg
    ###     - :version  The version installed, which might not match the version of the metapkg
    ###     - :installed_kb  The disk spaced used by this pkg when installed
    ###   When .[m]pkgs are installed, the identifiers and metadata for each are recorded in the OS's receipts database
    ###   and are accessible via the pkgutil command. (e.g. pkgutil --pkg-info com.company.application). Storing the apple rcpt
    ###   data in the DB allows us to do uninstalls and other client tasks without needing to index the pkg in jamf. This is
    ###   stored in the DB as a YAML string
    ###
    ### - :added_date [Time] when was this package was added to d3
    ###
    ### - :added_by [String,nil] the login name of the admin who added this packge to d3
    ###
    ### - :status [Integer] the status of this pkg, one of {D3::Basename::STATUSES}
    ###
    ### - :release_date [Time,nil] when was this package made live in d3
    ###
    ### - :released_by [String,nil] the login name of the admin who made it live
    ###
    ### - :auto_groups [Array] a list of JSS::ComputerGroup names whose members get this
    ###   package installed automatically. The special value :standard means all computers
    ###   get this package automatically, except those in excluded groups.
    ###
    ### - :excluded_groups [Array] a list of JSS::ComputerGroup names for whose members this
    ###   package is not available without force
    ###
    ### - :triggers_swu [Boolean] when installed, will this package trigger a GUI software update check,
    ###   either immediately if there's a console user, or at the next console login?
    ###
    ### - :prohibiting_processes [Array<String>] An array of strings for matching to the output lines
    ###   of '/bin/ps -A -c -o comm'. If there's a matching line, this pkg won't be installed
    ###
    ### - :remove_first [Boolean] should any currently installed versions of this basename
    ###   be uninstalled (if possible) before installing this package?
    ###
    ### - :pre_install_id [Integer,nil] the JSS::Script id of the pre-install script, if any
    ###
    ### - :post_install_id [Integer,nil] the JSS::Script id of the post-install script, if any
    ###
    ### - :pre_remove_id [Integer,nil] the JSS::Script id of the pre-remove script, if any
    ###
    ### - :post_remove_id [Integer,nil] the JSS::Script id of the post-remove script, if any
    ###
    ### See also the attributes of {D3::Package}, which mostly mirror the
    ###
    PACKAGE_TABLE = {
      table_name: 'd3_packages',

      field_definitions: {

        id: {
          field_name: 'package_id',
          sql_type: 'int(11) NOT NULL',
          index: :unique,
          to_sql: nil,
          to_ruby: STRING_TO_INT
        },

        basename: {
          field_name: 'basename',
          sql_type: 'varchar(60) NOT NULL',
          index: true,
          to_sql: nil,
          to_ruby: nil
        },

        version: {
          field_name: 'version',
          sql_type: 'varchar(30) NOT NULL',
          index: nil,
          to_sql: nil,
          to_ruby: nil
        },

        revision: {
          field_name: 'revision',
          sql_type: 'int(4) NOT NULL',
          index: nil,
          to_sql: nil,
          to_ruby: STRING_TO_INT
        },

        apple_receipt_data: {
          field_name: 'apple_receipt_data',
          sql_type: 'text',
          index: nil,
          to_sql: RUBY_TO_YAML,
          to_ruby: YAML_TO_RUBY
        },

        added_date: {
          field_name: 'added_date_epoch',
          sql_type: 'bigint(32) DEFAULT NULL',
          index: nil,
          to_sql: TIME_TO_EPOCH,
          to_ruby: EPOCH_TO_TIME
        },

        added_by: {
          field_name: 'added_by',
          sql_type: 'varchar(30)',
          index: nil,
          to_sql: nil,
          to_ruby: nil
        },

        status: {
          field_name: 'status',
          sql_type: "varchar(30) DEFAULT 'pilot'",
          index: nil,
          to_sql: STATUS_TO_STRING,
          to_ruby: STRING_TO_STATUS
        },

        release_date: {
          field_name: 'release_date_epoch',
          sql_type: 'bigint(32)  DEFAULT NULL',
          index: nil,
          to_sql: TIME_TO_EPOCH,
          to_ruby: EPOCH_TO_TIME
        },

        released_by: {
          field_name: 'released_by',
          sql_type: 'varchar(30)',
          index: nil,
          to_sql: nil,
          to_ruby: nil
        },

        auto_groups: {
          field_name: 'auto_install_groups',
          sql_type: 'text',
          index: nil,
          to_sql: ARRAY_TO_COMMA_STRING,
          to_ruby: COMMA_STRING_TO_ARRAY
        },

        excluded_groups: {
          field_name: 'excluded_groups',
          sql_type: 'text',
          index: nil,
          to_sql:  ARRAY_TO_COMMA_STRING,
          to_ruby: COMMA_STRING_TO_ARRAY
        },

        prohibiting_processes: {
          field_name: 'prohibiting_process',
          sql_type: 'varchar(100)',
          index: nil,
          to_sql: ARRAY_TO_COMMA_STRING,
          to_ruby: COMMA_STRING_TO_ARRAY
        },

        remove_first: {
          field_name: 'remove_first',
          sql_type: "tinyint(1) DEFAULT '0'",
          index: nil,
          to_sql: BOOL_TO_INT,
          to_ruby: INT_TO_BOOL
        },

        pre_install_script_id: {
          field_name: 'pre_install_id',
          sql_type: 'int(11)',
          index: nil,
          to_sql: nil,
          to_ruby: STRING_TO_INT
        },

        post_install_script_id: {
          field_name: 'post_install_id',
          sql_type: 'int(11)',
          index: nil,
          to_sql: nil,
          to_ruby: STRING_TO_INT
        },

        pre_remove_script_id: {
          field_name: 'pre_remove_id',
          sql_type: 'int(11)',
          index: nil,
          to_sql: nil,
          to_ruby: STRING_TO_INT
        },

        post_remove_script_id: {
          field_name: 'post_remove_id',
          sql_type: 'int(11)',
          index: nil,
          to_sql: nil,
          to_ruby: STRING_TO_INT
        },

        expiration: {
          field_name: 'expiration',
          sql_type: 'int(11)',
          index: nil,
          to_sql: nil,
          to_ruby: STRING_TO_INT
        },

        expiration_paths: {
          field_name: 'expiration_app_path',
          sql_type: 'varchar(300)',
          index: nil,
          to_sql: ARRAY_OF_PATHNAMES_TO_COMMA_STRING,
          to_ruby: COMMA_STRING_TO_ARRAY_OF_PATHNAMES
        }
      },

      other_indexes: [
        'UNIQUE KEY `edition` (`basename`,`version`,`revision`)'
      ]
    }.freeze # end PACKAGE_TABLE

    ################# Module Methods #################

    ### Retrieve all records for one of the tables defined in D3::Database
    ###
    ### This is generally used by the method {D3::Package.package_data},
    ###
    ### Returns an Array of Hashes, one for each record in the desired table.
    ### The keys of each hash are the keys of the :field_definitions hash from
    ### the table definition.
    ###
    ### @param table_def[Hash] one of the d3 mysql table definitions, currently only
    ###   {D3::Database::PACKAGE_TABLE}
    ###
    ### @return [Array<Hash>] the records from the desired table, with all values converted to
    ###   appropriate Ruby classes as defined in the table_def
    ###
    ###
    def self.table_records(table_def)
      recs = []

      result = JSS.db.query "SELECT * FROM #{table_def[:table_name]}"

      # parse each record into a hash
      result.each_hash do |record|
        rec = {}

        # go through each field in the record, adding it to the hash
        # converting it to its ruby data type if defined in field conversions
        table_def[:field_definitions].each_pair do |key, field_def|
          # do we convert the value from the DB to something else in ruby?
          if field_def[:to_ruby]
            rec[key] = field_def[:to_ruby].call record[field_def[:field_name]]

          # or do we use the value as it comes from the DB?
          else
            rec[key] = record[field_def[:field_name]]
          end # if
        end # do key, field_def

        recs << rec
      end # do record

      recs
    end # self.table_records(table_def)

    ### Print the sql for creating the d3_packages table
    ### as defined in the PACKAGE_TABLE constant
    ###
    ### @return [void]
    ###
    def self.table_creation_sql
      puts  self.create_table(:display)
    end

    ### Raise an exception if JSS schema is to old or too new
    def self.check_schema_version
      raw = JSS::DB_CNX.db.query("SELECT version FROM #{SCHEMA_TABLE}").fetch[0]
      simmered = raw.split('.')[0..1].join('.')
      current = JSS.parse_jss_version(simmered)[:version]
      min = JSS.parse_jss_version(MIN_SCHEMA_VERSION)[:version]
      max = JSS.parse_jss_version(MAX_SCHEMA_VERSION)[:version]
      raise JSS::InvalidConnectionError, "Invalid JSS database schema version: #{raw}, min: #{MIN_SCHEMA_VERSION}, max: #{MAX_SCHEMA_VERSION}" if (current < min) || (current > max)
      true
    end

    private


    ### Given a table constant defining a d3 table (PACKAGES_TABLE, at this point),
    ### create the table in the database.
    ###
    ### @param table_constant[Hash] one of the d3 table definition constants, currently only
    ###   {D3::Database::PACKAGE_TABLE}
    ###
    ### @param print[Boolean] just print to stdout the SQL statement for creating the table, don't execute it.
    ###
    ### @return [void]
    ###
    def self.create_table(display = false)
      # as of now, only one table.
      table_constant = PACKAGE_TABLE

      sql = "CREATE TABLE `#{table_constant[:table_name]}` ("
      indexes = ''

      table_constant[:field_definitions].keys.sort.each do |key|
        field = table_constant[:field_definitions][key]

        sql += "\n  `#{field[:field_name]}` #{field[:sql_type]},"

        indexes += case field[:index]
                   when :primary
                     "\n  PRIMARY KEY (`#{field[:field_name]}`),"
                   when :unique
                     "\n  UNIQUE KEY (`#{field[:field_name]}`),"
                   when true
                     "\n  KEY (`#{field[:field_name]}`),"
                   else
                     ''
                   end # indexes +=  case
      end # each do key

      sql += indexes

      table_constant[:other_indexes].each do |idx|
        sql += "\n  #{idx},"
      end

      sql.chomp! ','
      sql += "\n) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci"

      if display
        puts sql
        return
      end

      stmt = JSS::DB_CNX.db.prepare sql
      stmt.execute
    end # create d3 table

    ### @return [Array<String>] A list of all d3-related tables in the database
    ###
    def self.tables
      res = JSS::DB_CNX.db.query 'show tables'
      d3_tables = []
      res.each do |t|
        d3_tables << t[0] if t[0].start_with? 'd3_'
      end #  res.each do |t|
      res.free
      d3_tables
    end

  end # module Database

end # module D3
