popufare/server/sql_schema/create_tables.sql

--
-- Copyright (c) 2019 Clementine Computing LLC.
-- 
-- This file is part of PopuFare.
-- 
-- PopuFare is free software: you can redistribute it and/or modify
-- it under the terms of the GNU Affero General Public License as published by
-- the Free Software Foundation, either version 3 of the License, or
-- (at your option) any later version.
-- 
-- PopuFare is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
-- GNU Affero General Public License for more details.
-- 
-- You should have received a copy of the GNU Affero General Public License
-- along with PopuFare.  If not, see <https://www.gnu.org/licenses/>.
--

-- AVLS data table receives all AVLS chirps and stores them for later reference
CREATE TABLE IF NOT EXISTS avls_data (
  id bigint auto_increment primary key,
  equip_num INT,
  driver INT,
  paddle INT,
  route  INT,
  trip  INT,
  stop  INT,
  chirp_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  latitude DOUBLE,
  longitude DOUBLE,
  heading DOUBLE,
  velocity DOUBLE,

  INDEX chirp_idx (chirp_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Bus Pass Table holds all bus pass holders
-- DROP TABLE IF EXISTS `active_rider_table`;
CREATE TABLE IF NOT EXISTS active_rider_table (

  -- The following entries are stored on the client as well and kept in sync
  logical_card_id BIGINT NOT NULL,
  seq_num BIGINT UNIQUE PRIMARY KEY AUTO_INCREMENT,
  rfid_token VARCHAR(32),
  mag_token VARCHAR(32),
  rule_name VARCHAR(24),
  rule_param VARCHAR(24),
  
  -- The rest are for server-side recordkeeping...
  
  deleted BOOLEAN DEFAULT '0',
  parent_entity VARCHAR(32),
  notes  VARCHAR(64),
  
  -- And some additional index data
  
  INDEX id_idx (logical_card_id),
  INDEX id_and_seq (logical_card_id, seq_num)
  
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
-- Billing Log Table

-- DROP TABLE IF EXISTS `billing_log`;
CREATE TABLE IF NOT EXISTS billing_log (
  id bigint auto_increment primary key,

  -- MD5 sum of the orignal record as sent by the client, used to confirm storing the record
  conf_checksum VARCHAR(32) UNIQUE,

  -- Contents of the billing log entry coming from the client
  equip_num     INT,
  driver        INT,
  paddle        INT,
  route         INT,
  trip          INT,
  stop          INT,
  ride_time TIMESTAMP,
  latitude      DOUBLE,
  longitude     DOUBLE,
  action VARCHAR(16),
  rule  VARCHAR(24),
  ruleparam VARCHAR(24),
  reason VARCHAR(64),
  credential VARCHAR(32),
  logical_card_id BIGINT NOT NULL,
  cash_value INT,
  stop_name VARCHAR(64)
  
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS diagnostic_log (
  id bigINT auto_increment PRIMARY KEY,
  servertime TIMESTAMP default CURRENT_TIMESTAMP,
  loglvl VARCHAR(8),
  message VARCHAR(256),

  -- And some additional index data
  
  INDEX servertime_idx (servertime),
  UNIQUE INDEX lvl_msg_idx (servertime, loglvl)
  
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

drop table if exists pass_option;
create table pass_option (
  id bigint auto_increment primary key,
  group_id    int,
  param       int,
  name        varchar(255),
  rule        varchar(255),
  db_rule     varchar(255),
  type        varchar(255),
  description varchar(255),
  num_opt     int   default 0,
  option0     varchar(255),
  option1     varchar(255),
  option2     varchar(255),
  option3     varchar(255),
  
  active      tinyint default 0
  
  -- primary key (id),
  -- unique key id (id)
) engine=InnoDB ;


CREATE TABLE IF NOT EXISTS rule_class (
  id bigint auto_increment primary key,
  rulename VARCHAR(24),
  ruleclass VARCHAR(24)
  
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS update_level (
  equip_num INT NOT NULL DEFAULT 0,
  client_file VARCHAR(32) NOT NULL,
  checksum VARCHAR(32) NOT NULL,
  file_size INT NOT NULL,
  file_path VARCHAR(512) NOT NULL,
  fileversion VARCHAR(32),
  
  -- The following field is never manually set and is used only to pull the latest value
  -- serial BIGINT NOT NULL AUTO_INCREMENT,
  serial BIGINT AUTO_INCREMENT primary key,

  -- These indicies should make the operation of "give me the latest of each update for bus number X" fast  
  
  INDEX equip_num_idx (equip_num),
  INDEX client_file_idx (client_file),
  INDEX serial_idx (serial),
  INDEX file_eqip_idx (client_file, equip_num)
  
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS bus_checkin_log (
  id bigint auto_increment primary key,
   checkin_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   busunit_num INT NOT NULL DEFAULT 0,
   equip_num INT NOT NULL DEFAULT 0,
   eth0_mac VARCHAR(17),
   cell_imei VARCHAR(15),
   cell_imsi VARCHAR(15),
   version_data VARCHAR(256),

   INDEX time_index(checkin_time),
   INDEX equip_num_idx (equip_num),
   INDEX busunit_num_idx (busunit_num)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `user_pass`;
CREATE TABLE `user_pass` (
  user_pass_id BIGINT UNIQUE NOT NULL auto_increment,
  logical_card_id BIGINT default NULL,

  issued datetime default NULL,
  activated datetime default NULL,
  deactivated datetime default NULL,

  firstused datetime default NULL,
  lastused datetime default NULL,

  nrides_orig int(11) default NULL,
  nrides_remain int(11) default NULL,

  nday_orig int(11) default NULL,
  nday_expiration datetime default NULL,

  active tinyint(1) default 0,
  expired tinyint(1) default 0,

  rule varchar(255) default NULL,
  queue_order int(11) default NULL,

  comment varchar(255) default NULL,
  paytype varchar(255) default NULL,

  PRIMARY KEY  (user_pass_id),
  UNIQUE KEY user_pass_idx (user_pass_id),
  KEY user_pass_idx_logical_card_id (logical_card_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS `user_card`;
CREATE TABLE `user_card` (
  logical_card_id BIGINT UNIQUE NOT NULL auto_increment,

  -- legacy, will remove in final phase2
  -- card_number varchar(128) UNIQUE default NULL,
  -- rfsite int(127) default NULL,
  -- rfid int(127) default NULL,
  --

  mag_token varchar(255) default null,
  rfid_token varchar(255) default null,

  comment varchar(255) default NULL,
  userid int(11) default NULL,

  issued datetime default NULL,
  active tinyint(1) default 1,
  deactivated datetime default NULL,

  lastused datetime default NULL,
  firstused datetime default NULL,

  group_id int(11) default NULL,
  issuetype varchar(255) default NULL,


  PRIMARY KEY  (logical_card_id),
  UNIQUE KEY user_card_idx (logical_card_id),
  KEY user_card_idx_mag_token (mag_token),
  KEY user_card_idx_rfid_token (rfid_token),
  KEY user_card_idx_mag_rfid_token (mag_token,rfid_token),
  KEY user_card_idx_userid (userid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS `groups`;
CREATE TABLE `groups` (
  id bigint auto_increment primary key,
  group_id int(11) default NULL,
  group_name varchar(255) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS `audit_user_pass`;
CREATE TABLE `audit_user_pass` (
  `audit_user_pass_id` int(11) NOT NULL auto_increment,
  `old_user_pass_id` int(11) default NULL,
  `old_logical_card_id` int(11) default NULL,
  `old_issued` datetime default NULL,
  `old_firstused` datetime default NULL,
  `old_lastused` datetime default NULL,
  `old_nrides_orig` int(11) default NULL,
  `old_nrides_remain` int(11) default NULL,
  `old_nday_orig` int(11) default NULL,
  `old_nday_expiration` datetime default NULL,
  `old_active` int(11) default NULL,
  `old_rule` char(255) default NULL,
  `old_queue_order` int(11) default NULL,
  `old_log_id` int(11) default NULL,
  `old_comment` varchar(255) default NULL,
  `new_user_pass_id` int(11) default NULL,
  `new_logical_card_id` int(11) default NULL,
  `new_issued` datetime default NULL,
  `new_firstused` datetime default NULL,
  `new_lastused` datetime default NULL,
  `new_nrides_orig` int(11) default NULL,
  `new_nrides_remain` int(11) default NULL,
  `new_nday_orig` int(11) default NULL,
  `new_nday_expiration` datetime default NULL,
  `new_active` int(11) default NULL,
  `new_rule` char(255) default NULL,
  `new_queue_order` int(11) default NULL,
  `new_log_id` int(11) default NULL,
  `new_comment` varchar(255) default NULL,
  `comment` varchar(255) default NULL,
  `owner_id` int(11) default NULL,
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `old_expired` tinyint(10) default NULL,
  `new_expired` tinyint(1) default NULL,
  `old_paytype` varchar(255) default NULL,
  `new_paytype` varchar(255) default NULL,
  PRIMARY KEY  (`audit_user_pass_id`),
  INDEX audit_user_pass_id_idx (audit_user_pass_id),
  INDEX audit_user_pass_timestamp_idx (timestamp),
  INDEX audit_user_pass_new_user_pass_id_idx (new_user_pass_id),
  INDEX audit_user_pass_old_user_pass_id_idx (old_user_pass_id)


) ENGINE=InnoDB DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS `audit_user_card`;
CREATE TABLE `audit_user_card` (
  `audit_user_card_id` int(11) NOT NULL auto_increment,
  `old_logical_card_id` int(11) default NULL,
--  `old_card_number` char(128) default NULL,
--  `old_rfsite` int(11) default NULL,
--  `old_rfid` int(11) default NULL,
  `old_comment` varchar(255) default NULL,
  `old_lastused` datetime default NULL,
  `old_userid` int(11) default NULL,
  `old_issued` datetime default NULL,
  `old_firstused` datetime default NULL,
  `old_group_id` int(11) default NULL,
  `old_issuetype` varchar(255) default NULL,
  `new_logical_card_id` int(11) default NULL,
--  `new_card_number` char(128) default NULL,
--  `new_rfsite` int(11) default NULL,
--  `new_rfid` int(11) default NULL,
  `new_comment` varchar(255) default NULL,
  `new_lastused` datetime default NULL,
  `new_userid` int(11) default NULL,
  `new_issued` datetime default NULL,
  `new_firstused` datetime default NULL,
  `new_group_id` int(11) default NULL,
  `new_issuetype` varchar(255) default NULL,
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `comment` varchar(255) default NULL,
  `old_mag_token` varchar(31) default NULL,
  `new_mag_token` varchar(31) default NULL,
  `old_rfid_token` varchar(31) default NULL,
  `new_rfid_token` varchar(31) default NULL,
  `old_active` tinyint(1) default NULL,
  `new_active` tinyint(1) default NULL,
  `old_deactivated` datetime default null,
  `new_deactivated` datetime default null,
  PRIMARY KEY  (`audit_user_card_id`),
  INDEX audit_user_card_id_idx (audit_user_card_id),
  INDEX audit_user_card_timestampe_idx (timestamp),

  INDEX audit_user_card_new_logical_card_id_idx (new_logical_card_id),
  INDEX audit_user_card_old_logical_card_id_idx (old_logical_card_id),

  INDEX audit_user_card_new_mag_token_idx (new_mag_token),
  INDEX audit_user_card_old_mag_token_idx (old_mag_token),

  INDEX audit_user_card_new_rfid_token_idx (new_rfid_token),
  INDEX audit_user_card_old_rfid_token_idx (old_rfid_token)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
          `username` char(255) default NULL,
          `userid` int(127) NOT NULL auto_increment,
          `comment` char(255) default NULL,
          `first_name` char(128) default NULL,
          `last_name` char(128) default NULL,
          `phone` char(31) default NULL,
          `email` char(128) default NULL,
          `address` char(255) default NULL,
          `city` char(127) default NULL,
          `state` char(127) default NULL,
          `zip` char(31) default NULL,
          `created` datetime default NULL,
          `active` tinyint(1) default NULL,
          `passwordhash` varchar(255) default NULL,
          `shipping_address` varchar(255) default NULL,
          `shipping_city` varchar(255) default NULL,
          `shipping_state` varchar(255) default NULL,
          `shipping_zip` varchar(255) default NULL,
          `shipping_name` varchar(255) default NULL,
          `shipping_country_code` varchar(32) default NULL,
          `shipping_country_name` varchar(255) default NULL,
          `reset_attempts` int(11) default '0',
          PRIMARY KEY  (`userid`)
) ENGINE=InnoDB AUTO_INCREMENT=325 DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS `audit_users`;
CREATE TABLE `audit_users` (
          `audit_users_id` int(11) NOT NULL auto_increment,
          `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
          `comment` varchar(255) default NULL,
          `old_username` char(255) default NULL,
          `old_userid` int(127) default NULL,
          `old_comment` char(255) default NULL,
          `old_first_name` char(128) default NULL,
          `old_last_name` char(128) default NULL,
          `old_phone` char(31) default NULL,
          `old_email` char(128) default NULL,
          `old_address` char(255) default NULL,
          `old_city` char(127) default NULL,
          `old_state` char(127) default NULL,
          `old_zip` char(31) default NULL,
          `old_created` datetime default NULL,
          `old_active` tinyint(1) default NULL,
          `old_passwordhash` varchar(255) default NULL,
          `new_username` char(255) default NULL,
          `new_userid` int(127) default NULL,
          `new_comment` char(255) default NULL,
          `new_first_name` char(128) default NULL,
          `new_last_name` char(128) default NULL,
          `new_phone` char(31) default NULL,
          `new_email` char(128) default NULL,
          `new_address` char(255) default NULL,
          `new_city` char(127) default NULL,
          `new_state` char(127) default NULL,
          `new_zip` char(31) default NULL,
          `new_created` datetime default NULL,
          `new_active` tinyint(1) default NULL,
          `new_passwordhash` varchar(255) default NULL,
          `old_shipping_address` varchar(255) default NULL,
          `old_shipping_city` varchar(255) default NULL,
          `old_shipping_state` varchar(255) default NULL,
          `old_shipping_zip` varchar(255) default NULL,
          `old_shipping_name` varchar(255) default NULL,
          `old_shipping_country_code` varchar(255) default NULL,
          `old_shipping_country_name` varchar(32) default NULL,
          `old_reset_attempts` varchar(255) default NULL,
          `new_shipping_address` varchar(255) default NULL,
          `new_shipping_city` varchar(255) default NULL,
          `new_shipping_state` varchar(255) default NULL,
          `new_shipping_zip` varchar(255) default NULL,
          `new_shipping_name` varchar(255) default NULL,
          `new_shipping_country_code` varchar(255) default NULL,
          `new_shipping_country_name` varchar(32) default NULL,
          `new_reset_attempts` varchar(255) default NULL,
          PRIMARY KEY  (`audit_users_id`)
) ENGINE=InnoDB AUTO_INCREMENT=373 DEFAULT CHARSET=latin1;


-- Admin Web UI tables

DROP TABLE IF EXISTS `admins`;
CREATE TABLE `admins` (
          `userid` int(127) not NULL  auto_increment,
          `group_id` int default NULL,
          `username` char(255) default NULL,
          `password` char(255) default NULL,
          `active` tinyint default 1,
          `comment` varchar(255) default null,
          PRIMARY KEY (`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `audit_admins`;
CREATE TABLE `audit_admins` (
          `audit_admins_id` int(11) NOT NULL auto_increment,
          `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
          `comment` varchar(255) default NULL,
          `old_userid` int default NULL,
          `old_username` varchar(255) default NULL,
          `old_password` varchar(255) default NULL,
          `new_userid` int default NULL,
          `new_username` varchar(255) default NULL,
          `new_password` varchar(255) default NULL,
          PRIMARY KEY  (`audit_admins_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS `admins_session_info`;
CREATE TABLE `admins_session_info` (
  `id` int not null auto_increment,
          `userid` int(255) default NULL,
          `sessionid` char(255) default NULL,
          `lastactive` datetime default NULL,
          PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `admin_groups`;
CREATE TABLE `admin_groups` (
  `id` int not null auto_increment,
          `userid` int(127) default NULL,
          `group_id` int(11) default NULL,
          `permissions` int(127) default NULL,
          PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `rule_mappings`;
CREATE TABLE `rule_mappings` (
  `id` int not null auto_increment,
          `rule` char(255) NOT NULL default '',
          `rule_text` char(255) default NULL,
          `group_id` int(11) default NULL,
          `active` int,
          PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `org_default_pass_value`;
CREATE TABLE `org_default_pass_value` (
          `id` int(11) NOT NULL auto_increment,
          `name` varchar(255) default NULL,
          `nday` int(11) default NULL,
          `nride` int(11) default NULL,
          `description` varchar(255) default NULL,
          `start` datetime default NULL,
          `end` datetime default NULL,
          PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `org_default_card_value`;
CREATE TABLE `org_default_card_value` (
          `id` int(11) NOT NULL auto_increment,
          `group_id` int(11) default NULL,
          `mag_track` int(11) default NULL,
          `rf_length` int(11) default NULL,
          `rf_site` int(11) default NULL,
          PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


-- WEB API tables

DROP TABLE IF EXISTS `authorization_log`;
CREATE TABLE `authorization_log` (
          `authorization_log_id` int(11) NOT NULL auto_increment,
          `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
          `authorization_type` varchar(255) default NULL,
          `authorization_code` varchar(255) default NULL,
          `user_id` int(11) default NULL,
          `logical_card_id` int(11) default NULL,
          `user_pass_id` int(11) default NULL,
          `comment` varchar(255) default NULL,
          PRIMARY KEY  (`authorization_log_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1981 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `org_card_order_queue`;
CREATE TABLE `org_card_order_queue` (
          `org_card_order_queue_id` int(11) NOT NULL auto_increment,
          `userid` int(11) default NULL,
          `logical_card_id` int(11) default NULL,
          `created` timestamp NOT NULL default CURRENT_TIMESTAMP,
          `processed` timestamp NULL default NULL,
          `comment` varchar(255) default NULL,
          `pending` tinyint(1) default NULL,
          PRIMARY KEY  (`org_card_order_queue_id`)
) ENGINE=InnoDB AUTO_INCREMENT=54 DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS `org_api_session`;
CREATE TABLE `org_api_session` (
  id bigint auto_increment primary key,
          `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
          `ip` varchar(15) default NULL,
          `active` tinyint(4) default NULL,
          `server_token` varchar(255) default NULL,
          `user_token` varchar(255) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `org_api_log`;
CREATE TABLE `org_api_log` (
          `log_id` int(11) NOT NULL auto_increment,
          `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
          `log` blob,
          PRIMARY KEY  (`log_id`)
) ENGINE=InnoDB AUTO_INCREMENT=500 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `org_api_password_reset`;
CREATE TABLE `org_api_password_reset` (
  id bigint auto_increment primary key,
          `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
          `token` varchar(255) default NULL,
          `userid` int(11) default NULL,
          `email` varchar(255) default NULL,
          `active` tinyint(1) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `org_api_register_email`;
CREATE TABLE `org_api_register_email` (
  id bigint auto_increment primary key,
          `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
          `token` varchar(255) default NULL,
          `active` tinyint(1) default NULL,
          `email` varchar(255) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



-- drivers, stops and paddles + misc

DROP TABLE IF EXISTS `drivers`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `drivers` (
  `id` int(11) NOT NULL default '0',
  `pin` varchar(8) NOT NULL default '',
  `name` varchar(32) default NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;

--
-- Table structure for table `stops`
--

DROP TABLE IF EXISTS `stops`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `stops` (
  `id` int(11) NOT NULL default '0',
  `latitude` double NOT NULL default '0',
  `longitude` double NOT NULL default '0',
  `name` varchar(32) default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;

--
-- Table structure for table `paddles`
--

DROP TABLE IF EXISTS `paddles`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `paddles` (
  -- `id` int(11) NOT NULL default '0',
  `id` bigint auto_increment primary key,
  `slot` int(11) NOT NULL default '0',
  `arrival` time default NULL,
  `route` int(11) default NULL,
  `trip` int(11) default NULL,
  `stage` int(11) default NULL,
  `stop` int(11) default NULL,
  `stopid` int(11) NOT NULL default '0',
  KEY `ididx` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;

--
-- Table structure for table `old_stops`
--

DROP TABLE IF EXISTS `old_stops`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `old_stops` (
  `verstring` text,
  -- `id` int(11) NOT NULL default '0',
  `id` bigint auto_increment primary key,
  `latitude` double NOT NULL default '0',
  `longitude` double NOT NULL default '0',
  `name` varchar(32) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;

--
-- Table structure for table `old_paddles`
--

DROP TABLE IF EXISTS `old_paddles`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `old_paddles` (
  `verstring` text,
  -- `id` int(11) NOT NULL default '0',
  `id` bigint auto_increment primary key,
  `slot` int(11) NOT NULL default '0',
  `arrival` time default NULL,
  `route` int(11) default NULL,
  `trip` int(11) default NULL,
  `stage` int(11) default NULL,
  `stop` int(11) default NULL,
  `stopid` int(11) NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;

--
-- Table structure for table `live_stops`
--

DROP TABLE IF EXISTS `live_stops`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `live_stops` (
  `id` int(11) NOT NULL default '0',
  `latitude` double NOT NULL default '0',
  `longitude` double NOT NULL default '0',
  `name` varchar(32) default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;

--
-- Table structure for table `live_paddles`
--

DROP TABLE IF EXISTS `live_paddles`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `live_paddles` (
  -- `id` int(11) NOT NULL default '0',
  `id` bigint auto_increment primary key,
  `slot` int(11) NOT NULL default '0',
  `arrival` time default NULL,
  `route` int(11) default NULL,
  `trip` int(11) default NULL,
  `stage` int(11) default NULL,
  `stop` int(11) default NULL,
  `stopid` int(11) NOT NULL default '0',
  KEY `ididx` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;


DROP TABLE IF EXISTS `price_point`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `price_point` (
  -- `id` int(11) NOT NULL auto_increment,
  `id` bigint auto_increment primary key,
  `price` double default NULL,
  `param` int(11) default NULL,
  `name` varchar(255) default NULL,
  `rule` varchar(32) default NULL,
  `db_rule` varchar(32) default NULL,
  `group_id` int(11) default NULL,
  `type` varchar(255) default NULL,
  `description` varchar(1024) default NULL,
  `num_opt` int default 0,
  `price_option0` varchar(255) default null,
  `price_option1` varchar(255) default null,
  `price_option2` varchar(255) default null,
  `price_option3` varchar(255) default null,
  `active` tinyint(1) default 0,
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;

drop table if exists billing_log_annotation;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
create table if not exists billing_log_annotation (
  id bigint unique not null,
  seq_num bigint not null,
  rule varchar(24),
  ruleparam varchar(24),
  reason varchar(64),
  credential varchar(32),
  note varchar(512),
  timestamp timestamp default current_timestamp,
  primary key (id),
  key billing_log_annotation_seq_num_key (seq_num)
) ENGINE=InnoDB default CHARSET=utf8;
SET character_set_client = @saved_cs_client;