# This file is part of Zoph.
#
# Zoph 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 of the License, or
# (at your option) any later version.
# 
# Zoph 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 Zoph; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301  USA

-- For install of zoph 1.4

--
-- Table structure for table 'zoph_albums'
--

CREATE TABLE zoph_albums (
  album_id int(11) NOT NULL auto_increment,
  parent_album_id int(11) NOT NULL default '0',
  album varchar(64) NOT NULL default '',
  album_description varchar(255) default NULL,
  sortname char(32),
  coverphoto int(11) default NULL,
  pageset int(11) DEFAULT NULL,
  sortorder varchar(32) default NULL,
  createdby int(11) NOT NULL DEFAULT 1,
  PRIMARY KEY  (album_id),
  KEY album_parent_id (parent_album_id)
) ENGINE=InnoDB;

--
-- Dumping data for table 'zoph_albums'
--


INSERT INTO zoph_albums VALUES (1,0,'Album Root',NULL, NULL, NULL, NULL,NULL, 1);

--
-- Table structure for table 'zoph_categories'
--

CREATE TABLE zoph_categories (
  category_id int(11) NOT NULL auto_increment,
  parent_category_id int(11) NOT NULL default '0',
  category varchar(64) NOT NULL default '',
  category_description varchar(255) default NULL,
  sortname char(32),
  coverphoto int(11) default NULL,
  pageset int(11) DEFAULT NULL,
  sortorder varchar(32) default NULL,
  createdby int(11) NOT NULL DEFAULT 1,
  PRIMARY KEY  (category_id),
  KEY cat_parent_id (parent_category_id)
) ENGINE=InnoDB;

--
-- Dumping data for table 'zoph_categories'
--

INSERT INTO zoph_categories VALUES (1,0,'Category Root',NULL, NULL,NULL,NULL, NULL, 1);

--
-- Table structure for table 'zoph_color_schemes'
--

CREATE TABLE zoph_color_schemes (
  color_scheme_id int(11) NOT NULL auto_increment,
  name varchar(64) NOT NULL default '',
  page_bg_color varchar(6) default NULL,
  text_color varchar(6) default NULL,
  link_color varchar(6) default NULL,
  vlink_color varchar(6) default NULL,
  table_bg_color varchar(6) default NULL,
  table_border_color varchar(6) default NULL,
  breadcrumb_bg_color varchar(6) default NULL,
  title_bg_color varchar(6) default NULL,
  tab_bg_color varchar(6) default NULL,
  tab_font_color varchar(6) default NULL,
  selected_tab_bg_color varchar(6) default NULL,
  selected_tab_font_color varchar(6) default NULL,
  title_font_color varchar(6) default NULL,
  PRIMARY KEY  (color_scheme_id)
) ENGINE=InnoDB;

--
-- Dumping data for table 'zoph_color_schemes'
--


