<?php
$sql=array();
// Create MODULES
$sql[]="create table modules".$config["table_prefix"]." (
	id int not null auto_increment,
	title varchar(255),
	description text,
	primary key(id))";

// Create ACCESS
$sql[]="create table access".$config["table_prefix"]." (
	id int not null auto_increment,
	module_id int,
	title varchar(255),
	description text,
	primary key(id))";

// Create USER_ACCESS
$sql[]="create table user_access".$config["table_prefix"]." (
	user_id int,
	access_id int)";

// Create MAILING_GROUPS
$sql[]="create table mailing_groups".$config["table_prefix"]." (
	id int not null auto_increment,
	date_created int,
	title varchar(255),
	position int,
	description text,
	enabled bool,
	visible bool,
	allow_subscriptions bool,
	allow_unsubscriptions bool,
	owner_id int,
	modify_user int,
	modify_time int,
	primary key(id))";

// Create ENCODE
$sql[]="create table encodes".$config["table_prefix"]." (
	code varchar(30) not null,
	title varchar(255),
	primary key(code))";

// Create SUBSCRIBERS
$sql[]="create table subscribers".$config["table_prefix"]." (
	id int not null auto_increment,
	date_created int,
	name varchar(255),
	mail varchar(255),
	receive_format bool,
	encoding varchar(50),
	status tinyint,
	active_key varchar(255),
	bounce_count int default 0,
	ip bigint,
	owner_id int,
	modify_user int,
	modify_time int,
	primary key(id))";

// Create SUBSCRIBERS_GROUP
$sql[]="create table subscribers_group".$config["table_prefix"]." (
	subscribers_id int,
	group_id int)";

// Create TEMPLATES
$sql[]="create table templates".$config["table_prefix"]." (
	id int not null auto_increment,
	date_created int,
	title varchar(255),
	text_content text,
	html_header text,
	html_css text,
	html_content text,
	owner_id int,
	modify_user int,
	modify_time int,
	primary key(id))";

// Create BLACK_LIST
$sql[]="create table black_list".$config["table_prefix"]." (
	id int not null auto_increment,
	date_created int,
	title varchar(255),
	rule varchar(255),
	enabled bool,
	owner_id int,
	modify_user int,
	modify_time int,
	primary key(id))";

// Create NEWSLETTERS
$sql[]="create table newsletters".$config["table_prefix"]." (
	id int not null auto_increment,
	date_created int,
	name varchar(255),
	subject varchar(255),
	from_mail varchar(255),
	from_name varchar(255),
	reply_mail varchar(255),
	groups varchar(255),
	recipients int,
	smtp int default 0,
	pop3 int default 0,
	files text,
	text_content text,
	html_header text,
	html_css text,
	html_content text,
	status bool default 0,
	owner_id int,
	modify_user int,
	modify_time int,
	last_sent int,
	deliveries_count int default 0,
	last_delivery int,
	last_delivery_user int,
	last_bounced int,
	primary key(id))";

// Create AUTORESPONDER
$sql[]="create table autoresponder".$config["table_prefix"]." (
	id int not null auto_increment,
	date_created int,
	newsletter_id int,
	type int,
	start int,
	end int,
	period int,
	list varchar(255),
	enabled bool,
	last_check int default 0,
	is_newsletter bool default 0,
	owner_id int,
	modify_user int,
	modify_time int,
	primary key(id));";

// Create CAMPAIGN_SCHEDULE
$sql[]="create table campaign_schedule".$config["table_prefix"]." (
	id int not null auto_increment,
	date_created int,
	newsletter_id int,
	group_id int,
	day int,
	position int,
	enabled bool,
	owner_id int,
	modify_user int,
	modify_time int,
	primary key(id));";

// Create STATISTICS
$sql[]="create table stat".$config["table_prefix"]." (
	date_created int,
	code varchar(255),
	count int default 0)";
$sql[]="alter table stat".$config["table_prefix"]." add index (date_created)";
$sql[]="alter table stat".$config["table_prefix"]." add index (code)";

// Create STATISTICS_DAILY
$sql[]="create table stat_daily".$config["table_prefix"]." (
	date_created int,
	code varchar(255),
	ip varchar(31))";
$sql[]="alter table stat_daily".$config["table_prefix"]." add index (date_created)";
$sql[]="alter table stat_daily".$config["table_prefix"]." add index (code)";
$sql[]="alter table stat_daily".$config["table_prefix"]." add index (ip)";

