USE shquier_easychair;

SET @pwd := '$2y$10$ZQXcD4xqXSj5yU7fCZluVeMiRlu6l4pSXwqMioY6m6riK67GFrD3G';
/*
Password for all seed users:
Test@12345

Generated bcrypt hash above is safe for PHP password_verify().
*/

START TRANSACTION;

/* -------------------------------------------------------
   1) Ensure roles exist
------------------------------------------------------- */
INSERT IGNORE INTO roles (name, code) VALUES
('Super Admin', 'super_admin'),
('Conference Chair', 'conference_chair'),
('Track Chair', 'track_chair'),
('Reviewer', 'reviewer'),
('Author', 'author'),
('Organization Admin', 'org_admin'),
('Organizer', 'organizer'),
('Admin', 'admin');

/* -------------------------------------------------------
   2) Load role IDs
------------------------------------------------------- */
SELECT id INTO @role_super_admin FROM roles WHERE code='super_admin' LIMIT 1;
SELECT id INTO @role_conference_chair FROM roles WHERE code='conference_chair' LIMIT 1;
SELECT id INTO @role_track_chair FROM roles WHERE code='track_chair' LIMIT 1;
SELECT id INTO @role_reviewer FROM roles WHERE code='reviewer' LIMIT 1;
SELECT id INTO @role_author FROM roles WHERE code='author' LIMIT 1;
SELECT id INTO @role_org_admin FROM roles WHERE code='org_admin' LIMIT 1;
SELECT id INTO @role_organizer FROM roles WHERE code='organizer' LIMIT 1;
SELECT id INTO @role_admin FROM roles WHERE code='admin' LIMIT 1;

/* -------------------------------------------------------
   3) Existing conferences from your data:
      id 1 org_id 1
      id 2 org_id 1
      id 3 org_id 4
------------------------------------------------------- */
SELECT id INTO @conf1 FROM conferences WHERE id=1 LIMIT 1;
SELECT id INTO @conf2 FROM conferences WHERE id=2 LIMIT 1;
SELECT id INTO @conf3 FROM conferences WHERE id=3 LIMIT 1;

SELECT org_id INTO @org1 FROM conferences WHERE id=@conf1 LIMIT 1;
SELECT org_id INTO @org3 FROM conferences WHERE id=@conf3 LIMIT 1;

/* Fallbacks if variables are null */
SET @org1 := COALESCE(@org1, 1);
SET @org3 := COALESCE(@org3, 4);

/* -------------------------------------------------------
   4) Seed users
------------------------------------------------------- */

INSERT INTO users
(org_id, role_id, first_name, last_name, email, password_hash, mobile, job_title, preferred_lang, is_active, expertise, reviewer_score, created_at)
VALUES
(@org1, @role_super_admin, 'Seed', 'SuperAdmin', 'seed.superadmin@easychair.test', @pwd, '0790000001', 'System Manager', 'en', 1, 'Full platform administration', 100, NOW())
ON DUPLICATE KEY UPDATE
role_id=VALUES(role_id), org_id=VALUES(org_id), password_hash=VALUES(password_hash), is_active=1, job_title=VALUES(job_title), expertise=VALUES(expertise);

INSERT INTO users
(org_id, role_id, first_name, last_name, email, password_hash, mobile, job_title, preferred_lang, is_active, expertise, reviewer_score, created_at)
VALUES
(@org1, @role_org_admin, 'Seed', 'OrgAdmin', 'seed.orgadmin@easychair.test', @pwd, '0790000002', 'Organization Admin', 'en', 1, 'Organization administration', 90, NOW())
ON DUPLICATE KEY UPDATE
role_id=VALUES(role_id), org_id=VALUES(org_id), password_hash=VALUES(password_hash), is_active=1, job_title=VALUES(job_title), expertise=VALUES(expertise);

