-- phpMyAdmin SQL Dump
-- version 4.5.4.1deb2ubuntu2
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: May 12, 2017 at 03:29 PM
-- Server version: 5.7.18-0ubuntu0.16.04.1
-- PHP Version: 7.0.15-0ubuntu0.16.04.4

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `ababu`
--

-- --------------------------------------------------------

--
-- Table structure for table `locales`
--

DROP TABLE IF EXISTS `locales`;
CREATE TABLE `locales` (
  `locale` varchar(10) CHARACTER SET utf32 NOT NULL,
  `description` varchar(50) CHARACTER SET utf32 NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `locales`
--

INSERT INTO `locales` (`locale`, `description`) VALUES
('en_GB', 'English - United Kingdom'),
('it_IT', 'Italian - Italy');

-- --------------------------------------------------------

--
-- Table structure for table `menu`
--

DROP TABLE IF EXISTS `menu`;
CREATE TABLE `menu` (
  `name` varchar(255) NOT NULL,
  `item` varchar(255) NOT NULL,
  `url` varchar(255) NOT NULL,
  `rid` int(10) NOT NULL,
  `weight` int(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `menu`
--

INSERT INTO `menu` (`name`, `item`, `url`, `rid`, `weight`) VALUES
('main', 'calendar', 'calendar.php', 3, 0),
('main', 'credits', 'credits.php', 3, 1),
('main', 'dashboard', 'dashboard.php', 1, 0),
('main', 'help', 'help.php', 3, 10),
('main', 'owners', 'owner.php', 3, 0),
('main', 'pets', 'pet.php', 3, 0),
('main', 'profile', 'profile.php', 3, 0),
('main', 'search', 'search.php', 3, 1),
('main', 'settings', 'settings.php', 1, 0),
('main', 'species', 'species.php', 1, 0),
('main', 'users', 'user.php', 1, 0),
('main', 'visit', 'visit.history.php', 1, 0);

-- --------------------------------------------------------

--
-- Table structure for table `owners`
--

DROP TABLE IF EXISTS `owners`;
CREATE TABLE `owners` (
  `oid` int(10) NOT NULL,
  `country_id` varchar(2) NOT NULL,
  `firstname` varchar(255) NOT NULL,
  `lastname` varchar(255) NOT NULL,
  `address` varchar(255) NOT NULL,
  `zip` varchar(10) NOT NULL,
  `city` varchar(255) NOT NULL,
  `ssn` varchar(255) NOT NULL,
  `phone` varchar(64) NOT NULL,
  `mobile` varchar(64) NOT NULL,
  `email` varchar(255) NOT NULL,
  `created` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `owners`
--

INSERT INTO `owners` (`oid`, `country_id`, `firstname`, `lastname`, `address`, `zip`, `city`, `ssn`, `phone`, `mobile`, `email`, `created`) VALUES
(1, '', 'George Gordon', 'Byron', 'Gibbon Street', '00100', 'Plymouth dc', 'UK01234567890', '+44 01752 01234567', '+44 348 88 22 399', 'byron@romantic.org', 1467737328),
(2, 'ie', 'Rory', 'Gallagher', 'Thin Lizzy Road, 1', '50127', 'Cork', 'IR0101010101', '+353 0123 123456789', '+353 348 88 22 399', 'rory@rockatcork.org', 1467737346),
(3, 'ie', 'Phil', 'Lynott', 'Thin Lizzy Rd., 1', 'D01 123', 'Dublin', 'IRL0987654321', '+353 0000 01234567', '+353 348 88 22 399', 'phil.lynott@thinlizzy.com', 1467737416),
(4, 'gb', 'Sherlock', 'Holmes', '221B, Baker street', '', 'London', 'UK01234567890', '+44 01752 01234567', '+44 348 88 22 399', 'sh@scotlandyard.org', 1467737328),
(5, 'it', 'Roberto', 'Nannucci', 'via Valle 14', '50014', 'Fiesole', 'NNNRRT72C26D583N', '3488822399', '+393488822399', 'roberto.nannucci@gmail.com', 1468583717),
(6, 'gb', 'Paul', 'McCartney', 'Abbey Road, 1', 'SN123LN', 'London', 'EN101010', '+44123456789', '+44987654321', 'paul@beatles.com', 1493984647);

-- --------------------------------------------------------

--
-- Table structure for table `pets`
--

DROP TABLE IF EXISTS `pets`;
CREATE TABLE `pets` (
  `pid` int(10) NOT NULL,
  `tsn` int(11) NOT NULL,
  `oid` int(10) NOT NULL,
  `name` varchar(255) NOT NULL,
  `gender` char(1) NOT NULL,
  `date_of_birth` int(11) DEFAULT NULL,
  `date_of_green_meadows` int(11) DEFAULT NULL,
  `description` text,
  `color` varchar(255) DEFAULT NULL,
  `microchip` varchar(255) DEFAULT NULL,
  `microchip_timestamp` int(11) DEFAULT NULL,
  `microchip_location` varchar(100) DEFAULT NULL,
  `tatuatge` varchar(64) DEFAULT NULL,
  `tatuatge_timestamp` int(11) DEFAULT NULL,
  `created` int(11) NOT NULL,
  `modified` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `pets`
--

INSERT INTO `pets` (`pid`, `tsn`, `oid`, `name`, `gender`, `date_of_birth`, `date_of_green_meadows`, `description`, `color`, `microchip`, `microchip_timestamp`, `microchip_location`, `tatuatge`, `tatuatge_timestamp`, `created`, `modified`) VALUES
(1, 727488, 5, 'Ozzy', 'M', 1363277018, NULL, 'Beautiful and good Bergamasco, black and white', 'black and white', 'M12345', 1406127818, 'neck', 'T12345', 1406127818, 1406104554, 1494428618),
(2, 727488, 2, 'Muddy', 'M', 1361231500, NULL, 'Beautiful and gentle Bobtail, black and white', 'black and white', 'M09876', 1406204554, 'neck', 'T09876', 1406204554, 0, NULL),
(3, 727488, 3, 'Gnasher', 'M', 0, NULL, 'Good German Shepherd black and brown', 'black and brown', 'M45455', 1406104554, 'neck', 'T45455', 1406204554, 0, NULL),
(4, 727488, 1, 'Boatswain', 'M', 1052092800, 1226275200, 'Good and gentle and brave black dog.\r\nTo mark a friend\'s remains these stones arise;\r\nI never knew but one -- and here he lies.', 'black', 'M7878', 1406104554, 'neck', 'T7557', 1052392800, 0, NULL),
(7, 183815, 0, 'Martha', 'M', -210158656, 208535744, 'Old English Sheepdog referred to in the title of the 1968 Beatles song "Martha My Dear."', 'black, white', 'M1968', -24657856, 'Leg', 'T1968', -24657856, 1493904267, 1493904944);

-- --------------------------------------------------------

--
-- Table structure for table `roles`
--

DROP TABLE IF EXISTS `roles`;
CREATE TABLE `roles` (
  `rid` int(10) NOT NULL,
  `name` varchar(64) NOT NULL,
  `description` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `roles`
--

INSERT INTO `roles` (`rid`, `name`, `description`) VALUES
(1, 'admin', 'Administrator'),
(2, 'user', 'Default (standard) user account'),
(3, 'viewer', 'View only account');

-- --------------------------------------------------------

--
-- Table structure for table `species`
--

DROP TABLE IF EXISTS `species`;
CREATE TABLE `species` (
  `tsn` int(11) NOT NULL,
  `complete_name` varchar(164) CHARACTER SET utf32 NOT NULL,
  `familiar_name` varchar(255) CHARACTER SET utf32 NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `species`
--

INSERT INTO `species` (`tsn`, `complete_name`, `familiar_name`) VALUES
(177856, 'Otus asio', 'Owl'),
(179558, 'Serinus canaria', 'Atlantic canary'),
(180129, 'Oryctolagus cuniculus', 'European rabbit'),
(180690, 'Equus asinus', 'Donkey'),
(180691, 'Equus caballus', 'Horse'),
(183798, 'Felis catus', 'Cat'),
(183815, 'Canis familiaris', 'Dog (generic)'),
(183838, 'Bos taurus', 'Cattle'),
(632545, 'Cricetinae', 'Hamster'),
(658219, 'Gryllus cayensis', 'Gryllus'),
(727488, 'Canis familiaris domesticus', 'Dog');

-- --------------------------------------------------------

--
-- Table structure for table `translations`
--

DROP TABLE IF EXISTS `translations`;
CREATE TABLE `translations` (
  `hash` varchar(128) CHARACTER SET utf32 NOT NULL,
  `from_locale` varchar(10) CHARACTER SET utf32 NOT NULL,
  `message` text CHARACTER SET utf32 NOT NULL,
  `to_locale` varchar(10) CHARACTER SET utf32 DEFAULT NULL,
  `translation` text CHARACTER SET utf32
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `translations`
--

INSERT INTO `translations` (`hash`, `from_locale`, `message`, `to_locale`, `translation`) VALUES
('2ca63cddd54f9490efad22421891a9d1', 'en_GB', 'house', 'it_IT', 'casa'),
('06d80eb0c50b49a509b49f2424e8c805', 'en_GB', 'dog', '', '');

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `uid` int(10) NOT NULL,
  `rid` int(10) NOT NULL,
  `locale` varchar(10) NOT NULL,
  `email` varchar(255) NOT NULL,
  `password` varchar(128) NOT NULL,
  `firstname` varchar(255) NOT NULL,
  `lastname` varchar(255) NOT NULL,
  `phone` varchar(64) NOT NULL,
  `created` int(11) NOT NULL,
  `access` int(11) NOT NULL,
  `login` int(11) NOT NULL,
  `status` tinyint(4) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`uid`, `rid`, `locale`, `email`, `password`, `firstname`, `lastname`, `phone`, `created`, `access`, `login`, `status`) VALUES
(1, 1, 'en_GB', 'ronnie@oldauntie.org', '5f4dcc3b5aa765d61d8327deb882cf99', 'Ronnie James', 'Dio', '055959243', 60, 1477519208, 1494591949, 1),
(2, 1, 'en_GB', 'oldauntie@oldauntie.org', '5f4dcc3b5aa765d61d8327deb882cf99', 'Maggie', 'Magpie', '+39 3480000000', 70457400, 70457400, 1494591974, 1),
(3, 1, 'en_GB', 'byron@romantic.org', '5f4dcc3b5aa765d61d8327deb882cf99', 'Lord George Gordon', 'Byron', '+44 055 4578568', 1465408981, 10, 1494152963, 1);

-- --------------------------------------------------------

--
-- Table structure for table `visit`
--

DROP TABLE IF EXISTS `visit`;
CREATE TABLE `visit` (
  `vid` int(10) NOT NULL,
  `pid` int(10) NOT NULL,
  `oid` int(10) NOT NULL,
  `reason_id` int(11) NOT NULL,
  `diagnosis_id` int(11) NOT NULL,
  `teaser` varchar(255) DEFAULT NULL,
  `status_id` int(10) NOT NULL,
  `type_id` int(10) NOT NULL,
  `created` int(11) NOT NULL,
  `modified` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `visit`
--

INSERT INTO `visit` (`vid`, `pid`, `oid`, `reason_id`, `diagnosis_id`, `teaser`, `status_id`, `type_id`, `created`, `modified`) VALUES
(1, 1, 5, 19034, 524, 'Control Visit', 2, 2, 1492592799, 1494006746),
(2, 3, 3, 18812, 325, 'Cotroll Visit', 2, 1, 1493899500, 1493899543),
(3, 1, 5, 19034, 524, 'short', 2, 1, 1493997982, 1493997982),
(4, 1, 5, 18779, 524, 'un ricovero', 2, 2, 1493998947, 1493999108),
(5, 2, 2, 19034, 324, 'Vaccination (2nd.)', 2, 1, 1494430623, 1494430623);

-- --------------------------------------------------------

--
-- Table structure for table `visit_diagnostic_tests`
--

DROP TABLE IF EXISTS `visit_diagnostic_tests`;
CREATE TABLE `visit_diagnostic_tests` (
  `vid` int(10) NOT NULL,
  `country_id` varchar(2) NOT NULL,
  `id` int(11) NOT NULL,
  `created` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `visit_diagnostic_tests`
--

INSERT INTO `visit_diagnostic_tests` (`vid`, `country_id`, `id`, `created`) VALUES
(1, 'gb', 13379, 1492593883),
(1, 'gb', 13382, 1492593377),
(2, 'gb', 13383, 1493899500),
(9, 'gb', 13382, 1493997982),
(9, 'gb', 13393, 1493997982),
(10, 'gb', 13380, 1493999007),
(10, 'gb', 13382, 1493998947),
(10, 'gb', 13393, 1493998947);

-- --------------------------------------------------------

--
-- Table structure for table `visit_drugs`
--

DROP TABLE IF EXISTS `visit_drugs`;
CREATE TABLE `visit_drugs` (
  `vid` int(10) NOT NULL,
  `country_id` varchar(2) NOT NULL,
  `did` varchar(123) NOT NULL,
  `qty` int(11) NOT NULL,
  `created` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `visit_drugs`
--

INSERT INTO `visit_drugs` (`vid`, `country_id`, `did`, `qty`, `created`) VALUES
(1, 'gb', '01708/4596', 1, 1492594158),
(1, 'gb', '16516/4020', 3, 1492594177),
(2, 'gb', '01708/4505', 1, 1493899500),
(9, 'gb', '41821/4011', 1, 1493997982),
(9, 'gb', 'EU/2/12/142/005-006', 1, 1493997982),
(10, 'gb', '41821/4015', 1, 1493998947),
(10, 'gb', 'EU/2/08/089/008-011', 1, 1493999007),
(10, 'gb', 'EU/2/12/142/005-006', 1, 1493998947);

-- --------------------------------------------------------

--
-- Table structure for table `visit_notes`
--

DROP TABLE IF EXISTS `visit_notes`;
CREATE TABLE `visit_notes` (
  `nid` int(10) NOT NULL,
  `vid` int(10) NOT NULL,
  `uid` int(10) NOT NULL,
  `title` varchar(255) NOT NULL,
  `text` text NOT NULL,
  `created` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `visit_notes`
--

INSERT INTO `visit_notes` (`nid`, `vid`, `uid`, `title`, `text`, `created`) VALUES
(1, 1, 1, 'titolo', 'annotazione', 1492592799),
(2, 1, 1, 'una nuova nota', '... solo una', 1492594548),
(3, 1, 1, 'terza nota', '3rd annotation', 1492597699),
(4, 1, 1, 'test', 'test', 1492603573),
(5, 2, 1, 'Una nota', 'niente da annotare...', 1493899500),
(6, 2, 1, 'a new annotation ', 'to be added to present visit', 1493899543),
(7, 9, 3, 't', 'n', 1493997982),
(8, 1, 3, 'another note', 'another', 1493998840),
(9, 10, 3, 'esempio finto di ricovero', 'come prima', 1493998947),
(10, 5, 1, 'Dog status', 'Animal appears to be happy...', 1494430623);

-- --------------------------------------------------------

--
-- Table structure for table `visit_procedures`
--

DROP TABLE IF EXISTS `visit_procedures`;
CREATE TABLE `visit_procedures` (
  `vid` int(10) NOT NULL,
  `country_id` varchar(2) NOT NULL,
  `id` int(10) NOT NULL,
  `created` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `visit_procedures`
--

INSERT INTO `visit_procedures` (`vid`, `country_id`, `id`, `created`) VALUES
(1, 'gb', 13508, 1492603573),
(1, 'gb', 13577, 1492592799),
(2, 'gb', 13579, 1493899500),
(5, 'gb', 7426, 1494430623),
(5, 'gb', 7461, 1494430623),
(9, 'gb', 13577, 1493997982),
(9, 'gb', 13578, 1493997982),
(10, 'gb', 13577, 1493998947),
(10, 'gb', 13578, 1493999082),
(10, 'gb', 13579, 1493998947);

-- --------------------------------------------------------

--
-- Table structure for table `visit_status`
--

DROP TABLE IF EXISTS `visit_status`;
CREATE TABLE `visit_status` (
  `status_id` int(10) NOT NULL,
  `status` varchar(25) CHARACTER SET utf32 NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `visit_status`
--

INSERT INTO `visit_status` (`status_id`, `status`) VALUES
(0, 'cancelled'),
(1, 'opened / in progress'),
(2, 'closed');

-- --------------------------------------------------------

--
-- Table structure for table `visit_type`
--

DROP TABLE IF EXISTS `visit_type`;
CREATE TABLE `visit_type` (
  `type_id` int(10) NOT NULL,
  `type` varchar(255) CHARACTER SET utf32 NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `visit_type`
--

INSERT INTO `visit_type` (`type_id`, `type`) VALUES
(1, 'standard visit'),
(2, 'recovery');

-- --------------------------------------------------------

--
-- Table structure for table `watchdog`
--

DROP TABLE IF EXISTS `watchdog`;
CREATE TABLE `watchdog` (
  `WID` int(11) NOT NULL,
  `UID` int(10) NOT NULL DEFAULT '0',
  `TYPE` varchar(64) NOT NULL DEFAULT '',
  `MESSAGE` longtext NOT NULL,
  `SEVERITY` tinyint(3) UNSIGNED NOT NULL DEFAULT '0',
  `LINK` varchar(255) NOT NULL DEFAULT '',
  `LOCATION` text NOT NULL,
  `REFERER` varchar(128) DEFAULT '',
  `HOSTNAME` varchar(128) NOT NULL DEFAULT '',
  `TIMESTAMP` int(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `locales`
--
ALTER TABLE `locales`
  ADD PRIMARY KEY (`locale`);

--
-- Indexes for table `menu`
--
ALTER TABLE `menu`
  ADD PRIMARY KEY (`name`,`item`);

--
-- Indexes for table `owners`
--
ALTER TABLE `owners`
  ADD PRIMARY KEY (`oid`),
  ADD KEY `cid` (`country_id`);

--
-- Indexes for table `pets`
--
ALTER TABLE `pets`
  ADD PRIMARY KEY (`pid`);

--
-- Indexes for table `roles`
--
ALTER TABLE `roles`
  ADD PRIMARY KEY (`rid`);

--
-- Indexes for table `species`
--
ALTER TABLE `species`
  ADD PRIMARY KEY (`tsn`);

--
-- Indexes for table `translations`
--
ALTER TABLE `translations`
  ADD KEY `hash` (`hash`);

--
-- Indexes for table `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`uid`),
  ADD KEY `roleid` (`rid`);

--
-- Indexes for table `visit`
--
ALTER TABLE `visit`
  ADD PRIMARY KEY (`vid`);

--
-- Indexes for table `visit_diagnostic_tests`
--
ALTER TABLE `visit_diagnostic_tests`
  ADD PRIMARY KEY (`vid`,`country_id`,`id`);

--
-- Indexes for table `visit_drugs`
--
ALTER TABLE `visit_drugs`
  ADD PRIMARY KEY (`vid`,`country_id`,`did`);

--
-- Indexes for table `visit_notes`
--
ALTER TABLE `visit_notes`
  ADD PRIMARY KEY (`nid`);

--
-- Indexes for table `visit_procedures`
--
ALTER TABLE `visit_procedures`
  ADD PRIMARY KEY (`vid`,`country_id`,`id`);

--
-- Indexes for table `visit_status`
--
ALTER TABLE `visit_status`
  ADD PRIMARY KEY (`status_id`);

--
-- Indexes for table `visit_type`
--
ALTER TABLE `visit_type`
  ADD PRIMARY KEY (`type_id`);

--
-- Indexes for table `watchdog`
--
ALTER TABLE `watchdog`
  ADD PRIMARY KEY (`WID`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `owners`
--
ALTER TABLE `owners`
  MODIFY `oid` int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;
--
-- AUTO_INCREMENT for table `pets`
--
ALTER TABLE `pets`
  MODIFY `pid` int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;
--
-- AUTO_INCREMENT for table `users`
--
ALTER TABLE `users`
  MODIFY `uid` int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;
--
-- AUTO_INCREMENT for table `visit`
--
ALTER TABLE `visit`
  MODIFY `vid` int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;
--
-- AUTO_INCREMENT for table `visit_notes`
--
ALTER TABLE `visit_notes`
  MODIFY `nid` int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;
--
-- AUTO_INCREMENT for table `watchdog`
--
ALTER TABLE `watchdog`
  MODIFY `WID` int(11) NOT NULL AUTO_INCREMENT;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
