-- /queue/sql/seed.sql
SET NAMES utf8mb4;
SET time_zone = '+07:00';

START TRANSACTION;

-- 1) Company / Site
INSERT INTO queue_company (code, name)
VALUES ('DEMO', 'Demo Company')
ON DUPLICATE KEY UPDATE name=VALUES(name);

INSERT INTO queue_site (company_id, code, name)
SELECT c.id, 'MAIN', 'Main Site'
FROM queue_company c
WHERE c.code='DEMO'
ON DUPLICATE KEY UPDATE name=VALUES(name);

-- 2) Roles
INSERT INTO queue_role (code, name) VALUES
 ('counter', 'Counter/Front'),
 ('kitchen', 'Kitchen/Bar'),
 ('waiter',  'Waiter'),
 ('cashier', 'Cashier')
ON DUPLICATE KEY UPDATE name=VALUES(name);

-- 3) Users (ตาม schema: ใช้คอลัมน์ login ไม่ใช่ username)
INSERT INTO queue_app_user (login, display_name, is_active)
VALUES
 ('alice',   'Alice (Counter)', 1),
 ('bob',     'Bob (Kitchen)', 1),
 ('charlie', 'Charlie (Waiter)', 1),
 ('diana',   'Diana (Cashier)', 1)
ON DUPLICATE KEY UPDATE display_name=VALUES(display_name), is_active=VALUES(is_active);

-- 4) Map User→Role→Site (unique คือ (user_id, role_id, site_id) → ใช้ INSERT IGNORE ป้องกันซ้ำ)
INSERT IGNORE INTO queue_user_role_site (user_id, role_id, site_id)
SELECT u.id, r.id, s.id
FROM queue_app_user u
JOIN queue_role r ON r.code='counter'
JOIN queue_site s ON s.code='MAIN'
WHERE u.login='alice';

INSERT IGNORE INTO queue_user_role_site (user_id, role_id, site_id)
SELECT u.id, r.id, s.id
FROM queue_app_user u
JOIN queue_role r ON r.code='kitchen'
JOIN queue_site s ON s.code='MAIN'
WHERE u.login='bob';

INSERT IGNORE INTO queue_user_role_site (user_id, role_id, site_id)
SELECT u.id, r.id, s.id
FROM queue_app_user u
JOIN queue_role r ON r.code='waiter'
JOIN queue_site s ON s.code='MAIN'
WHERE u.login='charlie';

INSERT IGNORE INTO queue_user_role_site (user_id, role_id, site_id)
SELECT u.id, r.id, s.id
FROM queue_app_user u
JOIN queue_role r ON r.code='cashier'
JOIN queue_site s ON s.code='MAIN'
WHERE u.login='diana';

-- 5) Queues (unique: (site_id, code))
INSERT INTO queue_queue (site_id, code, name, role_id)
SELECT s.id, 'counter-order', 'Counter (Order)', r.id
FROM queue_site s
JOIN queue_role r ON r.code='counter'
WHERE s.code='MAIN'
ON DUPLICATE KEY UPDATE name=VALUES(name), role_id=r.id;

INSERT INTO queue_queue (site_id, code, name, role_id)
SELECT s.id, 'kitchen', 'Kitchen/Bar', r.id
FROM queue_site s
JOIN queue_role r ON r.code='kitchen'
WHERE s.code='MAIN'
ON DUPLICATE KEY UPDATE name=VALUES(name), role_id=r.id;

INSERT INTO queue_queue (site_id, code, name, role_id)
SELECT s.id, 'waiter', 'Waiter', r.id
FROM queue_site s
JOIN queue_role r ON r.code='waiter'
WHERE s.code='MAIN'
ON DUPLICATE KEY UPDATE name=VALUES(name), role_id=r.id;

INSERT INTO queue_queue (site_id, code, name, role_id)
SELECT s.id, 'cashier', 'Cashier', r.id
FROM queue_site s
JOIN queue_role r ON r.code='cashier'
WHERE s.code='MAIN'
ON DUPLICATE KEY UPDATE name=VALUES(name), role_id=r.id;

