<?php

/**
 * @file
 * Install, update and uninstall functions for the uc_shipping module.
 */

/**
 * Implements hook_schema().
 */
function uc_shipping_schema() {
  $schema = array();

  $schema['uc_shipments'] = array(
    'description' => 'Stores shipment information.',
    'fields' => array(
      'sid' => array(
        'description' => 'Primary key: the shipment ID.',
        'type' => 'serial',
        'unsigned' => TRUE,
        'not null' => TRUE,
      ),
      'order_id' => array(
        'description' => 'The {uc_orders}.order_id of the order associated with the shipment.',
        'type' => 'int',
        'unsigned' => TRUE,
        'not null' => TRUE,
        'default' => 0,
      ),
      'o_first_name' => array(
        'description' => 'Origin address: First name.',
        'type' => 'varchar',
        'length' => 255,
        'not null' => TRUE,
        'default' => '',
      ),
      'o_last_name' => array(
        'description' => 'Origin address: Last name.',
        'type' => 'varchar',
        'length' => 255,
        'not null' => TRUE,
        'default' => '',
      ),
      'o_company' => array(
        'description' => 'Origin address: Company name.',
        'type' => 'varchar',
        'length' => 255,
        'not null' => TRUE,
        'default' => '',
      ),
      'o_street1' => array(
        'description' => 'Origin address: Street line 1.',
        'type' => 'varchar',
        'length' => 255,
        'not null' => TRUE,
        'default' => '',
      ),
      'o_street2' => array(
        'description' => 'Origin address: Street line 2.',
        'type' => 'varchar',
        'length' => 255,
        'not null' => TRUE,
        'default' => '',
      ),
      'o_city' => array(
        'description' => 'Origin address: City.',
        'type' => 'varchar',
        'length' => 255,
        'not null' => TRUE,
        'default' => '',
      ),
      'o_zone' => array(
        'description' => 'Origin address: State/province, from {uc_zones}.zone_id.',
        'type' => 'int',
        'size' => 'medium',
        'unsigned' => TRUE,
        'not null' => TRUE,
        'default' => 0,
      ),
      'o_postal_code' => array(
        'description' => 'Origin address: Postal code.',
        'type' => 'varchar',
        'length' => 255,
        'not null' => TRUE,
        'default' => '',
      ),
      'o_country' => array(
        'description' => 'Origin address: Country, from {uc_countries}.country_id.',
        'type' => 'int',
        'size' => 'medium',
        'unsigned' => TRUE,
        'not null' => TRUE,
        'default' => 0,
      ),
      'd_first_name' => array(
        'description' => 'Destination address: First name.',
        'type' => 'varchar',
        'length' => 255,
        'not null' => TRUE,
        'default' => '',
      ),
      'd_last_name' => array(
        'description' => 'Destination address: Last name.',
        'type' => 'varchar',
        'length' => 255,
        'not null' => TRUE,
        'default' => '',
      ),
      'd_company' => array(
        'description' => 'Destination address: Company name.',
        'type' => 'varchar',
        'length' => 255,
        'not null' => TRUE,
        'default' => '',
      ),
      'd_street1' => array(
        'description' => 'Destination address: Street line 1.',
        'type' => 'varchar',
        'length' => 255,
        'not null' => TRUE,
        'default' => '',
      ),
      'd_street2' => array(
        'description' => 'Destination address: Street line 2.',
        'type' => 'varchar',
        'length' => 255,
        'not null' => TRUE,
        'default' => '',
      ),
      'd_city' => array(
        'description' => 'Destination address: City.',
        'type' => 'varchar',
        'length' => 255,
        'not null' => TRUE,
        'default' => '',
      ),
      'd_zone' => array(
        'description' => 'Destination address: State/province, from {uc_zones}.zone_id.',
        'type' => 'int',
        'size' => 'medium',
        'unsigned' => TRUE,
        'not null' => TRUE,
        'default' => 0,
      ),
      'd_postal_code' => array(
        'description' => 'Destination address: Postal code.',
        'type' => 'varchar',
        'length' => 255,
        'not null' => TRUE,
        'default' => '',
      ),
      'd_country' => array(
        'description' => 'Destination address: Country, from {uc_countries}.country_id.',
        'type' => 'int',
        'size' => 'medium',
        'unsigned' => TRUE,
        'not null' => TRUE,
        'default' => 0,
      ),
      'shipping_method' => array(
        'description' => 'The shipping method.',
        'type' => 'varchar',
        'length' => 255,
        'not null' => TRUE,
        'default' => '',
      ),
      'accessorials' => array(
        'description' => 'Shipping options and special instructions.',
        'type' => 'varchar',
        'length' => 255,
        'not null' => TRUE,
        'default' => '',
      ),
      'carrier' => array(
        'description' => 'The company making the delivery.',
        'type' => 'varchar',
        'length' => 255,
        'not null' => TRUE,
        'default' => '',
      ),
      'transaction_id' => array(
        'description' => "The carrier's shipment identifier.",
        'type' => 'varchar',
        'length' => 255,
        'not null' => TRUE,
        'default' => '',
      ),
      'tracking_number' => array(
        'description' => 'The number used by the carrier to locate the shipment while it is in transit.',
        'type' => 'varchar',
        'length' => 255,
        'not null' => TRUE,
        'default' => '',
      ),
      'ship_date' => array(
        'description' => 'The Unix timestamp indicating when the shipment left the origin address.',
        'type' => 'int',
        'not null' => TRUE,
        'default' => 0,
      ),
      'expected_delivery' => array(
        'description' => 'The Unix timestamp indicating the expected date of delivery.',
        'type' => 'int',
        'not null' => TRUE,
        'default' => 0,
      ),
      'cost' => array(
        'description' => 'The cost of the shipment.',
        'type' => 'numeric',
        'precision' => 16,
        'scale' => 5,
        'not null' => TRUE,
        'default' => 0.0,
      ),
      'changed' => array(
        'description' => 'The Unix timestamp indicating the last time the shipment was modified.',
        'type' => 'int',
        'not null' => TRUE,
        'default' => 0,
      ),
    ),
    'primary key' => array('sid'),
    'foreign keys' => array(
      'uc_orders' => array(
        'table' => 'uc_orders',
        'columns' => array('order_id' => 'order_id'),
      ),
    ),
    'indexes' => array(
      'order_id' => array('order_id'),
    ),
  );

  $schema['uc_packages'] = array(
    'description' => 'Stores shipment package information.',
    'fields' => array(
      'package_id' => array(
        'description' => 'Primary key: the package ID.',
        'type' => 'serial',
        'unsigned' => TRUE,
        'not null' => TRUE,
      ),
      'order_id' => array(
        'description' => 'The {uc_orders}.order_id.',
        'type' => 'int',
        'unsigned' => TRUE,
        'not null' => TRUE,
        'default' => 0,
      ),
      'shipping_type' => array(
        'description' => 'The basic type of shipment, e.g.: small package, freight, etc.',
        'type' => 'varchar',
        'length' => 255,
        'not null' => TRUE,
        'default' => '',
      ),
      'pkg_type' => array(
        'description' => 'The type of packaging.',
        'type' => 'varchar',
        'length' => 255,
        'not null' => TRUE,
        'default' => '',
      ),
      'length' => array(
        'description' => 'The package length.',
        'type' => 'float',
        'not null' => FALSE,
      ),
      'width' => array(
        'description' => 'The package width.',
        'type' => 'float',
        'not null' => FALSE,
      ),
      'height' => array(
        'description' => 'The package height.',
        'type' => 'float',
        'not null' => FALSE,
      ),
      'length_units' => array(
        'description' => 'The physical units of the length, width, and height.',
        'type' => 'varchar',
        'length' => 255,
        'not null' => FALSE,
      ),
      'value' => array(
        'description' => 'The monetary value of the package contents.',
        'type' => 'numeric',
        'precision' => 16,
        'scale' => 5,
        'not null' => FALSE,
        'default' => 0.0,
      ),
      'sid' => array(
        'description' => 'The {uc_shipments}.sid, if the package has been shipped.',
        'type' => 'int',
        'unsigned' => TRUE,
        'not null' => FALSE,
      ),
      'tracking_number' => array(
        'description' => 'The package-specific tracking number, if available.',
        'type' => 'varchar',
        'length' => 255,
        'not null' => FALSE,
      ),
      'label_image' => array(
        'description' => 'The {file}.fid that refers to an image of the shipping label of the package.',
        'type' => 'int',
        'unsigned' => TRUE,
        'not null' => FALSE,
      ),
    ),
    'primary key' => array('package_id'),
    'foreign keys' => array(
      'uc_orders' => array(
        'table' => 'uc_orders',
        'columns' => array('order_id' => 'order_id'),
      ),
      'uc_quote_shipping_types' => array(
        'table' => 'uc_quote_shipping_types',
        'columns' => array('shipping_type' => 'shipping_type'),
      ),
      'uc_shipments' => array(
        'table' => 'uc_shipments',
        'columns' => array('sid' => 'sid'),
      ),
      'file' => array(
        'table' => 'file',
        'columns' => array('label_image' => 'fid'),
      ),
    ),
    'indexes' => array(
      'order_id' => array('order_id'),
      'sid' => array('sid'),
    ),
  );

  $schema['uc_packaged_products'] = array(
    'description' => 'Stores packaged product information.',
    'fields' => array(
      'package_id' => array(
        'description' => 'The {uc_packages}.package_id in which the product is shipped.',
        'type' => 'int',
        'unsigned' => TRUE,
        'not null' => TRUE,
        'default' => 0,
      ),
      'order_product_id' => array(
        'description' => 'The {uc_order_products}.order_product_id of the ordered product.',
        'type' => 'int',
        'unsigned' => TRUE,
        'not null' => TRUE,
        'default' => 0,
      ),
      'qty' => array(
        'description' => 'The number of this product in this package.',
        'type' => 'int',
        'unsigned' => TRUE,
        'not null' => TRUE,
        'default' => 0,
      ),
    ),
    'primary key' => array('package_id', 'order_product_id'),
    'foreign keys' => array(
      'package_id' => array('uc_packages' => 'package_id'),
      'order_product_id' => array('uc_order_products' => 'order_product_id'),
    ),
    'indexes' => array(
      'order_product_id' => array('order_product_id'),
    ),
  );

  return $schema;
}