INSERT INTO users
(org_id, role_id, first_name, last_name, email, password_hash, mobile, job_title, preferred_lang, is_active, expertise, reviewer_score, created_at)
VALUES
(@org1, @role_organizer, 'Seed', 'Organizer', 'seed.organizer@easychair.test', @pwd, '0790000003', 'Conference Organizer', 'en', 1, 'Event and conference operations', 88, NOW())
ON DUPLICATE KEY UPDATE
role_id=VALUES(role_id), org_id=VALUES(org_id), password_hash=VALUES(password_hash), is_active=1, job_title=VALUES(job_title), expertise=VALUES(expertise);

INSERT INTO users
(org_id, role_id, first_name, last_name, email, password_hash, mobile, job_title, preferred_lang, is_active, expertise, reviewer_score, created_at)
VALUES
(@org1, @role_admin, 'Seed', 'Admin', 'seed.admin@easychair.test', @pwd, '0790000004', 'Conference Admin', 'en', 1, 'Conference administration', 85, NOW())
ON DUPLICATE KEY UPDATE
role_id=VALUES(role_id), org_id=VALUES(org_id), password_hash=VALUES(password_hash), is_active=1, job_title=VALUES(job_title), expertise=VALUES(expertise);

INSERT INTO users
(org_id, role_id, first_name, last_name, email, password_hash, mobile, job_title, preferred_lang, is_active, expertise, reviewer_score, created_at)
VALUES
(@org1, @role_conference_chair, 'Seed', 'Chair', 'seed.chair@easychair.test', @pwd, '0790000005', 'Conference Chair', 'en', 1, 'Scientific committee leadership', 95, NOW())
ON DUPLICATE KEY UPDATE
role_id=VALUES(role_id), org_id=VALUES(org_id), password_hash=VALUES(password_hash), is_active=1, job_title=VALUES(job_title), expertise=VALUES(expertise);

INSERT INTO users
(org_id, role_id, first_name, last_name, email, password_hash, mobile, job_title, preferred_lang, is_active, expertise, reviewer_score, created_at)
VALUES
(@org1, @role_track_chair, 'Seed', 'TrackChair', 'seed.trackchair@easychair.test', @pwd, '0790000006', 'Track Chair', 'en', 1, 'Track review management', 92, NOW())
ON DUPLICATE KEY UPDATE
role_id=VALUES(role_id), org_id=VALUES(org_id), password_hash=VALUES(password_hash), is_active=1, job_title=VALUES(job_title), expertise=VALUES(expertise);

INSERT INTO users
(org_id, role_id, first_name, last_name, email, password_hash, mobile, job_title, preferred_lang, is_active, expertise, reviewer_score, created_at)
VALUES
(@org1, @role_reviewer, 'Seed', 'Reviewer', 'seed.reviewer@easychair.test', @pwd, '0790000007', 'Reviewer', 'en', 1, 'AI, Cybersecurity, Software Engineering', 87, NOW())
ON DUPLICATE KEY UPDATE
role_id=VALUES(role_id), org_id=VALUES(org_id), password_hash=VALUES(password_hash), is_active=1, job_title=VALUES(job_title), expertise=VALUES(expertise), reviewer_score=VALUES(reviewer_score);

INSERT INTO users
(org_id, role_id, first_name, last_name, email, password_hash, mobile, job_title, preferred_lang, is_active, expertise, reviewer_score, created_at)
VALUES
(@org1, @role_author, 'Seed', 'Author', 'seed.author@easychair.test', @pwd, '0790000008', 'Author / Researcher', 'en', 1, 'Author test account', 50, NOW())
ON DUPLICATE KEY UPDATE
role_id=VALUES(role_id), org_id=VALUES(org_id), password_hash=VALUES(password_hash), is_active=1, job_title=VALUES(job_title), expertise=VALUES(expertise);

INSERT INTO users
(org_id, role_id, first_name, last_name, email, password_hash, mobile, job_title, preferred_lang, is_active, expertise, reviewer_score, created_at)
VALUES
(@org3, @role_org_admin, 'Seed', 'Org4Admin', 'seed.org4admin@easychair.test', @pwd, '0790000009', 'Organization 4 Admin', 'en', 1, 'Second organization isolation testing', 80, NOW())
ON DUPLICATE KEY UPDATE
role_id=VALUES(role_id), org_id=VALUES(org_id), password_hash=VALUES(password_hash), is_active=1, job_title=VALUES(job_title), expertise=VALUES(expertise);

