-- GulfLink MIS — Seed / Demo Data
-- All passwords are bcrypt of "password123"

INSERT IGNORE INTO role_categories (name) VALUES
('Construction'),('Hospitality'),('Logistics'),('Oil & Gas'),('Security'),('Healthcare'),('IT & Technical');

INSERT IGNORE INTO candidate_statuses (label, color_hex, sort_order) VALUES
('Applied',             '#6B7280', 1),
('Shortlisted',         '#3B82F6', 2),
('Interview Scheduled', '#F59E0B', 3),
('Interviewed',         '#8B5CF6', 4),
('Selected',            '#10B981', 5),
('Docs Pending',        '#F59E0B', 6),
('Medicals Pending',    '#F97316', 7),
('Visa Processing',     '#06B6D4', 8),
('On The Job',          '#059669', 9),
('Completed',           '#1D4ED8',10),
('Rejected',            '#EF4444',11),
('Withdrawn',           '#9CA3AF',12);

INSERT IGNORE INTO companies (user_id, name, country, city, contact_person, contact_email, contact_phone) VALUES
(4, 'Al Faris Construction', 'UAE',     'Dubai',    'Ahmed Al Faris',   'alfaris@company.ae',   '+971 50 123 4567'),
(5, 'Gulf Petrochem',        'Qatar',   'Doha',     'Tariq Hassan',     'petrochem@company.qa', '+974 55 234 5678'),
(NULL,'Al Noor Hospitality', 'Bahrain', 'Manama',   'Noor Al Mansoori', 'noor@alnoor.bh',       '+973 36 345 6789'),
(NULL,'Dhofar Infra',        'Oman',    'Salalah',  'Saif Al Balushi',  'saif@dhofar.om',       '+968 99 456 7890'),
(NULL,'Oman Logistics',      'Oman',    'Muscat',   'Khalid Rashid',    'khalid@omanlog.om',    '+968 92 567 8901'),
(NULL,'Al Raha Hotels',      'UAE',     'Abu Dhabi','Sara Al Mazrouei', 'sara@alraha.ae',       '+971 55 678 9012');

INSERT IGNORE INTO vacancies (company_id, category_id, role_title, total_seats, default_fee, deadline, status) VALUES
(1, 1, 'Civil Labourer',     350, 45000.00, '2026-06-30', 'active'),
(2, 4, 'Technician',         120, 55000.00, '2026-06-15', 'active'),
(3, 2, 'Housekeeping Staff',  80, 38000.00, '2026-05-19', 'urgent'),
(4, 1, 'Mason / Carpenter',  180, 42000.00, '2026-07-31', 'active'),
(5, 3, 'Driver / Helper',     60, 35000.00, '2026-06-30', 'active'),
(6, 2, 'F&B Staff',           52, 40000.00, '2026-07-15', 'active');

INSERT IGNORE INTO candidates (user_id, full_name, phone, email, city, state, category_id, trade_skill, reg_source) VALUES
(6,    'Rajesh Patil',   '+91 98765 43210', 'rajesh@candidate.in', 'Nagpur',     'Maharashtra', 1, 'Civil Labourer', 'advertisement'),
(NULL, 'Suresh Kumar',   '+91 87654 32109', 'suresh@email.com',    'Pune',       'Maharashtra', 4, 'Technician',     'walk_in'),
(NULL, 'Mohammed Aziz',  '+91 76543 21098', 'aziz@email.com',      'Mumbai',     'Maharashtra', 3, 'Driver',         'referral'),
(NULL, 'Priya Menon',    '+91 65432 10987', 'priya@email.com',     'Hyderabad',  'Telangana',   2, 'Housekeeping',   'online_portal'),
(NULL, 'Anwar Shaikh',   '+91 54321 09876', 'anwar@email.com',     'Aurangabad', 'Maharashtra', 1, 'Mason',          'walk_in'),
(NULL, 'Ravi Nair',      '+91 43210 98765', 'ravi@email.com',      'Kochi',      'Kerala',      2, 'Housekeeping',   'advertisement'),
(NULL, 'Fatima Begum',   '+91 32109 87654', 'fatima@email.com',    'Mumbai',     'Maharashtra', 2, 'F&B Staff',      'advertisement');

