# This file is part of AROUNDMe
#  
# Copyright (C) 2003, 2006 Barnraiser
# http://www.barnraiser.org/
# 
# This program is free software; you can redistribute it and/or modify it
# under the terms of the GNU General Public License as published by the
# Free Software Foundation; either version 2, or (at your option) any
# later version.
# 
# This program 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
# General Public License for more details.
# 
# You should have received a copy of the GNU General Public License
# along with program; see the file COPYING. If not, write to the Free
# Software Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA
# 02111-1307, USA.



# Table structure for table `am_announce`
CREATE TABLE `am_announce` (
  `announce_id` int(11) NOT NULL auto_increment,
  `announce_title` varchar(100) default NULL,
  `announce_body` text,
  `user_id` int(11) default NULL,
  `language_id` int(11) default NULL,
  `announce_create_datetime` datetime NOT NULL,
  `announce_edit_datetime` datetime default NULL,
  `batch_request` INT(1) NULL,
  `start_user_id` INT(11) NULL,
  PRIMARY KEY  (`announce_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


# Table structure for table `am_announce_membership_type`
CREATE TABLE `am_announce_membership_type` (
  `announce_id` INT( 11 ) NULL,
  `membership_type_id` INT( 3 ) NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


# Table structure for table `am_bookmark`
CREATE TABLE `am_bookmark` (
  `module_id` int(11) default NULL,
  `item_id` int(11) default NULL,
  `inner_item_id` int(11) default NULL,
  `bookmark_type` int(1) default NULL,
  `user_id` int(11) default NULL,
  `bookmark_lastview_datetime` datetime default NULL,
  `bookmark_alarm_datetime` datetime default NULL,
  `bookmark_notify` int(1) default NULL,
  `bookmark_batch_request` int(1) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


# Table structure for table `am_comment`
CREATE TABLE `am_comment` (
  `comment_id` int(11) NOT NULL auto_increment,
  `module_id` int(11) default NULL,
  `item_id` int(11) default NULL,
  `inner_item_id` int(11) default NULL,
  `inner_item_revision` int(11) default NULL,
  `comment_parent_id` int(11) default NULL,
  `comment_body` text,
  `comment_create_datetime` datetime default NULL,
  `comment_type` INT( 1 ) NULL,
  `user_id` int(11) default NULL,
  `item_user_id` int(11) default NULL,
  PRIMARY KEY  (`comment_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


# Table structure for table `am_contact`
CREATE TABLE `am_contact` (
  `contact_id` int(11) NOT NULL auto_increment,
  `contact_url` varchar(255) default NULL,
  `contact_source` text,
  `contact_message` text,
  `reportee_user_id` int(11) default NULL,
  `reportee_email` varchar(50) default NULL,
  `assigned_user_id` int(11) default NULL,
  `contact_report_type` int(3) default NULL,
  `reportee_language_id` int(3) default NULL,
  `report_datetime` datetime default NULL,
  `report_status_id` int(11) default NULL,
  PRIMARY KEY  (`contact_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


# Table structure for table `am_file`
CREATE TABLE `am_file` (
  `file_id` int(11) NOT NULL auto_increment,
  `module_id` int(3) default NULL,
  `file_type` varchar(20) default NULL,
  `file_size` int(11) default NULL,
  `file_save_name` varchar(255) default NULL,
  `file_md5_name` varchar(255) default NULL,
  `file_stamp` varchar(40) default NULL,
  `file_user_id` int(11) default NULL,
  `file_create_datetime` datetime default NULL,
  `item_id` int(11) default NULL,
  PRIMARY KEY  (`file_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


# Table structure for table `am_file_allocation`
CREATE TABLE `am_file_allocation` (
  `module_id` int(11) default NULL,
  `item_id` int(11) default NULL,
  `item_type` int(1) default NULL,
  `file_allocation` int(11) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


# Table structure for table `am_mem_type_access`
CREATE TABLE `am_mem_type_access` (
  `module_id` int(11) default NULL,
  `item_id` int(11) default NULL,
  `membership_type_id` int(3) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


# Table structure for table `am_participation`
CREATE TABLE `am_participation` (
  `module_id` int(11) NOT NULL,
  `item_id` int(11) default NULL,
  `user_id` int(11) default NULL,
  `status_id` int(2) default NULL,
  `identity_id` int(11) NOT NULL,
  `participation_permission` int(11) default NULL,
  `participation_create_datetime` datetime default NULL,
  `role_id` int(1) default NULL,
  `invitee_user_id` int(11) default NULL,
  KEY `idx1` (`identity_id`,`module_id`,`item_id`),
  KEY `idx2` (`identity_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


# Table structure for table `am_rate`
CREATE TABLE `am_rate` (
  `module_id` int(11) default NULL,
  `item_id` int(11) default NULL,
  `inner_item_id` int(11) default NULL,
  `rate_type` int(1) default NULL,
  `rate_value` int(1) default NULL,
  `user_id` int(11) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


# Table structure for table `am_relation`
CREATE TABLE `am_relation` (
  `module_id` int(11) NOT NULL,
  `item_id` int(11) NOT NULL,
  `identity_id` int(11) default NULL,
  `identity_id_relation` int(11) default NULL,
  `relation_create_datetime` datetime default NULL,
  `user_id` int(11) default NULL,
  KEY `idx2` (`module_id`,`item_id`,`identity_id`,`identity_id_relation`),
  KEY `idx1` (`identity_id`),
  KEY `idx3` (`identity_id_relation`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


# Table structure for table `am_role_permission`
CREATE TABLE `am_role_permission` (
  `role_id` int(3) default NULL,
  `role_core_permission` int(11) default NULL,
  `role_mod_permission` int(11) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


# Table structure for table `am_statistic`
CREATE TABLE `am_statistic` (
  `module_id` int(11) default NULL,
  `item_id` int(11) default NULL,
  `statistic_type_id` int(3) default NULL,
  `total` int(11) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


# Table structure for table `am_tag`
CREATE TABLE `am_tag` (
  `tag_id` int(11) NOT NULL auto_increment,
  `module_id` int(3) default NULL,
  `item_id` int(11) default NULL,
  `inner_item_id` int(11) default NULL,
  `tag_body` varchar(50) default NULL,
  `user_id` int(11) default NULL,
  `level_id` int(1) default NULL,
  PRIMARY KEY  (`tag_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


# Table structure for table `am_template`
CREATE TABLE `am_template` (
  `template_id` int(11) NOT NULL auto_increment,
  `template_title` varchar(50) default NULL,
  PRIMARY KEY  (`template_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


# Table structure for table `am_user`
CREATE TABLE `am_user` (
  `user_id` int(11) NOT NULL auto_increment,
  `user_core_permission` int(11) default NULL,
  `user_mod_permission` int(11) default NULL,
  `user_firstname` varchar(100) default NULL,
  `user_surname` varchar(100) default NULL,
  `user_email` varchar(100) NOT NULL default '',
  `user_email_new` varchar(100) default NULL,
  `user_dob` date default NULL,
  `role_id` int(3) default NULL,
  `membership_type_id` int(3) default NULL,
  `language_id` int(3) default NULL,
  `template_id` int(3) default NULL,
  `location_id` int(11) default NULL,
  `user_homepage` varchar(50) default NULL,
  `status_id` int(3) default NULL,
  `invitee_user_id` int(11) default NULL,
  `invitee_identity_id` int(11) default NULL,
  `user_password` varchar(32) NOT NULL default '',
  `user_feed_password` varchar(6) default NULL,
  `user_create_datetime` datetime default NULL,
  `active_time` timestamp NOT NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY  (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


# Table structure for table `am_user_blocked`
CREATE TABLE `am_user_blocked` (
  `user_id` int(11) default NULL,
  `user_id_blocked` int(11) default NULL,
  `block_datetime` datetime default NULL,
  `block_description` varchar(200) default NULL,
  `identity_id` int(11) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


# Table structure for table `am_user_identity`
CREATE TABLE `am_user_identity` (
  `identity_id` int(11) NOT NULL auto_increment,
  `user_id` int(11) default NULL,
  `identity_name` varchar(50) default NULL,
  `identity_type` int(1) default NULL,
  `identity_signature` varchar(200) default NULL,
  `identity_gender` int(1) default NULL,
  `identity_nic` varchar(50) default NULL,
  `identity_metadata` varchar(250) default NULL,
  `identity_allow_msg` int(1) default NULL,
  `identity_allow_export` int(1) default NULL,
  `identity_allow_usage` int(1) default NULL,
  `identity_create_datetime` datetime default NULL,
  `identity_edit_datetime` datetime default NULL,
  `file_id` int(11) default NULL,
  `identity_search` int(1) default NULL,
  PRIMARY KEY  (`identity_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


# Table structure for table `am_user_identity_custom`
CREATE TABLE `am_user_identity_custom` (
  `user_id` int(11) NOT NULL default '0',
  `identity_id` int(11) default NULL,
  `custom_item_id` int(3) NOT NULL default '0',
  `custom_item_body` varchar(50) NOT NULL default ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


# Table structure for table `am_user_invite`
CREATE TABLE `am_user_invite` (
  `invited_email` varchar(100) default NULL,
  `user_id` int(11) default NULL,
  `module_id` int(11) default NULL,
  `item_id` int(11) default NULL,
  `invite_url` varchar(200) default NULL,
  `identity_id` int(11) default NULL,
  `default_status_id` int(3) default NULL,
  `default_participation_permission` int(11) default NULL,
  `invite_create_datetime` datetime default NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


# Table structure for table `am_user_language`
CREATE TABLE `am_user_language` (
  `user_id` int(11) default NULL,
  `language_id` int(3) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


# Table structure for table `am_user_ledger`
CREATE TABLE `am_user_ledger` (
  `ledger_id` int(11) NOT NULL auto_increment,
  `user_id` int(11) NOT NULL,
  `ledger_net` int(11) unsigned NOT NULL,
  `ledger_type` int(3) NOT NULL,
  `ledger_payment_date` date NOT NULL,
  `ledger_expiry_date` date default NULL,
  `ledger_note` varchar(255) NOT NULL,
  PRIMARY KEY  (`ledger_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


# Table structure for table `am_user_location`
CREATE TABLE `am_user_location` (
  `location_id` int(3) NOT NULL auto_increment,
  `language_id` int(3) default NULL,
  `location_name` varchar(50) default NULL,
  PRIMARY KEY  (`location_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


# Table structure for table `am_user_message`
CREATE TABLE `am_user_message` (
  `message_id` int(11) NOT NULL auto_increment,
  `from_identity_id` int(11) default NULL,
  `to_identity_id` int(11) default NULL,
  `owner_user_id` int(11) default NULL,
  `message_status` int(3) default NULL,
  `message_box` int(3) default NULL,
  `message_subject` varchar(50) default NULL,
  `message_body` text,
  `message_create_datetime` datetime default NULL,
  `message_priority` int(1) default NULL,
  `message_response` int(1) default NULL,
  `to_user_id` int(11) default NULL,
  PRIMARY KEY  (`message_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


# Table structure for table `am_user_preference`
CREATE TABLE `am_user_preference` (
  `user_id` int(11) default NULL,
  `user_preference_id` int(3) default NULL,
  `user_preference_value` int(3) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


# Table structure for table `am_user_session`
CREATE TABLE `am_user_session` (
  `session_id` int(11) NOT NULL auto_increment,
  `user_id` int(11) default NULL,
  `session_md5` varchar(32) default NULL,
  `session_lastused` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `session_firstused` timestamp NOT NULL default '0000-00-00 00:00:00',
  `session_ip` varchar(15) default NULL,
  PRIMARY KEY  (`session_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


# Table structure for table `am_user_testimonial`
CREATE TABLE `am_user_testimonial` (
  `testifier_identity_id` int(11) default NULL,
  `identity_id` int(11) default NULL,
  `testimonial_body` varchar(255) default NULL,
  `testimonial_create_datetime` datetime default NULL,
  `testimonial_id` int(11) NOT NULL auto_increment,
  PRIMARY KEY  (`testimonial_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


# INSERT DEFAULTS -----------------------------------------

INSERT INTO `am_template` VALUES (1, 'barnraiser_01');


# END OF SQL
