CREATE SCHEMA hr;
USE hr;

CREATE TABLE `persons` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(64) NOT NULL,
  `middle_name` varchar(64) DEFAULT NULL,
  `maiden_name` varchar(32) DEFAULT NULL,
  `last_name` varchar(64) NOT NULL,
  `sex` enum('Male','Female') NOT NULL,
  `birth_date` date DEFAULT NULL,
  `death_date` date DEFAULT NULL,
  `picture` blob,
  `martial_status` enum('Single','Married','Cohabitation','Divorced','Widowed','Separated') DEFAULT NULL,
  `children` decimal(2,0) DEFAULT NULL,
  `personal_id` varchar(32) DEFAULT NULL,
  `linkedin_profile` varchar(256) DEFAULT NULL COMMENT 'URL to LinkedIn profile',
  `short_bio` text,
  `cv_doc` longblob,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_person_names` (`first_name`,`middle_name`,`last_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO persons (first_name, last_name, sex, birth_date, martial_status) VALUES ('John', 'Doe', 'Male'  , '1970-01-01', 'Single');
INSERT INTO persons (first_name, last_name, sex, birth_date, martial_status) VALUES ('Jane', 'Doe', 'Female', '1970-01-01', 'Single');


GRANT SELECT, INSERT, UPDATE, DELETE ON hr.* TO 'hr_user'@'%';

