-- =====================================================================
-- Optional demo/sample content — fictional customers, agents, tickets and
-- knowledge base articles so you can try the system immediately. Safe to
-- skip (install.php offers it as a checkbox) and delete later; nothing
-- else depends on this data. Run after schema.sql + core_setup.sql, with
-- exactly one users row (your own admin account) already present.
--
-- Every seeded login (agent or portal) uses the password:  Passw0rd!
-- =====================================================================

SET NAMES utf8mb4;

-- Demo agents (in addition to the admin account created during install) ---------
INSERT INTO users (employee_no, name, email, password_hash, role_id, is_active) VALUES
 ('EMP-002','Rafiul Karim','r.karim@erp.com.bd','$2y$12$RKjWDNtnbEePFF1ujeJzaOprmHs.WoiWstZgbOl0SlHW9wMSsQooW',(SELECT id FROM roles WHERE code='L2'),1),
 ('EMP-003','Shirin Nahar','s.nahar@erp.com.bd','$2y$12$RKjWDNtnbEePFF1ujeJzaOprmHs.WoiWstZgbOl0SlHW9wMSsQooW',(SELECT id FROM roles WHERE code='L1'),1),
 ('EMP-004','Tanvir Islam','t.islam@erp.com.bd','$2y$12$RKjWDNtnbEePFF1ujeJzaOprmHs.WoiWstZgbOl0SlHW9wMSsQooW',(SELECT id FROM roles WHERE code='L1'),1),
 ('EMP-005','Ariful Hoque','a.hoque@erp.com.bd','$2y$12$RKjWDNtnbEePFF1ujeJzaOprmHs.WoiWstZgbOl0SlHW9wMSsQooW',(SELECT id FROM roles WHERE code='LEAD'),1),
 ('EMP-006','Nusrat Jahan','n.jahan@erp.com.bd','$2y$12$RKjWDNtnbEePFF1ujeJzaOprmHs.WoiWstZgbOl0SlHW9wMSsQooW',(SELECT id FROM roles WHERE code='HEAD'),1),
 ('EMP-007','Farhana Yasmin','f.yasmin@erp.com.bd','$2y$12$RKjWDNtnbEePFF1ujeJzaOprmHs.WoiWstZgbOl0SlHW9wMSsQooW',(SELECT id FROM roles WHERE code='FINANCE'),1);

-- Give the demo team/queue created in core_setup.sql some members -----------------
INSERT INTO team_members (team_id, user_id, skills) VALUES
 (1,2,'Payroll,HR'), (1,3,'Payroll'), (1,4,'Inventory,Accounts');

-- Customers, contacts, contracts ---------------------------------------------
INSERT INTO customers (name, code, industry) VALUES
 ('Meghna Textiles Ltd.','CUST-001','Textiles'),
 ('Bay Foods Ltd.','CUST-002','FMCG'),
 ('Padma Group','CUST-003','Conglomerate'),
 ('Delta Pharma','CUST-004','Pharmaceuticals'),
 ('Shah Logistics','CUST-005','Logistics');

INSERT INTO contacts (customer_id, name, email, phone, is_primary, portal_access, password_hash, is_active) VALUES
 (1,'Nasrin Akter','nasrin@meghnatextiles.example','+880 17XX-XXXXXX',1,1,'$2y$12$RKjWDNtnbEePFF1ujeJzaOprmHs.WoiWstZgbOl0SlHW9wMSsQooW',1),
 (1,'Kamal Uddin','kamal@meghnatextiles.example','+880 18XX-XXXXXX',0,1,'$2y$12$RKjWDNtnbEePFF1ujeJzaOprmHs.WoiWstZgbOl0SlHW9wMSsQooW',1),
 (2,'Bilkis Sultana','bilkis@bayfoods.example','+880 17XX-XXXXXX',1,1,'$2y$12$RKjWDNtnbEePFF1ujeJzaOprmHs.WoiWstZgbOl0SlHW9wMSsQooW',1),
 (3,'Imran Chowdhury','imran@padmagroup.example','+880 19XX-XXXXXX',1,1,'$2y$12$RKjWDNtnbEePFF1ujeJzaOprmHs.WoiWstZgbOl0SlHW9wMSsQooW',1),
 (4,'Farzana Rahman','farzana@deltapharma.example','+880 16XX-XXXXXX',1,1,'$2y$12$RKjWDNtnbEePFF1ujeJzaOprmHs.WoiWstZgbOl0SlHW9wMSsQooW',1),
 (5,'Tarek Aziz','tarek@shahlogistics.example','+880 15XX-XXXXXX',1,1,'$2y$12$RKjWDNtnbEePFF1ujeJzaOprmHs.WoiWstZgbOl0SlHW9wMSsQooW',1);