// Create STATISTICS_LINKS
$sql[]="create table stat_links".$config["table_prefix"]." (
	id int not null auto_increment,
	date_created int,
	subscriber_id int,
	newsletter_id int,
	url varchar(255),
	ip varchar(15),
	browser varchar(255),
	os varchar(255),
	primary key(id))";

// Create STATISTICS_OPENED_NEWSLETTER
$sql[]="create TABLE stat_opened_newsletters".$config["table_prefix"]." (
	id int NOT NULL auto_increment,
	subscriber_id int,
	newsletter_id int,
	date_created int,
	ip varchar(15),
	browser varchar(255),
	os varchar(255),
	primary key(id))";

// Create STATISTICS_BOUNCED
$sql[]="create table stat_bounced".$config["table_prefix"]." (
	id int not null auto_increment,
	date_created int,
	subscriber_id int,
	newsletter_id int,
	type int,
	primary key(id))";

// Create STATISTICS_UNSUBSCRIBES
$sql[]="create table stat_unsubscribes".$config["table_prefix"]." (
	id int not null auto_increment,
	subscriber_id int,
	newsletter_id int,
	group_id int,
	date_created int,
	ip varchar(15),
	browser varchar(255),
	os varchar(255),
	status int,
	active_key  varchar(255),
	primary key(id))";

// Create MSG_CONFIRM_SUBSCRIBE
$sql[]="create table msg_confirm_subscribe".$config["table_prefix"]." (
	date_created int,
	subscriber_id int,
	groups_id varchar(255),
	active_key varchar(255))";
$sql[]="alter table msg_confirm_subscribe".$config["table_prefix"]." add key(date_created)";
$sql[]="alter table msg_confirm_subscribe".$config["table_prefix"]." add key(subscriber_id)";
$sql[]="alter table msg_confirm_subscribe".$config["table_prefix"]." add key(groups_id)";
$sql[]="alter table msg_confirm_subscribe".$config["table_prefix"]." add key(active_key)";

// Create MSG_CONFIRM_UNSUBSCRIBE
$sql[]="create table msg_confirm_unsubscribe".$config["table_prefix"]." (
	date_created int,
	subscriber_id int,
	group_id varchar(255),
	active_key varchar(255))";
$sql[]="alter table msg_confirm_subscribe".$config["table_prefix"]." add key(date_created)";
$sql[]="alter table msg_confirm_subscribe".$config["table_prefix"]." add key(subscriber_id)";
$sql[]="alter table msg_confirm_subscribe".$config["table_prefix"]." add key(group_id)";
$sql[]="alter table msg_confirm_subscribe".$config["table_prefix"]." add key(active_key)";

// Create MSG_CAMPAIGN_SCHEDULE
$sql[]="create table msg_campaign_schedule".$config["table_prefix"]." (
	id int not null auto_increment,
	date_created int,
	date_start int,
	last_update int,
	subscriber_id int,
	group_id int,
	task_id int,
	status int default 0,
	log text,
	message_id varchar(255),
	primary key(id))";
$sql[]="alter table msg_campaign_schedule".$config["table_prefix"]." add key(date_created)";
$sql[]="alter table msg_campaign_schedule".$config["table_prefix"]." add key(date_start)";
$sql[]="alter table msg_campaign_schedule".$config["table_prefix"]." add key(status)";
$sql[]="alter table msg_campaign_schedule".$config["table_prefix"]." add key(message_id)";

// Create MSG_AUTORESPONDER
$sql[]="create table msg_autoresponder".$config["table_prefix"]." (
	id int not null auto_increment,
	date_created int,
	date_start int,
	last_update int,
	subscriber_id int,
	task_id int,
	status int default 0,
	log text,
	message_id varchar(255),
	primary key(id))";
$sql[]="alter table msg_autoresponder".$config["table_prefix"]." add key(date_created)";
$sql[]="alter table msg_autoresponder".$config["table_prefix"]." add key(date_start)";
$sql[]="alter table msg_autoresponder".$config["table_prefix"]." add key(status)";
$sql[]="alter table msg_autoresponder".$config["table_prefix"]." add key(message_id)";

// Create MSG_NEWSLETTERS
$sql[]="create table msg_newsletters".$config["table_prefix"]." (
	id int not null auto_increment,
	date_created int,
	date_start int,
	last_update int,
	subscriber_id int,
	newsletter_id int,
	status int default 0,
	log text,
	message_id varchar(255),
	primary key(id))";
$sql[]="alter table msg_newsletters".$config["table_prefix"]." add key(date_created)";
$sql[]="alter table msg_newsletters".$config["table_prefix"]." add key(date_start)";
$sql[]="alter table msg_newsletters".$config["table_prefix"]." add key(status)";
$sql[]="alter table msg_newsletters".$config["table_prefix"]." add key(message_id)";

