/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
DROP TABLE IF EXISTS `DEVICE_MAP`;
DROP TABLE IF EXISTS `SWITCHES`;
DROP TABLE IF EXISTS `ADDRESSES`;
CREATE TABLE `ADDRESSES` (
    address varchar(15) NOT NULL,
    router varchar(15) NOT NULL,
    mask varchar(15) NOT NULL,
    mgmt_vlan varchar(16) NOT NULL,
    location varchar(32) NOT NULL,
    used tinyint(1) DEFAULT '0',
    reserved tinyint(1) DEFAULT '0',
    PRIMARY KEY (`address`),
    KEY `router` (`router`),
    KEY `mgmt_vlan` (`mgmt_vlan`),
    KEY `location` (`location`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `SWITCHES` (
    name varchar(64) NOT NULL,
    ip_address varchar(15),
    is_idf tinyint(1) DEFAULT '0',
    pid varchar(64) NOT NULL,
    location varchar(255),
    is_stack tinyint(1) DEFAULT '0',
    ports_required integer DEFAULT '8',
    PRIMARY KEY (`name`),
    KEY `pid` (`pid`),
    UNIQUE (`ip_address`),
    CONSTRAINT `ip_address_ibfk_1` FOREIGN KEY (`ip_address`) REFERENCES `ADDRESSES` (`address`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `DEVICE_MAP` (
    serial_number varchar(64) NOT NULL,
    pid varchar(64) NOT NULL,
    mac varchar(17) DEFAULT NULL, /*Note: This is not used now. */
    max_ports int NOT NULL,
    assigned_switch varchar(64),
    checked_out tinyint(1) DEFAULT '0',
    provisioned_status tinyint(1) DEFAULT '-2',
    device_status tinyint(1) DEFAULT '0',
    should_re_ztp tinyint(1) DEFAULT '0',
    PRIMARY KEY (`serial_number`),
    KEY `assigned_switch` (`assigned_switch`),
    KEY `pid` (`pid`),
    UNIQUE (`assigned_switch`),
    CONSTRAINT `assigned_switch_ibfk_1` FOREIGN KEY (`assigned_switch`) REFERENCES `SWITCHES` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `EXCEPTIONS`;
CREATE TABLE `EXCEPTIONS` (
    name varchar(255) NOT NULL,
    pid varchar(64) NOT NULL,
    path varchar(255) NOT NULL,
    PRIMARY KEY (`name`),
    KEY `pid` (`pid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;