INSERT INTO zoph_color_schemes VALUES (1,'default','ffffff','000000','111111','444444','ffffff','000000','ffffff','f0f0f0','000000','ffffff','c0c0c0','000000','000000');
INSERT INTO zoph_color_schemes VALUES (2,'blugram','909090','000000','111111','333333','eef0f0','000000','cce0e0','dde0cc','ccd0bb','000000','bbd0d0','000000','000000');
INSERT INTO zoph_color_schemes VALUES (3,'dow','444444','000000','000055','000033','cccccc','000000','aaaaaa','2222aa','2222aa','ffffff','cccccc','000000','ffffff');
INSERT INTO zoph_color_schemes VALUES (4,'hoenig','FFEFD6','5C1F00','330000','330000','FFFBF5','000000','FFF7EB','FFE7C2','FFE7C2','5C1F00','FFD799','000000','993300');
INSERT INTO zoph_color_schemes VALUES (5,'forest','336633','000000','000000','000000','99CC99','000000','669966','663300','663300','E0E0E0','996633','FFFFFF','99CC99');
INSERT INTO zoph_color_schemes VALUES (6,'black','000000','FFFFFF','FFFFFF','FFFFFF','000000','FFFFFF','000000','666666','666666','FFFFFF','999999','FFFFFF','FFFFFF');
INSERT INTO zoph_color_schemes VALUES (7,'beach','646D7E','000000','000000','000000','F9EEE2','000000','9AADC7','C6DEFF','617C58','D0D0D0','8BB381','000000','646D7E');
INSERT INTO zoph_color_schemes VALUES (8,'steel','cccccc','000000','0000aa','bb0000','eeeeef','222222','6699dd','dddddf','99bbdd','444444','0000bb','bbbbbb','555555');
INSERT INTO zoph_color_schemes VALUES (9,'happy','16c0ff','333333','444444','444444','ffff77','333333','ffff77','e43f7d','e43f7d','333333','e43f7d','333333','333333');
INSERT INTO zoph_color_schemes VALUES (10,'grey','eeeeee','111111','222222','555555','f4f4f4','111111','dddddd','dfdfdf','aaaaaa','222222','888888','111111','131313');
INSERT INTO zoph_color_schemes VALUES (11,'grey - blue','eeeeee','111111','222222','555555','f4f4f4','000000','dddddd','dfdfdf','0000ff','eeeeee','000077','ffffff','131313');
INSERT INTO zoph_color_schemes VALUES (12,'grey - green','eeeeee','111111','222222','555555','f4f4f4','000000','dddddd','dfdfdf','00dd00','eeeeee','007700','ffffff','131313');
INSERT INTO zoph_color_schemes VALUES (13,'grey - red','eeeeee','111111','222222','555555','f4f4f4','000000','dddddd','dfdfdf','ff0000','eeeeee','770000','ffffff','131313');

--
-- Table structure for table 'zoph_comments'
--

CREATE TABLE zoph_comments (
  comment_id int(11) NOT NULL auto_increment,
  user_id int(11) NOT NULL default '0',
  comment_date datetime default NULL,
  subject varchar(255) default NULL,
  comment blob,
  timestamp timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  ipaddr varchar(48) default '',
  PRIMARY KEY  (comment_id)
) ENGINE=InnoDB;

CREATE TABLE zoph_conf (
  conf_id char(64) NOT NULL,
  value varchar(128) default NULL,
  timestamp timestamp,
  PRIMARY kEY (conf_id)
) ENGINE=InnoDB;

INSERT INTO `zoph_conf` VALUES
('date.format','d-m-Y',NULL),
('date.guesstz','',NULL),
('date.timeformat','H:i:s T',NULL),
('date.tz','',NULL),
('feature.comments','',NULL),
('feature.download','',NULL),
('feature.mail','',NULL),
('feature.mail.bcc','',NULL),
('feature.rating','1',NULL),
('feature.sets','1',NULL),
('import.cli.copy','',NULL),
('import.cli.exif','1',NULL),
('import.cli.hash','1',NULL),
('import.cli.size','1',NULL),
('import.cli.thumbs','1',NULL),
('import.dated','',NULL),
('import.dated.hier','',NULL),
('import.dirmode','0755',NULL),
('import.enable','1',NULL),
('import.filemode','0644',NULL),
('import.maxupload','10000000',NULL),
('import.parallel','1',NULL),
('import.resize','resample',NULL),
('import.rotate','',NULL),
('import.upload','1',NULL),
('interface.autocomplete','1',NULL),
('interface.cookie.expire','0',NULL),
('interface.language','en',NULL),
('interface.max.days','30',NULL),
('interface.sort.dir','asc',NULL),
('interface.sort.order','date',NULL),
('interface.template','default',NULL),
('interface.title','Zoph',NULL),
('interface.user.cli','1',NULL),
('interface.user.default','0',NULL),
('interface.user.remote','',NULL),
('interface.width','1000px',NULL),
('maps.geocode','',NULL),
('maps.mapbox.apikey','pk.eyJ1IjoiamVyb2Vucm5sIiwiYSI6ImNpdmh6dnlsazAwYWUydXBrbG50cHhlbmMifQ.0pSkJxO6ycD2Wg5GL4yYyw',NULL),
('maps.provider','',NULL),
('path.images','/var/lib/zoph',NULL),
('path.magic','',NULL),
('path.trash','',NULL),
('path.unbz','',NULL),
('path.ungz','',NULL),
('path.untar','',NULL),
('path.unzip','',NULL),
('path.upload','upload',NULL),
('rotate.backup','1',NULL),
('rotate.backup.prefix','orig_',NULL),
('rotate.command','convert',NULL),
('rotate.enable','',NULL),
('share.enable','',NULL),
('share.url.override','',NULL),
('watermark.enable','',NULL),
('watermark.file','',NULL),
('watermark.pos.x','center',NULL),
('watermark.pos.y','center',NULL),
('watermark.transparency','50',NULL),
('zoph.version','v01.03.00.000',NULL);


