-- SQL file
drop database if exists sp_surveys;
create database sp_surveys;
use sp_surveys;

set foreign_key_checks = 0;

drop table if exists survey_instance;
create table survey_instance(
    survey_id int(3) not null auto_increment primary key,
    survey_name varchar(255) not null,
    survey_intro varchar(2500),
    survey_slug varchar(100),
    created_at timestamp not null default current_timestamp,
    updated_at timestamp not null default current_timestamp on update current_timestamp,
    unique (survey_slug)   
);


drop table if exists survey_instance_section;
create table survey_instance_section(
    section_id int(3) not null auto_increment primary key,
    section_name varchar(255) null,
    section_descr VARCHAR(1500),
    survey_id int(3) NULL,
    created_at timestamp not null default current_timestamp,
    updated_at timestamp not null default current_timestamp on update current_timestamp,
    constraint survey_instance_section_ibfk_1 FOREIGN KEY (survey_id) REFERENCES survey_instance (survey_id) ON DELETE set null ON UPDATE cascade 
);

drop TABLE if exists rating_scales;
create table rating_scales(
    rating_scale_id int(3) UNSIGNED not NULL AUTO_INCREMENT PRIMARY KEY,
    rating_scale_name VARCHAR(100),
    created_at timestamp not null default current_timestamp,
    updated_at timestamp not null default current_timestamp on update current_timestamp
    
);

drop TABLE if exists rating_scale_options;
create table rating_scale_options(
    scale_option_id int(6) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    scale_option_rank TINYINT(2),
    scale_option VARCHAR(60),
    rating_scale_id int(3) UNSIGNED,
    constraint rating_scale_options_ibfk_1 FOREIGN KEY (rating_scale_id) references rating_scales(rating_scale_id) on delete cascade on update cascade
);


drop table if exists survey_questions;
create table survey_questions(
    question_id int(3) unsigned not null auto_increment primary key,
    question_name varchar(500) not null,
    question_code varchar(50) null,
    survey_id int(3),
    section_id int(3),
    question_datatype enum('boolean','boolean-checkbox','text','textarea','number','date','date_time','opt_inclusive','opt_inclusive_input','opt_exclusive','opt_exclusive_select','list','table','table-vrtical'),
    rating_scale_id int(3) UNSIGNED,
    created_at timestamp not null default current_timestamp,
    updated_at timestamp not null default current_timestamp on update current_timestamp,
   constraint survey_questions_ibfk_1 FOREIGN key (section_id) REFERENCES survey_instance_section (section_id) ON DELETE CASCADE ON UPDATE CASCADE,
   constraint survey_questions_ibfk_2 FOREIGN KEY (rating_scale_id) references rating_scales(rating_scale_id) on delete set null on update cascade
);

drop table if exists survey_response_batch;
create table survey_response_batch(
    response_batch_id int(12) unsigned not null auto_increment primary key,
    survey_vendee CHAR(11),
    response_token varchar(140),
    created_at timestamp not null default current_timestamp,
    updated_at timestamp not null default current_timestamp on update current_timestamp
);

drop table if exists survey_responses;
create table survey_responses(
    response_id int(15) unsigned not null auto_increment primary key,
    response_batch_id int(12) UNSIGNED,
    question_id int(3) unsigned not null,
    response_data text,
    created_at timestamp not null default current_timestamp,
    updated_at timestamp not null default current_timestamp on update current_timestamp,
   constraint survey_responses_ibfk_1 FOREIGN KEY (response_batch_id) REFERENCES `survey_response_batch` (`response_batch_id`) ON DELETE CASCADE ON UPDATE CASCADE, 
   constraint survey_responses_ibfk_2 FOREIGN KEY (question_id) REFERENCES `survey_questions` (`question_id`) ON DELETE CASCADE ON UPDATE CASCADE   
);


drop table if exists survey_vendee;
CREATE TABLE `survey_vendee` (
  `vendee_id` int(3) NOT NULL AUTO_INCREMENT,
  `vendee_name` varchar(200) NOT NULL DEFAULT '',
  `short_vendee` varchar(11) DEFAULT NULL,
  `tel` varchar(80) DEFAULT NULL,
  `email` varchar(40) DEFAULT NULL,
  `url` varchar(60) DEFAULT NULL,
  PRIMARY KEY (`vendee_id`)
) ENGINE=InnoDB;

alter table survey_response_batch add vendee_id int(3) after response_batch_id;
alter table survey_response_batch add CONSTRAINT survey_response_batch_ibfk_1 foreign key (vendee_id) references survey_vendee (vendee_id) on delete cascade on update cascade;

alter table survey_vendee add suspended bool not null default 0;
alter table survey_response_batch add survey_id int(3) after response_batch_id;
alter table survey_response_batch drop column survey_vendee;









drop TABLE IF EXISTS staff_cadres;
CREATE TABLE `staff_cadres` (
  `cadre_id` int(3) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `cadre_name` varchar(80) DEFAULT NULL,
  `mutable` tinyint(1) NOT NULL DEFAULT '1'
) COMMENT='default-data';

drop TABLE IF EXISTS nigeriastates;
CREATE TABLE `nigeriastates` (
  `sNo` tinyint(2) NOT NULL,
  `state_code` varchar(2) NOT NULL DEFAULT '',
  `state_name` varchar(15) DEFAULT NULL,
  `stateCap` varchar(15) DEFAULT NULL,
  `stateGov` varchar(35) DEFAULT NULL,
  `LGAs` text NOT NULL,
  PRIMARY KEY (`state_code`)
) COMMENT='default-data';

drop TABLE IF EXISTS country;
CREATE TABLE `country` (
  `Code` char(3) NOT NULL,
  `Name` char(52) NOT NULL,
  UNIQUE KEY `Code` (`Code`)
) COMMENT='default-data';

drop TABLE IF EXISTS staff_status;
CREATE TABLE `staff_status` (
  `status_id` tinyint(1) NOT NULL AUTO_INCREMENT,
  `status_name` varchar(12) NOT NULL,
  PRIMARY KEY (`status_id`)
) COMMENT='default-data';