// Create SETTIGNS
$sql[]="create table settings".$config["table_prefix"]." (
	id int not null auto_increment,
	mail_mode int,
	autoresponder_smtp int,
	autoresponder_pop3 int,
	campaign_schedule_smtp int,
	campaign_schedule_pop3 int,
	mail_from varchar(255),
	mail_from_name varchar(255),
	encoding varchar(30),	
	mail_count int default 2,
	rec_count int default 10,
	date_format int default 0,
	is_quick_steps bool,
	email_activation bool,
	unsubscribe_confirm bool,
	show_encoding bool,
	owner_id int,
	modify_user int,
	modify_time int,
	primary key(id))";

$sql[]=get_sql_insert('settings'.$config["table_prefix"],
	'mail_mode, autoresponder_smtp, autoresponder_pop3, campaign_schedule_smtp, campaign_schedule_pop3, mail_from, mail_from_name, encoding, mail_count, rec_count, date_format, is_quick_steps, email_activation, unsubscribe_confirm, show_encoding, owner_id, modify_user, modify_time', array(
	"mail_mode"		=> 0,
	"mail_from"		=> $db->get_val('users'.$config["table_prefix"], 'mail'),
	"mail_from_name"	=> "Mailing List Manager Pro",
	"encoding"		=> "UTF-8",
	"mail_count"		=> 60,
	"rec_count"		=> 20,
	"is_quick_steps"	=> 1,
	"email_activation"	=> 1,
	"unsubscribe_confirm"	=> 1,
	"show_encoding"		=> 1,
	"owner_id"		=> $db->get_val('users'.$config["table_prefix"], 'id'),
));


// Create SMTP_ACCOUNTS
$sql[]="create table smtp_accounts".$config["table_prefix"]." (
	id int not null auto_increment,
	title varchar(255),
	login varchar(255),
	password varchar(255),
	host varchar(255),
	port int,
	owner_id int,
	modify_user int,
	modify_time int,
	primary key(id))";

// Create POP3_ACCOUNTS
$sql[]="create table pop3_accounts".$config["table_prefix"]." (
	id int not null auto_increment,
	title varchar(255),
	login varchar(255),
	password varchar(255),
	host varchar(255),
	port int,
	bounce_type int,
	bounce_count int,
	owner_id int,
	modify_user int,
	modify_time int,
	primary key(id))";

// Create CUSTOM_FIELDS
$sql[]="create table custom_fields".$config["table_prefix"]." (
	id int not null auto_increment,
	position int,
	title varchar(255),
	type varchar(8),
	validation varchar(4),
	def_value varchar(255),
	enabled bool DEFAULT 0,
	list_val text,
	not_null bool DEFAULT 0,
	owner_id int,
	modify_user int,
	modify_time int,
	primary key(id))";

// Create SCF
$sql[]="create table scf".$config["table_prefix"]." (
	custom_field_id int,
	subscriber_id int,
	value text)";
$sql[]="alter table scf".$config["table_prefix"]." add key(custom_field_id)";
$sql[]="alter table scf".$config["table_prefix"]." add key(subscriber_id)";

// Create USERS_NEWSLETTER
$sql[]="create table users_newsletter".$config["table_prefix"]." (
	user_id int,
	newsletter_id int)";
$sql[]="alter table users_newsletter".$config["table_prefix"]." add key(user_id)";
$sql[]="alter table users_newsletter".$config["table_prefix"]." add key(newsletter_id)";

// Create USERS_MAILING_GROUPS
$sql[]="create table users_mailing_groups".$config["table_prefix"]." (
	user_id int,
	group_id int)";
$sql[]="alter table users_mailing_groups".$config["table_prefix"]." add key(user_id)";
$sql[]="alter table users_mailing_groups".$config["table_prefix"]." add key(group_id)";

// Create PAGES
$sql[]="create table pages".$config["table_prefix"]." (
	name varchar(100),
	subject varchar(255),
	description text)";
$sql[]="alter table pages".$config["table_prefix"]." add key(name)";

// Create IPCountry
$sql[]="create table ipcountry".$config["table_prefix"]." (
	from_ip bigint,
	to_ip bigint,
	code varchar(2),
	s_name varchar(3),
	name varchar(255),
	unique key(from_ip, to_ip))";
$sql[]="alter table ipcountry".$config["table_prefix"]." add key(from_ip)";
$sql[]="alter table ipcountry".$config["table_prefix"]." add key(to_ip)";

