290 lines
7.9 KiB
SQL
290 lines
7.9 KiB
SQL
CREATE EXTENSION IF NOT EXISTS pgcrypto;
|
||
|
||
|
||
INSERT INTO users (
|
||
id,
|
||
first_name,
|
||
last_name,
|
||
gender,
|
||
birth_day,
|
||
email,
|
||
phone_number,
|
||
role,
|
||
password,
|
||
age_group,
|
||
education_level,
|
||
country,
|
||
region,
|
||
knowledge_level,
|
||
nick_name,
|
||
occupation,
|
||
learning_goal,
|
||
language_goal,
|
||
language_challange,
|
||
favourite_topic,
|
||
initial_assessment_completed,
|
||
email_verified,
|
||
phone_verified,
|
||
status,
|
||
last_login,
|
||
profile_completed,
|
||
profile_picture_url,
|
||
preferred_language,
|
||
created_at,
|
||
updated_at
|
||
)
|
||
VALUES
|
||
(
|
||
10,
|
||
'Demo',
|
||
'Student',
|
||
'Male',
|
||
'2000-01-01',
|
||
'student10@yimaru.com',
|
||
NULL,
|
||
'USER',
|
||
crypt('password@123', gen_salt('bf'))::bytea,
|
||
'25_34',
|
||
'Bachelor',
|
||
'Ethiopia',
|
||
'Addis Ababa',
|
||
'BEGINNER',
|
||
'Demo',
|
||
'Student',
|
||
'Learn programming',
|
||
'English',
|
||
'Grammar',
|
||
'Technology',
|
||
FALSE,
|
||
TRUE,
|
||
FALSE,
|
||
'ACTIVE',
|
||
NULL,
|
||
FALSE,
|
||
NULL,
|
||
'en',
|
||
CURRENT_TIMESTAMP,
|
||
NULL
|
||
),
|
||
(
|
||
11,
|
||
'System',
|
||
'Admin',
|
||
'Female',
|
||
'1995-01-01',
|
||
'admin@yimaru.com',
|
||
'0911001100',
|
||
'ADMIN',
|
||
crypt('password@123', gen_salt('bf'))::bytea,
|
||
'35_44',
|
||
'Master',
|
||
'Ethiopia',
|
||
'Addis Ababa',
|
||
'ADVANCED',
|
||
'SysAdmin',
|
||
'Administrator',
|
||
'Manage system',
|
||
'English',
|
||
'Writing',
|
||
'Management',
|
||
TRUE,
|
||
TRUE,
|
||
TRUE,
|
||
'ACTIVE',
|
||
NULL,
|
||
TRUE,
|
||
NULL,
|
||
'en',
|
||
CURRENT_TIMESTAMP,
|
||
NULL
|
||
),
|
||
(
|
||
12,
|
||
'Support',
|
||
'Agent',
|
||
'Female',
|
||
'1998-01-01',
|
||
'support@yimaru.com',
|
||
'0911223344',
|
||
'SUPPORT',
|
||
crypt('password@123', gen_salt('bf'))::bytea,
|
||
'55_PLUS',
|
||
'Diploma',
|
||
'Ethiopia',
|
||
'Addis Ababa',
|
||
'INTERMEDIATE',
|
||
'Support',
|
||
'Agent',
|
||
'Assist users',
|
||
'English',
|
||
'Conversation',
|
||
'Customer Service',
|
||
TRUE,
|
||
TRUE,
|
||
TRUE,
|
||
'ACTIVE',
|
||
NULL,
|
||
TRUE,
|
||
NULL,
|
||
'en',
|
||
CURRENT_TIMESTAMP,
|
||
NULL
|
||
)
|
||
ON CONFLICT (id) DO NOTHING;
|
||
|
||
-- ======================================================
|
||
-- Global Settings (LMS)
|
||
-- ======================================================
|
||
INSERT INTO global_settings (key, value)
|
||
VALUES
|
||
('platform_name', 'Yimaru LMS'),
|
||
('default_language', 'en'),
|
||
('allow_self_signup', 'true'),
|
||
('otp_expiry_minutes', '5'),
|
||
('certificate_enabled', 'true'),
|
||
('max_courses_per_instructor', '50')
|
||
ON CONFLICT (key) DO NOTHING;
|
||
-- ======================================================
|
||
|
||
-- ======================================================
|
||
-- Assessment Questions – Level A2 (EASY)
|
||
-- ======================================================
|
||
|
||
INSERT INTO assessment_questions (id, title, question_type, difficulty_level, points, is_active)
|
||
VALUES
|
||
(1, 'What would you say to greet someone before lunchtime?', 'MULTIPLE_CHOICE', 'EASY', 1, TRUE),
|
||
(2, 'Which question is correct to ask about your routine?', 'MULTIPLE_CHOICE', 'EASY', 1, TRUE),
|
||
(3, 'She ___ like pizza.', 'MULTIPLE_CHOICE', 'EASY', 1, TRUE),
|
||
(4, 'I usually go to school and start class ____ eight o’clock.', 'MULTIPLE_CHOICE', 'EASY', 1, TRUE),
|
||
(5, 'Someone says, “Here is the book you asked for.” What is the best response?', 'MULTIPLE_CHOICE', 'EASY', 1, TRUE)
|
||
ON CONFLICT (id) DO NOTHING;
|
||
|
||
INSERT INTO assessment_question_options (question_id, option_text, option_order, is_correct)
|
||
VALUES
|
||
-- Q1
|
||
(1, 'Good morning.', 1, TRUE),
|
||
(1, 'How do you do?', 2, FALSE),
|
||
(1, 'Good afternoon.', 3, FALSE),
|
||
(1, 'Goodbye.', 4, FALSE),
|
||
|
||
-- Q2
|
||
(2, 'What time you wake up?', 1, FALSE),
|
||
(2, 'What time do you wake up?', 2, TRUE),
|
||
(2, 'What time are you wake up?', 3, FALSE),
|
||
(2, 'What time waking you?', 4, FALSE),
|
||
|
||
-- Q3
|
||
(3, 'do not', 1, FALSE),
|
||
(3, 'not', 2, FALSE),
|
||
(3, 'is not', 3, FALSE),
|
||
(3, 'does not', 4, TRUE),
|
||
|
||
-- Q4
|
||
(4, 'about', 1, FALSE),
|
||
(4, 'on', 2, FALSE),
|
||
(4, 'at', 3, TRUE),
|
||
(4, 'in', 4, FALSE),
|
||
|
||
-- Q5
|
||
(5, 'Never mind.', 1, FALSE),
|
||
(5, 'Really?', 2, FALSE),
|
||
(5, 'What a pity!', 3, FALSE),
|
||
(5, 'Thank you.', 4, TRUE);
|
||
|
||
-- ======================================================
|
||
-- Assessment Questions – Level B1 (MEDIUM)
|
||
-- ======================================================
|
||
|
||
INSERT INTO assessment_questions (id, title, question_type, difficulty_level, points, is_active)
|
||
VALUES
|
||
(6, 'How do you introduce your friend to another person?', 'MULTIPLE_CHOICE', 'MEDIUM', 1, TRUE),
|
||
(7, 'How would you ask for the price of an item in a shop?', 'MULTIPLE_CHOICE', 'MEDIUM', 1, TRUE),
|
||
(8, 'Which sentence correctly gives simple directions?', 'MULTIPLE_CHOICE', 'MEDIUM', 1, TRUE),
|
||
(9, 'The watch shows 10:50, but the real time is 10:45. What can you say?', 'MULTIPLE_CHOICE', 'MEDIUM', 1, TRUE),
|
||
(10, 'Which instruction is correct when giving directions?', 'MULTIPLE_CHOICE', 'MEDIUM', 1, TRUE)
|
||
ON CONFLICT (id) DO NOTHING;
|
||
|
||
INSERT INTO assessment_question_options (question_id, option_text, option_order, is_correct)
|
||
VALUES
|
||
-- Q6
|
||
(6, 'Hello, my name is Samson.', 1, FALSE),
|
||
(6, 'Good morning. Nice to meet you.', 2, FALSE),
|
||
(6, 'Let me introduce myself to my friend.', 3, FALSE),
|
||
(6, 'This is my friend, Samson.', 4, TRUE),
|
||
|
||
-- Q7
|
||
(7, 'How many are these?', 1, FALSE),
|
||
(7, 'What is this?', 2, FALSE),
|
||
(7, 'How much is this?', 3, TRUE),
|
||
(7, 'Where is the nearest shop?', 4, FALSE),
|
||
|
||
-- Q8
|
||
(8, 'Thank you very much for asking.', 1, FALSE),
|
||
(8, 'Turn left and walk two blocks.', 2, TRUE),
|
||
(8, 'Why don’t you eat out.', 3, FALSE),
|
||
(8, 'Take the bus to the park.', 4, FALSE),
|
||
|
||
-- Q9
|
||
(9, 'My watch is slow.', 1, TRUE),
|
||
(9, 'My watch is late.', 2, FALSE),
|
||
(9, 'My watch is fast.', 3, FALSE),
|
||
(9, 'My watch is early.', 4, FALSE),
|
||
|
||
-- Q10
|
||
(10, 'Turn left.', 1, TRUE),
|
||
(10, 'Turn on left.', 2, FALSE),
|
||
(10, 'Turn left side.', 3, FALSE),
|
||
(10, 'Turn to straight.', 4, FALSE);
|
||
|
||
-- ======================================================
|
||
-- Assessment Questions – Level B2 (HARD)
|
||
-- ======================================================
|
||
|
||
INSERT INTO assessment_questions (id, title, question_type, difficulty_level, points, is_active)
|
||
VALUES
|
||
(11, 'What is the most polite way to ask to speak to someone on the phone?', 'MULTIPLE_CHOICE', 'HARD', 1, TRUE),
|
||
(12, 'How do you correctly state the age of a person who is 30 years old?', 'MULTIPLE_CHOICE', 'HARD', 1, TRUE),
|
||
(13, 'When asking for help with a new Yimaru App feature, which option is most appropriate?', 'MULTIPLE_CHOICE', 'HARD', 1, TRUE),
|
||
(14, 'Which word has the unvoiced “th” sound?', 'MULTIPLE_CHOICE', 'HARD', 1, TRUE),
|
||
(15, 'Which sentence sounds like a warning, not friendly advice?', 'MULTIPLE_CHOICE', 'HARD', 1, TRUE),
|
||
(16, 'What does this sentence mean? “I will definitely be there on time.”', 'MULTIPLE_CHOICE', 'HARD', 1, TRUE)
|
||
ON CONFLICT (id) DO NOTHING;
|
||
|
||
INSERT INTO assessment_question_options (question_id, option_text, option_order, is_correct)
|
||
VALUES
|
||
-- Q11
|
||
(11, 'May I speak to Mr. Tesfaye, please?', 1, TRUE),
|
||
(11, 'Can I talk to Mr. Tesfaye?', 2, FALSE),
|
||
(11, 'Is Mr. Tesfaye there?', 3, FALSE),
|
||
(11, 'I want to talk to Mr. Tesfaye.', 4, FALSE),
|
||
|
||
-- Q12
|
||
(12, 'He is thirty years.', 1, FALSE),
|
||
(12, 'He has thirty years.', 2, FALSE),
|
||
(12, 'He has thirty years old.', 3, FALSE),
|
||
(12, 'He is thirty.', 4, TRUE),
|
||
|
||
-- Q13
|
||
(13, 'Are you familiar with how this feature works?', 1, FALSE),
|
||
(13, 'Could you walk me through how this feature works?', 2, TRUE),
|
||
(13, 'I believe I understand how this feature works.', 3, FALSE),
|
||
(13, 'I’ve tried similar features before.', 4, FALSE),
|
||
|
||
-- Q14
|
||
(14, 'That', 1, FALSE),
|
||
(14, 'They', 2, FALSE),
|
||
(14, 'These', 3, FALSE),
|
||
(14, 'Three', 4, TRUE),
|
||
|
||
-- Q15
|
||
(15, 'You might want to plan your time better.', 1, FALSE),
|
||
(15, 'If I were you, I’d start earlier.', 2, FALSE),
|
||
(15, 'You’d better meet the deadline this time.', 3, TRUE),
|
||
(15, 'Why don’t you try using a planner?', 4, FALSE),
|
||
|
||
-- Q16
|
||
(16, 'The speaker is unsure about arriving.', 1, FALSE),
|
||
(16, 'The speaker is promising to arrive on time.', 2, TRUE),
|
||
(16, 'The speaker might arrive late.', 3, FALSE),
|
||
(16, 'The speaker has already arrived.', 4, FALSE);
|