INSERT IGNORE INTO candidate_vacancies (candidate_id, vacancy_id, status_id, agreed_fee, assigned_date, assigned_by) VALUES
(1, 1, 5,  45000.00, '2026-04-01', 3),
(2, 2, 3,  55000.00, '2026-04-15', 3),
(3, 5, 9,  35000.00, '2026-03-20', 3),
(4, 3, 6,  38000.00, '2026-04-25', 3),
(6, 3, 11, 38000.00, '2026-04-10', 3),
(7, 6, 5,  40000.00, '2026-04-28', 3);

INSERT IGNORE INTO campaigns (title, medium, city, run_date, budget, created_by) VALUES
('Civil Labour Drive - Pune',       'Times of India', 'Pune',      '2026-05-01', 38500.00, 3),
('Technician Recruitment - Mumbai', 'Facebook/Meta',  'Mumbai',    '2026-05-03', 18000.00, 3),
('Housekeeping - Hyderabad',        'WhatsApp/Flex',  'Hyderabad', '2026-05-10', 12000.00, 3);

INSERT IGNORE INTO interviews (vacancy_id, campaign_id, interview_date, city, venue, status, created_by) VALUES
(1, 1, '2026-05-18', 'Pune',      'Vivanta Hotel, Kharadi',   'confirmed', 3),
(2, 2, '2026-05-24', 'Mumbai',    'Hotel Orchid, Vile Parle', 'filling',   3),
(3, 3, '2026-06-02', 'Hyderabad', 'Fortune Inn, Begumpet',    'upcoming',  3),
(4, NULL,'2026-04-28','Nagpur',   'Le Meridien Nagpur',       'completed', 3),
(1, NULL,'2026-04-10','Hyderabad','Taj Deccan, Banjara Hills', 'completed', 3);

INSERT IGNORE INTO payments (candidate_vacancy_id, receipt_no, amount, payment_date, mode, installment_no, received_by) VALUES
(1, 'RCP-2026-101', 11250.00, '2026-04-10', 'cash',          1, 2),
(1, 'RCP-2026-102', 11250.00, '2026-05-01', 'upi',           2, 2),
(3, 'RCP-2026-103', 11667.00, '2026-03-15', 'cash',          1, 2),
(3, 'RCP-2026-104', 11667.00, '2026-04-01', 'cash',          2, 2),
(3, 'RCP-2026-105', 11666.00, '2026-04-20', 'upi',           3, 2),
(4, 'RCP-2026-106',  9500.00, '2026-05-05', 'cheque',        1, 2),
(2, 'RCP-2026-107', 13750.00, '2026-05-03', 'bank_transfer', 1, 2),
(6, 'RCP-2026-108', 10000.00, '2026-04-28', 'upi',           1, 2),
(6, 'RCP-2026-109', 10000.00, '2026-05-09', 'upi',           2, 2);

INSERT IGNORE INTO transactions (type, amount, txn_date, description, category, ref_type, ref_id, entered_by) VALUES
('income',  240000.00, '2026-05-09', 'Candidate fees - Batch #48',          'service_fee', 'payment',   9, 2),
('expense',  38500.00, '2026-05-08', 'Advertisement - Times of India, Pune','advertising', 'interview', 1, 3),
('expense',  14200.00, '2026-05-07', 'Medical camp - Mumbai batch',         'medical',     'interview', 2, 3),
('income',  180000.00, '2026-05-05', 'Candidate fees - Batch #47',          'service_fee', 'payment',   7, 2),
('expense',  22000.00, '2026-05-04', 'Venue booking - Vivanta Pune',        'venue',       'interview', 1, 3),
('expense',  18000.00, '2026-05-03', 'Social media ads - Facebook/Meta',    'advertising', 'campaign',  2, 3),
('income',   95000.00, '2026-05-02', 'Service fee - Oman Logistics',        'service_fee', 'invoice',   5, 2),
('income',  160000.00, '2026-04-28', 'Candidate fees - Batch #46',          'service_fee', 'payment',   6, 2),
('expense',   8500.00, '2026-04-25', 'Stationery and office supplies',      'admin',       NULL,     NULL, 3),
('income',  210000.00, '2026-04-10', 'Candidate fees - Batch #45',          'service_fee', 'payment',   4, 2),
('expense',  35000.00, '2026-04-08', 'Advertisement - Deccan Chronicle',    'advertising', 'interview', 5, 3),
('expense',  18000.00, '2026-04-06', 'Venue - Taj Deccan Hyderabad',        'venue',       'interview', 5, 3);