//Creating USERS_PERMISSIONS
$sql[]="create table users_permissions".$config["table_prefix"]." (
	user_id int not null,
	newsletters_type int,
	groups_type int,
	max_newsletters int,
	max_groups int,
	max_subscribers int,
	max_groups_subscribers int,
	max_newsletter_size int,
	max_mails_per_month int,
	max_mails_per_days int,
	mailing_php bool,
	mailing_smtp bool,
	mailing_smtp_type int,
	mailing_speed_type int,
	mailing_speed int,
	account_type int,
	account_start int,
	account_end int,
	newsletter_html_header text,
	newsletter_text_header text,
	newsletter_html_footer text,
	newsletter_text_footer text,
	attachments bool,
	max_attachments_size int,
	key(user_id)
)";

//Creating USERS_PERMISSIONS_SMTP
$sql[]="create table users_permissions_smtp".$config["table_prefix"]." (
	user_id int,
	account_id int)";
// ============================================

$modules_list=array(
	"Users",
	"Mailing Groups",
	"Subscribers",
	"Custom Fields",
	"Import",
	"Export",
	"Black List",
	"Templates",
	"Newsletters",
	"Autoresponder",
	"Campaign Schedule",
	"Subscribe Page",
	"Subscribe Form",
	"System Emails",
	"Settings",
	"POP3 Accounts",
	"SMTP Accounts",
	"Statistics",
);
$access_list=array(
	"view",
	"edit",
	"delete",
);

// Insert to MODULES & ACCESS 
for ($i=0; $i<count($modules_list); $i++) {
	$r=array(
		"id"	=> $i+1,
		"title"	=> $modules_list[$i],
	);
	$sql[]=get_sql_insert("modules".$config["table_prefix"], "id, title", $r);
	for ($j=0; $j<count($access_list); $j++) {
		$r=array(
			"module_id"	=> $i+1,
			"title"		=> $access_list[$j],
		);
		$sql[]=get_sql_insert("access".$config["table_prefix"], "module_id, title", $r);
	}
}

// Insert to ENCODE
$array=array(
	"MacArabic"		=> "Arabic (Macintosh)",
	"Windows-1256"		=> "Arabic (Windows)",
	"ISO-8859-2"		=> "Central European (ISO-8859-2)",
	"MacCentralEurope"	=> "Central European (MacCE)",
	"Windows-1250"		=> "Central European (Windows1250)",
	"ISO-8859-5"		=> "Turkish (Latin5)",
	"KOI8-R"		=> "Cyrillic (KOI8-R)",
	"KOI8-U"		=> "Cyrillic (KOI8-U)",
	"MacCyrillic"		=> "Cyrillic (MacCyrillic)",
	"Windows-1251"		=> "Cyrillic (Windows1251)",
	"ISO-8859-7"		=> "Greek (ISO-8859-7)",
	"MacGreek"		=> "Greek (MacGreek)",
	"Windows-1253"		=> "Greek (Windows1253)",
	"Windows-1255"		=> "Hebrew (Windows)",
	"MacHebrew"		=> "Hebrew (Macintosh)",
	"EUC-KR"		=> "Korean (EUC-KR)",
	"SHIFT_JIS"		=> "Japanese (Shift_JIS)",
	"EUC-JP"		=> "Japanese (EUC)",
	"ISO-2022-JP"		=> "Japanese (JIS)",
	"HZ"			=> "Simplified Chinese (HZ)",
	"GB2312"		=> "Simplified Chinese (gb2312)",
	"BIG5"			=> "Traditional Chinese (big5)",
	"MacThai"		=> "Thai (Macintosh)",
	"CP874"			=> "Thai (Windows)",
	"MacTurkish"		=> "Turkish (Macintosh)",
	"Windows-1254"		=> "Turkish (Windows)",
	"UTF-8"			=> "UTF-8",
	"ISO-8859-1"		=> "Western (Latin1)",
	"Macintosh"		=> "Western (Macintosh)",
	"Windows-1252"		=> "Western (Windows 1252)",
);
reset($array);
$key=key($array);
while ($key) {
	$r=array(
		"code"	=> $key,
		"title"	=> $array[$key],
	);
	$sql[]=get_sql_insert("encodes".$config["table_prefix"], "code, title", $r);
	next($array);
	$key=key($array);
}

// Insert to PAGES
$r=array(
	"name"		=> "subscribe_page",
	"description"	=> getFile('install/page.tpl'),
);
$sql[]=get_sql_insert("pages".$config["table_prefix"], "name, subject, description", $r);

// SYSTEM EMAILS