/* -------------------------------------------------------
   5) Load user IDs
------------------------------------------------------- */
SELECT id INTO @u_super FROM users WHERE email='seed.superadmin@easychair.test' LIMIT 1;
SELECT id INTO @u_orgadmin FROM users WHERE email='seed.orgadmin@easychair.test' LIMIT 1;
SELECT id INTO @u_organizer FROM users WHERE email='seed.organizer@easychair.test' LIMIT 1;
SELECT id INTO @u_admin FROM users WHERE email='seed.admin@easychair.test' LIMIT 1;
SELECT id INTO @u_chair FROM users WHERE email='seed.chair@easychair.test' LIMIT 1;
SELECT id INTO @u_track FROM users WHERE email='seed.trackchair@easychair.test' LIMIT 1;
SELECT id INTO @u_reviewer FROM users WHERE email='seed.reviewer@easychair.test' LIMIT 1;
SELECT id INTO @u_author FROM users WHERE email='seed.author@easychair.test' LIMIT 1;
SELECT id INTO @u_org4admin FROM users WHERE email='seed.org4admin@easychair.test' LIMIT 1;

/* -------------------------------------------------------
   6) Conference memberships
------------------------------------------------------- */

DELETE FROM user_conferences
WHERE user_id IN (@u_super, @u_orgadmin, @u_organizer, @u_admin, @u_chair, @u_track, @u_reviewer, @u_author, @u_org4admin);

INSERT INTO user_conferences (user_id, conference_id, role_id)
SELECT @u_super, id, @role_super_admin FROM conferences;

INSERT INTO user_conferences (user_id, conference_id, role_id) VALUES
(@u_orgadmin, @conf1, @role_org_admin),
(@u_orgadmin, @conf2, @role_org_admin),
(@u_organizer, @conf1, @role_organizer),
(@u_organizer, @conf2, @role_organizer),
(@u_admin, @conf1, @role_admin),
(@u_chair, @conf1, @role_conference_chair),
(@u_track, @conf1, @role_track_chair),
(@u_reviewer, @conf1, @role_reviewer),
(@u_author, @conf1, @role_author),
(@u_org4admin, @conf3, @role_org_admin);

/* Also set chair_user_id for test chair */
UPDATE conferences
SET chair_user_id = @u_chair
WHERE id = @conf1;

/* -------------------------------------------------------
   7) Seed submissions for author and conference dashboards
------------------------------------------------------- */

INSERT INTO submissions
(conference_id, author_id, submission_no, title, title_ar, abstract, abstract_ar, keywords, keywords_ar,
 status, stage, decision, current_round_no, current_version, submitted_at, org_id, author_name, avg_score, created_at)
VALUES
(@conf1, @u_author, 'SEED-001', 'Seed AI Governance Paper', 'بحث تجريبي حول حوكمة الذكاء الاصطناعي',
 'A seed abstract for AI governance.', 'ملخص تجريبي حول حوكمة الذكاء الاصطناعي.',
 'AI, Governance', 'ذكاء اصطناعي، حوكمة',
 'submitted', 'screening', NULL, 1, 1, NOW(), @org1, 'Seed Author', NULL, NOW())
ON DUPLICATE KEY UPDATE
author_id=VALUES(author_id), conference_id=VALUES(conference_id), org_id=VALUES(org_id),
title=VALUES(title), title_ar=VALUES(title_ar), status=VALUES(status), stage=VALUES(stage), decision=VALUES(decision);

INSERT INTO submissions
(conference_id, author_id, submission_no, title, title_ar, abstract, abstract_ar, keywords, keywords_ar,
 status, stage, decision, current_round_no, current_version, submitted_at, org_id, author_name, avg_score, created_at)