INSERT INTO contracts (customer_id, contract_no, tier, support_hours_total, support_hours_used, coverage_window, start_date, end_date) VALUES
 (1,'AMC-2026-0031','Platinum',40,18,'24x6',CURDATE(),DATE_ADD(CURDATE(), INTERVAL 1 YEAR)),
 (2,'AMC-2026-0044','Gold',24,6,'Business hours',CURDATE(),DATE_ADD(CURDATE(), INTERVAL 1 YEAR)),
 (3,'AMC-2026-0052','Standard',16,3,'Business hours',CURDATE(),DATE_ADD(CURDATE(), INTERVAL 1 YEAR)),
 (4,'AMC-2026-0061','Gold',24,20,'Business hours',CURDATE(),DATE_ADD(CURDATE(), INTERVAL 1 YEAR)),
 (5,'AMC-2025-0090','Standard',16,16,'Business hours',DATE_SUB(CURDATE(), INTERVAL 1 YEAR),CURDATE());

INSERT INTO contract_products (contract_id, product_id) VALUES
 (1,1),(1,2),(1,3),(1,4),
 (2,1),(2,3),
 (3,2),
 (4,1),(4,3),
 (5,2);

-- Canned responses -------------------------------------------------------------
INSERT INTO canned_responses (title, body) VALUES
 ('Acknowledge and investigating','Thank you {{contact_name}}, we have received your request and are investigating. We will update you shortly.'),
 ('Request more information','Thank you {{contact_name}}. Could you please share a screenshot and the exact steps that reproduce this issue?'),
 ('Resolution — please confirm','Hello {{contact_name}}, we believe this is now resolved. Please confirm so we can close ticket {{ticket_no}}, or let us know if you are still seeing the issue.');

-- Knowledge base ------------------------------------------------------------
INSERT INTO kb_categories (name) VALUES ('Payroll'), ('Inventory'), ('Accounts'), ('Access & Login');
INSERT INTO kb_articles (category_id, product_id, title, slug, body, keywords, visibility, status, author_id, published_at) VALUES
 (1,1,'Payroll pre-run checklist (all versions)','payroll-pre-run-checklist','Before running payroll: 1) Confirm attendance is locked for the period. 2) Verify all new joiners have a grade code assigned. 3) Check pending leave-without-pay entries. 4) Run the payroll simulation report and compare against last period.','payroll, pre-run, checklist','customer','published',2,NOW()),
 (1,1,'Resetting a locked user account','resetting-a-locked-user-account','If a user account is locked after failed login attempts, an administrator can unlock it from Admin > Users, or the user can use "Forgot password" on the login screen to reset it themselves.','locked account, password, reset','customer','published',2,NOW()),
 (1,1,'Object reference not set — during salary sheet generation','object-reference-not-set-salary-sheet','This error usually means an employee record is missing a grade code, most often after a bulk import. Check Admin > Employees for records with a blank grade, correct them, and re-run the payroll step.','object reference,payroll,error,grade code','customer','published',2,NOW()),
 (3,3,'VAT 6.3 return — common mismatches','vat-6-3-return-common-mismatches','Common causes of a VAT return mismatch are: sales invoices posted after the period lock, credit notes not linked to their original invoice, and manual journal entries bypassing the sales register.','vat, 6.3, return, mismatch','customer','published',4,NOW()),
 (2,2,'Closing a period in Inventory','closing-a-period-in-inventory','A stock period can only be closed once all pending GRNs and issue vouchers for that period are posted. Use Inventory > Period Close and review the exceptions list before confirming.','inventory, period close, stock','customer','published',4,NOW());

-- Demo tickets ------------------------------------------------------------------
-- 1) Breached P1, in progress
INSERT INTO tickets (ticket_no, customer_id, contact_id, contract_id, product_id, subject, description, category_id, priority_id, severity, channel, status, queue_id, assignee_id, sla_policy_id, first_response_minutes, resolution_minutes, response_due_at, resolution_due_at, first_response_at, is_response_breached, is_resolution_breached, escalation_level, is_out_of_entitlement, is_billable, created_by_contact_id, created_at) VALUES
 ('TKT-DEMO-00001',1,1,1,1,'Payroll run fails at step 3 — "Object reference not set"','Payroll for this month fails at step 3 with "Object reference not set" during salary sheet generation. 1,240 employees affected, disbursement is due in 2 days.',6,1,'Business stopped','Portal','IN_PROGRESS',1,2,1,60,480,DATE_SUB(NOW(), INTERVAL 2 HOUR),DATE_SUB(NOW(), INTERVAL 1 HOUR),DATE_SUB(NOW(), INTERVAL 3 HOUR),0,1,1,0,0,1,DATE_SUB(NOW(), INTERVAL 3 HOUR));

INSERT INTO ticket_messages (ticket_id, contact_id, type, body, created_at) VALUES
 (1,1,'public_reply','Payroll for this month fails at step 3 with "Object reference not set" during salary sheet generation. 1,240 employees affected, disbursement is due in 2 days. Screenshot and log attached.',DATE_SUB(NOW(), INTERVAL 3 HOUR));