// FORGOT PASSWORD
$r=array(
	"name"		=> "forgot_password",
	"subject"	=> "Password Reminder",
	"description"	=> "Dear %name%,
 
Login: %login%
 
Password: %password%
 
Thank you for using password reminder",
);
$sql[]=get_sql_insert("pages".$config["table_prefix"], "name, subject, description", $r);
// ----------

// Subscribe Confirm
$r=array(
	"name"		=> "subscribe_confirm",
	"subject"	=> "Please confirm your subscription",
	"description"	=> "Dear %name%,
 
Please click on the link below to confirm your subscription to the following mailing list:
%groups%
 
%link%",
);
$sql[]=get_sql_insert("pages".$config["table_prefix"], "name, subject, description", $r);
// ----------

// Subscription confirmation
$r=array(
	"name"		=> "subscribe_confirm_page_text",
	"subject"	=> "",
	"description"	=> "Please check your email to complete subscription process",
);
$sql[]=get_sql_insert("pages".$config["table_prefix"], "name, subject, description", $r);
// ----------

// Subscription successful 
$r=array(
	"name"		=> "subscribe_successful",
	"subject"	=> "Your subscription is now activated",
	"description"	=> "Thank you for joining our mailing list!
 
Your subscription is now complete.",
);
$sql[]=get_sql_insert("pages".$config["table_prefix"], "name, subject, description", $r);
// ----------

// UnSubscribe Confirm 
$r=array(
	"name"		=> "unsubscribe_confirm",
	"subject"	=> "Please confirm your unsubscription",
	"description"	=> "Dear %name%,
 
Please click on the link below to confirm your unsubscription from the folowing mailing list:
%groups%
 
%link%",
);
$sql[]=get_sql_insert("pages".$config["table_prefix"], "name, subject, description", $r);
// ----------

// UnSubscription confirmation 
$r=array(
	"name"		=> "unsubscribe_confirm_page_text",
	"subject"	=> "",
	"description"	=> "Please check your email to confirm your unsubscription...",
);
$sql[]=get_sql_insert("pages".$config["table_prefix"], "name, subject, description", $r);
// ----------

// UnSubscription successful 
$r=array(
	"name"		=> "unsubscribe_successful",
	"subject"	=> "Unsubscription successfully completed",
	"description"	=> "You have been successfully removed from our mailing list",
);
$sql[]=get_sql_insert("pages".$config["table_prefix"], "name, subject, description", $r);
// ----------



$actions=array();
$actions[]=array("title" => "Administrators", "res" => "Created");
$actions[]=array("title" => "Settings", "res" => "Created");
$actions[]=array("title" => "Mailing Groups", "res" => "Created");
$actions[]=array("title" => "Subscribers", "res" => "Created");
$actions[]=array("title" => "Custom Fields", "res" => "Created");
$actions[]=array("title" => "Templates", "res" => "Created");
$actions[]=array("title" => "Newsletters", "res" => "Created");
$actions[]=array("title" => "Newsletters » Outbox", "res" => "Created");
$actions[]=array("title" => "Newsletters » Sent", "res" => "Created");
$actions[]=array("title" => "Newsletters » Undelivered", "res" => "Created");
$actions[]=array("title" => "Newsletters » Deleted", "res" => "Created");
$actions[]=array("title" => "Autoresponder", "res" => "Created");
$actions[]=array("title" => "Autoresponder » Outbox", "res" => "Created");
$actions[]=array("title" => "Autoresponder » Sent", "res" => "Created");
$actions[]=array("title" => "Autoresponder » Undelivered", "res" => "Created");
$actions[]=array("title" => "Autoresponder » Deleted", "res" => "Created");
$actions[]=array("title" => "Campaign Schedule", "res" => "Created");
$actions[]=array("title" => "Campaign Schedule » Outbox", "res" => "Created");
$actions[]=array("title" => "Campaign Schedule » Sent", "res" => "Created");
$actions[]=array("title" => "Campaign Schedule » Undelivered", "res" => "Created");
$actions[]=array("title" => "Campaign Schedule » Deleted", "res" => "Created");
$actions[]=array("title" => "Design » Subscription Page", "res" => "Created");
$actions[]=array("title" => "Design » Subscription Form", "res" => "Created");
$actions[]=array("title" => "Design » System Emails", "res" => "Created");
$actions[]=array("title" => "Settings » Main Settings", "res" => "Created");
$actions[]=array("title" => "Settings » SMTP Accounts", "res" => "Created");
$actions[]=array("title" => "Settings » POP3 Accounts", "res" => "Created");
$actions[]=array("title" => "Statistics", "res" => "Created");
?>