VALUES
(@conf1, @u_author, 'SEED-002', 'Seed Cybersecurity Education Paper', 'بحث تجريبي حول تعليم الأمن السيبراني',
 'A seed abstract for cybersecurity education.', 'ملخص تجريبي حول تعليم الأمن السيبراني.',
 'Cybersecurity, Education', 'أمن سيبراني، تعليم',
 'under_review', 'reviewing', NULL, 1, 1, NOW(), @org1, 'Seed Author', NULL, NOW())
ON DUPLICATE KEY UPDATE
author_id=VALUES(author_id), conference_id=VALUES(conference_id), org_id=VALUES(org_id),
title=VALUES(title), title_ar=VALUES(title_ar), status=VALUES(status), stage=VALUES(stage), decision=VALUES(decision);

INSERT INTO submissions
(conference_id, author_id, submission_no, title, title_ar, abstract, abstract_ar, keywords, keywords_ar,
 status, stage, decision, current_round_no, current_version, submitted_at, org_id, author_name, avg_score, created_at)
VALUES
(@conf1, @u_author, 'SEED-003', 'Seed Accepted Paper', 'بحث تجريبي مقبول',
 'A seed abstract for accepted paper.', 'ملخص تجريبي لبحث مقبول.',
 'OCS, Accepted', 'نظام مؤتمرات، مقبول',
 'accepted', 'accepted', 'accepted', 1, 1, NOW(), @org1, 'Seed Author', 8.75, NOW())
ON DUPLICATE KEY UPDATE
author_id=VALUES(author_id), conference_id=VALUES(conference_id), org_id=VALUES(org_id),
title=VALUES(title), title_ar=VALUES(title_ar), status=VALUES(status), stage=VALUES(stage), decision=VALUES(decision), avg_score=VALUES(avg_score);

INSERT INTO submissions
(conference_id, author_id, submission_no, title, title_ar, abstract, abstract_ar, keywords, keywords_ar,
 status, stage, decision, current_round_no, current_version, submitted_at, org_id, author_name, avg_score, created_at)
VALUES
(@conf3, @u_org4admin, 'SEED-ORG4-001', 'Seed Org4 Isolation Paper', 'بحث تجريبي لعزل المؤسسة الرابعة',
 'A seed abstract for org isolation.', 'ملخص تجريبي لاختبار عزل المؤسسة.',
 'Isolation, Test', 'عزل، اختبار',
 'submitted', 'screening', NULL, 1, 1, NOW(), @org3, 'Seed Org4 Admin', NULL, NOW())
ON DUPLICATE KEY UPDATE
conference_id=VALUES(conference_id), org_id=VALUES(org_id), author_id=VALUES(author_id),
title=VALUES(title), status=VALUES(status), stage=VALUES(stage);

/* -------------------------------------------------------
   8) Load submission IDs
------------------------------------------------------- */
SELECT id INTO @sub1 FROM submissions WHERE submission_no='SEED-001' LIMIT 1;
SELECT id INTO @sub2 FROM submissions WHERE submission_no='SEED-002' LIMIT 1;
SELECT id INTO @sub3 FROM submissions WHERE submission_no='SEED-003' LIMIT 1;

/* -------------------------------------------------------
   9) Review assignments
------------------------------------------------------- */

DELETE FROM reviews
WHERE submission_id IN (@sub1, @sub2, @sub3);

DELETE FROM review_assignments
WHERE submission_id IN (@sub1, @sub2, @sub3);

INSERT INTO review_assignments
(submission_id, conference_id, reviewer_id, assigned_by, due_date, status, assigned_at, round_no, org_id)
VALUES
(@sub1, @conf1, @u_reviewer, @u_chair, DATE_ADD(CURDATE(), INTERVAL 14 DAY), 'assigned', NOW(), 1, @org1),
(@sub2, @conf1, @u_reviewer, @u_chair, DATE_ADD(CURDATE(), INTERVAL 10 DAY), 'accepted', NOW(), 1, @org1),
(@sub3, @conf1, @u_reviewer, @u_chair, DATE_ADD(CURDATE(), INTERVAL 7 DAY), 'completed', NOW(), 1, @org1);

