-- ============================================================
-- NexusIT ITSM Platform — Seed Data
-- Run AFTER schema.sql
-- ============================================================
USE nexusit_db;

-- NOTE: The hash above = "password" for testing only.
-- To set real passwords run: cd api && node scripts/seed.js

-- ============================================================
-- SETTINGS
-- ============================================================
INSERT INTO settings (setting_key, setting_value, updated_by) VALUES
('app_name',         'NexusIT ITSM', 1),
('social_packages',  '["Basic - 3 Platforms","Standard - 5 Platforms","Premium - 8 Platforms","Enterprise - Full Suite"]', 1),
('hosting_packages', '["Shared Hosting","VPS - 2 Core","VPS - 4 Core","Dedicated Server","Cloud Hosting - Basic","Cloud Hosting - Pro"]', 1),
('website_packages', '["10 Hours Package","20 Hours Package","40 Hours Package","80 Hours Package","160 Hours Package"]', 1),
('cctv_visits',      '["2 Preventive / Unlimited Call-based","4 Preventive / Unlimited Call-based","Monthly Preventive + Callout","Quarterly Preventive Only"]', 1),
('it_visits',        '["2 Preventive / Unlimited Call-based","4 Preventive / Unlimited Call-based","Monthly Preventive","Quarterly Preventive","Call-based Only"]', 1),
('doc_types',        '["Passport","Emirates ID","Visa","Labour Card","Offer Letter","Contract","Insurance","Trade License","Certificate","Other"]', 1),
('hr_companies',     '["Pinetree IT","Pinetree Advertising","Quix Technical"]', 1),
('alert_days',       '[30,15,7]', 1),
('smtp_host',        'smtp.gmail.com', 1),
('smtp_port',        '587', 1),
('smtp_user',        'alerts@nexusit.ae', 1),
('smtp_from_name',   'NexusIT ITSM Alerts', 1),
('expiry_threshold', '30', 1)
ON DUPLICATE KEY UPDATE setting_value = VALUES(setting_value);

-- ============================================================
-- IT AMC SAMPLE DATA
-- ============================================================
INSERT INTO it_amc (contract_no,company_name,contact_person,contact_email,contact_phone,description,visit_type,start_date,expiry_date,contract_amount,status,created_by) VALUES
('IT001','Emirates NBD','Mohammed Al Ali','malali@enbd.ae','+971 4 609 0000','Full IT infrastructure support across 12 branches.','4 Preventive / Unlimited Call-based',DATE_SUB(CURDATE(),INTERVAL 300 DAY),DATE_ADD(CURDATE(),INTERVAL 65 DAY),48000.00,'active',1),
('IT002','DEWA','Saeed Al Tayer','saltayer@dewa.gov.ae','+971 4 601 9999','Data center and network infrastructure AMC.','2 Preventive / Unlimited Call-based',DATE_SUB(CURDATE(),INTERVAL 180 DAY),DATE_ADD(CURDATE(),INTERVAL 22 DAY),72000.00,'critical',1),
('IT003','Dubai Municipality','Ali Hassan','ahassan@dm.gov.ae','+971 4 221 5555','Server room and IT equipment maintenance.','Monthly Preventive',DATE_SUB(CURDATE(),INTERVAL 400 DAY),DATE_SUB(CURDATE(),INTERVAL 5 DAY),36000.00,'expired',3),
('IT004','Majid Al Futtaim','Rania Khalid','rkhalid@maf.ae','+971 4 294 9999','Retail IT infrastructure management across 12 malls.','4 Preventive / Unlimited Call-based',DATE_SUB(CURDATE(),INTERVAL 30 DAY),DATE_ADD(CURDATE(),INTERVAL 335 DAY),120000.00,'active',1);

-- ============================================================
-- CCTV SAMPLE DATA
-- ============================================================
INSERT INTO cctv_amc (contract_no,company_name,contact_person,contact_email,contact_phone,description,visit_type,start_date,expiry_date,contract_amount,status,created_by) VALUES
('CC001','Dubai International Airport','Tariq Al Mansouri','talmansouri@dxb.ae','+971 4 224 5555','CCTV certification and AMC for 420 cameras across 3 terminals.','4 Preventive / Unlimited Call-based',DATE_SUB(CURDATE(),INTERVAL 200 DAY),DATE_ADD(CURDATE(),INTERVAL 165 DAY),85000.00,'active',1),
('CC002','Emaar Hospitality Group','Nour Al Ahmad','nalahmad@emaar.ae','+971 4 399 9999','Hotel CCTV certification — 210 cameras across 3 properties.','2 Preventive / Unlimited Call-based',DATE_SUB(CURDATE(),INTERVAL 60 DAY),DATE_ADD(CURDATE(),INTERVAL 12 DAY),42000.00,'critical',1);

