-- =====================================================================
--  DEV FIXTURE — DO NOT IMPORT INTO PRODUCTION (KF-20260908-06)
--
--  Three fictional companies that completed the full journey (guest
--  assessment -> claim -> activation -> registers/docs/reviews -> retake),
--  for demo/staging environments ONLY. Owner logins use password
--  Kualifi-Demo-2026 (aina@santapan.example etc.).
--
--  THIS FILE SHIPS DISARMED. Importing it untouched inserts NOTHING:
--    1. @armed is 0 below — edit it to 1 deliberately to arm.
--    2. Even armed, it refuses any database holding a REAL tenant
--       (any user whose email is not *.example).
--    3. The original re-import guard (@already) still applies.
--  When any gate is closed the companies insert zero rows and the import
--  ABORTS at the first dependent statement with a foreign-key error
--  (company_id 0). AN ERROR HERE IS THE GUARD WORKING: nothing was
--  inserted. That loud stop is deliberate — a silent partial import is
--  the failure mode this file is built to prevent.
--
--  Cleanup for a demo environment: the DELETE block at the bottom, or
--  bin/purge_demo_companies.php.
-- =====================================================================

-- The disarm failsafe relies on foreign-key rejection of id 0, so force
-- FK checks ON regardless of the session (phpMyAdmin's Import screen has
-- an off switch; this SET overrides it).
SET FOREIGN_KEY_CHECKS = 1;

-- ARM DELIBERATELY: set to 1 only on a demo/staging database.
SET @armed := 0;
-- Any real tenant present forces every gate closed, armed or not.
SET @real_tenants := (SELECT COUNT(*) FROM users WHERE email NOT LIKE '%.example');

SET @std  = (SELECT id FROM standards WHERE code='SIRIM55');
SET @already = (SELECT COUNT(*) FROM users WHERE email='aina@santapan.example');
SET @go = IF(@armed = 1 AND @real_tenants = 0 AND @already = 0, 1, 0);
SET @hash = '$2y$10$K54qFW0uBZv2Om2U7nVcseG4twdGY8kHplNYItsZ3dKP8bul3NVXy';
SET @free = (SELECT id FROM plans WHERE code='free');

-- ================= 1. Companies + owners + subscriptions ==============
INSERT INTO companies (name, state, ai_quota_month, created_at)
SELECT 'Santapan Nusantara Sdn Bhd', 'SGR', 20, '2026-04-18 09:12:00' WHERE @go=1;
SET @c1 = IF(@go=1, LAST_INSERT_ID(), 0);
INSERT INTO companies (name, state, ai_quota_month, created_at)
SELECT 'Kargo Lestari Logistics Sdn Bhd', 'JHR', 20, '2026-06-02 14:40:00' WHERE @go=1;
SET @c2 = IF(@go=1, LAST_INSERT_ID(), 0);
INSERT INTO companies (name, state, ai_quota_month, created_at)
SELECT 'Binaan Pantai Timur Sdn Bhd', 'TRG', 20, '2026-07-10 11:05:00' WHERE @go=1;
SET @c3 = IF(@go=1, LAST_INSERT_ID(), 0);

INSERT INTO users (company_id, name, email, password_hash, role, verified_at, created_at, last_login_at)
SELECT @c1, 'Aina Rahman',  'aina@santapan.example',      @hash, 'owner', '2026-04-18 09:20:00', '2026-04-18 09:12:00', '2026-07-28 08:41:00' WHERE @go=1;
SET @u1 = IF(@go=1, LAST_INSERT_ID(), 0);
INSERT INTO users (company_id, name, email, password_hash, role, verified_at, created_at, last_login_at)
SELECT @c2, 'Daniel Wong',  'daniel@kargolestari.example', @hash, 'owner', '2026-06-02 14:52:00', '2026-06-02 14:40:00', '2026-07-27 16:03:00' WHERE @go=1;
SET @u2 = IF(@go=1, LAST_INSERT_ID(), 0);
INSERT INTO users (company_id, name, email, password_hash, role, verified_at, created_at, last_login_at)
SELECT @c3, 'Wan Azlan',    'azlan@binaanpt.example',      @hash, 'owner', '2026-07-10 11:15:00', '2026-07-10 11:05:00', '2026-07-29 10:22:00' WHERE @go=1;
SET @u3 = IF(@go=1, LAST_INSERT_ID(), 0);

