<?php
require_once dirname(__FILE__).'/accesscheck.php';

define("STRUCTUREVERSION","2.10.19");

$DBstruct = array( # order of tables is essential for smooth upgrade
    "attribute" => array ( # attributes of a user or a message
        "id" => array("integer not null primary key auto_increment","ID"),
        "name" => array("varchar(255) not null","Name"),
        "type" => array("varchar(30)","Type of attribute"),
        "listorder" => array("integer","order of listing"),
        "default_value" => array("varchar(255)","Default value"),
        "required" => array("tinyint","Required for user to fill out"),
        "tablename" => array("varchar(255)","Name of table with values"),
        "index_1" => array("nameindex (name)",""),
        "index_2" => array("idnameindex (id,name)","")
    ),
    "user_attribute" => array(
        "attributeid" => array("integer not null","attribute"),
        "userid" => array("integer not null","user"),
        "value" => array("text","Value of this attribute for this user"),
        "primary key" => array("(attributeid,userid)","PKey"),
        "index_1" => array("userindex (userid)",""),
        "index_2" => array("attindex (attributeid)",""),
        "index_3" => array("userattid (attributeid,userid)",""),
        "index_4" => array("attuserid (userid,attributeid)",""),
    ),
    "user" => array ( # a user in the system
        "id" => array("integer not null primary key auto_increment","sysexp:ID"),
        "email" => array("varchar(255) not null","Email"),
        "confirmed" => array("tinyint default 0","sys:Is this user confirmed"),
        "blacklisted" => array("tinyint default 0","sys:Is this user blacklisted"),
        "bouncecount" => array("integer default 0","sys:Number of bounces on this user"),
        "entered" => array("datetime", "sysexp:Entered"),
        "modified" => array("timestamp", "sysexp:Last Modified"),
        "uniqid" => array("varchar(255)","sys:Unique ID for User"),
        "unique_1" => array("(email)","sys:unique"),
        "htmlemail" => array("tinyint default 0","Send this user HTML emails"),
        "subscribepage" => array("integer","sysexp:Which page was used to subscribe"),
        "rssfrequency" => array("varchar(100)","RSS Frequency"),
        "password" => array("varchar(255)","Password"),
        "passwordchanged" => array("date","sys:Last time password was changed"),
        "disabled" => array("tinyint default 0","Is this account disabled?"),
        "extradata" => array("text","Additional data"),
        "foreignkey" => array("varchar(100)","Foreign Key"),
        "index_1" => array("(foreignkey)","sys:Foreign Key"),
        "index_2" => array("idx_phplist_user_user_uniqid (uniqid)","sys:index"),
        "index_3" => array("emailidx (email)","sys:index"),
        "index_4" => array("enteredindex (entered)","sys:index"),
    ),
    "user_history" => array(
        "id" => array("integer not null primary key auto_increment","sys:ID"),
        "userid" => array("integer not null",""),
        "ip" => array("varchar(255)",""),
        "date" => array("datetime",""),
        "summary" => array("varchar(255)","History Summary"),
        "detail" => array("text","History Detail"),
        "systeminfo" => array("text","System Information"),
        "index_1" => array("userididx (userid)","sys:index"),
        "index_2" => array("dateidx (date)","sys:index"),
    ),
    "user_blacklist" => array(
        "email" => array("varchar(100) not null unique","Email"),
        "added" => array("datetime","When added to blacklist"),
        "index_1" => array("emailidx (email)",""),
    ),
    "user_blacklist_data" => array(
        "email" => array("varchar(100) not null unique","Email"),
        "name" => array("varchar(50) not null","Name of Dataitem"),
        "data" => array("text",""),
        "index_1" => array("emailidx (email)",""),
        "index_2" => array("emailnameidx (email,name)",""),
    ),
    "list" => array ( # a list in the system
        "id" => array("integer not null primary key auto_increment","ID"),
        "name" => array("varchar(255) not null","Name"),
        "description" => array("Text","Description"),
        "entered" => array("datetime","Entered"),
        "listorder" => array("integer","Order of listing"),
        "prefix" => array("varchar(10)","Subject prefix"),
        "rssfeed" => array("varchar(255)","Rss Feed"),
        "modified" => array("timestamp", "Modified"),
        "active" => array("tinyint","Active"),
        "owner" => array("integer","Admin who is owner of this list"),
        "index_1" => array("nameidx (name)",""),
        "index_2" => array("listorderidx (listorder)",""),
    ),
    "listrss" => array( # rss details for a RSS source of a list
        "listid" => array("integer not null","List ID"),
        "type" => array("varchar(255) not null","Type of this entry"),
        "entered" => array("datetime not null",""),
        "info" => array("text",""),
        "index_1" => array("listididx (listid)",""),
        "index_2" => array("enteredidx (entered)",""),
    ),
    "listuser" => array ( # user subscription to a list
        "userid" => array("integer not null","User ID"),
        "listid" => array("integer not null","List ID"),
        "entered" => array("datetime", "Entered"),
        "modified" => array("timestamp", "Modified"),
        "primary key" => array("(userid,listid)","Primary Key"),
        "index_1" => array("userenteredidx (userid,entered)",""),
        "index_2" => array("userlistenteredidx (userid,listid,entered)",""),
    ),
    "message" => array ( # a message
        "id" => array("integer not null primary key auto_increment","ID"),
        "subject" => array("varchar(255) not null default '(no subject)'","subject"),
        "fromfield" => array("varchar(255) not null default ''","from"),
        "tofield" => array("varchar(255) not null default ''","tofield"),
        "replyto" => array("varchar(255) not null default ''","reply-to"),
        "message" => array("longtext","Message"),
        "textmessage" => array("longtext","Text version of Message"),
        "footer" => array("text","Footer for a message"),
        "entered" => array("datetime","Entered"),
        "modified" => array("timestamp", "Modified"),
        "embargo" => array("datetime","Time to send message"),
        "repeatinterval" => array("integer default 0","Number of seconds to repeat the message"),
        "repeatuntil" => array("datetime","Final time to stop repetition"),
#        "status" => array("enum('submitted','inprocess','sent','cancelled','prepared','draft')","Status"),
        "status" => array("varchar(255)","Status"),
        "userselection" => array("text","query to select the users for this message"),
        "sent" => array("datetime", "sent"),
        "htmlformatted" => array("tinyint default 0","Is this message HTML formatted"),
        "sendformat" => array("varchar(20)","Format to send this message in"),
        "template" => array("integer","Template to use"),
        "processed" => array("mediumint unsigned default 0", "Number Processed"),
        "astext" => array("integer default 0","Sent as text"),
        "ashtml" => array("integer default 0","Sent as HTML"),
        "astextandhtml" => array("integer default 0","Sent as Text and HTML"), // obsolete
        "aspdf" => array("integer default 0","Sent as PDF"),
        "astextandpdf" => array("integer default 0","Sent as Text and PDF"),
        "viewed" => array("integer default 0","Was the message viewed"),
        "bouncecount" => array("integer default 0","How many bounces on this message"),
        "sendstart" => array("datetime","When did sending of this message start"),
        "rsstemplate" => array("varchar(100)","if used as a RSS template, what frequency"),
        "owner" => array("integer","Admin who is owner")
    ),
    "messagedata" => array(
        "name" => array("varchar(100) not null","Name of field"),
        "id" => array("integer not null", "Message ID"),
        "data" => array("text","Data"),
        "primary key" => array("(name,id)",""),
    ),
    "listmessage" => array ( # linking messages to a list
        "id" => array("integer not null primary key auto_increment","ID"),
        "messageid" => array("integer not null","Message ID"),
        "listid" => array("integer not null","List ID"),
        "entered" => array("datetime", "Entered"),
        "modified" => array("timestamp","Modified"),
        "unique_1" => array("(messageid,listid)",""),
        "index_1" => array("listmessageidx (listid,messageid)",""),
    ),
    "rssitem" => array(
        "id" => array("integer not null primary key auto_increment","ID"),
        "title" => array("varchar(100) not null","Title"),
        "link" => array("varchar(100) not null","Link"),
        "source" => array("varchar(255)",""),
        "list" => array("integer not null",""),
        "index_1" => array("titlelinkidx (title,link)",""),
        "index_2" => array("titleidx (title)",""),
        "index_3" => array("listidx (list)",""),
        "added" => array("datetime",""),
        "processed" => array("mediumint unsigned default 0", "Number Processed"),
        "astext" => array("integer default 0","Sent as text"),
        "ashtml" => array("integer default 0","Sent as HTML"),
    ),
    "rssitem_data" => array(
        "itemid" => array("integer not null","rss item id"),
        "tag" => array("varchar(100) not null",""),
        "primary key" => array("(itemid,tag)",""),
        "data" => array("text","")
    ),
    "rssitem_user" => array(
        "itemid" => array("integer not null","rss item id"),
        "userid" => array("integer not null","user id"),
        "entered" => array("timestamp", "Entered"),
        "primary key" => array("(itemid,userid)","")
    ),
    "user_rss" => array(
        "userid" => array("integer not null primary key","user id"),
        "last" => array("datetime", "Last time this user was sent something")
    ),
    "message_attachment" => array( # attachments for a message
        "id" => array("integer not null primary key auto_increment","ID"),
        "messageid" => array("integer not null","Message ID"),
        "attachmentid" => array("integer not null","Attachment ID"),
        "index_1" => array("messageidx (messageid)",""),
        "index_2" => array("messageattidx (messageid,attachmentid)",""),
    ),
    "attachment" => array (
      "id" => array("integer not null primary key auto_increment","ID"),
      "filename" => array("varchar(255)","file"),
      "remotefile" => array("varchar(255)","The original location on the uploader machine"),
      "mimetype" => array("varchar(255)","The type of attachment"),
      "description" => array("text","Description"),
      "size" => array("integer","Size of the file")
    ),
    "usermessage" => array ( # linking messages to a user
        #"id" => array("integer not null primary key auto_increment","ID"),
        "messageid" => array("integer not null","Message ID"),
        "userid" => array("integer not null","User ID"),
        "entered" => array("datetime not null", "Entered"),
        "viewed" => array("datetime","When viewed"),
        "status" => array("varchar(255)","Status of message"),
        "primary key" => array("(userid,messageid)","Primary key"),
        "index_1" => array("messageidindex (messageid)",""),
        "index_2" => array("useridindex (userid)",""),
        "index_3" => array("enteredindex (entered)",""),
    ),
    "sendprocess" => array( # keep track of running send processes to avoid to many running concurrently
        "id" => array("integer not null primary key auto_increment","ID"),
        "started" => array("datetime", "Start Time"),
        "modified" => array("timestamp","Modified"),
        "alive" => array("integer default 1","Is this process still alive?"),
        "ipaddress" => array("varchar(50)","IP Address of who started it"),
        "page" => array("varchar(100)","The page that this process runs in")
    ),
    "template" => array(
        "id" => array("integer not null primary key auto_increment","ID"),
        "title" => array("varchar(255) not null","Title"),
        "template" => array("longblob","The template"),
        "listorder" => array("integer",""),
        "unique_1" => array("(title)","")
    ),
    "templateimage" => array(
        "id" => array("integer not null primary key auto_increment","ID"),
        "template" => array("integer not null default 0","id of template"),
        "mimetype" => array("varchar(100)","Mime Type"),
        "filename" => array("varchar(100)","Filename"),
        "data" => array("longblob","The image"),
        "width" => array("integer",""),
        "height" => array("integer",""),
        "index_1" => array("templateidx (template)",""),
    ),
    "bounce" => array(
        "id" => array("integer not null primary key auto_increment","ID"),
        "date" => array("datetime","Date received"),
        "header" => array("text","Header of bounce"),
        "data" => array("blob","The bounce"),
        "status" => array("varchar(255)","Status of this bounce"),
        "comment" => array("text","System Comment"),
        "index_1" => array("dateindex (date)",""),
        "index_2" => array("statusindex (status(10))",""),
    ),
    "user_message_bounce" => array( # bounce. We can have one usermessage bounce multiple times
        "id" => array("integer not null primary key auto_increment","ID"),
        "user" => array("integer not null","User ID"),
        "message" => array("integer not null","Message ID"),
        "bounce" => array("integer not null","Bounce ID"),
        "time" => array("timestamp","When did it bounce"),
        "index_1" => array("umbindex (user,message,bounce)","index"),
        "index_2" => array("useridx (user)","index"),
        "index_3" => array("msgidx (message)","index"),
        "index_4" => array("bounceidx (bounce)","index"),
    ),
    "user_message_forward" => array(
        "id" => array("integer not null primary key auto_increment","ID"),
        "user" => array("integer not null","User ID"),
        "message" => array("integer not null","Message ID"),
        "forward" => array("varchar(255)","Forward email"),
        "status" => array("varchar(255)","Status of forward"),
        "time" => array("timestamp","When was it forwarded"),
        "index_1" => array("usermessageidx (user,message)","index"),
        "index_2" => array("useridx (user)","index"),
        "index_3" => array("messageidx (message)","index"),
    ),
    "config" => array(
        "item" => array("varchar(35) not null primary key","ID"),
        "value" => array("longtext","Value"),
        "editable" => array("tinyint default 1","Editable?"),
        "type" => array("varchar(25)","Type of data"),
    ),
    "admin" => array (
        "id" => array("integer not null primary key auto_increment","sys:ID"),
        "loginname" => array("varchar(25) not null","Login Name (max 25 chars)"),
        "namelc" => array("varchar(255)","sys:Normalised loginname"),
        "email" => array("varchar(255) not null","Email"),
        "created" => array("datetime","sys:Time Created"),
        "modified" => array("timestamp","sys:Time modified"),
        "modifiedby" => array("varchar(25)","sys:Modified by"),
        "password" => array("varchar(255)","Password"),
        "passwordchanged" => array("date","sys:Last time password was changed"),
        "superuser" => array("tinyint default 0","Is this user Super Admin?"),
        "disabled" => array("tinyint default 0","Is this account disabled?"),
        "unique_1" => array("(loginname)",""),
        "index_1" => array("loginnameidx (loginname)",""),
    ),
    "adminattribute" => array ( # attributes for an admin
        "id" => array("integer not null primary key auto_increment","ID"),
        "name" => array("varchar(255) not null","Name"),
        "type" => array("varchar(30)","Type of attribute"),
        "listorder" => array("integer","order of listing"),
        "default_value" => array("varchar(255)","Default value"),
        "required" => array("tinyint","Required for user to fill out"),
        "tablename" => array("varchar(255)","Name of table with values")
    ),
    "admin_attribute" => array( # attributes of an admin
        "adminattributeid" => array("integer not null","attribute number"),
        "adminid" => array("integer not null","id of admin"),
        "value" => array("varchar(255)","Value of this attribute for this admin"),
        "primary key" => array("(adminattributeid,adminid)","PKey")
    ),
    "task" => array(
        "id" => array("integer not null primary key auto_increment","ID"),
        "page" => array("varchar(25) not null","Page, page in system"),
        "type" => array("varchar(25) not null","Type: system, list, user, message, admin"),
        "unique_1" => array("(page)",""),
        "index_1" => array("pageidx (page)","index"),
        "index_2" => array("pagetypeidx (page,type)","index"),
    ),
    "admin_task" => array(
        "adminid" => array("integer not null","id of admin"),
        "taskid" => array("integer not null","id of task"),
        "level" => array("integer","Level: all,none,view,add,edit,delete,self"),
        "primary key" => array("(adminid,taskid)","PKey")
    ),
    "subscribepage" => array(
        "id" => array("integer not null primary key auto_increment","ID"),
        "title" => array("varchar(255) not null","Title"),
        "active" => array("tinyint default 0",""),
        "owner" => array("integer","Admin who is owner of this page")
    ),
    "subscribepage_data" => array(
        "id" => array("integer not null","ID"),
        "name" => array("varchar(100) not null","Name of field"),
        "data" => array("text","data"),
        "primary key" => array("(id,name)","")
    ),
    "eventlog" => array(
        "id" => array("integer not null primary key auto_increment","ID"),
        "entered" => array("datetime",""),
        "page" => array("varchar(100)","page this log was for"),
        "entry" => array("text",""),
        "index_1" => array("enteredidx (entered)","index"),
        "index_2" => array("pageidx (page)","index"),
    ),
    "urlcache" => array(
       "id" => array("integer not null primary key auto_increment","ID"),
       "url" => array("varchar(255) not null",""),
       "lastmodified" => array("integer",""),
       "added" => array("datetime",""),
       "content" => array("mediumtext",""),
       "index_1" => array("urlindex (url)",""),
    ),
    "linktrack" => array (
        "linkid" => array("integer not null primary key auto_increment", "Link ID"),
        "messageid" => array("integer not null","Message ID"),
        "userid" => array("integer not null","User ID"),
        "url" => array("varchar(255)", "URL to log"),
        "forward" => array("text","URL to forward to"),
        "firstclick" => array("datetime","When first clicked"),
        "latestclick" => array("timestamp", "When last clicked"),
        "clicked" => array("integer default 0", "Number of clicks"),
        "index_1" => array("midindex (messageid)",""),
        "index_2" => array("uidindex (userid)",""),
        "index_3" => array("urlindex (url)",""),
        "index_4" => array("miduidindex (messageid,userid)",""),
        "index_5" => array("miduidurlindex (messageid,userid,url)",""),
        "unique_1" => array("(messageid,userid,url)","")
    ),
    "linktrack_userclick" => array (
        "linkid" => array("integer not null",""),
        "userid" => array("integer not null",""),
        "messageid" => array("integer not null",""),
        "name" => array("varchar(255)","Name of data"),
        "data" => array("text",""),
        "date" => array("datetime",""),
        "index_1" => array("linkindex (linkid)",""),
        "index_2" => array("uidindex (userid)",""),
        "index_3" => array("midindex (messageid)",""),
        "index_4" => array("linkuserindex (linkid,userid)",""),
        "index_5" => array("linkusermessageindex (linkid,userid,messageid)",""),
    ),
    "userstats" => array(
        "id" => array("integer not null primary key auto_increment",""),
        "unixdate" => array("integer","date in unix format"),
        "item" => array("varchar(255)",""),
        "listid" => array("integer default 0",""),
        "value" => array("integer default 0",""),
        "index_1" => array("dateindex (unixdate)",""),
        "index_2" => array("itemindex (item)",""),
        "index_3" => array("listindex (listid)",""),
        "index_4" => array("listdateindex (listid,unixdate)",""),
        "unique_1" => array("entry (unixdate,item,listid)",""),
    ),
    # session table structure, table is created as the name identified in the config file
#   "sessiontable" => array(
#      "sessionid" => array("CHAR(32) NOT NULL PRIMARY KEY",""),
#      "lastactive" => array("INTEGER NOT NULL",""),
#      "data" => array("LONGTEXT",""),
#    ),
    "bounceregex" => array(
        "id" => array("integer not null primary key auto_increment","ID"),
        "regex" => array("varchar(255)","Regex"),
        "action" => array("varchar(255)","Action on rule"),
        "listorder" => array("integer default 0",""),
        "admin" => array("integer",""),
        "comment" => array("text",""),
        "status" => array("varchar(255)",""),
        "count" => array("integer default 0","Count of matching bounces on this rule"),
        "unique_1" => array("regex (regex)",""),
     ),
     "bounceregex_bounce" => array(
        "regex" => array("integer not null","Related regex"),
        "bounce" => array("integer not null","Related bounce"),
        "primary key" => array("(regex,bounce)",""),
      ),
      "admintoken" => array(
        "id" => array("integer not null primary key auto_increment","ID"),
        "adminid" => array("integer not null","adminid"),
        "value" => array('varchar(255)',''),
        "entered" => array('integer not null',''),
        "expires" => array('datetime not null',''),
      ),
/*    "translation" => array(
      "id" => array("integer not null primary key auto_increment",""),
      "translator" => array("varchar(255)","Name of translator"),
      "email" => array("varchar(255)","email of translator"),
      "pass" => array("varchar(255)","encrypted password for translation"),
      "ident" => array("varchar(255)","Translation identifier")
    ),
    "translation_data" => array(
      "id" => array("integer not null","Translation ID"),
      "item" => array("varchar(100)","Item to translate"),
      "primary key" => array("(id,item)","")
    )
*/
);

?>
