-- phpMyAdmin SQL Dump
-- version 2.11.6
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jul 30, 2010 at 09:56 PM
-- Server version: 5.0.51
-- PHP Version: 5.2.12

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";


/*!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 utf8 */;

--
-- Database: `open311_php-api`
--

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

--
-- Table structure for table `categories`
--

CREATE TABLE IF NOT EXISTS `categories` (
  `category_id` varchar(255) NOT NULL,
  `category_name` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `group` varchar(255) NOT NULL,
  `keywords` text NOT NULL,
  `metadata` varchar(255) NOT NULL,
  `type` varchar(255) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `categories`
--

INSERT INTO `categories` (`category_id`, `category_name`, `description`, `group`, `keywords`, `metadata`, `type`) VALUES
('001', 'Cans_Left_Out_24x7', 'Garbage or recycling cans that have been left out for more than 24 hours after collection. Violators will be cited.', 'Litter Receptacles', '', 'false', 'realtime'),
('002', 'Construction_Plate_Shifted', 'Metal construction plate covering the street or sidewalk has been moved.\n', 'Street Defects', '', 'false', 'realtime'),
('003', 'Abandoned Vehicle', 'A vehicle has been unattended in a public space for more than 72 hours', 'Parking', '', 'true', 'realtime');

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

--
-- Table structure for table `category_attributes`
--

CREATE TABLE IF NOT EXISTS `category_attributes` (
  `category_id` varchar(255) NOT NULL,
  `attribute_id` varchar(255) NOT NULL,
  `variable` varchar(255) NOT NULL,
  `datatype` varchar(255) NOT NULL,
  `required` varchar(255) NOT NULL,
  `datatype_description` text NOT NULL,
  `order` int(10) NOT NULL,
  `description` text NOT NULL,
  `values` text NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `category_attributes`
--

INSERT INTO `category_attributes` (`category_id`, `attribute_id`, `variable`, `datatype`, `required`, `datatype_description`, `order`, `description`, `values`) VALUES
('003', 'DMV66', 'true', 'text', 'true', '', 1, 'What is the make of the vehicle?', '');

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

--
-- Table structure for table `reports`
--

CREATE TABLE IF NOT EXISTS `reports` (
  `report_id` int(255) NOT NULL auto_increment,
  `status` varchar(255) default NULL,
  `status_notes` text,
  `category_name` varchar(255) default NULL,
  `category_id` varchar(255) default NULL,
  `description` text,
  `agency_responsible` varchar(255) default NULL,
  `service_notice` text,
  `requested_datetime` datetime default NULL,
  `updated_datetime` datetime default NULL,
  `expected_datetime` datetime default NULL,
  `address` text,
  `address_id` varchar(255) default NULL,
  `postal_code` varchar(255) default NULL,
  `lat` double default NULL,
  `long` double default NULL,
  `email` varchar(255) default NULL,
  `device_id` varchar(255) default NULL,
  `account_id` varchar(255) default NULL,
  `first_name` varchar(255) default NULL,
  `last_name` varchar(255) default NULL,
  `phone` varchar(255) default NULL,
  `media_url` text,
  PRIMARY KEY  (`report_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=702641 ;

--
-- Dumping data for table `reports`
--

INSERT INTO `reports` (`report_id`, `status`, `status_notes`, `category_name`, `category_id`, `description`, `agency_responsible`, `service_notice`, `requested_datetime`, `updated_datetime`, `expected_datetime`, `address`, `address_id`, `postal_code`, `lat`, `long`, `email`, `device_id`, `account_id`, `first_name`, `last_name`, `phone`, `media_url`) VALUES
(698804, 'closed', '', 'RCS - DPT - Color Curb - G', '149', '', '', '', '2010-07-22 23:35:55', '0000-00-00 00:00:00', '0000-00-00 00:00:00', 'INTERSECTION  of  23RD ST  and  SAN BRUNO AVE', '', '', 37.754395, -122.40449, '', '', '', '', '', '', ''),
(698792, 'open', '', 'Damaged_curb_or_ramp', '042', '', '', '', '2010-07-22 23:05:00', '0000-00-00 00:00:00', '2010-07-30 08:30:00', 'INTERSECTION  of  22ND ST  and  SAN BRUNO AVE', '', '', 37.756954, -122.40473, '', '', '', '', '', '', ''),
(698790, 'open', '', 'Lifted_sidewalk_other', '071', '', '', '', '2010-07-22 23:02:09', '0000-00-00 00:00:00', '2010-07-30 08:30:00', 'INTERSECTION  of  23RD ST  and  UTAH ST', '', '', 37.754337, -122.40546, '', '', '', '', '', '', ''),
(702637, 'open', '', 'RCS - Escalated KB Questions Queue', '191', '', '', '', '2010-07-29 11:57:22', '0000-00-00 00:00:00', '2010-08-05 11:57:22', 'SAN FRANCISCO , CA ', '', '', 0, 0, '', '', '', '', '', '', ''),
(702636, 'open', '', 'Abandoned Vehicle', '033', '', '', '', '2010-07-29 11:56:59', '0000-00-00 00:00:00', '2010-08-06 11:56:59', 'INTERSECTION  of  23RD ST  and  TENNESSEE ST', '', '', 37.75533, -122.389, '', '', '', '', '', '', ''),
(702634, 'open', '', 'SFHA - Preventive - Priority', '310', '', '', '', '2010-07-29 11:55:07', '0000-00-00 00:00:00', '0000-00-00 00:00:00', '67  NICHOLS WAY SAN FRANCISCO , CA', '', '94124', 37.71959, -122.38728, '', '', '', '', '', '', ''),
(702638, NULL, NULL, NULL, '001', 'A large sinkhole is destroying the street', NULL, NULL, '2010-07-30 21:41:51', NULL, NULL, '1234 5th street', NULL, NULL, 37.76524078, -122.4212043, 'smit333@sfgov.edu', 'tt222111', '123456', 'john', 'smith', '111111111', 'http://farm3.static.flickr.com/2002/2212426634_5ed477a060.jpg'),
(702639, NULL, NULL, NULL, '001', 'A large sinkhole is destroying the street', NULL, NULL, '2010-07-30 21:43:04', NULL, NULL, '1234 5th street', NULL, NULL, 37.76524078, -122.4212043, 'smit333@sfgov.edu', 'tt222111', '123456', 'john', 'smith', '111111111', 'http://farm3.static.flickr.com/2002/2212426634_5ed477a060.jpg'),
(702640, NULL, NULL, NULL, '001', 'A large sinkhole is destroying the street', NULL, NULL, '2010-07-30 21:49:58', NULL, NULL, '1234 5th street', '', NULL, 37.76524078, -122.4212043, 'smit333@sfgov.edu', 'tt222111', '123456', 'john', 'smith', '111111111', 'http://farm3.static.flickr.com/2002/2212426634_5ed477a060.jpg');
