<?php
// $Id: ad.install,v 1.2.2.4.2.2 2007/04/04 18:08:51 jeremy Exp $

/**
 * Ad module database schema.
 * Copyright (c) 2005-2006 Jeremy Andrews <jeremy@kerneltrap.org>.  
 * All rights reserved.
 */

function ad_install() {
  switch ($GLOBALS['db_type']) {
    case 'mysql':
    case 'mysqli':
    default:

      /* The ad table stores administrative information about each ad.  The
       * actual ad itself can be found in the appropriate ad type table.
       */
      db_query("CREATE TABLE {ads} (
        aid INT(10) UNSIGNED NOT NULL UNIQUE DEFAULT '0',
        uid INT(10) UNSIGNED NOT NULL DEFAULT '0',
        gid INT(10) UNSIGNED NOT NULL DEFAULT '0',
  
        adstatus VARCHAR(255) NOT NULL DEFAULT '',
        adtype VARCHAR(255) NOT NULL DEFAULT '',

        redirect VARCHAR(255) NOT NULL DEFAULT '',

        autoactivate INT UNSIGNED NOT NULL DEFAULT '0',
        autoactivated INT UNSIGNED NOT NULL DEFAULT '0',
        autoexpire INT UNSIGNED NOT NULL DEFAULT '0',
        autoexpired INT UNSIGNED NOT NULL DEFAULT '0',

        activated INT UNSIGNED NOT NULL DEFAULT '0',
        maxviews INT UNSIGNED NOT NULL DEFAULT '0',
        maxclicks INT UNSIGNED NOT NULL DEFAULT '0',
        expired INT UNSIGNED NOT NULL DEFAULT '0',

        PRIMARY KEY  (aid),
        KEY (uid),
        KEY (gid),
        KEY (autoactivate),
        KEY (autoexpire)) DEFAULT CHARSET UTF8 COLLATE utf8_general_ci");

      /**
       * All ads are assigned to at least one group.  The ad_groups table
       * defines these groups.  It is possible for a group to be comprised of
       * multiple ad_types.  Ads are generally displayed by group, so for
       * example you may call a group 'header', and then use it to randomly
       * display ads in your website's header area.
       */
      db_query("CREATE TABLE {ad_groups} (
        gid INT UNSIGNED NOT NULL AUTO_INCREMENT,
        name VARCHAR(255) NOT NULL DEFAULT '',
        description TEXT NOT NULL DEFAULT '',

        PRIMARY KEY  (gid),
        UNIQUE KEY  (name)) DEFAULT CHARSET UTF8 COLLATE utf8_general_ci");

      /**
       * All ads must be assigned to a group.  To get started, we create the
       * default group to which all new ads will be assigned.  The default 
       * group can not be deleted or modified.  If a non-default group is 
       * deleted, ads in that group are assigned to the default group.
       */
      db_query("INSERT INTO {ad_groups} (gid,name, description) VALUES(1, 'default', 'All new advertisements will be added to this default group until you create your own custom advertisement groups.')");

      /**
       * Every ad can have one or more owners.
       */
      db_query("CREATE TABLE {ad_owners} (
        oid INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
        aid INT(10) UNSIGNED NOT NULL DEFAULT '0',
        uid INT(10) UNSIGNED NOT NULL DEFAULT '0',

        PRIMARY KEY  (oid),
        KEY  (aid),
        KEY  (uid)) DEFAULT CHARSET UTF8 COLLATE utf8_general_ci");

      /**
       * Permissions can be granted to each owner of each ad.  The same owner
       * can own multiple ads, and can have different permissions for each ad.
       */
      db_query("CREATE TABLE {ad_permissions} (
        oid INT(11) UNSIGNED NOT NULL DEFAULT '0',
        permissions LONGTEXT NULL DEFAULT '',
        PRIMARY KEY  (oid)) DEFAULT CHARSET UTF8 COLLATE utf8_general_ci");

      /**
       * This table counts each time a given action occurs on an ad.  Actions
       * include when the ad is viewed, clicked, enabled and disabled.
       * Statistics are collected at an hourly granularity.
       *
       * The source column is used for tracking statistics for externally 
       * hosted ads.
       *
       * Actions:
       *  'view', 'click', 'enable', 'disable'
       */
      db_query("CREATE TABLE {ad_statistics} (
        sid INT UNSIGNED NOT NULL AUTO_INCREMENT,
        aid INT UNSIGNED NOT NULL DEFAULT '0',

        date INT(10) UNSIGNED NOT NULL DEFAULT '0',
        action VARCHAR(255) NOT NULL DEFAULT '',
        hostid VARCHAR(32) NULL DEFAULT '',
        count INT(11) UNSIGNED NOT NULL DEFAULT '0',

        PRIMARY KEY  (sid),
        KEY (aid),
        KEY (date),
        KEY (action),
        KEY (hostid)) DEFAULT CHARSET UTF8 COLLATE utf8_general_ci");

      /**
       * The ad_clicks table tracks when a given advertisement was clicked, 
       * who clicked it (uid if any and IP address), and what page they were
       * on when they clicked it.
       */
      db_query("CREATE TABLE {ad_clicks} (
        cid INT UNSIGNED NOT NULL AUTO_INCREMENT,
        aid INT UNSIGNED NOT NULL DEFAULT '0',
        uid int(10) UNSIGNED NOT NULL DEFAULT '0',

        hostname varchar(128) NOT NULL DEFAULT '',
        hostid varchar(32) NOT NULL DEFAULT '',
        url varchar(255) DEFAULT '',
        timestamp INT(11) UNSIGNED NOT NULL DEFAULT '0',

        PRIMARY KEY  (cid),
        KEY  (aid),
        KEY  (hostname),
        KEY  (hostid),
        KEY  (url)) DEFAULT CHARSET UTF8 COLLATE utf8_general_ci");

      /**
       * The ad_hosts table is used to configure users that can display ads
       * remotely. 
       */
      db_query("CREATE TABLE {ad_hosts} (
        uid INT UNSIGNED NOT NULL DEFAULT '0',

        hostid varchar(32) DEFAULT '',
        description TEXT NOT NULL DEFAULT '',

        PRIMARY KEY  (uid),
        KEY  (hostid)) DEFAULT CHARSET UTF8 COLLATE utf8_general_ci");
  }

  drupal_set_message(t('The necessary ad module tables have been created.'));
}