INSERT INTO subscriptions (company_id, plan_id, status, started_at)
SELECT c.id, @free, 'active', c.created_at FROM companies c WHERE c.id IN (@c1,@c2,@c3) AND @free IS NOT NULL;

-- ================= 2. Assessments (guest attempt claimed + retake) ====
-- Answers are inserted per question, then score/outcome are COMPUTED with
-- the same formula and thresholds the app uses — always consistent.

-- Santapan: guest attempt (later claimed), Building-range answers
INSERT INTO assessments (standard_id, company_id, guest_token, guest_email, guest_company_name, completed_at, created_at)
VALUES (@std, @c1, MD5('demo-santapan-1'), 'aina@santapan.example', 'Santapan Nusantara Sdn Bhd', '2026-04-17 21:34:00', '2026-04-17 21:20:00');
SET @a1 = IF(@go=1, LAST_INSERT_ID(), 0);
INSERT INTO assessment_answers (assessment_id, question_id, answer)
SELECT @a1, q.id, CASE MOD(q.sort_order DIV 10, 3) WHEN 0 THEN 2 WHEN 1 THEN 1 ELSE 0 END
FROM assessment_questions q WHERE q.standard_id=@std AND q.active=1;

-- Santapan: in-app retake after three months of work
INSERT INTO assessments (standard_id, company_id, completed_at, created_at)
VALUES (@std, @c1, '2026-07-20 10:05:00', '2026-07-20 09:55:00');
SET @a2 = IF(@go=1, LAST_INSERT_ID(), 0);
INSERT INTO assessment_answers (assessment_id, question_id, answer)
SELECT @a2, q.id, CASE WHEN MOD(q.sort_order DIV 10, 6)=0 THEN 1 ELSE 2 END
FROM assessment_questions q WHERE q.standard_id=@std AND q.active=1;

-- Kargo: guest attempt, Foundation-range
INSERT INTO assessments (standard_id, company_id, guest_token, guest_email, guest_company_name, completed_at, created_at)
VALUES (@std, @c2, MD5('demo-kargo-1'), 'daniel@kargolestari.example', 'Kargo Lestari Logistics Sdn Bhd', '2026-06-01 16:11:00', '2026-06-01 15:58:00');
SET @a3 = IF(@go=1, LAST_INSERT_ID(), 0);
INSERT INTO assessment_answers (assessment_id, question_id, answer)
SELECT @a3, q.id, CASE MOD(q.sort_order DIV 10, 3) WHEN 0 THEN 1 WHEN 1 THEN 1 ELSE 0 END
FROM assessment_questions q WHERE q.standard_id=@std AND q.active=1;

-- Kargo: retake, Progressing-range
INSERT INTO assessments (standard_id, company_id, completed_at, created_at)
VALUES (@std, @c2, '2026-07-25 09:30:00', '2026-07-25 09:18:00');
SET @a4 = IF(@go=1, LAST_INSERT_ID(), 0);
INSERT INTO assessment_answers (assessment_id, question_id, answer)
SELECT @a4, q.id, CASE WHEN MOD(q.sort_order DIV 10, 3)=0 THEN 2 ELSE 1 END
FROM assessment_questions q WHERE q.standard_id=@std AND q.active=1;