drop TABLE IF EXISTS system_privileges;
CREATE TABLE `system_privileges` (
  `priv_id` tinyint(2) NOT NULL AUTO_INCREMENT,
  `priv_name` varchar(20) DEFAULT NULL,
  `priv_rank` tinyint(1) NOT NULL DEFAULT '2',
  `remarks` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`priv_id`)
) COMMENT='default-data';

drop TABLE IF EXISTS staff;
CREATE TABLE `staff` (
  `staff_id` int(3) NOT NULL AUTO_INCREMENT,
  `lastname` varchar(20) DEFAULT NULL,
  `firstname` varchar(20) DEFAULT NULL,
  `middlename` varchar(20) DEFAULT NULL,
  `maiden_name` varchar(20) DEFAULT NULL,
  `staff_basename` varchar(80) GENERATED ALWAYS AS (concat_ws(_utf8mb3' ',upper(`lastname`),`firstname`)) STORED,
  `staff_fullname` varchar(100) GENERATED ALWAYS AS (concat_ws(_utf8mb3' ',upper(`lastname`),`firstname`,`middlename`)) STORED,
  `staff_title` varchar(10) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `gender` enum('M','F') DEFAULT NULL,
  `cadre_id` int(3) DEFAULT NULL,
  `staff_designation` varchar(1000) DEFAULT NULL,
  `glevel` varchar(10) DEFAULT NULL,
  `status_id` tinyint(1) DEFAULT '1',
  `emp_date` date DEFAULT NULL,
  `email` varchar(80) DEFAULT NULL,
  `tel1` varchar(20) DEFAULT NULL,
  `tel2` varchar(20) DEFAULT NULL,
  `mobile1` varchar(20) DEFAULT NULL,
  `mobile2` varchar(20) DEFAULT NULL,
  `address1` varchar(255) DEFAULT NULL,
  `address2` varchar(255) DEFAULT NULL,
  `lga` varchar(20) DEFAULT NULL,
  `state_code` varchar(2) DEFAULT NULL,
  `ethnic_grp_id` int(3) DEFAULT NULL,
  `nationality_id` char(3) DEFAULT NULL,
  `nok_name` varchar(50) DEFAULT NULL,
  `nok_rel` varchar(15) DEFAULT NULL,
  `nok_addr` varchar(255) DEFAULT NULL,
  `nok_tel1` varchar(30) DEFAULT NULL,
  `nok_tel2` varchar(30) DEFAULT NULL,
  `reg_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `mod_date` datetime DEFAULT NULL,
  `last_editor` int(3) DEFAULT NULL,
  `staff_fullname_rev` varchar(100) GENERATED ALWAYS AS (concat_ws(_utf8mb3' ',`firstname`,`middlename`,upper(`lastname`))) STORED,
  `staff_basename_rev` varchar(100) GENERATED ALWAYS AS (concat_ws(_utf8mb3' ',`firstname`,upper(`lastname`))) STORED,
  PRIMARY KEY (`staff_id`),
  KEY `cadre_id` (`cadre_id`),
  KEY `state_id` (`state_code`),
  KEY `nationality_id` (`nationality_id`),
  KEY `last_editor` (`last_editor`),
  KEY `status_id` (`status_id`),
  CONSTRAINT `staff_ibfk_1` FOREIGN KEY (`cadre_id`) REFERENCES `staff_cadres` (`cadre_id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `staff_ibfk_2` FOREIGN KEY (`state_code`) REFERENCES `nigeriastates` (`state_code`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `staff_ibfk_3` FOREIGN KEY (`nationality_id`) REFERENCES `country` (`Code`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `staff_ibfk_4` FOREIGN KEY (`status_id`) REFERENCES `staff_status` (`status_id`) ON DELETE SET NULL ON UPDATE CASCADE
);

drop TABLE IF EXISTS login;
CREATE TABLE `login` (
  `login_id` int(3) NOT NULL AUTO_INCREMENT,
  `staff_id` int(3) NOT NULL,
  `username` varchar(20) NOT NULL,
  `password` varchar(40) NOT NULL,
  `pass_hint` varchar(255) DEFAULT NULL,
  `priv_id` tinyint(2) NOT NULL,
  `updated_by` int(3) NOT NULL DEFAULT '0',
  `updated_on` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  `auth` tinyint(1) DEFAULT '0',
  `online_status` tinyint(1) DEFAULT '0',
  `resetreqtime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`login_id`),
  UNIQUE KEY `username` (`username`),
  KEY `priv_id` (`priv_id`),
  KEY `staff_id` (`staff_id`),
  CONSTRAINT `login_ibfk_1` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `login_ibfk_2` FOREIGN KEY (`priv_id`) REFERENCES `system_privileges` (`priv_id`) ON DELETE CASCADE ON UPDATE CASCADE
);

drop TABLE IF EXISTS userlog;
CREATE TABLE `userlog` (
  `log_id` int(15) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `login_id` int(3) DEFAULT NULL,
  `logon` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `logoff` datetime DEFAULT NULL,
  `ip` varchar(100) NOT NULL
);


drop TABLE IF EXISTS staff_vendee_bridge;
create TABLE staff_vendee_bridge(
  staff_vendee_bridge_id int(5) not null auto_increment primary key,
  staff_id int(3) not null,
  vendee_id int(3) not null,
  created_at timestamp not null default current_timestamp,
  updated_at timestamp not null default current_timestamp on update current_timestamp,
  key staff_id (staff_id),
  key vendee_id (vendee_id),
  CONSTRAINT `staff_vendee_bridge_ibfk_1` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `staff_vendee_bridge_ibfk_2` FOREIGN KEY (`vendee_id`) REFERENCES `survey_vendee` (`vendee_id`) ON DELETE CASCADE ON UPDATE CASCADE
);


drop TABLE IF EXISTS survey_vendee_bridge;
create TABLE survey_vendee_bridge(
  survey_vendee_bridge_id int(5) not null auto_increment primary key,
  survey_id int(3) not null,
  vendee_id int(3) not null,
  created_at timestamp not null default current_timestamp,
  updated_at timestamp not null default current_timestamp on update current_timestamp,
  key survey_id (survey_id),
  key vendee_id (vendee_id),
  CONSTRAINT `survey_vendee_bridge_ibfk_1` FOREIGN KEY (`survey_id`) REFERENCES `survey_instance` (`survey_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `survey_vendee_bridge_ibfk_2` FOREIGN KEY (`vendee_id`) REFERENCES `survey_vendee` (`vendee_id`) ON DELETE CASCADE ON UPDATE CASCADE
);



INSERT INTO `country` (`Code`, `Name`) VALUES
('ABW', 'Aruba'),
('AFG', 'Afghanistan'),
('AGO', 'Angola'),
('AIA', 'Anguilla'),
('ALB', 'Albania'),
('AND', 'Andorra'),
('ANT', 'Netherlands Antilles'),
('ARE', 'United Arab Emirates'),
('ARG', 'Argentina'),
('ARM', 'Armenia'),
('ASM', 'American Samoa'),
('ATA', 'Antarctica'),
('ATF', 'French Southern territories'),
('ATG', 'Antigua and Barbuda'),
('AUS', 'Australia'),
('AUT', 'Austria'),
('AZE', 'Azerbaijan'),
('BDI', 'Burundi'),
('BEL', 'Belgium'),
('BEN', 'Benin'),
('BFA', 'Burkina Faso'),
('BGD', 'Bangladesh'),
('BGR', 'Bulgaria'),
('BHR', 'Bahrain'),
('BHS', 'Bahamas'),
('BIH', 'Bosnia and Herzegovina'),
('BLR', 'Belarus'),
('BLZ', 'Belize'),
('BMU', 'Bermuda'),
('BOL', 'Bolivia'),
('BRA', 'Brazil'),
('BRB', 'Barbados'),
('BRN', 'Brunei'),
('BTN', 'Bhutan'),
('BVT', 'Bouvet Island'),
('BWA', 'Botswana'),
('CAF', 'Central African Republic'),
('CAN', 'Canada'),
('CCK', 'Cocos (Keeling) Islands'),
('CHE', 'Switzerland'),
('CHL', 'Chile'),
('CHN', 'China'),
('CIV', 'Côte d’Ivoire'),
('CMR', 'Cameroon'),
('COD', 'Congo, The Democratic Republic of the'),
('COG', 'Congo'),
('COK', 'Cook Islands'),
('COL', 'Colombia'),
('COM', 'Comoros'),
('CPV', 'Cape Verde'),
('CRI', 'Costa Rica'),
('CUB', 'Cuba'),
('CXR', 'Christmas Island'),
('CYM', 'Cayman Islands'),
('CYP', 'Cyprus'),
('CZE', 'Czech Republic'),
('DEU', 'Germany'),
('DJI', 'Djibouti'),
('DMA', 'Dominica'),
('DNK', 'Denmark'),
('DOM', 'Dominican Republic'),
('DZA', 'Algeria'),
('ECU', 'Ecuador'),
('EGY', 'Egypt'),
('ERI', 'Eritrea'),
('ESH', 'Western Sahara'),
('ESP', 'Spain'),
('EST', 'Estonia'),
('ETH', 'Ethiopia'),
('FIN', 'Finland'),
('FJI', 'Fiji Islands'),
('FLK', 'Falkland Islands'),
('FRA', 'France'),
('FRO', 'Faroe Islands'),
('FSM', 'Micronesia, Federated States of'),
('GAB', 'Gabon'),
('GBR', 'United Kingdom'),
('GEO', 'Georgia'),
('GHA', 'Ghana'),
('GIB', 'Gibraltar'),
('GIN', 'Guinea'),
('GLP', 'Guadeloupe'),
('GMB', 'Gambia'),
('GNB', 'Guinea-Bissau'),
('GNQ', 'Equatorial Guinea'),
('GRC', 'Greece'),
('GRD', 'Grenada'),
('GRL', 'Greenland'),
('GTM', 'Guatemala'),
('GUF', 'French Guiana'),
('GUM', 'Guam'),
('GUY', 'Guyana'),
('HKG', 'Hong Kong'),
('HMD', 'Heard Island and McDonald Islands'),
('HND', 'Honduras'),
('HRV', 'Croatia'),
('HTI', 'Haiti'),
('HUN', 'Hungary'),
('IDN', 'Indonesia'),
('IND', 'India'),
('IOT', 'British Indian Ocean Territory'),
('IRL', 'Ireland'),
('IRN', 'Iran'),
('IRQ', 'Iraq'),
('ISL', 'Iceland'),
('ISR', 'Israel'),
('ITA', 'Italy'),
('JAM', 'Jamaica'),
('JOR', 'Jordan'),
('JPN', 'Japan'),
('KAZ', 'Kazakstan'),
('KEN', 'Kenya'),
('KGZ', 'Kyrgyzstan'),
('KHM', 'Cambodia'),
('KIR', 'Kiribati'),
('KNA', 'Saint Kitts and Nevis'),
('KOR', 'South Korea'),
('KWT', 'Kuwait'),
('LAO', 'Laos'),
('LBN', 'Lebanon'),
('LBR', 'Liberia'),
('LBY', 'Libyan Arab Jamahiriya'),
('LCA', 'Saint Lucia'),
('LIE', 'Liechtenstein'),
('LKA', 'Sri Lanka'),
('LSO', 'Lesotho'),
('LTU', 'Lithuania'),
('LUX', 'Luxembourg'),
('LVA', 'Latvia'),
('MAC', 'Macao'),
('MAR', 'Morocco'),
('MCO', 'Monaco'),
('MDA', 'Moldova'),
('MDG', 'Madagascar'),
('MDV', 'Maldives'),
('MEX', 'Mexico'),
('MHL', 'Marshall Islands'),
('MKD', 'Macedonia'),
('MLI', 'Mali'),
('MLT', 'Malta'),
('MMR', 'Myanmar'),
('MNG', 'Mongolia'),
('MNP', 'Northern Mariana Islands'),
('MOZ', 'Mozambique'),
('MRT', 'Mauritania'),
('MSR', 'Montserrat'),
('MTQ', 'Martinique'),
('MUS', 'Mauritius'),
('MWI', 'Malawi'),
('MYS', 'Malaysia'),
('MYT', 'Mayotte'),
('NAM', 'Namibia'),
('NCL', 'New Caledonia'),
('NER', 'Niger'),
('NFK', 'Norfolk Island'),
('NGA', 'Nigeria'),
('NIC', 'Nicaragua'),
('NIU', 'Niue'),
('NLD', 'Netherlands'),
('NOR', 'Norway'),
('NPL', 'Nepal'),
('NRU', 'Nauru'),
('NZL', 'New Zealand'),
('OMN', 'Oman'),
('PAK', 'Pakistan'),
('PAN', 'Panama'),
('PCN', 'Pitcairn'),
('PER', 'Peru'),
('PHL', 'Philippines'),
('PLW', 'Palau'),
('PNG', 'Papua New Guinea'),
('POL', 'Poland'),
('PRI', 'Puerto Rico'),
('PRK', 'North Korea'),
('PRT', 'Portugal'),
('PRY', 'Paraguay'),
('PSE', 'Palestine'),
('PYF', 'French Polynesia'),
('QAT', 'Qatar'),
('REU', 'Réunion'),
('ROM', 'Romania'),
('RUS', 'Russian Federation'),
('RWA', 'Rwanda'),
('SAU', 'Saudi Arabia'),
('SDN', 'Sudan'),
('SEN', 'Senegal'),
('SGP', 'Singapore'),
('SGS', 'South Georgia and the South Sandwich Islands'),
('SHN', 'Saint Helena'),
('SJM', 'Svalbard and Jan Mayen'),
('SLB', 'Solomon Islands'),
('SLE', 'Sierra Leone'),
('SLV', 'El Salvador'),
('SMR', 'San Marino'),
('SOM', 'Somalia'),
('SPM', 'Saint Pierre and Miquelon'),
('SSD', 'South Sudan'),
('STP', 'Sao Tome and Principe'),
('SUR', 'Suriname'),
('SVK', 'Slovakia'),
('SVN', 'Slovenia'),
('SWE', 'Sweden'),
('SWZ', 'Swaziland'),
('SYC', 'Seychelles'),
('SYR', 'Syria'),
('TCA', 'Turks and Caicos Islands'),
('TCD', 'Chad'),
('TGO', 'Togo'),
('THA', 'Thailand'),
('TJK', 'Tajikistan'),
('TKL', 'Tokelau'),
('TKM', 'Turkmenistan'),
('TMP', 'East Timor'),
('TON', 'Tonga'),
('TTO', 'Trinidad and Tobago'),
('TUN', 'Tunisia'),
('TUR', 'Turkey'),
('TUV', 'Tuvalu'),
('TWN', 'Taiwan'),
('TZA', 'Tanzania'),
('UGA', 'Uganda'),
('UKR', 'Ukraine'),
('UMI', 'United States Minor Outlying Islands'),
('URY', 'Uruguay'),
('USA', 'United States'),
('UZB', 'Uzbekistan'),
('VAT', 'Holy See (Vatican City State)'),
('VCT', 'Saint Vincent and the Grenadines'),
('VEN', 'Venezuela'),
('VGB', 'Virgin Islands, British'),
('VIR', 'Virgin Islands, U.S.'),
('VNM', 'Vietnam'),
('VUT', 'Vanuatu'),
('WLF', 'Wallis and Futuna'),
('WSM', 'Samoa'),
('YEM', 'Yemen'),
('YUG', 'Yugoslavia'),
('ZAF', 'South Africa'),
('ZMB', 'Zambia'),
('ZWE', 'Zimbabwe');


INSERT INTO `nigeriastates` (`sNo`, `state_code`, `state_name`, `stateCap`, `stateGov`, `LGAs`) VALUES
(1, 'AB', 'Abia', NULL, NULL, 'Aba-North,Aba-South,Arochokwu,Bende,Ikwuano,Isiala-Ngwa-North,Isiala-Ngwa-South,Isuikwuato,Obi-Nwga,Ohafia,Osisioma,Ugwunagbo,Ukwa-East,Ukwa-West,Umuahia-North,Umuahia-South,Umu-Nneochi'),
(2, 'AD', 'Adamawa', NULL, NULL, 'Demsa,Fufure,Ganye,Gayuk,Gombi,Grie,Hong,Jada,Larmurde,Madgali,Maiha,Mayo-Belwa,Michika,Mobi-North,Mobi-South,Numan,Shelleng,Song,Teungo,Yola-North,Yola-South'),
(3, 'AK', 'Akwa Ibom', NULL, NULL, 'Abak,Eastern-Obolo,Eket,Esit-Eket,Essien-Udim,Etim-Ekpo,Etinan,Ibeno,Ibesikpo-Asutan,Ibiono-Ibom,Ika,Ikono,Ikot-Abasi,Ikot-Ekpene,Ini,Itu,Mbo,Mpkat-Enin,Nsit-Atai,Nsit-Ibom,Nsit-Ubuim,Obot-Akara,Okobo,Onna,Oron,Oruk-Anam,Udung-Uko,Ukanafun,Uruan,Urue-Offong/Oruko,Uyo'),
(4, 'AN', 'Anambra', NULL, NULL, 'Aguata,Anambra-East,Anambra-West,Anaocha,Awka-North,Awka-South,Ayamelum,Dunukofia,Ekwusigo,Idemili-North,Idemili-South,Ihiala,Njikoka,Nnewi-North,Nnewi-South,Ogbaru,Onitsha-North,Onitsha-south,Orumba-North,Orumba-South,Oyi'),
(5, 'BA', 'Bauchi', NULL, NULL, 'Alkaleri,Bauchi,Bogoro,Damban,Darazo,Dass,Gamawa,Ganjuwa,Giade,Itas/Gadau,Jama\'are,Katagun,Kirfi,Misau,Ningi,Shira,Tafawa-Balewa,Toro,Warji,Zakki'),
(7, 'BN', 'Benue', NULL, NULL, 'Agatu,Apa,Ado,Buruku,Gboko,Guma,Gwer-East,Gwer-West,Katsina-Ala,Konshisha,Kwande,Logo,Makurdi,Obi,Ogbadibo,Ohimini,Oju,Okpokwu,Oturkpo,Tarka,Ukum,Ushongo,Vandeikya'),
(8, 'BO', 'Borno', NULL, NULL, 'Abadam,Askira/Uba,BAMA,Bayo,Biu,Chibok,Damboa,Dikwa,Gubio,Guzamala,Gwoza,Hawul,Jere,Kaga,Kala/Balge,Konduga,Kukawa,Kwaya-Kusar,Mafa,Magumeri,Maiduguri,Marte,Mobbar,Monguno,Ngala,Nganzai,Shani'),
(6, 'BY', 'Bayelsa', NULL, NULL, 'Brass,Ekeremor,Kolokuma/Opokuma,Nembe,Ogbia,Sagbama,Southern-Ijaw,Yenagoa'),
(9, 'CR', 'Cross River', NULL, NULL, 'Abi,Akamkpa,Akpabuyo,Bakassi,Bekwarra,Biase,Boki,Calabar-Municipal,Calabar-South,Etung,Ikom,Obanliku,Obubra,Obudu,Odukpani,Ogoja,Yakurr,Yala'),
(10, 'DT', 'Delta', NULL, NULL, 'Aniocha-North,Aniocha-South,Bomadi,Burutu,Ethiope-East,Ethiope-West,Ika-North,Ika-South,Isoko-north,Isoko-South,Ndokwa-East,Ndokwa-West,Okpe,Oshimili-North,Oshimili-South,Patani,Sapele,Udu,Ughelli-North,Ughelli-South,Ukwuani,Uvwie,Warri-North,Warri-South,Warri-South-West'),
(11, 'EB', 'Ebonyi', NULL, NULL, 'Abakaliki,Afikpo-North,Afikpo-South,Ebonyi,Ezza-North,Ezza-South,Ikwo,Ishielu,Ivo,Izzi,Ohaozara,Ohaukwu,Onicha'),
(12, 'ED', 'Edo', NULL, NULL, 'Akoko-Edo,Egor,Esan-Central,Esan-North-East,Esan-South-East,Esan-West,Etsako-Central,Etsako-East,Etsako-West,Iguegben,Ikpoba-Okha,Ohionmwon,Oredo,Ovia-North-East,Ovia-South-West,Owan-East,Owan-West,Uhunmwonde'),
(13, 'EK', 'Ekiti', NULL, NULL, 'Ado-Ekiti,Efon,Ekiti-East,Ekiti-South-West,Ekiti-West,Emure,Gbonyin,Ido-Osi,Ijero,Ikere,Ikole,Ilejemeje,Irepodun/Ifelodun,Ise/Orun,Moba,Oye'),
(14, 'EN', 'Enugu', NULL, NULL, 'Aninri,Awgu,Enugu-East,Enugu-North,Enugu-South,Ezeagu,Igbo=Etiti,Igbo-Eze-North,Igbo-Eze-South,Isi-Uzo,Nkanu-East,Nkanu-West,Nsukka,Oji-River,Udenu,Udi,Uzo-Uwani'),
(37, 'FC', 'FCT', NULL, NULL, 'Abaji,Bwari,Gwagwalada,Kuje,Kwali,Municipal-Area-Council'),
(15, 'GO', 'Gombe', NULL, NULL, 'Akko,Balanga,Billiri,Dukku,Funakaye,Gombe,Kaltungo,Kwani,Nafada,Shomgom,Yamaltu/Deba'),
(16, 'IM', 'Imo', NULL, NULL, 'Aboh-Mbaise,Ahiazu,Ehime-Mbano,Ezinihitte,Ideato-North,Ideato-South,Ihitte/Uboma,Ikeduru,Isiala-Mbano,Isu,Mbatoli,Ngor-Okpala,Njaba,Nkwerre,Nwangele,Obowo,Oguta,Ohaji/Egbema,Okigwe,Orlu,Orsu,Oru-East,Oru-West,Owerri-Municipal,Owerri-North,Owerri-west,Unuimo'),
(17, 'JG', 'Jigawa', NULL, NULL, 'Auyo,Babura,Biriniwa,Birnin-Kudu,Buji,Dutse,Gagarawa,Garki,Gumel,Guri,Gwaram,Gwiwa,Hadejia,Jahun,Kafin-Hausa,Kazuare,Kiri-Kasama,kiyawa,Kuagama,Maigatari,Malam-Maduri,Miga,Ringim,Roni,Sule-Tankakar,Taura,Yankwashi'),
(21, 'KB', 'Kebbi', NULL, NULL, 'Aleiro,Arewa-Dandi,Argungu,Augie,Bagudo,Birnin-Kebbi,Bunza,Dandi,Fakai,Gwandu,Jega,Kalgo,Koko/Besse,Maiyama,Ngaski,Sakaba,Shanga,Suru,Wasagu/Danko,Yauri,Zuru'),
(18, 'KD', 'Kaduna', NULL, NULL, 'Birnin-Gwari,Chikun,Giwa,Igabi,Ikara,Jaba,Jema\'a,Kachia,Kaduna-North,kaduna-South,Kagarko,Kajuru,Kaura,Kauru,Kubau,Kudan,Lere,Makarfi,Sabon-Gari,Sanga,Soba,Zangon-Kataf,Zaria'),
(22, 'KG', 'Kogi', NULL, NULL, 'Adavi,Ajaokuta,Ankpa,Bassa,Dekina,Ibaji,Idah,Igalamela-Odolu,Ijumu,Kabba/Bunu,Kogi,Lokoja,Mopa-Muro,Ofu,Ogori/Magongo,Okehi,Okene,Olamaboro,Omala,Yagba-East,Yagba-West'),
(19, 'KN', 'Kano', NULL, NULL, 'Ajingi,Albasu,Bagwai,Bebeji,Bichi,Bunkure,Dala,Dambatta,Dawakin-Kudu,Dawakin-Tofa,Doguwa,Fagge,Gabasawa,Garko,Garum-Mallam,Gaya,Gazewa,Gwale,Gwarzo,Kabo,Kano-Municipal,Karaye,Kibiya,Kiru,Kumbotso,Kunchi,Kura,Madobi,Makoda,Minjibir,Nasarawa,Rano,Rimin-Gado,Rogo,Shanono,Sumaila,Takai,Tarauni,Tofa,Tsanyawa,Tudun-Wada,Ungongo,Warawa,Wudil'),
(20, 'KT', 'Katsina', NULL, NULL, 'Bakori,Batagarawa,Batsari,Baure,Bindawa,Charanchi,Dandume,Danja,Dan-Musa,Duara,Dutsi,Dutsin-Ma,Faskari,Funtua,Ingawa,Jibia,Kafur,kaita,Kankara,Kankia,Katsina,Kurfi,Kusada,Mai\'Adua,Malumfashi,Mani,Mashi,Matazu,Musawa,Rimi,Sabuwa,Safana,Sandamu,Zango'),
(23, 'KW', 'Kwara', NULL, NULL, 'Asa,Baruten,Edu,Ekiti,Ifelodun,Ilorin-East,Ilorin-South,Ilorin-west,Irepodun,Isin,Kaiama,Moro,Offa,Oke-oro,Oyun,Pategi'),
(24, 'LA', 'Lagos', NULL, NULL, 'Agege,Ajeromi/Ifelodun,Alimosho,Amuwo-Odofin,Apapa,Badagry,Epe,Eti-osa,Ibeju-lekki,Ifako/Ijaye,Ikeja,Ikorodu,Kosofe,Lagos-Island,Lagos-Mainland,Mushin,Ojo,Oshodi/Isolo,Shomolu,Surulere'),
(26, 'NG', 'Niger', NULL, NULL, 'Agaie,Agawara,Bida,Borgu,Bosso,Chanchaga,Edati,Gbako,Gurara,Katcha,Kontagora,Lapai,Lavun,Magama,Mariga,Mashegu,Mokwa,Moya,Paikoro,Rafi,Rijau,Shiroro,Suleja,Tafa,Wushishi'),
(38, 'NN', 'Non-Nigerian', NULL, NULL, ''),
(25, 'NS', 'Nasarawa', NULL, NULL, 'Akwanga,Awe,Doma,Karu,Keana,Keffi,Kokona,Lafia,Nasarawa,Nasarawa-Egon,Obi,Toto,Wamba'),
(28, 'OD', 'Ondo', NULL, NULL, 'Akoko-North-East,Akoko-North-West,Akoko-South-West,Akok-South-East,Akure-North,Akure-South,Ese-Odo,Idanre,Ifedore,ijaye,Ile-Oluji-Okeigbo,Irele,Odigbo,Okitipupa,Ondo-East,Ondo-west,Ose,Owo'),
(27, 'OG', 'Ogun', NULL, NULL, 'Abeokuta-north,Abeokuta-south,Ado-Odo/Ota,Egbado-North,Egbado-South,Ewekoro,Ifo,Ijebu-East,Ijebu-North,Ijebu-North-East,Ijebu-ode,Ikenne,Imeko-Afon,Ipokia,Obafemi-Owode,Odeda,Odogbolu,Ogun-waterfront,Remo-North,Shagamu'),
(29, 'OS', 'Osun', NULL, NULL, 'Aiyedire,Atakunmosa-East,Atakunmosa-West,Ayedire,Boluwaduro,Boripe,Ede-North,Ede-South,Efe-Central,Efe-East,Efe-North,Efe-South,Egbedore,Ejigbo,Ifedayo,Ifelodun,Ila,Ilesha-East,Ilesha-West,Irepodun,Irewole,Isokan,Iwo,Obokun,Odo-otin,Ola-oluwa,Olorunda,Oriade,Orolu,Osogbo'),
(30, 'OY', 'Oyo', NULL, NULL, 'Afijio,Akinyele,Atiba,Atisbo,Egbeda,Ibadan-North,Ibadan-North-East,Ibadan-North-West,Ibadan-South-East,Ibadan-South-West,Ibarapa-Central,Ibarapa-East,Ibarapa-North,Ido,Irepo,Iseyin,Itesiwaju,Iwajowa,Kajola,Lagelu,Ogbomosho-North,Ogbomosho-South,Ogo-Oluwa,Olorunsogo,Oluyole,Ona-ara,Orelope,Ori-Ire,Oyo,Oyo-East,Saki-East,Saki-west,Surulere'),
(31, 'PL', 'Plateau', NULL, NULL, 'Bokkos,Barkin-Ladi,Bassa,Jos-East,Jos-North,Jos-South,Kanam,Kanke,Langtang-South,Langtan-North,Mangu,Mikang,Panshkin,Qua\'an-Pan,Riyom,Shendam,Wase'),
(32, 'RV', 'Rivers', NULL, NULL, 'Abua-Odual,Ahoada-East,Ahoada-West,Akuku-Toru,Andoni,Asari-Toru,Bonny,Degema,Eleme,Emuoha,Etche,Gokana,Ikwere,Khana,Obio/Akpor,Ogba/Egbema/Ndoni,Ogo-Bolo,Okrika,Omumma,Opobo/Nkoro,Oyigbo,Port-Harcourt,Tai'),
(33, 'SO', 'Sokoto', NULL, NULL, 'Binji,Bodinga,Dange-shuni,Gada,Goronyo,Gudu,Gwadabawa,Illela,Isa,Kebbe,Kware,Rabah,Sabon-Birni,Shagari,Silame,Sokoto-North,Sokoto-South,Tambuwal,Tangaza,Tureta,Wamako,Wurno,Yabo'),
(34, 'TA', 'Taraba', NULL, NULL, 'Ardo-Kola,Bali,Donga,Gashaka,Gassol,Ibi,Jalingo,Karim-Lamido,Kumi,Lau,Sardauna,Takum,Ussa,Wukari,Yorro,Zing'),
(35, 'YB', 'Yobe', NULL, NULL, 'Barde,Bosari,Damaturu,Fika,Fune,Geidam,Gujba,Gulani,Jakusko,Karasuwa,Machina,Nangere,Nguru,Potiskum,Tarmua,Yunusari,Yusufari'),
(36, 'ZA', 'Zamfara', NULL, NULL, 'Anka,Bakura,Birnin-Magaji/Kiyaw,Bukkuyum,Bungudu,Gummi,Gusau,Kaura-Namoda,Maradun,Maru,Shinkafi,Talata-Mafara,Tsafe,Zurmi');


INSERT INTO `staff_status` (`status_id`, `status_name`) VALUES
(1, 'current'),
(2, 'distant'),
(3, 'retired'),
(4, 'resigned'),
(5, 'dismissed');


INSERT INTO `staff_cadres` (`cadre_id`, `cadre_name`, `mutable`) VALUES
(1, 'Director', 0);

INSERT INTO `system_privileges` (`priv_id`, `priv_name`, `priv_rank`, `remarks`) VALUES
(1, 'boss', 4, 'can do all things'),
(2, 'user', 2, 'routine read and write privileges'),
(3, 'admin', 2, ''),
(4, 'guest', 1, 'essentially read-only of non-sensitives'),
(5, 'sys_admin', 3, 'access to technical stuff'),
(12, 'techno', 5, NULL);

INSERT INTO `staff` (`staff_id`, `lastname`, `firstname`, `middlename`, `maiden_name`, `staff_title`, `dob`, `gender`, `cadre_id`, `staff_designation`, `glevel`, `status_id`, `emp_date`, `email`, `tel1`, `tel2`, `mobile1`, `mobile2`, `address1`, `address2`, `lga`, `state_code`, `ethnic_grp_id`, `nationality_id`, `nok_name`, `nok_rel`, `nok_addr`, `nok_tel1`, `nok_tel2`, `reg_date`, `mod_date`, `last_editor`) VALUES
(1, 'Okonkwo', 'Ifedi', NULL, NULL, '6', NULL, 'M', 1, 'MD', NULL, 1, NULL, 'iiokonkwo@gmail.com', NULL, NULL, '08061514838', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '2022-06-13 05:26:52', NULL, NULL);


INSERT INTO `login` (`login_id`, `staff_id`, `username`, `password`, `pass_hint`, `priv_id`, `updated_by`, `updated_on`, `auth`, `online_status`, `resetreqtime`) VALUES
(1, 1, 'ifedi', '6d17de09e78268e4a9776408960d54c7b3e9b8d5', '', 12, 1, '2022-06-11 13:15:49', 1, 1, '0000-00-00 00:00:00');



INSERT INTO `survey_instance` (`survey_id`, `survey_name`, `survey_intro`, `survey_slug`, `created_at`, `updated_at`) VALUES
(1, 'Outpatient Visit Satisfaction Survey\n', 'At {VENDEE}, we are dedicated to continuous improvement of the quality of our clinical services. Please help us by taking
a few moments to complete this questionnaire. Your participation is voluntary, and your responses are strictly confidential.\n Thank you for participating!', 'opd-visit-satisfaction-survey', '2022-05-20 06:43:56', '2022-05-20 06:43:56'),
(2, 'Customer Survey for In-Patient Care', '{VENDEE} is dedicated to continuous improvement of the quality of our clinical services. Please help us by taking a few moments to complete this questionnaire. Your participation is voluntary, and your responses will be treated with strict confidentiality. Thank you for participating!', 'perf-benchmark-ipd', '2022-05-13 02:57:37', '2022-05-20 06:44:20');

INSERT INTO `survey_instance_section` (`section_id`, `section_name`, `section_descr`, `survey_id`, `created_at`, `updated_at`) VALUES
(1, 'Please rate your experience or perception of the following:', NULL, 1, '2022-05-13 03:34:00', '2022-05-13 03:34:00'),
(2, NULL, NULL, 1, '2022-05-13 03:36:38', '2022-05-13 03:36:38'),
(3, 'Please rate your experience or perception of the following:', NULL, 2, '2022-05-13 03:34:00', '2022-05-13 03:34:00'),
(4, NULL, NULL, 2, '2022-05-13 03:36:38', '2022-05-13 03:36:38');



INSERT INTO `survey_questions` (`question_id`, `question_name`, `question_code`, `survey_id`, `section_id`, `question_datatype`, `rating_scale_id`, `created_at`, `updated_at`) VALUES
(1, 'How easy it was to schedule an appointment', 'ease-of-appointment', NULL, 1, 'opt_exclusive', 3, '2022-05-13 02:10:39', '2022-06-24 22:37:22'),
(2, 'How promptly you got to see the doctor', 'prompt-see-doctor', NULL, 1, 'opt_exclusive', 3, '2022-05-13 02:10:39', '2022-06-24 22:37:38'),
(3, 'Cleanliness and appearance of the facility', 'clean-appearance', NULL, 1, 'opt_exclusive', 3, '2022-05-13 02:10:39', '2022-06-24 22:37:49'),
(4, 'Billing of services and payment processes', 'billing-payment', NULL, 1, 'opt_exclusive', 3, '2022-05-13 02:10:39', '2022-06-24 22:38:01'),
(5, 'Diagnostic services (Laboratory/Radiology)', 'diagnostic-services', NULL, 1, 'opt_exclusive', 3, '2022-05-13 02:10:39', '2022-06-24 22:38:13'),
(6, 'Response of staff to your complaint', 'response-to-complaint', NULL, 1, 'opt_exclusive', 3, '2022-05-13 02:10:39', '2022-06-24 22:38:27'),
(7, 'Staff professionalism', 'staff-professionalism', NULL, 1, 'opt_exclusive', 3, '2022-05-13 02:10:39', '2022-06-24 22:38:46'),
(8, 'Staff politeness and positive attitude', 'staff-politeness', NULL, 1, 'opt_exclusive', 3, '2022-05-13 02:10:39', '2022-06-24 22:38:56'),
(9, 'Outcome of care (how effective the treatment was)', 'outcome-of-care', NULL, 1, 'opt_exclusive', 3, '2022-05-13 02:10:39', '2022-06-24 22:39:06'),
(10, 'Understandable and clear explanations of services', 'clear-explanations', NULL, 1, 'opt_exclusive', 3, '2022-05-13 02:10:39', '2022-06-24 22:39:23'),
(11, 'How comfortable you felt talking to the healthcare provider about your health needs', 'comfort-talking-to-provider', NULL, 1, 'opt_exclusive', 3, '2022-05-13 02:10:39', '2022-06-24 22:39:54'),
(12, 'If your rating is 3 or below for any of the responses, would you like to share your experience with us?\r\n', 'low-rating-experience', NULL, 2, 'textarea', NULL, '2022-05-13 02:16:18', '2022-06-24 22:40:48'),
(13, 'Mention any other aspect of your experience with us that you like immediate improvement\r\n', 'areas-needing-improvement', NULL, 2, 'textarea', NULL, '2022-05-13 02:16:18', '2022-06-24 22:41:12'),
(14, 'Respect for my opinion and preferences', 'respect-for-my-opinion', NULL, 3, 'opt_exclusive', 3, '2022-05-13 02:10:39', '2022-06-24 22:41:33'),
(15, 'Freshness and comfort of the room', 'fresh-comfort-room', NULL, 3, 'opt_exclusive', 3, '2022-05-13 02:10:39', '2022-06-24 22:41:44'),
(16, 'Feeling of safety', 'feeling-safety', NULL, 3, 'opt_exclusive', 3, '2022-05-13 02:10:39', '2022-06-24 22:41:51'),
(17, 'Absence of noise from staff', 'no-noise-staff', NULL, 3, 'opt_exclusive', 3, '2022-05-13 02:10:39', '2022-06-24 22:42:06'),
(18, 'Absence of noise from other patients', 'no-noise-other-patients', NULL, 3, 'opt_exclusive', 3, '2022-05-13 02:10:39', '2022-06-24 22:42:16'),
(19, 'Help with personal care and needs', 'help-with-needs', NULL, 3, 'opt_exclusive', 3, '2022-05-13 02:10:39', '2022-06-24 22:42:24'),
(20, 'Staff politeness, patience and courteousness', 'staff-politeness', NULL, 3, 'opt_exclusive', 3, '2022-05-13 02:10:39', '2022-06-24 22:42:35'),
(21, 'Clear explanations and answers by the doctor on treatment processes and services received', 'clear-explanations', NULL, 3, 'opt_exclusive', 3, '2022-05-13 02:10:39', '2022-06-24 22:42:53'),
(22, 'Staff professionalism', 'staff-professionalism', NULL, 3, 'opt_exclusive', 3, '2022-05-13 02:10:39', '2022-06-24 22:43:04'),
(23, 'Feeding and other catering services', 'feeding-and-catering', NULL, 3, 'opt_exclusive', 3, '2022-05-13 02:10:39', '2022-06-24 22:43:12'),
(24, 'Promptness of response from the nursing staff', 'prompt-response', NULL, 3, 'opt_exclusive', 3, '2022-05-13 02:10:39', '2022-06-24 22:43:20'),
(25, 'Continuous cleanliness (hygiene) of the room', 'continuous-hygiene', NULL, 3, 'opt_exclusive', 3, '2022-05-13 02:10:39', '2022-06-24 22:43:27'),
(26, 'If your rating is 3 or below for any of the responses, would you like to share your experience with us?\r\n', 'low-rating-experience', NULL, 4, 'textarea', NULL, '2022-05-13 02:16:18', '2022-06-24 22:44:01'),
(27, 'Mention any other aspect of your experience with us that you like immediate improvement\r\n', 'areas-needing-improvement', NULL, 4, 'textarea', NULL, '2022-05-13 02:16:18', '2022-06-24 22:44:07');

INSERT INTO `survey_vendee` (`vendee_id`, `vendee_name`, `short_vendee`, `tel`, `email`, `url`, `suspended`) VALUES
(1, 'DIFF MEDICAL CENTRE, GUDU', 'DIFF', '090200111222', 'feedback@diffmedicalcentre.org', NULL, 0),
(2, 'PRIMECARE FERTILITY CLINIC, GARKI', 'PRIMECARE', '070200111222', 'customercare@primecarehospital.com', NULL, 0);


INSERT INTO `rating_scales` (`rating_scale_id`, `rating_scale_name`, `created_at`, `updated_at`) VALUES
(1, 'Ordinal Scale', '2022-05-12 21:01:03', '2022-05-12 21:01:03'),
(2, 'Interval Scale', '2022-05-12 21:01:03', '2022-05-12 21:01:03'),
(3, 'Comparative Scale', '2022-05-12 21:02:41', '2022-05-12 21:02:41'),
(4, 'Likert Scale', '2022-05-12 21:02:41', '2022-05-12 21:02:41'),
(5, '5-Star Review', '2022-05-13 02:47:56', '2022-05-13 02:47:56');


INSERT INTO `rating_scale_options` (`scale_option_id`, `scale_option_rank`, `scale_option`, `rating_scale_id`) VALUES
(1, 5, 'Advanced', 1),
(2, 4, 'Intermediate', 1),
(3, 3, 'Basic', 1),
(4, 2, 'Novice', 1),
(5, 1, 'Zero', 1),
(6, 5, 'Very satisfied', 2),
(7, 4, 'Somewhat satisfied', 2),
(8, 3, 'Neutral', 2),
(9, 2, 'Somewhat unsatisfied', 2),
(10, 1, 'Very unsatisfied', 2),
(11, 0, 'Not sure', 2),
(12, 5, 'Excellent', 3),
(13, 4, 'Very Good', 3),
(14, 3, 'Good', 3),
(15, 2, 'Poor', 3),
(16, 1, 'Very Poor', 3),
(17, 0, 'Not sure', 3);

set foreign_key_checks = 1;