CREATE TABLE zoph_groups (
        group_id int(11) NOT NULL auto_increment,
        group_name varchar(32),
        description varchar(128),
        PRIMARY KEY  (group_id)
) ENGINE=InnoDB;

CREATE TABLE zoph_group_permissions (
        group_id int(11) NOT NULL default '0',
        album_id int(11) NOT NULL default '0',
        access_level tinyint(4) NOT NULL default '0',
        watermark_level tinyint(4) NOT NULL default '0',
        writable char(1) NOT NULL default '0',
	subalbums CHAR(1) NOT NULL DEFAULT '0',
        changedate timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY  (group_id,album_id),
        KEY ap_access_level (access_level)
) ENGINE=InnoDB;

CREATE TABLE zoph_groups_users (
        group_id int(11) NOT NULL default '0',
        user_id int(11) NOT NULL default '0',
        changedate timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY  (group_id,user_id)
) ENGINE=InnoDB;
--
-- Table structure for table 'zoph_people'
--

CREATE TABLE zoph_people (
  `person_id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(32) DEFAULT NULL,
  `last_name_prefix` varchar(32) DEFAULT NULL,
  `last_name` varchar(32) DEFAULT NULL,
  `last_name_suffix` varchar(32) DEFAULT NULL,
  `middle_name` varchar(32) DEFAULT NULL,
  `called` varchar(16) DEFAULT NULL,
  `display_name` varchar(64) DEFAULT NULL,
  `full_name` varchar(64) DEFAULT NULL,
  `short_name` varchar(32) DEFAULT NULL,
  gender char(1) default NULL,
  dob date default NULL,
  dod date default NULL,
  home_id int(11) default NULL,
  work_id int(11) default NULL,
  father_id int(11) default NULL,
  mother_id int(11) default NULL,
  spouse_id int(11) default NULL,
  notes varchar(255) default NULL,
  coverphoto int(11) default NULL,
  pageset int(11) DEFAULT NULL,
  email varchar(64) default NULL,
  createdby int(11) NOT NULL DEFAULT 1,
  PRIMARY KEY  (person_id),
  KEY person_last_name (last_name(10)),
  KEY person_first_name (first_name(10))
) ENGINE=InnoDB;

--
-- Dumping data for table 'zoph_people'
--



INSERT INTO `zoph_people` VALUES
(1,'Unknown',NULL,'Person',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1);

-- Table structure for table 'zoph_circles'
--

CREATE TABLE zoph_circles (
        circle_id int(11) NOT NULL auto_increment,
        circle_name varchar(32) default NULL,
        description varchar(128) default NULL,
        coverphoto int(11) default NULL,
	hidden char(1) default '0',
        createdby int(11) NOT NULL DEFAULT 1,
        PRIMARY KEY  (circle_id)
) ENGINE=InnoDB;

CREATE TABLE zoph_circles_people (
        circle_id int(11) NOT NULL default '0',
        person_id int(11) NOT NULL default '0',
        changedate timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY  (circle_id,person_id)
) ENGINE=InnoDB;

--
-- Table structure for table 'zoph_photo_albums'
--

CREATE TABLE zoph_photo_albums (
  photo_id int(11) NOT NULL default '0',
  album_id int(11) NOT NULL default '0',
  PRIMARY KEY  (photo_id,album_id)
) ENGINE=InnoDB;

--
-- Dumping data for table 'zoph_photo_albums'
--



--
-- Table structure for table 'zoph_photo_categories'
--

CREATE TABLE zoph_photo_categories (
  photo_id int(11) NOT NULL default '0',
  category_id int(11) NOT NULL default '0',
  PRIMARY KEY  (photo_id,category_id)
) ENGINE=InnoDB;

--
-- Dumping data for table 'zoph_photo_categories'
--

--
-- Table structure for table 'zoph_photo_comments'
--

CREATE TABLE zoph_photo_comments (
  photo_id int(11) NOT NULL default '0',
  comment_id int(11) NOT NULL default '0',
  PRIMARY KEY  (photo_id,comment_id)
) ENGINE=InnoDB;



--
-- Table structure for table 'zoph_photo_people'
--

CREATE TABLE zoph_photo_people (
  photo_id int(11) NOT NULL default '0',
  person_id int(11) NOT NULL default '0',
  position int(11) default NULL,
  row tinyint unsigned NOT NULL default 0,
  PRIMARY KEY  (photo_id,person_id)
) ENGINE=InnoDB;

--
-- Dumping data for table 'zoph_photo_people'
--

--
-- Table structure for table 'zoph_photo_relations'
--

CREATE TABLE zoph_photo_relations (
  photo_id_1 int(11) NOT NULL default '0',
  photo_id_2 int(11) NOT NULL default '0',
  desc_1 varchar(128) default NULL,
  desc_2 varchar(128) default NULL,
  PRIMARY KEY  (photo_id_1,photo_id_2)
) ENGINE=InnoDB;

--
-- Dumping data for table 'zoph_photos'
--



--
-- Table structure for table 'zoph_places'
--

CREATE TABLE zoph_places (
  place_id int(11) NOT NULL auto_increment,
  parent_place_id int(11) NOT NULL,
  title varchar(64) NOT NULL default '',
  address varchar(64) default NULL,
  address2 varchar(64) default NULL,
  city varchar(32) default NULL,
  state varchar(32) default NULL,
  zip varchar(10) default NULL,
  country varchar(32) default NULL,
  url varchar(1024) DEFAULT NULL,
  urldesc varchar(32) DEFAULT NULL,
  coverphoto int(11) default NULL,
  pageset int(11) DEFAULT NULL,
  notes varchar(255) default NULL,
  lat float(10,6),
  lon float(10,6),
  mapzoom tinyint unsigned,
  timezone varchar(50) default NULL,
  createdby int(11) NOT NULL DEFAULT 1,
  PRIMARY KEY  (place_id),
  KEY place_city (city(10)),
  KEY place_title (title(10))
) ENGINE=InnoDB;

INSERT INTO zoph_places VALUES (0,0,"World",NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL, 1);

--
-- Dumping data for table 'zoph_places'
--



--
-- Table structure for table 'zoph_prefs'
--

CREATE TABLE zoph_prefs (
  user_id int(11) NOT NULL default '0',
  show_breadcrumbs char(1) NOT NULL default '1',
  num_breadcrumbs smallint(5) unsigned NOT NULL default '8',
  num_rows tinyint(3) unsigned NOT NULL default '3',
  num_cols tinyint(3) unsigned NOT NULL default '4',
  max_pager_size tinyint(3) unsigned NOT NULL default '10',
  random_photo_min_rating tinyint(3) unsigned NOT NULL default '0',
  reports_top_n smallint(5) unsigned NOT NULL default '5',
  color_scheme_id int(11) NOT NULL default '1',
  slideshow_time smallint(6) NOT NULL default '5',
  language char(5) default NULL,
  recent_photo_days smallint(6) NOT NULL default '7',
  auto_edit char(1) NOT NULL default '0',
  camera_info char(1) NOT NULL default '1',
  allexif char(1) NOT NULL default '0',
  autocomp_albums char(1) default '1',
  autocomp_places char(1) default '1',
  autocomp_categories char(1) default '1',
  autocomp_photographer char(1) default '1',
  autocomp_people char(1) default '1',
  fullsize_new_win char(1) NOT NULL default '0',
  view enum('list','tree','thumbs') default 'list' NOT NULL,
  autothumb enum('oldest','newest','first','last','highest','random') 
  	default 'highest' NOT NULL,
  child_sortorder enum('name', 'sortname', 'oldest', 'newest', 
	'first', 'last', 'lowest', 'highest', 'average', 'random') default 'sortname' NOT NULL,
  PRIMARY KEY  (user_id)
) ENGINE=InnoDB;

--
-- Dumping data for table 'zoph_prefs'
--


INSERT INTO zoph_prefs VALUES (1,'1',8,3,4,10,0,5,1,5,NULL,7,'0','1','0','1','1','1','1','1','0','list','highest','sortname');

--
-- Table structure for table 'zoph_photo_ratings'
--

CREATE TABLE zoph_photo_ratings (
  rating_id int(11) auto_increment NOT NULL,
  user_id int(11) NOT NULL default '0',
  photo_id int(11) NOT NULL default '0',
  rating tinyint(4) NOT NULL default '0',
  ipaddress varchar(48),
  timestamp timestamp,
  PRIMARY KEY  (rating_id)
) ENGINE=InnoDB;

CREATE INDEX user_photo ON zoph_photo_ratings (user_id,photo_id);
CREATE INDEX photo_id ON zoph_photo_ratings(photo_id);



--
-- Dumping data for table 'zoph_photo_ratings'
--

CREATE TABLE zoph_saved_search (
        search_id int(11) NOT NULL auto_increment,
        name varchar(64) NOT NULL default '',
        owner int(11) default NULL,
        public tinyint(1) default '0',
        search varchar(2000) default NULL,
        timestamp timestamp NOT NULL default CURRENT_TIMESTAMP
                on update CURRENT_TIMESTAMP,
        PRIMARY KEY  (`search_id`)
) ENGINE=InnoDB;

CREATE TABLE zoph_pageset (
  pageset_id int(11) NOT NULL auto_increment,
  title varchar(128),
  show_orig enum('never', 'first', 'last', 'all') NOT NULL DEFAULT 'all',
  orig_pos enum('top', 'bottom') NOT NULL DEFAULT 'top',
  date datetime,
  user int(11) ,
  timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY  (pageset_id)
) ENGINE=InnoDB;

CREATE TABLE zoph_pages (
  page_id int(11) NOT NULL auto_increment,
  title varchar(128),
  text blob,
  date datetime,
  timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY  (page_id)
) ENGINE=InnoDB;

CREATE TABLE zoph_pages_pageset (
  pageset_id int(11) NOT NULL,
  page_id int(11) NOT NULL,
  page_order int(5) unsigned
) ENGINE=InnoDB;

CREATE TABLE zoph_track (
  track_id int(11) NOT NULL auto_increment,
  name varchar(64) NOT NULL default 'track',
  PRIMARY KEY  (track_id)
) ENGINE=InnoDB;

CREATE TABLE zoph_point (
  point_id int(11) NOT NULL auto_increment,
  name varchar(64) default NULL,
  track_id int(11) default NULL,
  lat float(10,6) default NULL,
  lon float(10,6) default NULL,
  ele float(12,4) default NULL,
  speed float(12,4) default NULL,
  datetime datetime default NULL,
  PRIMARY KEY  (point_id)
) ENGINE=InnoDB;


CREATE TABLE `zoph_sets` (
  `set_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) DEFAULT NULL,
  `coverphoto` int(11) DEFAULT NULL,
  `createdby` int(11) NOT NULL DEFAULT 1,
  `timestamp` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`set_id`),
  KEY `person_last_name` (`name`(10))
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;


CREATE TABLE IF NOT EXISTS `zoph_photo_sets` (
  `photo_id` int(11) NOT NULL,
  `set_id` int(11) NOT NULL,
  `photo_order` int(11) NOT NULL DEFAULT 0,
  `timestamp` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`photo_id`,`set_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;