/**
 * Implements hook_update_last_removed().
 */
function uc_shipping_update_last_removed() {
  return 6005;
}

/**
 * Changes {uc_packages}.label_image to an integer file id.
 */
function uc_shipping_update_7000(&$sandbox) {
  $sandbox['#finished'] = 0;

  $schema = array(
    'type' => 'int',
    'unsigned' => TRUE,
    'not null' => FALSE,
  );

  if (!isset($sandbox['total'])) {
    db_add_field('uc_packages', 'label_image_id', $schema);

    $sandbox['last'] = 0;
    $sandbox['count'] = 0;
    $sandbox['total'] = db_query("SELECT COUNT(*) FROM {uc_packages} WHERE label_image IS NOT NULL")->fetchField();
  }
  else {
    $found = FALSE;
    $scheme = variable_get('file_default_scheme', 'public');

    if ($sandbox['total']) {
      $limit = 200;
      $images = array();

      $packages = db_query_range("SELECT package_id, label_image FROM {uc_packages} WHERE package_id > :package_id AND label_image IS NOT NULL", 0, $limit, array(':package_id' => $sandbox['last']));

      foreach ($packages as $package) {
        $found = TRUE;

        $uri = file_stream_wrapper_uri_normalize($scheme . '://' . $package->label_image);

        $fid = db_query("SELECT fid FROM {file_managed} WHERE uri = :uri", array(':uri' => $uri))->fetchField();

        if (!$fid) {
          $stat = stat($package->label_image);
          $info = getimagesize($package->label_image);
          $file = array(
            'uid' => 1,
            'filename' => basename($package->label_image),
            'uri' => $uri,
            'filemime' => $info['mime'],
            'filesize' => $stat['size'],
            'status' => FILE_STATUS_PERMANENT,
            'timestamp' => $stat['ctime'],
          );

          $fid = db_insert('file_managed')->fields($file)->execute();
        }

        db_update('uc_packages')
          ->fields(array('label_image_id' => $fid))
          ->condition('package_id', $package->package_id)
          ->execute();

        $sandbox['last'] = $package->package_id;
        $sandbox['count']++;
        $sandbox['message'] = check_plain($package->label_image);
      }

      $sandbox['#finished'] = min(0.99, $sandbox['count'] / $sandbox['total']);
    }

    if (!$found) {
      db_drop_field('uc_packages', 'label_image');
      db_change_field('uc_packages', 'label_image_id', 'label_image', $schema, array(
        'foreign keys' => array(
          'label_image' => array('file_managed' => 'fid'),
        ),
      ));

      $sandbox['#finished'] = 1;

      return t('!number label images moved to the file table.', array('!number' => $sandbox['total']));
    }
  }
}