SELECT id INTO @ra1 FROM review_assignments WHERE submission_id=@sub1 AND reviewer_id=@u_reviewer LIMIT 1;
SELECT id INTO @ra2 FROM review_assignments WHERE submission_id=@sub2 AND reviewer_id=@u_reviewer LIMIT 1;
SELECT id INTO @ra3 FROM review_assignments WHERE submission_id=@sub3 AND reviewer_id=@u_reviewer LIMIT 1;

/* -------------------------------------------------------
   10) Completed review for accepted paper
------------------------------------------------------- */

INSERT INTO reviews
(assignment_id, review_assignment_id, originality_score, clarity_score, relevance_score, overall_score,
 recommendation, comments_to_author, comments_to_editor, submitted_at, org_id, conference_id, reviewer_id,
 submission_id, score, decision, round_no, created_at)
VALUES
(@ra3, @ra3, 9.00, 8.50, 8.75, 8.75,
 'accept', 'Strong paper. Recommended for acceptance.', 'Good contribution for proceedings.',
 NOW(), @org1, @conf1, @u_reviewer, @sub3, 9, 'accept', 1, NOW());

UPDATE review_assignments
SET completed_at = NOW(), status='completed'
WHERE id = @ra3;

UPDATE submissions
SET avg_score = 8.75, recommended_decision='accept', decision='accepted', status='accepted', stage='accepted'
WHERE id = @sub3;

/* -------------------------------------------------------
   11) Workflow settings and stages for selected conferences
------------------------------------------------------- */

INSERT INTO workflow_settings (conference_id, org_id, review_rounds, allow_revision, auto_decision)
SELECT c.id, c.org_id, 1, 1, 0
FROM conferences c
LEFT JOIN workflow_settings ws ON ws.conference_id = c.id
WHERE ws.id IS NULL;

INSERT INTO conference_workflow (conference_id, stage, is_enabled, org_id)
SELECT c.id, x.stage, 1, c.org_id
FROM conferences c
JOIN (
    SELECT 'submission' AS stage UNION ALL
    SELECT 'screening' UNION ALL
    SELECT 'review' UNION ALL
    SELECT 'revision' UNION ALL
    SELECT 'decision' UNION ALL
    SELECT 'galley' UNION ALL
    SELECT 'production' UNION ALL
    SELECT 'issue' UNION ALL
    SELECT 'published'
) x
LEFT JOIN conference_workflow cw
       ON cw.conference_id = c.id
      AND cw.stage = x.stage
WHERE cw.id IS NULL;

COMMIT;

/* -------------------------------------------------------
   12) Verification output
------------------------------------------------------- */

SELECT 'SEED USERS - password is Test@12345' AS info;

SELECT
    u.id,
    u.email,
    r.code AS global_role,
    u.org_id
FROM users u
JOIN roles r ON r.id = u.role_id
WHERE u.email LIKE 'seed.%@easychair.test'
ORDER BY u.id;

SELECT
    u.email,
    r.code AS conference_role,
    uc.conference_id
FROM user_conferences uc
JOIN users u ON u.id = uc.user_id
JOIN roles r ON r.id = uc.role_id
WHERE u.email LIKE 'seed.%@easychair.test'
ORDER BY uc.conference_id, r.id, u.email;

SELECT
    s.id,
    s.submission_no,
    s.title,
    s.org_id,
    s.conference_id,
    s.author_id,
    s.status,
    s.stage,
    s.decision,
    s.avg_score
FROM submissions s
WHERE s.submission_no LIKE 'SEED-%'
ORDER BY s.id;

SELECT
    ra.id,
    ra.submission_id,
    ra.conference_id,
    ra.reviewer_id,
    ra.status,
    ra.round_no,
    ra.org_id
FROM review_assignments ra
WHERE ra.submission_id IN (@sub1, @sub2, @sub3)
ORDER BY ra.id;

SELECT
    rv.id,
    rv.review_assignment_id,
    rv.submission_id,
    rv.reviewer_id,
    rv.conference_id,
    rv.org_id,
    rv.overall_score,
    rv.score,
    rv.recommendation
FROM reviews rv
WHERE rv.submission_id IN (@sub1, @sub2, @sub3)
ORDER BY rv.id;