/**
 * Introduce fields for specifying an optional maximum number of view or clicks
 * for each ad.  Also introduce fields for tracking the last time the ad was
 * activated or expired.
 */
function ad_update_1() {
  $ret = array();
  switch ($GLOBALS['db_type']) {
    case 'mysql':
    case 'mysqli':
    default:
      $ret[] = update_sql("ALTER TABLE {ads} ADD activated INT UNSIGNED NOT NULL DEFAULT '0' AFTER autoexpired");
      $ret[] = update_sql("ALTER TABLE {ads} ADD maxviews INT UNSIGNED NOT NULL DEFAULT '0' AFTER activated");
      $ret[] = update_sql("ALTER TABLE {ads} ADD maxclicks INT UNSIGNED NOT NULL DEFAULT '0' AFTER maxviews");
      $ret[] = update_sql("ALTER TABLE {ads} ADD expired INT UNSIGNED NOT NULL DEFAULT '0' AFTER maxclicks");
  }
  return $ret;
}

/**
 * Ad notifications are being moved to an external module, so remove all
 * references from the core module.
 */
function ad_update_2() {
  $ret = array();
  switch ($GLOBALS['db_type']) {
    default:
      $ret[] = update_sql("DROP TABLE {ad_notifications}");
      $ret[] = update_sql("ALTER TABLE {ads} DROP expire_notified");
      $ret[] = update_sql("ALTER TABLE {ads} DROP renew_notified");
  }
  return $ret;
}

/**
 * Patch by nomad.
 * Change collation and character set of tables to provide compatibility with drupal (and Russian language =))
 */
function ad_update_3() {
  $ret = array();
  switch ($GLOBALS['db_type']) {
    default:
      $ret[] = update_sql("ALTER TABLE {ads} CONVERT TO CHARACTER SET utf8");
      $ret[] = update_sql("ALTER TABLE {ad_clicks} CONVERT TO CHARACTER SET utf8");
      $ret[] = update_sql("ALTER TABLE {ad_groups} CONVERT TO CHARACTER SET utf8");
      $ret[] = update_sql("ALTER TABLE {ad_hosts} CONVERT TO CHARACTER SET utf8");
      $ret[] = update_sql("ALTER TABLE {ad_image} CONVERT TO CHARACTER SET utf8");
      $ret[] = update_sql("ALTER TABLE {ad_image_format} CONVERT TO CHARACTER SET utf8");
      $ret[] = update_sql("ALTER TABLE {ad_owners} CONVERT TO CHARACTER SET utf8");
      $ret[] = update_sql("ALTER TABLE {ad_permissions} CONVERT TO CHARACTER SET utf8");
      $ret[] = update_sql("ALTER TABLE {ad_statistics} CONVERT TO CHARACTER SET utf8");
      $ret[] = update_sql("ALTER TABLE {ad_text} CONVERT TO CHARACTER SET utf8");
  }
  return $ret;
}