<?php

// $Id: lm_paypal.install,v 1.26 2007/06/14 00:31:53 leemcl Exp $

/**
 * @file
 *
 * Install or update the PayPal interface.
 *
 * Lee McLoughlin <lee@lmmrtech.com>. July 2006
 *
 * These database fields are from:
 *  https://www.paypal.com/en_US/pdf/PP_OrderManagement_IntegrationGuide.pdf
 * See also:
 *  https://www.paypal.com/IntegrationCenter/ic_ipn-pdt-variable-reference.html
 *  https://www.paypal.com/uk/cgi-bin/webscr?cmd=p/acc/ipn-subscriptions-outside
 */

// TODO: Once I've been running lm_paypal for a while work out which
// ipn fields are unnecessary and remove them.

function lm_paypal_install() {
  $mysql_create_ipns = "
    CREATE TABLE {lm_paypal_ipns} (
      id          mediumint not null auto_increment, # My ID for received IPNs
      timestamp   int(11) unsigned not null,         # unix time when ipn arrived
      
      txn_id      varchar(20) not null default '',   # Paypal generated transaction id
      processed   int(1) default '0',                # set if processed
      
      test_ipn    int(1) default null,               # Only set by sandbox to 1
      verify_sign varchar(120) default null,         # Security string (Was 20)
      
      # Website Payments Standard, Website Payments Pro, and Refund Information 
      # SOME FIELDS OMITTED 
      
      parent_txn_id  varchar(20) default null, # In the case of a refund, reversal, or canceled reversal, this variable contains the txn_id of the original transaction, while txn_id contains a new ID for the new transaction.
      payment_date   varchar(28) default null, # Time/Date stamp generated by PayPal , in the following format: HH:MM:SS DD Mmm YY, YYYY PST
      payment_status varchar(20) default null, # Canceled_Reversal,Completed,Denied,Expired,Failed,In-Progress,Partially-Refunded,Pending,Processed,Refunded,Reversed,Voided
      payment_type   varchar(20) default null, # echeck,instant
      pending_reason varchar(20) default null, # address,authorization,echeck,intl,multicurrency,unilateral,upgrade,verify,other
      reason_code    varchar(20) default null, # chargeback,guarantee,buyer-complaint,refund,other
      
      # Buyer Information
      
      payer_email          varchar(127) default null, # Customers primary email address. Use this email to provide any credits.
      first_name           varchar(64) default null, # Customers first name
      last_name varchar(64) default null, # Customers last name
      payer_business_name  varchar(127) default null, # Customers company name, if customer represents a business
      address_name         varchar(128) default null, # Name used with address (included when the customer provides a Gift Address)
      address_street       varchar(200) default null, # Customers street address
      address_city         varchar(40) default null, # City of customers address
      address_zip          varchar(20) default null, # Zip code of customers address.
      address_country      varchar(64) default null, # Country of customers address.
      address_country_code varchar(2) default null, # Two-character ISO 3166 country code
      address_state        varchar(40) default null, # State of customers address
      address_status       int(1) default null, # Customer provided a confirmed/unconfirmed address.
      payer_id varchar(13) default null, # Unique customer ID.
      payer_status varchar(40) default null, # Customer has a Verified|Unverified PayPal account.
      residence_country    varchar(2) default null, # Two-character ISO 3166 country code
      
      # Business Information
      
      business             varchar(127) default null, # Email address or account ID of the payment recipient (that is, the merchant). Equivalent to the values of receiver_email (if payment is sent to primary account) and business set in the Website Payment HTML.  NOTE: The value of this variable is normalized to lowercase characters.
      item_name            varchar(127) default null, # Item name as passed by you, the merchant. Or, if not passed by you, as entered by your customer. If this is a shopping cart transaction, PayPal will append the number of the item (e.g., item_name1, item_name2).
      quantity             int unsigned not null default '0',
      shipping             decimal(10,2) default null,
      item_number          varchar(127) default null, # Pass-through variable for you to track purchases. It will get passed back to you at the completion of the payment. If omitted, no variable will be passed back to you.
      receiver_email       varchar(127) default null, # Primary email address of the payment recipient (that is, the merchant). If the payment is sent to a non-primary email address on your PayPal account, the receiver_email is still your primary email.  NOTE: The value of this variable is normalized to lowercase characters.
      receiver_id          varchar(13) default null, # Unique account ID of the payment recipient (i.e., the merchant).  This is the same as the recipient's referral ID.
      
      # Advanced and Custom Information
      
      custom               varchar(255) default null, # Custom value as passed by you, the merchant. These are passthrough variables that are never presented to your customer
      invoice              varchar(127) default null, # Passthrough variable you can use to identify your Invoice Number for this purchase. If omittted, no variable is passed back.
      memo                 varchar(255) default null, # Memo as entered by your customer in PayPal Website Payments note field.
      option_name1         varchar(64) default null, # Option 1 name as requested by you.
      option_name2         varchar(64) default null, # Option 2 name as requested by you.
      option_selection1    varchar(200) default null, # Option 1 choice as entered by your customer.
      option_selection2    varchar(200) default null, # Option 2 choice as entered by your customer.
      tax                  varchar(20) default null, # Amount of tax charged on payment.
      
      # SOME FIELDS OMITTED
      
      mc_currency          varchar(3) default null, # Three character currency code
      # SOME FIELDS OMITTED
      payment_fee          decimal(10,2) default null, # USD transaction fee associated with the payment.  payment_gross minus payment_fee equals the amount deposited into the receiver email account. Is empty for non-USD payments. If this amount is negative, it signifies a refund or reversal, and either of those payment statuses can be for the full or partial amount of the original transaction fee.  NOTE: This is a deprecated field. Use mc_fee instead.
      payment_gross        decimal(10,2) default null, # Full USD amount of the customer's payment, before transaction fee is subtracted. Will be empty for non-USD payments. This is a legacy field replaced by mc_gross. If this amount is negative, it signifies a refund or reversal, and either of those payment statuses can be for the full or partial amount of the original transaction.
      mc_fee               decimal(10,2) default null, # Transaction fee associated with the payment. mc_gross minus mc_fee equals the amount deposited into the receiver_email account. Equivalent to payment_fee for USD payments. If this amount is negative, it signifies a refund or reversal, and either of those payment statuses can be for the full or partial amount of the original transaction fee.
      mc_gross             decimal(10,2) default null, #  Full amount of the customer's payment, before transaction fee is subtracted. Equivalent to payment_gross for USD payments. If this amount is negative, it signifies a refund or reversal, and either of those payment statuses can be for the full or partial amount of the original transaction.
      settle_amount        decimal(10,2) default null, # Amount that is deposited into the accounts primary balance after a currency conversion from automatic conversion (through your Payment Receiving Preferences) or manual conversion (through manually accepting a payment).
      settle_currency      varchar(3) default null, # Three character currency code.
      exchange_rate decimal(12,6) default null, # Exchange rate used if a currency conversion occurred.
      
      # Auctions - NOT SUPPORTED
      
      # Mass Payment - NOT SUPPORTED
      
      # Subscriptions Variables
      
      txn_type             varchar(20) default null, # subscr_signup, subscr_cancel, subscr_failed, subscr_payment, subscr_eot, subscr_modify
      subscr_date varchar(28) default null, # Start date or cancellation date depending
      subscr_effective     varchar(28) default null, # Date when the subscription modification will be effective
      
      period1              varchar(20) default null, # Trial subscription interval in days, weeks, months, years (example: a 4 day interval is period1: 4 D).
      period2              varchar(20) default null, # Trial subscription interval in days, weeks, months, or years.
      period3              varchar(20) default null, # Regular subscription interval in days, weeks, months, or years.
      amount1              decimal(10,2) default null, # Amount of payment for trial period 1 for USD payments; otherwise blank (optional).
      amount2              decimal(10,2) default null, # A mount of payment for trial period 2 for USD payments; otherwise blank (optional).
      amount3              decimal(10,2) default null, # Amount of payment for regular subscription period for USD payments; otherwise blank.
      mc_amount1           decimal(10,2) default null, # Amount of payment for trial period 1, regardless of currency (optional).
      mc_amount2           decimal(10,2) default null, # Amount of payment for trial period 2, regardless of currency (optional).
      mc_amount3           decimal(10,2) default null, # Amount of payment for regular subscription period, regardless of currency.
      recurring            int(1) default null, # Indicates whether regular rate recurs (1 is yes, blank is no).
      reattempt            int(1) default null, # Indicates whether reattempts should occur upon payment failures (1 is yes, blank is no).
      retry_at             varchar(28) default null, # Date PayPal will retry a failed subscription payment.
      recur_times          int default null, # The number of payment installments that will occur at the regular rate.
      subscr_id            varchar(20) default null, # ID generated by PayPal for the subscriber.

      KEY txn_id (txn_id),
      PRIMARY KEY(id)
    )  /*!40100 DEFAULT CHARACTER SET utf8 */;";

  $ret = '';
  switch ($GLOBALS['db_type']) {
    case 'mysql':
      $ret .= lm_paypal_create_table('lm_paypal_ipns', $mysql_create_ipns);
      break;
   
    case 'mysqli':  // Bad Lee... using mysql_insert_id in lm_paypal!
    case 'pgsql':
      watchdog('lm_paypal', t('%db_type not supported yet', array('%db_type' => $GLOBALS['db_type'])), WATCHDOG_ERROR);
      return;
      break;
  }
  if ($ret == '') {
    $msg = t('lm_paypal created tables');
  }
  else {
    $msg = t('lm_paypal FAILED to create tables:') . $ret;
  }
  drupal_set_message($msg);
}

function lm_paypal_create_table($table, $sql) {
  $res = db_query($sql);
  if (!$res) {
    $ret = t('Failed to create table:') . $table . t('. ');
    watchdog('lm_paypal', $ret, WATCHDOG_ERROR);
    return $ret;
  }
  return '';
}

function lm_paypal_update_1() {
  $items = array();
  $items[] = update_sql("ALTER TABLE {lm_paypal_ipns} CHANGE verify_sign verify_sign varchar(120) default null");
  $items[] = update_sql("ALTER TABLE {lm_paypal_ipns} CHANGE payer_status payer_status varchar(40) default null");
  return $items;
}


/*
 * Example from: http://drupal.org/node/51220
function example_update_1() {
  $items = array();
  $items[] = update_sql("ALTER TABLE {example} ADD new_column text");
  $items[] = update_sql("ALTER TABLE {example} DROP old_column");
  return $items;
}
*/

?>