/**
 * Add 'changed' column to shipments.
 */
function uc_shipping_update_7001() {
  if (!db_field_exists('uc_shipments', 'changed')) {
    db_add_field('uc_shipments', 'changed', array(
      'description' => 'The Unix timestamp indicating the last time the shipment was modified.',
      'type' => 'int',
      'not null' => TRUE,
      'default' => 0,
    ));
  }
}

/**
 * Add indexes to package and shipment tables.
 */
function uc_shipping_update_7300() {
  // Alter {uc_shipments} table.
  if (!db_index_exists('uc_shipments', 'order_id')) {
    db_add_index('uc_shipments', 'order_id', array('order_id'));
  }

  // Alter {uc_packages} table.
  if (!db_index_exists('uc_packages', 'order_id')) {
    db_add_index('uc_packages', 'order_id', array('order_id'));
  }
  if (!db_index_exists('uc_packages', 'sid')) {
    db_add_index('uc_packages', 'sid', array('sid'));
  }

  // Alter {uc_packaged_products} table.
  if (!db_index_exists('uc_packaged_products', 'order_product_id')) {
    db_add_index('uc_packaged_products', 'order_product_id', array('order_product_id'));
  }
}

/**
 * Remove orphaned package and shipment records.
 */
function uc_shipping_update_7301() {
  $subquery = db_select('uc_orders', 'o')
    ->fields('o', array('order_id'));

  db_delete('uc_shipments')
    ->condition('order_id', $subquery, 'NOT IN')
    ->execute();

  db_delete('uc_packages')
    ->condition('order_id', $subquery, 'NOT IN')
    ->execute();

  $subquery = db_select('uc_packages', 'p')
    ->fields('p', array('package_id'));

  db_delete('uc_packaged_products')
    ->condition('package_id', $subquery, 'NOT IN')
    ->execute();
}