-- =====================================================================
-- Core setup — essential reference data every install needs, regardless
-- of whether demo content is loaded. Run this after schema.sql. This is
-- configuration (roles, priorities, SLA targets, calendar, notification
-- templates), not fictional content — install.php always runs this.
-- =====================================================================

SET NAMES utf8mb4;

-- Roles -----------------------------------------------------------------
INSERT INTO roles (code, name) VALUES
 ('L1','L1 Support Agent'),
 ('L2','L2 Specialist'),
 ('L3','L3 Engineering'),
 ('LEAD','Support Team Lead'),
 ('HEAD','Head of Customer Service'),
 ('FINANCE','Finance'),
 ('ADMIN','System Administrator');

-- Default permission grants (BRD section 9's matrix). Admin can edit this from
-- Admin > Roles & Permissions — these are just the starting defaults.
INSERT INTO role_permissions (role_id, permission_key)
SELECT id, 'ticket.assign' FROM roles WHERE code IN ('LEAD','HEAD');
INSERT INTO role_permissions (role_id, permission_key)
SELECT id, 'ticket.escalate' FROM roles WHERE code IN ('L1','L2','L3','LEAD','HEAD');
INSERT INTO role_permissions (role_id, permission_key)
SELECT id, 'ticket.resolve_close' FROM roles WHERE code IN ('L1','L2','L3','LEAD','HEAD');
INSERT INTO role_permissions (role_id, permission_key)
SELECT id, 'billing.mark_billable' FROM roles WHERE code IN ('LEAD','HEAD','FINANCE');
INSERT INTO role_permissions (role_id, permission_key)
SELECT id, 'sla.configure' FROM roles WHERE code IN ('HEAD');

-- Products — rename/add your own later under Admin > Products. At least one
-- is required before any ticket can be raised.
INSERT INTO products (name, module) VALUES
 ('ERPCloud','HR & Payroll'),
 ('ERPCloud','Inventory'),
 ('ERPCloud','Accounts'),
 ('ERPCloud','Sales & CRM');

-- Categories ----------------------------------------------------------------
INSERT INTO categories (name, parent_id) VALUES
 ('Functional query', NULL),
 ('Defect / Bug', NULL),
 ('Data correction', NULL),
 ('Change request', NULL),
 ('Access / user setup', NULL);
INSERT INTO categories (name, parent_id) VALUES
 ('Payroll run', 1), ('Report mismatch', 1),
 ('Application error', 2), ('Data missing', 2),
 ('Ledger correction', 3),
 ('New feature', 4),
 ('Password reset', 5), ('New account', 5);

-- Priorities (the four-level scale itself is fixed by the system — FR-SUP-013 —
-- only the wording/colour here are meant to be edited later) --------------------
INSERT INTO priorities (code, name, definition, color, sort_order) VALUES
 ('P1','Critical','Business stopped, no workaround','#dc2626',1),
 ('P2','High','Major function impaired, workaround exists','#ea580c',2),
 ('P3','Medium','Minor function affected, limited users','#ca8a04',3),
 ('P4','Low','Query, cosmetic issue, enhancement idea','#64748b',4);

-- Business calendar + holidays ----------------------------------------------
INSERT INTO business_calendars (name, timezone, work_days, start_time, end_time, is_default) VALUES
 ('Business Hours (Mon-Fri)','Asia/Dhaka','1,2,3,4,5','09:00:00','18:00:00',1);

-- Teams & queues (membership is added once agents exist — Admin > Queues & Teams) --
INSERT INTO teams (name, description) VALUES
 ('Support Team','Default support team — rename or split as needed');
INSERT INTO queues (name, team_id, description, auto_assign) VALUES
 ('General Support',1,'Default queue for incoming tickets','none');

-- SLA policies — one per contract tier, matching the tiers used on the
-- Contracts screen (Standard / Gold / Platinum). Edit targets under
-- Admin > SLA Policies once you know your own commitments. -----------------
INSERT INTO sla_policies (name, contract_tier, calendar_id, is_24x7, effective_from, version, is_active) VALUES
 ('Platinum AMC','Platinum',1,0,CURDATE(),1,1),
 ('Gold AMC','Gold',1,0,CURDATE(),1,1),
 ('Standard AMC','Standard',1,0,CURDATE(),1,1);

-- Platinum targets (minutes)
INSERT INTO sla_targets (sla_policy_id, priority_id, first_response_minutes, resolution_minutes, update_frequency_minutes, warn_pct_1, warn_pct_2) VALUES
 (1,1,60,480,120,50,80),
 (1,2,240,960,240,50,80),
 (1,3,480,2400,480,50,80),
 (1,4,960,4800,1440,50,80);
-- Gold targets
INSERT INTO sla_targets (sla_policy_id, priority_id, first_response_minutes, resolution_minutes, update_frequency_minutes, warn_pct_1, warn_pct_2) VALUES
 (2,1,120,960,240,50,80),
 (2,2,480,1920,480,50,80),
 (2,3,960,3600,960,50,80),
 (2,4,1440,7200,1440,50,80);
-- Standard targets
INSERT INTO sla_targets (sla_policy_id, priority_id, first_response_minutes, resolution_minutes, update_frequency_minutes, warn_pct_1, warn_pct_2) VALUES
 (3,1,240,1920,480,50,80),
 (3,2,960,3600,960,50,80),
 (3,3,1440,7200,1440,50,80),
 (3,4,2880,9600,2880,50,80);

-- Escalation matrix (applies to all three policies here for simplicity) -----------
INSERT INTO escalation_levels (sla_policy_id, level, trigger_pct, trigger_on_breach, notify_role, action_note) VALUES
 (1,1,80,0,'LEAD','Warn team lead at 80% of resolution target'),
 (1,2,0,1,'HEAD','Escalate to Head of Customer Service on breach'),
 (2,1,80,0,'LEAD','Warn team lead at 80% of resolution target'),
 (2,2,0,1,'HEAD','Escalate to Head of Customer Service on breach'),
 (3,1,80,0,'LEAD','Warn team lead at 80% of resolution target'),
 (3,2,0,1,'HEAD','Escalate to Head of Customer Service on breach');

-- Notification templates — required before any email/in-app alert can be sent;
-- there is currently no "add new template" screen, only edit, so these must exist. --
INSERT INTO notification_templates (event_code, channel, subject, body) VALUES
 ('ticket_created_ack','email','Ticket {{ticket_no}} received — {{subject}}','Dear {{contact_name}},\n\nThank you for contacting {{company_name}} support. Your request has been logged as ticket {{ticket_no}}.\n\nSubject: {{subject}}\nPriority: {{priority}}\nPromised first response: {{response_due}}\n\nYou can track this ticket at any time from the customer portal.\n\nRegards,\nSupport Desk'),
 ('ticket_public_reply','email','Update on ticket {{ticket_no}} — {{subject}}','Dear {{contact_name}},\n\n{{agent_name}} has replied to your ticket {{ticket_no}}:\n\n{{message_body}}\n\nRegards,\nSupport Desk'),
 ('ticket_status_change','email','Ticket {{ticket_no}} is now {{status}}','Dear {{contact_name}},\n\nYour ticket {{ticket_no}} ({{subject}}) status changed to {{status}}.\n\nRegards,\nSupport Desk'),
 ('ticket_closed','email','Ticket {{ticket_no}} closed','Dear {{contact_name}},\n\nYour ticket {{ticket_no}} has been closed. Please rate your experience from the portal.\n\nRegards,\nSupport Desk'),
 ('ticket_assigned','in_app','','Ticket {{ticket_no}} assigned to you: {{subject}}'),
 ('ticket_customer_reply','in_app','','Customer replied on ticket {{ticket_no}}'),
 ('sla_warning','in_app','','Ticket {{ticket_no}} is at {{pct}}% of its resolution target'),
 ('sla_breach','in_app','','Ticket {{ticket_no}} has BREACHED its SLA target'),
 ('ticket_escalated','in_app','','Ticket {{ticket_no}} escalated to level {{level}}');