/**
 * Sigma Apprentissage dump
 *
 */



/* Dump of table `sa_user_type`
 * ------------------------------------------------------------*/


CREATE TABLE `sa_user_type` (
  `ID` smallint unsigned NOT NULL AUTO_INCREMENT,
  `user_type_category` varchar(64) NOT NULL DEFAULT '',
  PRIMARY KEY (`ID`),
  UNIQUE KEY `user_type_category` (`user_type_category`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `sa_user_type` ( ID, user_type_category ) VALUES ( 1, 'Administrateur' );
INSERT INTO `sa_user_type` ( ID, user_type_category ) VALUES ( 2, 'Pôle Administration' );
INSERT INTO `sa_user_type` ( ID, user_type_category ) VALUES ( 64, 'Pôle Pédagogique' );
INSERT INTO `sa_user_type` ( ID, user_type_category ) VALUES ( 65, 'Responsable pédagogique' );



/* Dump of table `sa_contacts`
 * ------------------------------------------------------------*/


CREATE TABLE `sa_contacts` (
  `ID` int unsigned NOT NULL AUTO_INCREMENT,
  `contact_person_id` int unsigned NOT NULL DEFAULT '0',
  `contact_value` varchar(100) NOT NULL DEFAULT '',
  `contact_label` varchar(60) DEFAULT NULL,
  `contact_type` smallint unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  CONSTRAINT `fk_contact_person_id` FOREIGN KEY (`contact_person_id`) REFERENCES `sa_persons`(`ID`),
  CONSTRAINT `fk_contact_type` FOREIGN KEY (`contact_type`) REFERENCES `sa_contact_type`(`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


INSERT INTO `sa_contacts` ( ID, contact_person_id, contact_value, contact_label, contact_type ) VALUES ( 1, 1, '+33688471935', NULL, 1 );



/* Dump of table `sa_contact_type`
 * ------------------------------------------------------------*/


CREATE TABLE `sa_contact_type` (
  `ID` smallint unsigned NOT NULL AUTO_INCREMENT,
  `contact_type_category` varchar(64) NOT NULL DEFAULT '',
  PRIMARY KEY (`ID`),
  UNIQUE KEY `contact_type_category` (`contact_type_category`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `sa_contact_type` ( ID, contact_type_category ) VALUES ( 1, 'Téléphone' );
INSERT INTO `sa_contact_type` ( ID, contact_type_category ) VALUES ( 2, 'Mail' );
INSERT INTO `sa_contact_type` ( ID, contact_type_category ) VALUES ( 3, 'Fax' );



/* Dump of table `sa_persons`
 * ------------------------------------------------------------*/


CREATE TABLE `sa_persons` (
  `ID` int unsigned NOT NULL AUTO_INCREMENT,
  `person_name` varchar(60) NOT NULL DEFAULT '',
  `person_firstname` varchar(60) NOT NULL DEFAULT '',
  `person_address` varchar(1024) DEFAULT NULL ,
  `person_city` varchar(100) DEFAULT NULL,
  `person_postal` varchar(10) DEFAULT NULL,
  `person_region`  smallint unsigned NOT NULL DEFAULT '0',
  `person_country`  smallint unsigned NOT NULL DEFAULT '1',
  `person_mail` varchar(100) DEFAULT NULL,
  `person_phone` varchar(24) DEFAULT NULL,
  `person_mobile` varchar(24) DEFAULT NULL,
  `person_fax` varchar(24) DEFAULT NULL,
  `person_title` varchar(256) NOT NULL DEFAULT '',
  `person_themes` varchar(512) DEFAULT NULL,
  `person_comment` text,
  `person_active` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`ID`),
  CONSTRAINT `fk_person_region` FOREIGN KEY (`person_region`) REFERENCES `sa_regions`(`ID`),
  CONSTRAINT `fk_person_country` FOREIGN KEY (`person_country`) REFERENCES `sa_countries`(`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


INSERT INTO `sa_persons` ( ID, person_name, person_firstname, person_address, person_city, person_postal, person_region, person_country, person_mail, person_phone, person_mobile, person_fax, person_title, person_themes, person_comment, person_active ) VALUES ( 1, 'Koenig', 'Sébastien', '59 rue des Archives', 'Paris', '75003', 0, 1, 'skoenig@sigma-apprentissage.fr', '+33142780375', '+33688471935', '+33956685292', 'Président de Sigma Apprentissage', NULL, NULL, 1 );
INSERT INTO `sa_persons` ( ID, person_name, person_firstname, person_address, person_city, person_postal, person_region, person_country, person_mail, person_phone, person_mobile, person_fax, person_title, person_themes, person_comment, person_active ) VALUES ( 2, 'Manière', 'Franck', '129 avenue Charles de Gaulle', 'Charleville-Mézières', '08000', 0, 1, 'manieref.cfa08@wanadoo.fr', NULL, NULL, NULL, 'Responsable Pédagogique', NULL, NULL, 1 );
INSERT INTO `sa_persons` ( ID, person_name, person_firstname, person_address, person_city, person_postal, person_region, person_country, person_mail, person_phone, person_mobile, person_fax, person_title, person_themes, person_comment, person_active ) VALUES ( 3, 'Wittmar-Dufour', 'Caroline', '29 avenue Bel Air', 'Paris', '75012', 0, 1, 'avocat@wittmardufour.com', '+33143400203', NULL, NULL, 'Avocate', NULL, 'Création de Sigma Apprentissage et acquisition du Groupe Sigma Apprentissage d\'IPESUP', 1 );
INSERT INTO `sa_persons` ( ID, person_name, person_firstname, person_address, person_city, person_postal, person_region, person_country, person_mail, person_phone, person_mobile, person_fax, person_title, person_themes, person_comment, person_active ) VALUES ( 4, 'Noël', 'Patrick', '18 rue du Cloître Notre-Dame', 'Paris', '75004', 0, 1, 'p.noel@ipesup.fr', '+33144321200', NULL, NULL, 'Directeur d\'IPESUP', NULL, 'Ex-responsable de Groupe Sigma Apprentissage', 1 );
INSERT INTO `sa_persons` ( ID, person_name, person_firstname, person_address, person_city, person_postal, person_region, person_country, person_mail, person_phone, person_mobile, person_fax, person_title, person_themes, person_comment, person_active ) VALUES ( 5, 'Saint Dizier', 'Martine', '5 rue Pasteur', 'Pinterville', '27400', 0, 1, 'martine.saint-dizier@orange.fr', '+33232252793', '+33688593142', NULL, 'Directrice de CFA', ';1;', NULL, 1 );
INSERT INTO `sa_persons` ( ID, person_name, person_firstname, person_address, person_city, person_postal, person_region, person_country, person_mail, person_phone, person_mobile, person_fax, person_title, person_themes, person_comment, person_active ) VALUES ( 6, 'Poulain', 'Virginie', '23 boulevard Diderot', 'Paris', '75012', 0, 1, 'virginie.poulain@bnpparibas.com', '+33144743350', '+33144743365', NULL, 'Chargée d\'Affaires Professionnels BNP', NULL, NULL, 1 );



/* Dump of table `sa_regions`
 * ------------------------------------------------------------*/


CREATE TABLE `sa_regions` (
  `ID` smallint unsigned NOT NULL AUTO_INCREMENT,
  `region_name` varchar(64) NOT NULL DEFAULT '',
  `region_counties` varchar(5256) NOT NULL DEFAULT '',
  PRIMARY KEY (`ID`),
  UNIQUE KEY `region_name` (`region_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `sa_regions` ( ID, region_name, region_counties ) VALUES ( 44, 'Alsace-Champagne-Ardenne-Lorraine', ';08;57;' );
INSERT INTO `sa_regions` ( ID, region_name, region_counties ) VALUES ( 75, 'Aquitaine-Limousin-Poitou-Charentes', '' );
INSERT INTO `sa_regions` ( ID, region_name, region_counties ) VALUES ( 84, 'Auvergne-Rhône-Alpes', '' );
INSERT INTO `sa_regions` ( ID, region_name, region_counties ) VALUES ( 27, 'Bourgogne-Franche-Comté', '' );
INSERT INTO `sa_regions` ( ID, region_name, region_counties ) VALUES ( 53, 'Bretagne', '' );
INSERT INTO `sa_regions` ( ID, region_name, region_counties ) VALUES ( 24, 'Centre-Val de Loire', '' );
INSERT INTO `sa_regions` ( ID, region_name, region_counties ) VALUES ( 94, 'Corse', '' );
INSERT INTO `sa_regions` ( ID, region_name, region_counties ) VALUES ( 970, 'DOM-TOM', '' );
INSERT INTO `sa_regions` ( ID, region_name, region_counties ) VALUES ( 11, 'Île-de-France', ';75;' );
INSERT INTO `sa_regions` ( ID, region_name, region_counties ) VALUES ( 76, 'Languedoc-Roussillon-Midi-Pyrénées', '' );
INSERT INTO `sa_regions` ( ID, region_name, region_counties ) VALUES ( 32, 'Nord-Pas-de-Calais-Picardie', '' );
INSERT INTO `sa_regions` ( ID, region_name, region_counties ) VALUES ( 28, 'Normandie', ';27;' );
INSERT INTO `sa_regions` ( ID, region_name, region_counties ) VALUES ( 52, 'Pays de la Loire', '' );
INSERT INTO `sa_regions` ( ID, region_name, region_counties ) VALUES ( 93, 'Provence-Alpes-Côte d\'Azur', '' );
INSERT INTO `sa_regions` ( ID, region_name, region_counties ) VALUES ( 32767, 'Non disponible', '' );



/* Dump of table `sa_countries`
 * ------------------------------------------------------------*/


CREATE TABLE `sa_countries` (
  `ID` smallint unsigned NOT NULL AUTO_INCREMENT,
  `country_name` varchar(64) NOT NULL DEFAULT '',
  PRIMARY KEY (`ID`),
  UNIQUE KEY `country_name` (`country_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `sa_countries` ( ID, country_name ) VALUES ( 1, 'France' );



/* Dump of table `sa_workers`
 * ------------------------------------------------------------*/


CREATE TABLE `sa_workers` (
  `ID` int unsigned NOT NULL AUTO_INCREMENT,
  `worker_person_id` int unsigned NOT NULL DEFAULT '0',
  `worker_diploma` varchar(255) NOT NULL DEFAULT '',
  `worker_date_signature` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `worker_last_update` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `worker_state_employee` tinyint(1) NOT NULL DEFAULT '0',
  `worker_state_employee_job` varchar(128) DEFAULT NULL,
  `worker_state_employee_payslip_url` varchar(255) DEFAULT NULL,
  `worker_foreign` tinyint(1) NOT NULL DEFAULT '0',
  `worker_contract_url` varchar(255) NOT NULL DEFAULT '',
  `worker_prefered_payment_type` smallint unsigned NOT NULL DEFAULT '2',
  `worker_prefered_payment_doc_url` varchar(255) DEFAULT NULL,
  `worker_comment` text,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `worker_person_id` (`worker_person_id`),
  UNIQUE KEY `worker_contract_url` (`worker_contract_url`),
  CONSTRAINT `fk_worker_person_id` FOREIGN KEY (`worker_person_id`) REFERENCES `sa_persons`(`ID`),
  CONSTRAINT `fk_worker_prefered_payment_type` FOREIGN KEY (`worker_prefered_payment_type`) REFERENCES `sa_payment_type`(`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


INSERT INTO `sa_workers` ( ID, worker_person_id, worker_diploma, worker_date_signature, worker_last_update, worker_state_employee, worker_state_employee_job, worker_state_employee_payslip_url, worker_foreign, worker_contract_url, worker_prefered_payment_type, worker_prefered_payment_doc_url, worker_comment ) VALUES ( 1, 5, 'DESS Ingénierie de la Formation', '2015-12-08 12:00:00', '2015-12-08 12:00:00', 0, NULL, NULL, 0, 'test_contract', 2, NULL, NULL );



/* Dump of table `sa_sites`
 * ------------------------------------------------------------*/


CREATE TABLE `sa_sites` (
  `ID` int unsigned NOT NULL AUTO_INCREMENT,
  `site_name` varchar(100) NOT NULL DEFAULT '',
  `site_category` smallint unsigned NOT NULL DEFAULT '0',
  `site_address` varchar(1024) NOT NULL DEFAULT '',
  `site_city` varchar(100) NOT NULL DEFAULT '',
  `site_postal` varchar(10) NOT NULL DEFAULT '',
  `site_region` smallint unsigned NOT NULL DEFAULT '0',
  `site_country` smallint unsigned NOT NULL DEFAULT '1',
  `site_mail` varchar(100) DEFAULT NULL,
  `site_phone` varchar(24) NOT NULL DEFAULT '',
  `site_fax` varchar(24) DEFAULT NULL,
  `site_themes` varchar(512) DEFAULT NULL,
  `site_comment` text,
  `site_active` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`ID`),
  CONSTRAINT `fk_site_category` FOREIGN KEY (`site_category`) REFERENCES `sa_site_type`(`ID`),
  CONSTRAINT `fk_site_region` FOREIGN KEY (`site_region`) REFERENCES `sa_regions`(`ID`),
  CONSTRAINT `fk_site_country` FOREIGN KEY (`site_country`) REFERENCES `sa_countries`(`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


INSERT INTO `sa_sites` ( ID, site_name, site_category, site_address, site_city, site_postal, site_region, site_country, site_mail, site_phone, site_fax, site_themes, site_comment, site_active ) VALUES ( 1, 'Sigma Apprentissage', 1, '59 rue des Archives', 'Paris', '75003', 0, 1, 'sigma@sigma-apprentissage.fr', '+33142780375', '+33956685292', NULL, 'Siège social de Sigma Apprentissage', 1 );
INSERT INTO `sa_sites` ( ID, site_name, site_category, site_address, site_city, site_postal, site_region, site_country, site_mail, site_phone, site_fax, site_themes, site_comment, site_active ) VALUES ( 2, 'CFA Interprofessionnel des Ardennes', 1024, '129 avenue Charles de Gaulle', 'Charleville-Mézières', '08000', 0, 1, 'cfa08.contact@orange.fr', '+33324330224', '+33324339094', NULL, NULL, 1 );
INSERT INTO `sa_sites` ( ID, site_name, site_category, site_address, site_city, site_postal, site_region, site_country, site_mail, site_phone, site_fax, site_themes, site_comment, site_active ) VALUES ( 3, 'Centre des Impôts', 127, '10 rue Michel le Comte', 'Paris', '75003', 0, 1, '', '+33144595959', '', NULL, NULL, 1 );
INSERT INTO `sa_sites` ( ID, site_name, site_category, site_address, site_city, site_postal, site_region, site_country, site_mail, site_phone, site_fax, site_themes, site_comment, site_active ) VALUES ( 4, '1\&1', 128, '7 place de la Gare', 'Sarreguemines', '57200', 0, 1, '', '+33970808911', '', NULL, 'Pour l\'aide en ligne passez par le site http://assistance.1and1.fr/contact?hc=Control-Panel', 1 );



/* Dump of table `sa_site_type`
 * ------------------------------------------------------------*/


CREATE TABLE `sa_site_type` (
  `ID` smallint unsigned NOT NULL AUTO_INCREMENT,
  `site_type_category` varchar(64) NOT NULL DEFAULT '',
  PRIMARY KEY (`ID`),
  UNIQUE KEY `site_type_category` (`site_type_category`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `sa_site_type` ( ID, site_type_category ) VALUES ( 1, 'Siège' );
INSERT INTO `sa_site_type` ( ID, site_type_category ) VALUES ( 127, 'Administration' );
INSERT INTO `sa_site_type` ( ID, site_type_category ) VALUES ( 128, 'Commercial' );
INSERT INTO `sa_site_type` ( ID, site_type_category ) VALUES ( 1024, 'CFA' );



/* Dump of table `sa_assignments`
 * ------------------------------------------------------------*/


CREATE TABLE `sa_assignments` (
  `ID` int unsigned NOT NULL AUTO_INCREMENT,
  `assignment_person_id` int unsigned NOT NULL DEFAULT '0',
  `assignment_site_id` int unsigned NOT NULL DEFAULT '0',
  `assignment_category` smallint unsigned NOT NULL DEFAULT '0',
  `assignment_active` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`ID`),
  CONSTRAINT `fk_assignment_person_id` FOREIGN KEY (`assignment_person_id`) REFERENCES `sa_persons`(`ID`),
  CONSTRAINT `fk_assignment_site_id` FOREIGN KEY (`assignment_site_id`) REFERENCES `sa_sites`(`ID`),
  CONSTRAINT `fk_assignment_category` FOREIGN KEY (`assignment_category`) REFERENCES `sa_actor_type`(`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


INSERT INTO `sa_assignments` ( ID, assignment_person_id, assignment_site_id, assignment_category, assignment_active ) VALUES ( 1, 1, 1, 1, 1 );
INSERT INTO `sa_assignments` ( ID, assignment_person_id, assignment_site_id, assignment_category, assignment_active ) VALUES ( 2, 2, 2, 2, 1 );



/* Dump of table `sa_actor_type`
 * ------------------------------------------------------------*/


CREATE TABLE `sa_actor_type` (
  `ID` smallint unsigned NOT NULL AUTO_INCREMENT,
  `actor_type_category` varchar(64) NOT NULL DEFAULT '',
  PRIMARY KEY (`ID`),
  UNIQUE KEY `actor_type_category` (`actor_type_category`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


INSERT INTO `sa_actor_type` ( ID, actor_type_category ) VALUES ( 1, 'Président / Directeur' );
INSERT INTO `sa_actor_type` ( ID, actor_type_category ) VALUES ( 2, 'Responsable Pédagogique' );
INSERT INTO `sa_actor_type` ( ID, actor_type_category ) VALUES ( 3, 'Responsable Financier / Comptable' );
INSERT INTO `sa_actor_type` ( ID, actor_type_category ) VALUES ( 4, 'Responsable Administratif' );
INSERT INTO `sa_actor_type` ( ID, actor_type_category ) VALUES ( 5, 'Secrétaire' );
INSERT INTO `sa_actor_type` ( ID, actor_type_category ) VALUES ( 4093, 'Interlocuteur' );
INSERT INTO `sa_actor_type` ( ID, actor_type_category ) VALUES ( 4094, 'Interlocuteur principal' );
INSERT INTO `sa_actor_type` ( ID, actor_type_category ) VALUES ( 4095, 'Formateur' );
INSERT INTO `sa_actor_type` ( ID, actor_type_category ) VALUES ( 4096, 'Stagiaire' );




/* Dump of table `sa_formations`
 * ------------------------------------------------------------*/


CREATE TABLE `sa_formations` (
  `ID` int unsigned NOT NULL AUTO_INCREMENT,
  `formation_title` varchar(255) NOT NULL DEFAULT '',
  `formation_category` smallint unsigned NOT NULL DEFAULT '0',
  `formation_themes` varchar(512) NOT NULL DEFAULT '',
  `formation_comment` text,
  `formation_active` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`ID`),
  UNIQUE KEY `formation_title` (`formation_title`),
  CONSTRAINT `fk_formation_category` FOREIGN KEY (`formation_category`) REFERENCES `sa_formation_type`(`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


INSERT INTO `sa_formations` ( ID, formation_title, formation_category, formation_themes, formation_comment, formation_active ) VALUES ( 1, 'Débuter dans le métier de formateur en CFA', 1, ';1;2;3;6;', NULL, 1 );
INSERT INTO `sa_formations` ( ID, formation_title, formation_category, formation_themes, formation_comment, formation_active ) VALUES ( 2, 'Faire de l\'hétérogénéité un atout pédagogique', 2, ';4;5;6;', NULL, 1 );



/* Dump of table `sa_themes`
 * ------------------------------------------------------------*/


CREATE TABLE `sa_themes` (
  `ID` int unsigned NOT NULL AUTO_INCREMENT,
  `theme_label` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`ID`),
  UNIQUE KEY `theme_label` (`theme_label`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


INSERT INTO `sa_themes` ( ID, theme_label ) VALUES ( 1, 'cfa' );
INSERT INTO `sa_themes` ( ID, theme_label ) VALUES ( 2, 'formateur' );
INSERT INTO `sa_themes` ( ID, theme_label ) VALUES ( 3, 'début' );
INSERT INTO `sa_themes` ( ID, theme_label ) VALUES ( 4, 'hétérogénéité' );
INSERT INTO `sa_themes` ( ID, theme_label ) VALUES ( 5, 'atout' );
INSERT INTO `sa_themes` ( ID, theme_label ) VALUES ( 6, 'pédagogie' );



/* Dump of table `sa_formation_type`
 * ------------------------------------------------------------*/


CREATE TABLE `sa_formation_type` (
  `ID` smallint unsigned NOT NULL AUTO_INCREMENT,
  `formation_type_category` varchar(64) NOT NULL DEFAULT '',
  PRIMARY KEY (`ID`),
  UNIQUE KEY `formation_type_category` (`formation_type_category`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


INSERT INTO `sa_formation_type` ( ID, formation_type_category ) VALUES ( 1, 'Pédagogie de l\'alternance' );
INSERT INTO `sa_formation_type` ( ID, formation_type_category ) VALUES ( 2, 'Pédagogie générale' );



/* Dump of table `sa_contacts`
 * ------------------------------------------------------------*/


CREATE TABLE `sa_contracts` (
  `ID` int unsigned NOT NULL AUTO_INCREMENT,
  `contract_category` smallint unsigned NOT NULL DEFAULT '0',
  `contract_interlocutor_id` int unsigned NOT NULL DEFAULT '0',
  `contract_date_signature` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `contract_abstract` varchar(1024) NOT NULL DEFAULT '',
  `contract_url` varchar(255) NOT NULL DEFAULT '',
  `contract_comment` text,
  `contract_active` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`ID`),
  UNIQUE KEY `contract_url` (`contract_url`),
  CONSTRAINT `fk_contract_category` FOREIGN KEY (`contract_category`) REFERENCES `sa_contract_type`(`ID`),
  CONSTRAINT `fk_contract_interlocutor_id` FOREIGN KEY (`contract_interlocutor_id`) REFERENCES `sa_assignments`(`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


INSERT INTO `sa_contracts` ( ID, contract_category, contract_interlocutor_id, contract_date_signature, contract_abstract, contract_url, contract_comment, contract_active ) VALUES ( 1, 1, 2, '2015-12-08 12:00:00', 'Faire de l\'hétérogénéité un atout pédagogique (2j) + suivi (1j)', '/CFA Ardennes/20151210-recu/Convention.pdf', NULL, 1 );



/* Dump of table `sa_contract_type`
 * ------------------------------------------------------------*/


CREATE TABLE `sa_contract_type` (
  `ID` smallint unsigned NOT NULL AUTO_INCREMENT,
  `contract_type_category` varchar(64) NOT NULL DEFAULT '',
  PRIMARY KEY (`ID`),
  UNIQUE KEY `contract_type_category` (`contract_type_category`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


INSERT INTO `sa_contract_type` ( ID, contract_type_category ) VALUES ( 1, 'Convention' );



/* Dump of table `sa_actions`
 * ------------------------------------------------------------*/


CREATE TABLE `sa_actions` (
  `ID` int unsigned NOT NULL AUTO_INCREMENT,
  `action_contract_id` int unsigned NOT NULL DEFAULT '0',
  `action_formation_id` int unsigned NOT NULL DEFAULT '0',
  `action_site_id` int unsigned NOT NULL DEFAULT '0',
  `action_date_start` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `action_date_end` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `action_nb_hours` smallint unsigned NOT NULL DEFAULT '6',
  `action_comment` text,
  PRIMARY KEY (`ID`),
  CONSTRAINT `fk_action_contract_id` FOREIGN KEY (`action_contract_id`) REFERENCES `sa_contracts`(`ID`),
  CONSTRAINT `fk_action_formation_id` FOREIGN KEY (`action_formation_id`) REFERENCES `sa_formations`(`ID`),
  CONSTRAINT `fk_action_site_id` FOREIGN KEY (`action_site_id`) REFERENCES `sa_sites`(`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


INSERT INTO `sa_actions` ( ID, action_contract_id, action_formation_id, action_site_id, action_date_start, action_date_end, action_nb_hours, action_comment ) VALUES ( 1, 1, 2, 2, '2105-12-14 09:00:00', '2015-12-15 16:30:00', 12, NULL );



/* Dump of table `sa_actors`
 * ------------------------------------------------------------*/


CREATE TABLE `sa_actors` (
  `ID` int unsigned NOT NULL AUTO_INCREMENT,
  `actor_action_id` int unsigned NOT NULL DEFAULT '0',
  `actor_person_id` int unsigned NOT NULL DEFAULT '0',
  `actor_type_id` smallint unsigned NOT NULL DEFAULT '0',
  `actor_comment` text,
  PRIMARY KEY (`ID`),
  CONSTRAINT `fk_actor_action_id` FOREIGN KEY (`actor_action_id`) REFERENCES `sa_actions`(`ID`),
  CONSTRAINT `fk_actor_person_id` FOREIGN KEY (`actor_person_id`) REFERENCES `sa_persons`(`ID`),
  CONSTRAINT `fk_actor_type_id` FOREIGN KEY (`actor_type_id`) REFERENCES `sa_actor_type`(`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


INSERT INTO `sa_actors` ( ID, actor_action_id, actor_person_id, actor_type_id, actor_comment ) VALUES ( 1, 1, 2, 3, NULL );



/* Dump of table `sa_recurrent_payments`
 * ------------------------------------------------------------*/


CREATE TABLE `sa_recurrent_payments` (
  `ID` int unsigned NOT NULL AUTO_INCREMENT,
  `recurrent_payment_amount` float NOT NULL DEFAULT '0.0',
  `recurrent_payment_person_id` int unsigned DEFAULT NULL,
  `recurrent_payment_site_id` int unsigned DEFAULT NULL,
  `recurrent_payment_date_signature` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `recurrent_payment_next_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `recurrent_payment_iterator` smallint unsigned NOT NULL DEFAULT '1',
  `recurrent_payment_unit` varchar(24) NOT NULL DEFAULT 'MONTH',
  `recurrent_payment_ref` varchar(256) DEFAULT NULL,
  `recurrent_payment_url` varchar(255) NOT NULL DEFAULT '',
  `recurrent_payment_comment` text,
  `recurrent_payment_active` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`ID`),
  UNIQUE KEY `recurrent_payment_url` (`recurrent_payment_url`),
  CONSTRAINT `fk_recurrent_payment_person_id` FOREIGN KEY (`recurrent_payment_person_id`) REFERENCES `sa_persons`(`ID`),
  CONSTRAINT `fk_recurrent_payment_site_id` FOREIGN KEY (`recurrent_payment_site_id`) REFERENCES `sa_sites`(`ID`),
  CONSTRAINT `ck_recurrent_payment_origin` CHECK ((`recurrent_payment_person_id` IS NOT NULL) OR (`recurrent_payment_site_id` IS NOT NULL))
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


INSERT INTO `sa_recurrent_payments` ( ID, recurrent_payment_amount, recurrent_payment_person_id, recurrent_payment_site_id, recurrent_payment_date_signature, recurrent_payment_next_date, recurrent_payment_iterator, recurrent_payment_unit, recurrent_payment_ref, recurrent_payment_url, recurrent_payment_comment, recurrent_payment_active ) VALUES ( 1, 20.8, 6, NULL, '2016-02-05 12:00:00', '2016-02-05 12:00:00', 1, 'MONTH', 'BNP Frais Compte Pro', 'test1', 'Frais de gestion de banque à 50% pendant un an', 1 );
INSERT INTO `sa_recurrent_payments` ( ID, recurrent_payment_amount, recurrent_payment_person_id, recurrent_payment_site_id, recurrent_payment_date_signature, recurrent_payment_next_date, recurrent_payment_iterator, recurrent_payment_unit, recurrent_payment_ref, recurrent_payment_url, recurrent_payment_comment, recurrent_payment_active ) VALUES ( 2, 43.06, 3, NULL, '2016-07-19 12:00:00', '2016-07-19 12:00:00', 1, 'YEAR', '1\&1 Abonnement annuel', 'test2', 'Frais d\'abonnement 1\&1 basic 12 mois', 1 );
INSERT INTO `sa_recurrent_payments` ( ID, recurrent_payment_amount, recurrent_payment_person_id, recurrent_payment_site_id, recurrent_payment_date_signature, recurrent_payment_next_date, recurrent_payment_iterator, recurrent_payment_unit, recurrent_payment_ref, recurrent_payment_url, recurrent_payment_comment, recurrent_payment_active ) VALUES ( 3, 11.99, NULL, 4, '2016-08-19 12:00:00', '2016-08-19 12:00:00', 1, 'YEAR', '1\&1 Renouvellement domaine', 'test3', 'Renouvellement de domaine par 1\&1', 1 );



/* Dump of table `sa_factures`
 * ------------------------------------------------------------*/


CREATE TABLE `sa_factures` (
  `ID` int unsigned NOT NULL AUTO_INCREMENT,
  `facture_amount` float NOT NULL DEFAULT '0.0',
  `facture_action_id` int unsigned DEFAULT NULL,
  `facture_person_id` int unsigned DEFAULT NULL,
  `facture_site_id` int unsigned DEFAULT NULL,
  `facture_date_issue` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `facture_date_payment` datetime DEFAULT NULL,
  `facture_payment_type` smallint unsigned DEFAULT NULL,
  `facture_payment_ref` varchar(256) DEFAULT NULL,
  `facture_url` varchar(255) NOT NULL DEFAULT '',
  `facture_comment` text,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `facture_url` (`facture_url`),
  CONSTRAINT `fk_facture_action_id` FOREIGN KEY (`facture_action_id`) REFERENCES `sa_actions`(`ID`),
  CONSTRAINT `fk_facture_person_id` FOREIGN KEY (`facture_person_id`) REFERENCES `sa_persons`(`ID`),
  CONSTRAINT `fk_facture_site_id` FOREIGN KEY (`facture_site_id`) REFERENCES `sa_sites`(`ID`),
  CONSTRAINT `ck_facture_origin` CHECK ((`facture_person_id` IS NOT NULL) OR (`facture_site_id` IS NOT NULL)),
  CONSTRAINT `fk_facture_payment_type` FOREIGN KEY (`facture_payment_type`) REFERENCES `sa_payment_type`(`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


INSERT INTO `sa_factures` ( ID, facture_amount, facture_action_id, facture_person_id, facture_site_id, facture_date_issue, facture_date_payment, facture_payment_type, facture_payment_ref, facture_url, facture_comment ) VALUES ( 1, -10000.0, NULL, 4, NULL, '2015-12-17 14:15:00', '2016-01-20 12:00:00', 2, 'REMB ACQUISITION DEBOURS ENREGIST', 'test1', 'Acquisition de Sigma Apprentissage, avancés par Sébastien en chèque le 17/12/2015' );
INSERT INTO `sa_factures` ( ID, facture_amount, facture_action_id, facture_person_id, facture_site_id, facture_date_issue, facture_date_payment, facture_payment_type, facture_payment_ref, facture_url, facture_comment ) VALUES ( 2, -750.0, NULL, 3, NULL, '2015-12-07 12:00:00', '2016-01-20 12:00:00', 2, 'REMB ACQUISITION DEBOURS ENREGIST', 'test2', 'Frais de debours, avancés par Sébastien en chèque le 07/12/2015' );
INSERT INTO `sa_factures` ( ID, facture_amount, facture_action_id, facture_person_id, facture_site_id, facture_date_issue, facture_date_payment, facture_payment_type, facture_payment_ref, facture_url, facture_comment ) VALUES ( 3, -4500.0, NULL, 3, NULL, '2015-12-17 14:15:00', '2016-01-13 12:00:00', 2, 'HONORAIRES AVOCAT /BEN VIEILLECHA', 'test3', 'Honoraires de l\'avocate, avancés par Guy en chèque le 13/01/2016' );
INSERT INTO `sa_factures` ( ID, facture_amount, facture_action_id, facture_person_id, facture_site_id, facture_date_issue, facture_date_payment, facture_payment_type, facture_payment_ref, facture_url, facture_comment ) VALUES ( 4, -1085.5, NULL, 5, NULL, '2015-12-18 12:00:00', '2016-01-18 12:00:00', 2, '/BEN MME AUDIBERT MARTINE /REFDO', 'test4', '2 journées + frais' );
INSERT INTO `sa_factures` ( ID, facture_amount, facture_action_id, facture_person_id, facture_site_id, facture_date_issue, facture_date_payment, facture_payment_type, facture_payment_ref, facture_url, facture_comment ) VALUES ( 5, -25.0, NULL, 4, NULL, '2015-12-17 14:15:00', '2016-01-20 12:00:00', 2, 'REMB ACQUISITION DEBOURS ENREGIST', 'test5', 'Paiement enregistrement des statuts au SIE, avancés par Sébastien en chèque le 28/12/2015' );
INSERT INTO `sa_factures` ( ID, facture_amount, facture_action_id, facture_person_id, facture_site_id, facture_date_issue, facture_date_payment, facture_payment_type, facture_payment_ref, facture_url, facture_comment ) VALUES ( 6, 2285.5, 1, 2, NULL, '2015-12-31 12:00:00', NULL, NULL, NULL, 'test6', 'Formation réalisée par Martine Saint Dizier en décembre 2015' );



/* Dump of table `sa_payment_type`
 * ------------------------------------------------------------*/


CREATE TABLE `sa_payment_type` (
  `ID` smallint unsigned NOT NULL AUTO_INCREMENT,
  `payment_type_category` varchar(256) NOT NULL DEFAULT '',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


INSERT INTO `sa_payment_type` ( ID, payment_type_category ) VALUES ( 1, 'Chèque' );
INSERT INTO `sa_payment_type` ( ID, payment_type_category ) VALUES ( 2, 'Virement' );
INSERT INTO `sa_payment_type` ( ID, payment_type_category ) VALUES ( 3, 'Espèces' );
INSERT INTO `sa_payment_type` ( ID, payment_type_category ) VALUES ( 4, 'Prélèvement' );



