# GRANT select, insert, update, delete, drop, create, alter ON test.* to test@localhost IDENTIFIED BY 'asdf';

DROP DATABASE IF EXISTS test;
CREATE DATABASE test;
USE test;

DROP TABLE IF EXISTS category_tbl;
CREATE TABLE category_tbl (
    category_id smallint(6) NOT NULL auto_increment,
    category_name varchar(255) NOT NULL default '',
    added_datetime datetime NOT NULL default '0000-00-00 00:00:00',
    PRIMARY KEY (category_id),
    KEY category_name (category_name)
) TYPE=MyISAM;

DROP TABLE IF EXISTS article_category_tbl;
CREATE TABLE article_category_tbl (
    article_id smallint(6) NOT NULL default '0',
    category_id smallint(6) NOT NULL default '0',
    KEY article_id (article_id),
    KEY category_id (category_id)
) TYPE=MyISAM;

DROP TABLE IF EXISTS glossary_category_tbl;
CREATE TABLE glossary_category_tbl (
    glossary_id smallint(6) NOT NULL default '0',
    category_id smallint(6) NOT NULL default '0',
    KEY glossary_id (glossary_id),
    KEY category_id (category_id)
) TYPE=MyISAM;

DROP TABLE IF EXISTS article_tbl;
CREATE TABLE article_tbl (
    article_id smallint(6) NOT NULL auto_increment,
    title varchar(255) NOT NULL default '',
    author varchar(255) NOT NULL default '',
    pub_date date NOT NULL default '0000-00-00',
    img_file varchar(255) NOT NULL default '',
    source varchar(255) NOT NULL default '',
    summary text NOT NULL,
    content mediumtext NOT NULL,
    featured enum('true') default NULL,
    publish enum('true') NOT NULL default 'true',
    added_datetime datetime NOT NULL default '0000-00-00 00:00:00',
    PRIMARY KEY  (article_id),
    KEY pub_date (pub_date),
    KEY publish (publish),
    KEY featured (featured),
    FULLTEXT KEY content (content)
) TYPE=MyISAM;

DROP TABLE IF EXISTS glossary_tbl;
CREATE TABLE glossary_tbl (
    glossary_id smallint(6) NOT NULL auto_increment,
    term varchar(255) NOT NULL default '',
    definition text NOT NULL,
    img_file varchar(255) NOT NULL default '',
    urllinks text NOT NULL,
    author varchar(255) NOT NULL default '',
    pub_date text NOT NULL,
    publish enum('true') NOT NULL default 'true',
    added_datetime datetime NOT NULL default '0000-00-00 00:00:00',
    PRIMARY KEY (glossary_id),
    KEY term (term),
    KEY publish (publish)
) TYPE=MyISAM;

DROP TABLE IF EXISTS access_site_tbl;
CREATE TABLE access_site_tbl (
    access_site_id smallint(6) NOT NULL auto_increment,
    site_name varchar(255) NOT NULL default '',
    owner varchar(255) NOT NULL default '',
    city varchar(255) NOT NULL default '',
    country varchar(255) NOT NULL default '',
    land_use enum('Public','Private') default NULL,
    acres float NOT NULL default '0',
    rivermile_start float NOT NULL default '0',
    rivermile_end float NOT NULL default '0',
    gps_lat float NOT NULL default '0',
    gps_lon float NOT NULL default '0',
    gps_alt float NOT NULL default '0',
    location_description text NOT NULL,
    site_description text NOT NULL,
    topology_hydrology text NOT NULL,
    vegitation_wildlife text NOT NULL,
    current_land_use text NOT NULL,
    adjacent_land_use text NOT NULL,
    access_via enum('Public road','River only') default NULL,
    access_description text NOT NULL,
    facility_signage enum('Entrance','Directional','Facility Rules','Services') default NULL,
    facility_boating enum('Marina','Boat Ramp(s)','Manual Put-in/Put-out','Boat Dock/Landing','Boat Trailer Parking') default NULL,
    facility_parking enum('Paved Lot','Gravel Lot','Dirt Lot') default NULL,
    facility_pedestrian_access enum('ADA Accessible','Paved Walkways','Trails') default NULL,
    facility_picnic_sites enum('Picnic Table(s)','Picnic Shelter(s)','Barbecue Grill(s)/Pit(s)','Turf Area','Tree Shaded') default NULL,
    facility_water enum('Potable Water','Drinking Fountains') default NULL,
    facility_bathrooms enum('Flush Toilets & Sinks','Porta-Potties','Showers') default NULL,
    facility_other enum('Public Phones','Amphitheater','Store','Resturant','Informational kiosk','Bar/Tavern/Pub') default NULL,
    activities enum('Motor Boating','Kayaking/Canoeing','Fishing','Hunting','Swimming','Tubing','Birding','Photography','Walking Trails','Hiking Trails','Nature Observation','Nature Walk') default NULL,
    activities_tent_sites enum('true') default NULL,
    activities_tent_sites_num smallint(6) NOT NULL default '0',
    activities_tent_sites_reservable enum('true') default NULL,
    activities_group_camp_sites enum('true') default NULL,
    activities_group_camp_sites_num smallint(6) NOT NULL default '0',
    activities_group_camp_sites_reservable enum('true') default NULL,
    activities_trailer_sites enum('true') default NULL,
    activities_trailer_sites_num smallint(6) NOT NULL default '0',
    activities_trailer_sites_reservable enum('true') default NULL,
    activities_rc_sites enum('true') default NULL,
    activities_rc_sites_num smallint(6) NOT NULL default '0',
    activities_rc_sites_reservable enum('true') default NULL,
    activities_cabins enum('true') default NULL,
    activities_cabins_num smallint(6) NOT NULL default '0',
    activities_cabins_reservable enum('true') default NULL,
    added_datetime datetime NOT NULL default '0000-00-00 00:00:00',
    PRIMARY KEY  (access_site_id),
    KEY gps_lat (gps_lat),
    KEY gps_lon (gps_lon),
    KEY gps_alt (gps_alt)
) TYPE=MyISAM;

