CREATE TABLE IF NOT EXISTS `config` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(24) NOT NULL,
  `value` varchar(64) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci AUTO_INCREMENT=2 ;

INSERT INTO `config` (`id`, `name`, `value`) VALUES
(1, 'db-version', '1');


CREATE TABLE IF NOT EXISTS `agorae` (
  `id` smallint NOT NULL AUTO_INCREMENT,
  `agora` varchar(64) NOT NULL,
  `cia_version_name` varchar(8) NOT NULL,
  
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;


CREATE TABLE IF NOT EXISTS `CIA` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `category` varchar(32) NOT NULL,
  `title` varchar(128) NOT NULL,
  `order` smallint NOT NULL,
  `last_change` varchar(255),
  `title2` varchar(128),
  `order2` smallint,
  `article` smallint NOT NULL,
  `article_title` varchar(128),
  `paragraph` smallint NOT NULL,
  `sentence` smallint NOT NULL,
  `sentence_text` varchar(5000) NOT NULL,
  `agora_id_start` smallint NOT NULL,
  `agora_id_end` smallint NOT NULL,

  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

CREATE TABLE IF NOT EXISTS `CIA_history` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cia_id` int(11),
  `category` varchar(32) NOT NULL,
  `title` varchar(128) NOT NULL,
  `order` smallint NOT NULL,
  `last_change` varchar(255),
  `title2` varchar(128),
  `order2` smallint,
  `article` smallint NOT NULL,
  `article_title` varchar(128),
  `paragraph` smallint NOT NULL,
  `sentence_number` smallint NOT NULL,
  `sentence` varchar(5000) NOT NULL,
  `agora_id_start` smallint NOT NULL,
  `agora_id_end` smallint NOT NULL,

  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;


CREATE TABLE IF NOT EXISTS `proposals` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uid` varchar(100) NOT NULL,
  `agora_id` smallint NOT NULL,
  `proposers` varchar(500) NOT NULL,
  `title` varchar(255) NOT NULL,
  `motivation` varchar(1000),
  `status` varchar(32),
  `jc_comment` varchar(1000),
  `submit_date` datetime,


  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;


CREATE TABLE IF NOT EXISTS `proposals_sentences` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `proposal_id` int(11) NOT NULL,
  `change_type` varchar(16) NOT NULL,
  `sentence_id` int(11),
  `new_sentence` varchar(5000) NOT NULL,
  `jc_comment` varchar(1000),
  
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;


CREATE TABLE IF NOT EXISTS `proposals_management` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `proposal_id` int(11) NOT NULL,
  `chairman_uid` varchar(100),
  `jc_member_uid` varchar(100),
  `session_type` varchar(16),
  `session_info` varchar(100),
  `result` varchar(16),
  `bodies_present` varchar(16),
  `votes_in_favour` varchar(16),
  `votes_against` varchar(16),
  `votes_abstention` varchar(16),
  
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

CREATE TABLE IF NOT EXISTS `amendments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `proposal_id` int(11) NOT NULL,
  `proposals_sentence_id` int(11) NOT NULL,
  `proposer_uid` varchar(100) NOT NULL,
  `status` varchar(16),
  `amendment` varchar(5000),
  
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