INSERT INTO ticket_messages (ticket_id, user_id, type, body, created_at) VALUES
 (1,2,'internal_note','Reproduced on the customer sandbox. Two employees have a null grade code after the last grade-restructure import. Raising a defect against the payroll build.',DATE_SUB(NOW(), INTERVAL 2 HOUR)),
 (1,2,'public_reply','Thank you Nasrin. We have reproduced the issue and identified the cause as two employee records missing a grade code. Our development team is preparing a fix; meanwhile we can process payroll by correcting those two records manually. Shall we proceed?',DATE_SUB(NOW(), INTERVAL 1 HOUR));
UPDATE tickets SET linked_record_type='Defect', linked_record_ref='BUG-0001', linked_record_status='In Dev' WHERE id=1;

-- 2) New, unassigned
INSERT INTO tickets (ticket_no, customer_id, contact_id, contract_id, product_id, subject, description, category_id, priority_id, severity, channel, status, queue_id, sla_policy_id, first_response_minutes, resolution_minutes, response_due_at, resolution_due_at, is_out_of_entitlement, is_billable, created_by_contact_id, created_at) VALUES
 ('TKT-DEMO-00002',3,4,3,3,'Cannot post GRN — item locked','GRN posting fails saying the item is locked by another user, but no one else is using it.',9,2,'Major impact','Portal','NEW',1,3,960,3600,DATE_ADD(NOW(), INTERVAL 4 HOUR),DATE_ADD(NOW(), INTERVAL 3 DAY),0,0,4,NOW());
INSERT INTO ticket_messages (ticket_id, contact_id, type, body, created_at) VALUES
 (2,4,'public_reply','GRN posting fails saying the item is locked by another user, but no one else is using it. Happens every time for item code INV-2231.',NOW());

-- 3) Resolved, awaiting customer confirmation
INSERT INTO tickets (ticket_no, customer_id, contact_id, contract_id, product_id, subject, description, category_id, priority_id, severity, channel, status, queue_id, assignee_id, sla_policy_id, first_response_minutes, resolution_minutes, response_due_at, resolution_due_at, first_response_at, resolved_at, resolution_code, root_cause, is_out_of_entitlement, is_billable, created_by_contact_id, created_at) VALUES
 ('TKT-DEMO-00003',1,1,1,1,'User cannot log in after reset','User account remains locked after a password reset was performed.',12,2,'Minor impact','Email','RESOLVED',1,4,1,240,960,DATE_SUB(NOW(), INTERVAL 1 DAY),DATE_SUB(NOW(), INTERVAL 12 HOUR),DATE_SUB(NOW(), INTERVAL 1 DAY),DATE_SUB(NOW(), INTERVAL 18 HOUR),'Configuration fix','User setup error',0,0,1,DATE_SUB(NOW(), INTERVAL 1 DAY));
INSERT INTO ticket_messages (ticket_id, contact_id, type, body, created_at) VALUES
 (3,1,'public_reply','User account remains locked after a password reset was performed.',DATE_SUB(NOW(), INTERVAL 1 DAY));
INSERT INTO ticket_messages (ticket_id, user_id, type, body, created_at) VALUES
 (3,4,'public_reply','Hello Nasrin, the account is unlocked now and a temporary password has been sent to the user separately. Please confirm this resolves it so we can close the ticket.',DATE_SUB(NOW(), INTERVAL 18 HOUR));

-- 4) Closed with CSAT
INSERT INTO tickets (ticket_no, customer_id, contact_id, contract_id, product_id, subject, description, category_id, priority_id, severity, channel, status, queue_id, assignee_id, sla_policy_id, first_response_minutes, resolution_minutes, response_due_at, resolution_due_at, first_response_at, resolved_at, closed_at, resolution_code, root_cause, is_out_of_entitlement, is_billable, created_by_contact_id, created_at) VALUES
 ('TKT-DEMO-00004',1,2,1,4,'New user access — accounts team','Please create portal access for two new accounts-team users.',13,4,'Cosmetic','Portal','CLOSED',1,3,1,960,4800,DATE_SUB(NOW(), INTERVAL 10 DAY),DATE_SUB(NOW(), INTERVAL 9 DAY),DATE_SUB(NOW(), INTERVAL 10 DAY),DATE_SUB(NOW(), INTERVAL 9 DAY),DATE_SUB(NOW(), INTERVAL 9 DAY),'Completed as requested','User setup',0,0,2,DATE_SUB(NOW(), INTERVAL 10 DAY));
INSERT INTO csat_responses (ticket_id, rating, comment) VALUES (4,5,'Fast and friendly, thank you.');

-- Effort logs -------------------------------------------------------------------
INSERT INTO time_logs (ticket_id, user_id, log_date, duration_minutes, activity_type, description, is_billable) VALUES
 (1,2,CURDATE(),225,'Investigation','Reproduced defect and diagnosed root cause',0),
 (3,4,CURDATE(),30,'Fix','Unlocked account and reset credentials',0);