-- ============================================================
-- WEBSITE AMC SAMPLE DATA
-- ============================================================
INSERT INTO website_amc (contract_no,company_name,contact_person,contact_email,contact_phone,description,hours_package,start_date,expiry_date,contract_amount,status,created_by) VALUES
('WS001','RAK Properties','Hessa Al Qasimi','halqasimi@rakproperties.ae','+971 7 206 9999','Corporate website maintenance and monthly content updates.','20 Hours Package',DATE_SUB(CURDATE(),INTERVAL 150 DAY),DATE_ADD(CURDATE(),INTERVAL 215 DAY),18000.00,'active',4),
('WS002','Al Habtoor Group','Waleed Al Habtoor','walhabtoor@habtoor.ae','+971 4 207 9999','E-commerce platform and 5 subsidiary websites full AMC.','80 Hours Package',DATE_SUB(CURDATE(),INTERVAL 45 DAY),DATE_ADD(CURDATE(),INTERVAL 320 DAY),56000.00,'active',1),
('WS003','Ajman Bank','Lina Saad','lsaad@ajmanbank.ae','+971 6 714 0000','Banking website compliance and maintenance.','40 Hours Package',DATE_SUB(CURDATE(),INTERVAL 400 DAY),DATE_SUB(CURDATE(),INTERVAL 30 DAY),28000.00,'expired',3);

-- ============================================================
-- SOCIAL MEDIA SAMPLE DATA
-- ============================================================
INSERT INTO social_media (contract_no,company_name,contact_person,contact_email,contact_phone,description,package_name,start_date,expiry_date,contract_amount,status,created_by) VALUES
('SM001','Aldar Properties','Dana Al Suwaidi','dalsuwaidi@aldar.ae','+971 2 810 5555','Full social media management + SEO across 8 platforms.','Premium - 8 Platforms',DATE_SUB(CURDATE(),INTERVAL 90 DAY),DATE_ADD(CURDATE(),INTERVAL 275 DAY),96000.00,'active',1),
('SM002','Rotana Hotels','Ibrahim Youssef','iyoussef@rotana.com','+971 2 697 9000','Hotel group digital marketing and SEO - 8 hotel brands.','Enterprise - Full Suite',DATE_SUB(CURDATE(),INTERVAL 30 DAY),DATE_ADD(CURDATE(),INTERVAL 25 DAY),144000.00,'critical',1);

-- ============================================================
-- MICROSOFT EMAIL SAMPLE DATA
-- ============================================================
INSERT INTO microsoft_email (contract_no,company_name,contact_person,contact_email,contact_phone,description,total_licenses,cost_per_license,start_date,expiry_date,status,created_by) VALUES
('MS001','Abu Dhabi Commercial Bank','Khalifa Al Mazrouei','kalmazrouei@adcb.com','+971 2 621 1000','Microsoft 365 Business Premium - banking compliance edition.',850,18.00,DATE_SUB(CURDATE(),INTERVAL 180 DAY),DATE_ADD(CURDATE(),INTERVAL 185 DAY),'active',1),
('MS002','DP World','Sarah Al Zarouni','salzarouni@dpworld.com','+971 4 881 5555','Microsoft 365 Enterprise E3 for global logistics operations.',1200,22.00,DATE_SUB(CURDATE(),INTERVAL 60 DAY),DATE_ADD(CURDATE(),INTERVAL 7 DAY),'critical',1),
('MS003','Sharjah City Municipality','Omar Bin Rashid','obinrashid@sharjahcity.ae','+971 6 567 7777','Microsoft 365 Business Standard for government office.',320,14.00,DATE_SUB(CURDATE(),INTERVAL 400 DAY),DATE_SUB(CURDATE(),INTERVAL 15 DAY),'expired',3);

-- ============================================================
-- HOSTING SAMPLE DATA
-- ============================================================
INSERT INTO hosting_domain (contract_no,company_name,contact_person,contact_email,contact_phone,description,package_name,start_date,expiry_date,contract_amount,status,created_by) VALUES
('HO001','Gulf News','Priya Menon','pmenon@gulfnews.com','+971 4 344 0000','Dedicated server hosting and 12 domain renewals for news portal.','Dedicated Server',DATE_SUB(CURDATE(),INTERVAL 270 DAY),DATE_ADD(CURDATE(),INTERVAL 95 DAY),24000.00,'active',4),
('HO002','Lulu Hypermarket','Shams Al Muhairbi','salmuhairbi@luluhyper.com','+971 2 635 9000','Cloud hosting for e-commerce platform and 45 store microsites.','Cloud Hosting - Pro',DATE_SUB(CURDATE(),INTERVAL 20 DAY),DATE_ADD(CURDATE(),INTERVAL 345 DAY),36000.00,'active',1);