-- 6) Workflow mapping: case_type=restaurant_order (unique: case_type+state+site_id)
INSERT INTO queue_workflow_route (case_type, state, site_id, queue_id, assign_strategy)
SELECT 'restaurant_order','new', s.id, q.id, 'pull'
FROM queue_site s
JOIN queue_queue q ON q.code='counter-order' AND q.site_id=s.id
WHERE s.code='MAIN'
ON DUPLICATE KEY UPDATE queue_id=VALUES(queue_id), assign_strategy=VALUES(assign_strategy);

INSERT INTO queue_workflow_route (case_type, state, site_id, queue_id, assign_strategy)
SELECT 'restaurant_order','preparing', s.id, q.id, 'push'
FROM queue_site s
JOIN queue_queue q ON q.code='kitchen' AND q.site_id=s.id
WHERE s.code='MAIN'
ON DUPLICATE KEY UPDATE queue_id=VALUES(queue_id), assign_strategy=VALUES(assign_strategy);

INSERT INTO queue_workflow_route (case_type, state, site_id, queue_id, assign_strategy)
SELECT 'restaurant_order','ready_to_serve', s.id, q.id, 'pull'
FROM queue_site s
JOIN queue_queue q ON q.code='waiter' AND q.site_id=s.id
WHERE s.code='MAIN'
ON DUPLICATE KEY UPDATE queue_id=VALUES(queue_id), assign_strategy=VALUES(assign_strategy);

INSERT INTO queue_workflow_route (case_type, state, site_id, queue_id, assign_strategy)
SELECT 'restaurant_order','waiting_for_bill', s.id, q.id, 'push'
FROM queue_site s
JOIN queue_queue q ON q.code='cashier' AND q.site_id=s.id
WHERE s.code='MAIN'
ON DUPLICATE KEY UPDATE queue_id=VALUES(queue_id), assign_strategy=VALUES(assign_strategy);

-- state 'done' ไม่เข้าคิว (queue_id = NULL)
INSERT INTO queue_workflow_route (case_type, state, site_id, queue_id, assign_strategy)
SELECT 'restaurant_order','done', s.id, NULL, 'pull'
FROM queue_site s
WHERE s.code='MAIN'
ON DUPLICATE KEY UPDATE queue_id=VALUES(queue_id), assign_strategy=VALUES(assign_strategy);

-- 7) Sequences (idempotent)
INSERT INTO queue_code_seq (key_name, last_number) VALUES
 ('CASE', 0), ('JOB', 0)
ON DUPLICATE KEY UPDATE last_number=last_number;

-- 8) สร้าง Case ตัวอย่าง + Job ตัวอย่าง ให้ครบ FK
--    เลือกใช้สถานะ case = 'new' ให้ตรงกับ workflow
INSERT INTO queue_case_header (case_code, company_id, site_id, case_type, customer_ref, status, created_at)
SELECT 'CASE0001', c.id, s.id, 'restaurant_order', 'Demo Order #1', 'new', NOW()
FROM queue_company c
JOIN queue_site s ON s.company_id=c.id AND s.code='MAIN'
WHERE c.code='DEMO'
ON DUPLICATE KEY UPDATE status='new';

--    JOB แรก (J1001): current_state = 'queued', queue_id = route ของ state 'new'
INSERT INTO queue_job (case_id, queue_id, job_code, job_type, current_state, created_at)
SELECT cs.id,
       (SELECT qwr.queue_id
        FROM queue_workflow_route qwr
        WHERE qwr.case_type='restaurant_order'
          AND qwr.state='new'
          AND qwr.site_id=cs.site_id
        LIMIT 1),
       'J1001', 'demo', 'queued', NOW()
FROM queue_case_header cs
WHERE cs.case_code='CASE0001'
ON DUPLICATE KEY UPDATE current_state='queued';

COMMIT;