-- Binaan: guest attempt, Foundation-range
INSERT INTO assessments (standard_id, company_id, guest_token, guest_email, guest_company_name, completed_at, created_at)
VALUES (@std, @c3, MD5('demo-binaan-1'), 'azlan@binaanpt.example', 'Binaan Pantai Timur Sdn Bhd', '2026-07-09 20:45:00', '2026-07-09 20:31:00');
SET @a5 = IF(@go=1, LAST_INSERT_ID(), 0);
INSERT INTO assessment_answers (assessment_id, question_id, answer)
SELECT @a5, q.id, CASE WHEN MOD(q.sort_order DIV 10, 3)=1 THEN 1 ELSE 0 END
FROM assessment_questions q WHERE q.standard_id=@std AND q.active=1;

-- Binaan: retake two weeks in, Building-range
INSERT INTO assessments (standard_id, company_id, completed_at, created_at)
VALUES (@std, @c3, '2026-07-28 15:12:00', '2026-07-28 15:00:00');
SET @a6 = IF(@go=1, LAST_INSERT_ID(), 0);
INSERT INTO assessment_answers (assessment_id, question_id, answer)
SELECT @a6, q.id, CASE MOD(q.sort_order DIV 10, 3) WHEN 0 THEN 2 WHEN 1 THEN 1 ELSE 0 END
FROM assessment_questions q WHERE q.standard_id=@std AND q.active=1;

-- Compute score_pct + outcome exactly as Assessment::score() does.
UPDATE assessments a
JOIN standards s ON s.id = a.standard_id
JOIN (SELECT aa.assessment_id,
             ROUND(SUM(aa.answer * q.weight) / SUM(2 * q.weight) * 100) AS pct
      FROM assessment_answers aa JOIN assessment_questions q ON q.id = aa.question_id
      GROUP BY aa.assessment_id) t ON t.assessment_id = a.id
SET a.score_pct = t.pct,
    a.outcome = CASE
      WHEN t.pct >= s.assess_ready_from       THEN 'assessment_ready'
      WHEN t.pct >= s.assess_progressing_from THEN 'progressing'
      WHEN t.pct >= s.assess_building_from    THEN 'building'
      ELSE 'foundation' END
WHERE a.id IN (@a1,@a2,@a3,@a4,@a5,@a6);

-- ================= 3. Activation (profile + template copy) ============
INSERT INTO company_profile (company_id, standard_id, boundaries, activities, exclusions, significant_threshold, setup_step, activated_at) VALUES
  (@c1, @std, 'Main plant and warehouse, Shah Alam, Selangor', 'Manufacture and distribution of ready-to-eat sauces and pastes for retail and food service', NULL, 12, 8, '2026-04-20 15:30:00'),
  (@c2, @std, 'HQ Johor Bahru; depots in Pasir Gudang and Kluang', 'Road freight, warehousing and last-mile distribution across Peninsular Malaysia', NULL, 12, 8, '2026-06-05 10:00:00'),
  (@c3, @std, 'Head office Kuala Terengganu; active project sites on the East Coast', 'Civil construction and infrastructure works for state and private clients', NULL, 12, 8, '2026-07-12 16:45:00');