-- ============================================================
-- HR EMPLOYEES SAMPLE DATA
-- ============================================================
INSERT INTO employees (company,name,company_email,personal_email,mobile,date_of_joining,work_mode,location,created_by) VALUES
('Pinetree IT','Raj Kumar','raj.kumar@pinetreeit.ae','raj.kumar@gmail.com','+971 50 123 4567','2022-03-15','Onsite','UAE',1),
('Pinetree IT','Aisha Al Mansoori','aisha@pinetreeit.ae','aisha.personal@gmail.com','+971 55 234 5678','2023-01-10','Onsite','UAE',1),
('Pinetree Advertising','Priya Sharma','priya@pinetreeads.ae','priya.sharma@gmail.com','+91 98765 43210','2021-06-01','Online','India',1),
('Pinetree Advertising','Mohammed Salim','m.salim@pinetreeads.ae','msalim@gmail.com','+971 52 345 6789','2022-09-20','Onsite','UAE',1),
('Quix Technical','Deepak Nair','deepak@quixtechnical.ae','deepak.nair@gmail.com','+91 87654 32109','2023-04-05','Online','India',1),
('Quix Technical','Fatima Al Blooshi','fatima@quixtechnical.ae','fatima.b@gmail.com','+971 56 456 7890','2022-11-15','Onsite','UAE',1);

-- ============================================================
-- HR EMPLOYEE DOCUMENTS SAMPLE DATA
-- ============================================================
INSERT INTO employee_documents (employee_id,doc_type,expiry_date,description,status,created_by) VALUES
(1,'Passport',DATE_ADD(CURDATE(),INTERVAL 180 DAY),'Indian Passport','active',1),
(1,'Visa',DATE_ADD(CURDATE(),INTERVAL 25 DAY),'UAE Employment Visa','expiring',1),
(1,'Emirates ID',DATE_ADD(CURDATE(),INTERVAL 200 DAY),'Emirates ID Card','active',1),
(2,'Passport',DATE_ADD(CURDATE(),INTERVAL 365 DAY),'UAE Passport','active',1),
(2,'Labour Card',DATE_SUB(CURDATE(),INTERVAL 5 DAY),'Labour Card - Expired','expired',1),
(3,'Passport',DATE_ADD(CURDATE(),INTERVAL 90 DAY),'Indian Passport','active',1),
(4,'Visa',DATE_ADD(CURDATE(),INTERVAL 45 DAY),'UAE Employment Visa','active',1),
(5,'Passport',DATE_ADD(CURDATE(),INTERVAL 20 DAY),'Indian Passport - Expiring Soon','expiring',1),
(6,'Emirates ID',DATE_ADD(CURDATE(),INTERVAL 300 DAY),'Emirates ID Card','active',1);

-- ============================================================
-- HR COMPANY DOCUMENTS SAMPLE DATA
-- ============================================================
INSERT INTO company_documents (company,title,doc_type,expiry_date,description,status,created_by) VALUES
('Pinetree IT','Trade License 2025','Trade License',DATE_ADD(CURDATE(),INTERVAL 120 DAY),'Dubai Trade License renewal','active',1),
('Pinetree IT','Office Lease Agreement','Contract',DATE_ADD(CURDATE(),INTERVAL 200 DAY),'DIFC office lease','active',1),
('Pinetree IT','Professional Indemnity Insurance','Insurance',DATE_ADD(CURDATE(),INTERVAL 15 DAY),'Annual PI insurance renewal - expiring soon','expiring',1),
('Pinetree Advertising','Trade License 2025','Trade License',DATE_ADD(CURDATE(),INTERVAL 90 DAY),'Abu Dhabi Trade License','active',1),
('Pinetree Advertising','Media Production License','Certificate',DATE_SUB(CURDATE(),INTERVAL 10 DAY),'Media production certification - expired','expired',1),
('Quix Technical','Trade License 2025','Trade License',DATE_ADD(CURDATE(),INTERVAL 250 DAY),'Sharjah Trade License','active',1),
('Quix Technical','ISO 9001 Certificate','Certificate',DATE_ADD(CURDATE(),INTERVAL 180 DAY),'Quality management certification','active',1);