DROP TABLE IF EXISTS pr_tbl;
CREATE TABLE pr_tbl (
    pr_id smallint(6) NOT NULL auto_increment,
    pub_date date NOT NULL default '0000-00-00',
    source varchar(255) NOT NULL default '',
    source_logo varchar(255) NOT NULL default '',
    title varchar(255) NOT NULL default '',
    pullquote text NOT NULL,
    story mediumtext NOT NULL,
    featured enum('true') default NULL,
    added_datetime datetime NOT NULL default '0000-00-00 00:00:00',
    article_category int(11) NOT NULL default '0',
    order_id int(11) NOT NULL default '0',
    PRIMARY KEY (pr_id),
    KEY pub_date (pub_date),
    KEY featured (featured)
) TYPE=MyISAM;

DROP TABLE IF EXISTS testimonial_tbl;
CREATE TABLE testimonial_tbl (
    testimonial_id smallint(6) NOT NULL auto_increment,
    pub_date date NOT NULL default '0000-00-00',
    source_name varchar(255) NOT NULL default '',
    source_company varchar(255) NOT NULL default '',
    source_logo varchar(255) NOT NULL default '',
    summary text NOT NULL,
    content mediumtext NOT NULL,
    featured enum('true') default NULL,
    added_datetime datetime NOT NULL default '0000-00-00 00:00:00',
    article_category int(11) NOT NULL default '0',
    order_id int(11) NOT NULL default '0',
    publish enum('true') default NULL,
    PRIMARY KEY (testimonial_id),
    KEY pub_date (pub_date),
    KEY publish (publish)
) TYPE=MyISAM;

DROP TABLE IF EXISTS admin_tbl;
CREATE TABLE admin_tbl (
    admin_id smallint(6) NOT NULL auto_increment,
    username varchar(255) NOT NULL default '',
    userpass varchar(255) NOT NULL default '',
    priv enum('root','admin','editor') default NULL,
    seconds_online int(11) NOT NULL default '0',
    added_datetime datetime NOT NULL default '0000-00-00 00:00:00',
    last_login_datetime datetime NOT NULL default '0000-00-00 00:00:00',
    last_access_datetime datetime NOT NULL default '0000-00-00 00:00:00',
    last_login_ip varchar(255) NOT NULL default '0.0.0.0',
    PRIMARY KEY (admin_id),
    KEY username (username),
    KEY userpass (userpass)
) TYPE=MyISAM;

DROP TABLE IF EXISTS textnode_tbl;
CREATE TABLE textnode_tbl (
    textnode_id smallint(6) NOT NULL auto_increment,
    short_title varchar(255) NOT NULL default '',
    long_title varchar(255) NOT NULL default '',
    summary text NOT NULL,
    content mediumtext NOT NULL,
    publish enum('true') NOT NULL default 'true',
    PRIMARY KEY (textnode_id),
    KEY publish (publish)
) TYPE=MyISAM;

DROP TABLE IF EXISTS node_tbl;
CREATE TABLE node_tbl (
    parent_type enum('textnode','box_spot') default NULL,
    parent_id mediumint(6) NOT NULL default '0',
    child_type enum('textnode','product') default NULL,
    child_id mediumint(6) NOT NULL default '0',
    relationship_type enum('container','link','attribute') default NULL,
    subnode_quantity mediumint(6) NOT NULL default '0',
    title varchar(255) NOT NULL default '',
    KEY parent_type (parent_type,parent_id),
    KEY child_type (child_type,child_id),
    KEY relationship_type (relationship_type)
) TYPE=MyISAM;

DROP TABLE IF EXISTS session_tbl;
CREATE TABLE session_tbl (
    session_id char(32) NOT NULL default '',
    session_data mediumtext NOT NULL,
    last_access timestamp(14) NOT NULL,
    PRIMARY KEY (session_id),
    KEY last_access (last_access)
) TYPE=MyISAM;


#########################################################################

REPLACE INTO admin_tbl VALUES (
    '1',
    'q',
    md5(''),
    'root',
    '0',
    NOW(),
    '0000-00-00 00:00:00',
    '0000-00-00 00:00:00',
    '0.0.0.0'
);


REPLACE INTO node_tbl VALUES (
    'textnode',
    '0',
    'textnode',
    '1',
    NULL,
    '0',
    'root'
);

REPLACE INTO textnode_tbl VALUES (
    '1',
    'root',
    'this is the root node',
    'summary',
    'content',
    'true'
);

INSERT INTO category_tbl VALUES ('', 'test category 1', NOW());
INSERT INTO category_tbl VALUES ('', 'test category asdf', NOW());
INSERT INTO category_tbl VALUES ('', 'test category 2', NOW());
INSERT INTO category_tbl VALUES ('', 'test category qwer', NOW());
INSERT INTO category_tbl VALUES ('', 'test category 3', NOW());
INSERT INTO category_tbl VALUES ('', 'test category zxcv', NOW());
INSERT INTO category_tbl VALUES ('', 'test category 44', NOW());
INSERT INTO category_tbl VALUES ('', 'test category tt', NOW());
INSERT INTO category_tbl VALUES ('', 'test category azq', NOW());
INSERT INTO category_tbl VALUES ('', 'test category gr', NOW());