-- Template copy — same blocks as Tenant::activateStandardContent(), except
-- obligations get active=1 (the wizard's activation step enables them).
INSERT IGNORE INTO company_requirements (company_id, requirement_id)
SELECT c.id, r.id FROM companies c JOIN std_requirements r ON r.standard_id=@std WHERE c.id IN (@c1,@c2,@c3);

INSERT INTO esg_stakeholders (company_id, party, std_code, needs_expectations, engagement, sort_order)
SELECT c.id, s.party, s.code, s.needs_expectations, s.engagement, s.sort_order
FROM companies c JOIN std_stakeholders s ON s.standard_id=@std WHERE c.id IN (@c1,@c2,@c3);

INSERT INTO esg_law_register (company_id, pillar, act_name, std_code, applicable, sort_order)
SELECT c.id, l.pillar, l.act_name, l.code, l.default_applicable, l.sort_order
FROM companies c JOIN std_laws l ON l.standard_id=@std WHERE c.id IN (@c1,@c2,@c3);

INSERT INTO esg_aspects (company_id, pillar, category, aspect, std_code, issue, impact, sort_order)
SELECT c.id, a.pillar, a.category, a.aspect, a.code, a.issue, a.impact, a.sort_order
FROM companies c JOIN std_aspects a ON a.standard_id=@std WHERE c.id IN (@c1,@c2,@c3);

INSERT INTO company_obligations (company_id, code, name, legal_month, legal_day, internal_month, internal_day, legal_rule, checklist, active, sort_order)
SELECT c.id, o.code, o.name, o.legal_month, o.legal_day, o.internal_month, o.internal_day, o.legal_rule, o.checklist, 1, o.sort_order
FROM companies c JOIN std_obligations o ON o.standard_id=@std WHERE c.id IN (@c1,@c2,@c3);

-- ================= 4. Requirement statuses (per maturity) =============
-- Wizard auto-marks 4–4.4, 5.2, 5.3, 6.1 ready and 5.1/6.2/8.2/8.3
-- in_progress at activation; statuses below stay consistent with that.
UPDATE company_requirements SET status='ready', owner='Aina Rahman' WHERE company_id=@c1;
UPDATE company_requirements cr JOIN std_requirements r ON r.id=cr.requirement_id
   SET cr.status='in_progress'
 WHERE cr.company_id=@c1 AND r.clause_no IN ('8.1','8.2','8.3');

UPDATE company_requirements SET status='not_started', owner='Daniel Wong' WHERE company_id=@c2;
UPDATE company_requirements cr JOIN std_requirements r ON r.id=cr.requirement_id
   SET cr.status='ready'
 WHERE cr.company_id=@c2 AND r.clause_no IN ('4','4.1','4.2','4.3','4.4','5.1','5.2','5.3','6.1');
UPDATE company_requirements cr JOIN std_requirements r ON r.id=cr.requirement_id
   SET cr.status='in_progress'
 WHERE cr.company_id=@c2 AND r.clause_no IN ('6.2','7','7.1','7.2','8.2','8.3');

UPDATE company_requirements SET status='not_started', owner='Wan Azlan' WHERE company_id=@c3;
UPDATE company_requirements cr JOIN std_requirements r ON r.id=cr.requirement_id
   SET cr.status='ready'
 WHERE cr.company_id=@c3 AND r.clause_no IN ('4','4.1','4.2','4.3','4.4','5.2','5.3','6.1');
UPDATE company_requirements cr JOIN std_requirements r ON r.id=cr.requirement_id
   SET cr.status='in_progress'
 WHERE cr.company_id=@c3 AND r.clause_no IN ('5.1','6.2','8.2','8.3');

-- ================= 5. Risk ratings, laws, roles =======================
-- Ratings target aspects BY NAME so controls sit on the right rows.
UPDATE esg_aspects SET likelihood=4, severity=4, significant=1, controls='Metered supply; monthly consumption review; compressor timers' WHERE company_id=@c1 AND aspect LIKE 'Energy consumption%';
UPDATE esg_aspects SET likelihood=3, severity=4, significant=1, controls='Flow restrictors fitted; usage tracked against production'      WHERE company_id=@c1 AND aspect LIKE 'Water consumption%';
UPDATE esg_aspects SET likelihood=3, severity=3, significant=0, controls='SOPs and PPE issued; briefings each shift'                       WHERE company_id=@c1 AND aspect LIKE 'Occupational safety%';
UPDATE esg_aspects SET likelihood=2, severity=4, significant=0, controls='Approved supplier list; annual integrity declarations'           WHERE company_id=@c1 AND aspect LIKE 'Anti-corruption%';

UPDATE esg_aspects SET likelihood=4, severity=4, significant=1, controls='Route planning; driver eco-training scheduled'     WHERE company_id=@c2 AND aspect LIKE 'GHG emission from business travel%';
UPDATE esg_aspects SET likelihood=4, severity=3, significant=1, controls='Rest-hour policy drafted; telematics being fitted' WHERE company_id=@c2 AND aspect LIKE 'Occupational safety%';

UPDATE esg_aspects SET likelihood=4, severity=5, significant=1, controls='Site safety plan; toolbox talks each morning' WHERE company_id=@c3 AND aspect LIKE 'Occupational safety%';

-- The wizard requires EVERY active aspect rated before activation — sweep
-- the rest with low ratings (2x2=4, below the significance threshold).
UPDATE esg_aspects SET likelihood=2, severity=2, significant=0 WHERE company_id IN (@c1,@c2,@c3) AND likelihood IS NULL;

-- Law statuses only on APPLICABLE acts (the Assessment Pack filters on it).
UPDATE esg_law_register SET compliance_status='compliant', notes='Licences current; evidence filed' WHERE company_id=@c1 AND applicable=1;
UPDATE esg_law_register SET compliance_status='partial',   notes='Waste contractor licence renewal submitted, awaiting approval' WHERE company_id=@c1 AND act_name LIKE 'Solid Waste%';
UPDATE esg_law_register SET compliance_status='compliant', notes='Verified during onboarding' WHERE company_id=@c2 AND (act_name LIKE 'Employment Act%' OR act_name LIKE 'Occupational Safety%' OR act_name LIKE 'Companies Act%' OR act_name LIKE 'Income Tax%');
UPDATE esg_law_register SET compliance_status='partial',   notes='Gap review in progress' WHERE company_id=@c2 AND (act_name LIKE 'Industrial Relations%' OR act_name LIKE 'Malaysian Anti-Corruption%' OR act_name LIKE 'Personal Data Protection%');
UPDATE esg_law_register SET compliance_status='compliant', notes='Confirmed with company secretary' WHERE company_id=@c3 AND (act_name LIKE 'Companies Act%' OR act_name LIKE 'Occupational Safety%');


INSERT INTO esg_roles (company_id, person, role_title, responsibilities, reports_to, sort_order) VALUES
  (@c1, 'Aina Rahman', 'Managing Director — ESG Sponsor', 'Owns the ESG policy; chairs management review', NULL, 10);
SET @r1 = IF(@go=1, LAST_INSERT_ID(), 0);
INSERT INTO esg_roles (company_id, person, role_title, responsibilities, reports_to, sort_order) VALUES
  (@c1, 'Hafiz Ismail', 'ESG Coordinator', 'Maintains registers, evidence and duty calendar', @r1, 20),
  (@c1, 'Mei Ling Tan', 'Production Manager', 'Environmental controls on the plant floor', @r1, 30);
INSERT INTO esg_roles (company_id, person, role_title, responsibilities, reports_to, sort_order) VALUES
  (@c2, 'Daniel Wong', 'Director — ESG Sponsor', 'Accountable for ESG readiness', NULL, 10);
SET @r2 = IF(@go=1, LAST_INSERT_ID(), 0);
INSERT INTO esg_roles (company_id, person, role_title, responsibilities, reports_to, sort_order) VALUES
  (@c2, 'Siti Nurhaliza', 'HR & Compliance Executive', 'Social pillar actions; training records', @r2, 20);
INSERT INTO esg_roles (company_id, person, role_title, responsibilities, reports_to, sort_order) VALUES
  (@c3, 'Wan Azlan', 'Project Director — ESG Sponsor', 'Drives ESG readiness across sites', NULL, 10);

-- ================= 6. Documents (approved, no standard named) =========
INSERT INTO esg_documents (company_id, kind, title, content, version, status, approved_by, approved_at, created_at) VALUES
(@c1, 'policy', 'ESG Policy', CONCAT(
'## Purpose and scope\nThis policy covers all operations of Santapan Nusantara Sdn Bhd at our Shah Alam plant and warehouse.\n\n',
'## Our values\nWe believe good food starts with responsible operations—safe people, careful use of resources and honest dealings.\n\n',
'## Environmental commitments\nWe measure and reduce energy and water use per tonne produced, manage waste through licensed contractors and prevent pollution at source.\n\n',
'## Social commitments\nWe provide a safe workplace, fair terms of employment and training for every worker, and we do not tolerate discrimination or harassment.\n\n',
'## Governance commitments\nWe act with integrity, comply with the laws and requirements that apply to us and keep accurate records.\n\n',
'## Continual improvement\nManagement reviews ESG objectives and performance at least yearly and commits the resources to improve them.\n\n',
'Approved by the Managing Director.'), 1, 'approved', @u1, '2026-04-22 11:00:00', '2026-04-20 16:00:00'),
(@c1, 'code_of_ethics', 'Code of Ethics', '## Integrity in our dealings\nWe are honest with customers, suppliers and each other. Conflicts of interest are declared. Confidential information—including personal data under PDPA 2010—is protected. Gifts beyond modest value are refused. Records are accurate. Anyone may raise a concern without fear of retaliation.', 1, 'approved', @u1, '2026-04-22 11:05:00', '2026-04-20 16:10:00'),
(@c1, 'anti_corruption', 'Anti-Corruption Statement', '## Zero tolerance\nBribery in any form, including facilitation payments, is prohibited. Procurement is fair and documented. Business partners are assessed before appointment. Concerns are reported to the Managing Director and protected. Top management is accountable for this commitment, consistent with the MACC Act 2009 including Section 17A.', 1, 'approved', @u1, '2026-04-22 11:10:00', '2026-04-20 16:20:00'),
(@c2, 'policy', 'ESG Policy', '## Purpose and scope\nThis policy applies to Kargo Lestari Logistics Sdn Bhd—our Johor Bahru HQ and all depots.\n\n## Commitments\nWe reduce fuel use and emissions per delivery, keep our drivers safe and fairly treated, comply with the requirements that apply to us and improve our ESG management system year on year.\n\nApproved by the Director.', 1, 'approved', @u2, '2026-06-08 09:30:00', '2026-06-05 10:30:00'),
(@c2, 'code_of_ethics', 'Code of Ethics', 'Draft — under management review.', 1, 'draft', NULL, NULL, '2026-07-15 14:00:00'),
(@c3, 'policy', 'ESG Policy', '## Purpose and scope\nThis policy applies to Binaan Pantai Timur Sdn Bhd, covering our head office and all active project sites.\n\n## Commitments\nSafe sites for every worker and the public, responsible handling of materials and waste, honest contracting, and continual improvement of our ESG management system.\n\nApproved by the Project Director.', 1, 'approved', @u3, '2026-07-14 10:00:00', '2026-07-12 17:00:00');

-- ================= 7. Objectives, reviews, actions, logbook ===========
INSERT INTO esg_objectives (company_id, pillar, aspect_id, title, resources, target, owner, due_date, indicator, baseline, data_method, status, progress, created_at) VALUES
(@c1, 'E', (SELECT id FROM esg_aspects WHERE company_id=@c1 AND pillar='E' ORDER BY sort_order LIMIT 1),
 'Cut electricity per tonne produced by 10%', 'RM15k meter upgrade; maintenance hours', '10% reduction by Dec 2026', 'Mei Ling Tan', '2026-12-31',
 'kWh per tonne produced', '412 kWh/t (2025 avg)', 'Monthly utility bills against production output', 'on_track', 'Jun: 396 kWh/t (-3.9%). New compressor timers installed.', '2026-04-25 10:00:00'),
(@c1, 'S', (SELECT id FROM esg_aspects WHERE company_id=@c1 AND pillar='S' ORDER BY sort_order LIMIT 1),
 'Zero lost-time injuries', 'Safety training budget; monthly walkabouts', '0 LTI through 2026', 'Hafiz Ismail', '2026-12-31',
 'Lost-time injuries per month', '2 LTIs in 2025', 'HR incident register, reviewed monthly', 'on_track', 'Six months LTI-free as of June.', '2026-04-25 10:10:00'),
(@c1, 'G', NULL, 'Complete supplier integrity declarations', 'Coordinator time', '100% of active suppliers by Oct 2026', 'Aina Rahman', '2026-10-31',
 '% suppliers declared', '0%', 'Signed declarations filed per supplier', 'at_risk', '62% returned; chasing the remainder.', '2026-04-25 10:20:00'),
(@c2, 'E', (SELECT id FROM esg_aspects WHERE company_id=@c2 AND pillar='E' ORDER BY sort_order LIMIT 1),
 'Reduce litres per 100km fleet average by 6%', 'Eco-driving course; telematics', '6% by Mar 2027', 'Daniel Wong', '2027-03-31',
 'L/100km fleet average', '34.1 L/100km (May 2026)', 'Telematics + fuel card reports, monthly', 'open', 'Telematics fitted to 60% of fleet.', '2026-06-10 09:00:00'),
(@c3, 'S', (SELECT id FROM esg_aspects WHERE company_id=@c3 AND pillar='S' ORDER BY sort_order LIMIT 1),
 'Daily toolbox talks on every active site', 'Site supervisors; talk templates', '100% of working days', 'Wan Azlan', '2026-12-31',
 '% working days with recorded talk', 'Not tracked before', 'Signed attendance sheets scanned weekly', 'open', 'Started on both active sites mid-July.', '2026-07-15 08:30:00');

INSERT INTO esg_reviews (company_id, kind, review_date, attendees, findings, objectives_status, compliance_status, indicators_status, created_by, created_at) VALUES
(@c1, 'monitoring', '2026-06-12', 'Aina Rahman, Hafiz Ismail, Mei Ling Tan',
 'H1 monitoring completed. Action plans on schedule; one supplier-declaration gap flagged.',
 'Energy: on track (-3.9%). Safety: on track. Supplier declarations: behind, escalated.',
 'All applicable requirements compliant; one licence renewal pending approval.',
 'kWh/t and LTI tracked monthly; both trending in the right direction.', @u1, '2026-06-12 15:00:00'),
(@c2, 'monitoring', '2026-07-18', 'Daniel Wong, Siti Nurhaliza',
 'First monitoring cycle. Baseline data now flowing from telematics; social-pillar training records centralised.',
 'Fuel objective set with baseline; no target movement yet.',
 'Four requirements verified compliant; gap review continuing on three.',
 'L/100km baseline established at 34.1.', @u2, '2026-07-18 11:00:00');
SET @rev1 = (SELECT id FROM esg_reviews WHERE company_id=@c1 AND kind='monitoring' LIMIT 1);

INSERT INTO esg_actions (company_id, review_id, description, owner, due_date, status, closed_at, closure_notes, created_at) VALUES
(@c1, @rev1, 'Chase outstanding supplier integrity declarations; set weekly follow-up list', 'Aina Rahman', '2026-08-15', 'open', NULL, NULL, '2026-06-12 15:20:00'),
(@c1, @rev1, 'Fix compressed-air leak on line 2 found during energy walkabout', 'Mei Ling Tan', '2026-06-30', 'closed', '2026-06-24 10:00:00', 'Leak repaired by maintenance; verified on the June meter reading.', '2026-06-12 15:25:00'),
(@c2, NULL, 'Complete telematics installation on remaining 40% of fleet', 'Daniel Wong', '2026-09-30', 'open', NULL, NULL, '2026-07-18 11:20:00'),
(@c3, NULL, 'Appoint trained first-aiders for the Kuala Nerus site', 'Wan Azlan', '2026-08-31', 'open', NULL, NULL, '2026-07-15 09:00:00');

INSERT INTO esg_log (company_id, kind, log_date, description, people, created_by, created_at) VALUES
(@c1, 'training',      '2026-05-06', 'ESG awareness briefing for all production staff — policy, objectives and how to raise concerns', 'All plant staff (42)', @u1, '2026-05-06 17:00:00'),
(@c1, 'communication', '2026-05-10', 'ESG Policy shared with top 20 suppliers together with the integrity declaration request', 'Procurement + suppliers', @u1, '2026-05-10 09:00:00'),
(@c1, 'change',        '2026-06-24', 'Compressor timer retrofit on line 2 — risk assessed before installation, no new hazards introduced', 'Maintenance team', @u1, '2026-06-24 10:30:00'),
(@c2, 'training',      '2026-06-20', 'Eco-driving course, first driver cohort (12 drivers)', 'Drivers, HR', @u2, '2026-06-20 16:00:00'),
(@c2, 'communication', '2026-06-08', 'ESG Policy circulated to all staff and posted at depots', 'All staff', @u2, '2026-06-08 10:00:00'),
(@c3, 'training',      '2026-07-16', 'Site induction refreshed to include ESG policy and daily toolbox talk requirement', 'Site supervisors', @u3, '2026-07-16 08:00:00');

-- ================= 8. Completed duties (H1 monitoring done) ===========
INSERT IGNORE INTO company_periods (company_id, obligation_id, period_label, legal_due_date, internal_due_date, status, checklist_state, notes, completed_at, completed_by)
SELECT @c1, o.id, '2026', '2026-06-30', '2026-06-15', 'done', o.checklist, 'Recorded under Reviews (12 Jun).', '2026-06-12 15:30:00', @u1
FROM company_obligations o WHERE o.company_id=@c1 AND o.code='monitor_h1';
INSERT IGNORE INTO company_periods (company_id, obligation_id, period_label, legal_due_date, internal_due_date, status, checklist_state, notes, completed_at, completed_by)
SELECT @c2, o.id, '2026', '2026-06-30', '2026-06-15', 'done', o.checklist, 'First cycle completed late (18 Jul) — noted for next year.', '2026-07-18 11:30:00', @u2
FROM company_obligations o WHERE o.company_id=@c2 AND o.code='monitor_h1';
-- Binaan activated after H1 — its schedule starts from H2 (engine floor).

-- AI usage: dated inside the CURRENT month so padmin's monthly counter
-- shows non-zero numbers whenever the demo is imported.
INSERT INTO ai_usage (company_id, user_id, kind, created_at) VALUES
(@c1, @u1, 'policy',          DATE_ADD(DATE_FORMAT(NOW(), '%Y-%m-01'), INTERVAL 9 HOUR)),
(@c1, @u1, 'code_of_ethics',  DATE_ADD(DATE_FORMAT(NOW(), '%Y-%m-01'), INTERVAL 10 HOUR)),
(@c1, @u1, 'anti_corruption', DATE_ADD(DATE_FORMAT(NOW(), '%Y-%m-01'), INTERVAL 11 HOUR)),
(@c2, @u2, 'policy',          DATE_ADD(DATE_FORMAT(NOW(), '%Y-%m-01'), INTERVAL 33 HOUR)),
(@c3, @u3, 'policy',          DATE_ADD(DATE_FORMAT(NOW(), '%Y-%m-01'), INTERVAL 34 HOUR));

-- =====================================================================
--  CLEANUP (run when the demo data should go — cascades remove all
--  child rows: users, profile, registers, documents, assessments, etc.)
--
--  DELETE FROM companies WHERE name IN
--    ('Santapan Nusantara Sdn Bhd','Kargo Lestari Logistics Sdn Bhd','Binaan Pantai Timur Sdn Bhd');
--
--  (Empty duplicates from an accidental double import — companies with
--   no users — can be removed on their own with:)
--  DELETE c FROM companies c LEFT JOIN users u ON u.company_id=c.id
--  WHERE c.name IN ('Santapan Nusantara Sdn Bhd','Kargo Lestari Logistics Sdn Bhd','Binaan Pantai Timur Sdn Bhd')
--    AND u.id IS NULL;
-- =====================================================================