SET NAMES utf8mb4;

INSERT INTO churches (id, name, slug, status, plan) VALUES
(1, 'Default Church', 'default', 'active', 'free');

INSERT INTO translations (id, name, abbreviation, language, is_public_domain, is_active) VALUES
(1, 'King James Version', 'KJV', 'English', 1, 1),
(2, 'American Standard Version', 'ASV', 'English', 1, 1),
(3, 'World English Bible', 'WEB', 'English', 1, 1);

INSERT INTO books (name, abbreviation, testament, order_no) VALUES
('Genesis','Gen','OT',1), ('Exodus','Exo','OT',2), ('Leviticus','Lev','OT',3),
('Numbers','Num','OT',4), ('Deuteronomy','Deu','OT',5), ('Joshua','Jos','OT',6),
('Judges','Jdg','OT',7), ('Ruth','Rut','OT',8), ('1 Samuel','1Sa','OT',9),
('2 Samuel','2Sa','OT',10), ('1 Kings','1Ki','OT',11), ('2 Kings','2Ki','OT',12),
('1 Chronicles','1Ch','OT',13), ('2 Chronicles','2Ch','OT',14), ('Ezra','Ezr','OT',15),
('Nehemiah','Neh','OT',16), ('Esther','Est','OT',17), ('Job','Job','OT',18),
('Psalms','Psa','OT',19), ('Proverbs','Pro','OT',20), ('Ecclesiastes','Ecc','OT',21),
('Song of Solomon','Sng','OT',22), ('Isaiah','Isa','OT',23), ('Jeremiah','Jer','OT',24),
('Lamentations','Lam','OT',25), ('Ezekiel','Ezk','OT',26), ('Daniel','Dan','OT',27),
('Hosea','Hos','OT',28), ('Joel','Jol','OT',29), ('Amos','Amo','OT',30),
('Obadiah','Oba','OT',31), ('Jonah','Jon','OT',32), ('Micah','Mic','OT',33),
('Nahum','Nam','OT',34), ('Habakkuk','Hab','OT',35), ('Zephaniah','Zep','OT',36),
('Haggai','Hag','OT',37), ('Zechariah','Zec','OT',38), ('Malachi','Mal','OT',39),
('Matthew','Mat','NT',40), ('Mark','Mrk','NT',41), ('Luke','Luk','NT',42),
('John','Jhn','NT',43), ('Acts','Act','NT',44), ('Romans','Rom','NT',45),
('1 Corinthians','1Co','NT',46), ('2 Corinthians','2Co','NT',47), ('Galatians','Gal','NT',48),
('Ephesians','Eph','NT',49), ('Philippians','Php','NT',50), ('Colossians','Col','NT',51),
('1 Thessalonians','1Th','NT',52), ('2 Thessalonians','2Th','NT',53), ('1 Timothy','1Ti','NT',54),
('2 Timothy','2Ti','NT',55), ('Titus','Tit','NT',56), ('Philemon','Phm','NT',57),
('Hebrews','Heb','NT',58), ('James','Jas','NT',59), ('1 Peter','1Pe','NT',60),
('2 Peter','2Pe','NT',61), ('1 John','1Jn','NT',62), ('2 John','2Jn','NT',63),
('3 John','3Jn','NT',64), ('Jude','Jud','NT',65), ('Revelation','Rev','NT',66);

-- A small sample of well-known verses so search can be demoed/tested immediately.
-- verse_text_normalized = lowercased, punctuation stripped (generated in app on import,
-- included here directly for seed simplicity).
INSERT INTO verses (book_id, chapter, verse, translation_id, verse_text, verse_text_normalized, popularity) VALUES
((SELECT id FROM books WHERE abbreviation='Jhn'), 3, 16, 1,
 'For God so loved the world, that he gave his only begotten Son, that whosoever believeth in him should not perish, but have everlasting life.',
 'for god so loved the world that he gave his only begotten son that whosoever believeth in him should not perish but have everlasting life', 100),

((SELECT id FROM books WHERE abbreviation='Psa'), 23, 1, 1,
 'The LORD is my shepherd; I shall not want.',
 'the lord is my shepherd i shall not want', 90),

((SELECT id FROM books WHERE abbreviation='Psa'), 23, 4, 1,
 'Yea, though I walk through the valley of the shadow of death, I will fear no evil: for thou art with me; thy rod and thy staff they comfort me.',
 'yea though i walk through the valley of the shadow of death i will fear no evil for thou art with me thy rod and thy staff they comfort me', 85),

((SELECT id FROM books WHERE abbreviation='Rom'), 8, 28, 1,
 'And we know that all things work together for good to them that love God, to them who are the called according to his purpose.',
 'and we know that all things work together for good to them that love god to them who are the called according to his purpose', 80),

((SELECT id FROM books WHERE abbreviation='Php'), 4, 13, 1,
 'I can do all things through Christ which strengtheneth me.',
 'i can do all things through christ which strengtheneth me', 88),

((SELECT id FROM books WHERE abbreviation='Jer'), 29, 11, 1,
 'For I know the thoughts that I think toward you, saith the LORD, thoughts of peace, and not of evil, to give you an expected end.',
 'for i know the thoughts that i think toward you saith the lord thoughts of peace and not of evil to give you an expected end', 82),

((SELECT id FROM books WHERE abbreviation='Pro'), 3, 5, 1,
 'Trust in the LORD with all thine heart; and lean not unto thine own understanding.',
 'trust in the lord with all thine heart and lean not unto thine own understanding', 75),

((SELECT id FROM books WHERE abbreviation='Isa'), 41, 10, 1,
 'Fear thou not; for I am with thee: be not dismayed; for I am thy God: I will strengthen thee; yea, I will help thee.',
 'fear thou not for i am with thee be not dismayed for i am thy god i will strengthen thee yea i will help thee', 70);

INSERT INTO settings (church_id, setting_key, setting_value) VALUES
(1, 'site_name', 'VerseLive'),
(1, 'church_name', 'Default Church'),
(1, 'default_translation_id', '1'),
(1, 'default_theme', 'light'),
(1, 'default_font_size', '48'),
(1, 'logo_path', '');

-- users.password below is a bcrypt hash of "ChangeMe123!" — replace immediately after install.
INSERT INTO users (church_id, fullname, email, password, role, status) VALUES
(1, 'Super Administrator', 'admin@verselive.local', '$2b$10$MxeYptlgX8PSAylixikxmuplB5flGMZQbj.WJ9YPLF1NWVayNt77a', 'super_admin', 'active');