-- Database/table generation (c) D13th
-- Version 0.1
-- Author(s) : D13-th_Zayets
-- Aprover(s): D13-th_Rhyno
-- Database: `d13`
-- 

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

-- 
-- Table structure for table `d13_cell`
-- 

DROP TABLE IF EXISTS `d13_cell`;
CREATE TABLE IF NOT EXISTS `d13_cell` (
  `cell_id` int(8) unsigned zerofill NOT NULL auto_increment,
  `cell_nbr` char(3) NOT NULL default '',
  `cel_name` varchar(50) NOT NULL default '',
  `audit_add_user` varchar(32) NOT NULL default '',
  `audit_add_dttm` datetime NOT NULL default '0000-00-00 00:00:00',
  `audit_mod_user` varchar(32) NOT NULL default '',
  `audit_mod_dttm` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`cell_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci COMMENT='Cell information' AUTO_INCREMENT=1 ;

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

-- 
-- Table structure for table `d13_cell_flight`
-- 

DROP TABLE IF EXISTS `d13_cell_flight`;
CREATE TABLE IF NOT EXISTS `d13_cell_flight` (
  `flight_id` int(8) unsigned zerofill NOT NULL default '00000000',
  `cell_id` int(8) unsigned zerofill NOT NULL default '00000000',
  `audit_add_user` varchar(32) collate latin1_general_ci NOT NULL default '',
  `audit_add_dttm` datetime NOT NULL default '0000-00-00 00:00:00',
  `audit_mod_user` varchar(32) collate latin1_general_ci NOT NULL default '',
  `audit_mod_dttm` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`flight_id`,`cell_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci COMMENT='Cells per flight';

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

-- 
-- Table structure for table `d13_flight`
-- 

DROP TABLE IF EXISTS `d13_flight`;
CREATE TABLE IF NOT EXISTS `d13_flight` (
  `flight_id` int(8) unsigned zerofill NOT NULL auto_increment,
  `flight_nbr` varchar(50) default NULL,
  `flight_name` varchar(100) default NULL,
  `audit_add_user` varchar(32) NOT NULL default '',
  `audit_add_dttm` datetime NOT NULL default '0000-00-00 00:00:00',
  `audit_mod_user` varchar(32) NOT NULL default '',
  `audit_mod_dttm` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`flight_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=1 ;

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

-- 
-- Table structure for table `d13_mission`
-- 

DROP TABLE IF EXISTS `d13_mission`;
CREATE TABLE IF NOT EXISTS `d13_mission` (
  `mission_id` int(12) unsigned zerofill NOT NULL auto_increment,
  `mission_name` varchar(100) collate latin1_general_ci NOT NULL default '',
  `mission_type` int(8) unsigned zerofill NOT NULL default '00000000',
  `mission_host` varchar(100) collate latin1_general_ci default NULL,
  `audit_add_user` varchar(32) collate latin1_general_ci NOT NULL default '',
  `audit_add_dttm` datetime NOT NULL default '0000-00-00 00:00:00',
  `audit_mod_user` varchar(32) collate latin1_general_ci NOT NULL default '',
  `audit_mod_dttm` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`mission_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci COMMENT='Mission information' AUTO_INCREMENT=1 ;

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

-- 
-- Table structure for table `d13_outcome`
-- 

DROP TABLE IF EXISTS `d13_outcome`;
CREATE TABLE IF NOT EXISTS `d13_outcome` (
  `outcome` tinyint(4) unsigned zerofill NOT NULL auto_increment,
  `descr` varchar(254) collate latin1_general_ci NOT NULL default '',
  `audit_add_user` varchar(32) collate latin1_general_ci NOT NULL default '',
  `audit_add_dttm` datetime NOT NULL default '0000-00-00 00:00:00',
  `audit_mod_user` varchar(32) collate latin1_general_ci NOT NULL default '',
  `audit_mod_dttm` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`outcome`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci COMMENT='Outcome code list' AUTO_INCREMENT=1 ;

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

-- 
-- Table structure for table `d13_person`
-- 

DROP TABLE IF EXISTS `d13_person`;
CREATE TABLE IF NOT EXISTS `d13_person` (
  `person_id` int(8) unsigned zerofill NOT NULL auto_increment,
  `person_first_name` varchar(100) collate latin1_general_ci NOT NULL default '',
  `person_last_name` varchar(100) collate latin1_general_ci NOT NULL default '',
  `audit_add_user` varchar(32) collate latin1_general_ci NOT NULL default '',
  `audit_add_dttm` datetime NOT NULL default '0000-00-00 00:00:00',
  `audit_mod_user` varchar(32) collate latin1_general_ci NOT NULL default '',
  `audit_mod_dttm` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`person_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci COMMENT='Person information' AUTO_INCREMENT=1 ;

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

-- 
-- Table structure for table `d13_pilot`
-- 

DROP TABLE IF EXISTS `d13_pilot`;
CREATE TABLE IF NOT EXISTS `d13_pilot` (
  `pilot_id` int(10) unsigned zerofill NOT NULL auto_increment,
  `pilot_name` varchar(50) NOT NULL default '',
  `audit_add_user` varchar(32) NOT NULL default '',
  `audit_add_dttm` datetime NOT NULL default '0000-00-00 00:00:00',
  `audit_mod_user` varchar(32) NOT NULL default '',
  `audit_mod_dttm` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`pilot_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci COMMENT='Pilot information' AUTO_INCREMENT=1 ;

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

-- 
-- Table structure for table `d13_pilot_cell`
-- 

DROP TABLE IF EXISTS `d13_pilot_cell`;
CREATE TABLE IF NOT EXISTS `d13_pilot_cell` (
  `cell_id` int(8) unsigned zerofill NOT NULL default '00000000',
  `pilot_id` int(8) unsigned zerofill NOT NULL default '00000000',
  `audit_add_user` varchar(32) collate latin1_general_ci NOT NULL default '',
  `audit_add_dttm` datetime NOT NULL default '0000-00-00 00:00:00',
  `audit_mod_user` varchar(32) collate latin1_general_ci NOT NULL default '',
  `audit_mod_dttm` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`cell_id`,`pilot_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci COMMENT='Pilots per cell';

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

-- 
-- Table structure for table `d13_pilot_mission`
-- 

DROP TABLE IF EXISTS `d13_pilot_mission`;
CREATE TABLE IF NOT EXISTS `d13_pilot_mission` (
  `pilot_id` int(8) NOT NULL default '0',
  `mission_id` int(10) NOT NULL default '0',
  `time_hrs` decimal(10,0) NOT NULL default '0',
  `eaa_kills` decimal(10,0) NOT NULL default '0',
  `eag_kills` decimal(10,0) NOT NULL default '0',
  `faa_kills` decimal(10,0) NOT NULL default '0',
  `fag_kills` decimal(10,0) NOT NULL default '0',
  `chu_kills` tinyint(4) NOT NULL default '0',
  `ammo_spent` int(12) NOT NULL default '0',
  `a_hit` int(12) NOT NULL default '0',
  `g_hit` int(12) NOT NULL default '0',
  `outcome` tinyint(4) NOT NULL default '0',
  `rule` tinyint(4) NOT NULL default '0',
  `audit_add_user` varchar(32) collate latin1_general_ci NOT NULL default '',
  `audit_add_dttm` datetime NOT NULL default '0000-00-00 00:00:00',
  `audit_mod_user` varchar(32) collate latin1_general_ci NOT NULL default '',
  `audit_mod_dttm` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`pilot_id`,`mission_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci COMMENT='Pilot stats per mission';

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

-- 
-- Table structure for table `d13_pilot_person`
-- 

DROP TABLE IF EXISTS `d13_pilot_person`;
CREATE TABLE IF NOT EXISTS `d13_pilot_person` (
  `pilot_id` int(8) unsigned zerofill NOT NULL default '00000000',
  `person_id` int(8) unsigned zerofill NOT NULL default '00000000',
  `audit_add_user` varchar(32) NOT NULL default '',
  `audit_add_dttm` datetime NOT NULL default '0000-00-00 00:00:00',
  `audit_mod_user` varchar(32) NOT NULL default '',
  `audit_mod_dttm` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`pilot_id`,`person_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci COMMENT='Pilots per person';

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

-- 
-- Table structure for table `d13_rule`
-- 

DROP TABLE IF EXISTS `d13_rule`;
CREATE TABLE IF NOT EXISTS `d13_rule` (
  `rule` tinyint(4) NOT NULL auto_increment,
  `descr` varchar(254) collate latin1_general_ci NOT NULL default '',
  `audit_add_user` varchar(32) collate latin1_general_ci NOT NULL default '',
  `audit_add_dttm` datetime NOT NULL default '0000-00-00 00:00:00',
  `audit_mod_user` varchar(32) collate latin1_general_ci NOT NULL default '',
  `audit_mod_dttm` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`rule`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci COMMENT='Rule definiction code' AUTO_INCREMENT=1 ;

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

-- 
-- Table structure for table `d13_squad`
-- 

DROP TABLE IF EXISTS `d13_squad`;
CREATE TABLE IF NOT EXISTS `d13_squad` (
  `squad_id` int(8) unsigned zerofill NOT NULL auto_increment,
  `squad_no` varchar(50) collate latin1_general_ci default NULL,
  `squad_name` varchar(100) collate latin1_general_ci default NULL,
  `audit_add_user` varchar(32) collate latin1_general_ci NOT NULL default '',
  `audit_add_dttm` datetime NOT NULL default '0000-00-00 00:00:00',
  `audit_mod_user` varchar(32) collate latin1_general_ci NOT NULL default '',
  `audit_mod_dttm` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`squad_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=1 ;

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

-- 
-- Table structure for table `d13_squad_flight`
-- 

DROP TABLE IF EXISTS `d13_squad_flight`;
CREATE TABLE IF NOT EXISTS `d13_squad_flight` (
  `squad_id` int(8) unsigned zerofill NOT NULL default '00000000',
  `flight_id` int(8) unsigned zerofill NOT NULL default '00000000',
  `audit_add_user` varchar(32) collate latin1_general_ci NOT NULL default '',
  `audit_add_dttm` datetime NOT NULL default '0000-00-00 00:00:00',
  `audit_mod_user` varchar(32) collate latin1_general_ci NOT NULL default '',
  `audit_mod_dttm` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`squad_id`,`flight_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci COMMENT='Flights per squad';