-- Bible Study Suite - database schema
-- Run once: mysql -u youruser -p yourdb < schema.sql

CREATE DATABASE IF NOT EXISTS deepcore_biblehub CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE deepcore_biblehub;

-- Translations available (KJV, WEB, ASV, etc.)
CREATE TABLE IF NOT EXISTS translations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(10) NOT NULL UNIQUE,      -- e.g. 'kjv', 'web', 'asv'
    name VARCHAR(100) NOT NULL,            -- e.g. 'King James Version'
    is_default TINYINT(1) NOT NULL DEFAULT 0
) ENGINE=InnoDB;

-- The 66 books, in canonical order
CREATE TABLE IF NOT EXISTS books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    slug VARCHAR(50) NOT NULL UNIQUE,      -- e.g. 'genesis', '1_corinthians'
    name VARCHAR(50) NOT NULL,             -- e.g. 'Genesis', '1 Corinthians'
    testament ENUM('OT','NT') NOT NULL,
    book_order INT NOT NULL,               -- 1-66, canonical order
    chapter_count INT NOT NULL
) ENGINE=InnoDB;

-- Every verse, per translation
CREATE TABLE IF NOT EXISTS verses (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    book_id INT NOT NULL,
    chapter INT NOT NULL,
    verse INT NOT NULL,
    translation_id INT NOT NULL,
    text MEDIUMTEXT NOT NULL,
    FOREIGN KEY (book_id) REFERENCES books(id) ON DELETE CASCADE,
    FOREIGN KEY (translation_id) REFERENCES translations(id) ON DELETE CASCADE,
    UNIQUE KEY uniq_verse (book_id, chapter, verse, translation_id),
    KEY idx_lookup (book_id, chapter, translation_id),
    FULLTEXT KEY ft_text (text)
) ENGINE=InnoDB;

-- Cross-references: "this verse relates to that passage", with a vote/weight
-- score from the source data (higher = stronger connection).
CREATE TABLE IF NOT EXISTS cross_references (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    from_book_id INT NOT NULL,
    from_chapter INT NOT NULL,
    from_verse INT NOT NULL,
    to_book_id INT NOT NULL,
    to_chapter_start INT NOT NULL,
    to_verse_start INT NOT NULL,
    to_chapter_end INT NOT NULL,
    to_verse_end INT NOT NULL,
    votes INT NOT NULL DEFAULT 0,
    FOREIGN KEY (from_book_id) REFERENCES books(id) ON DELETE CASCADE,
    FOREIGN KEY (to_book_id) REFERENCES books(id) ON DELETE CASCADE,
    KEY idx_from (from_book_id, from_chapter, from_verse),
    KEY idx_votes (votes)
) ENGINE=InnoDB;

-- Commentary entries (Matthew Henry, JFB, etc.) keyed to a verse or verse range.
CREATE TABLE IF NOT EXISTS commentaries (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    source VARCHAR(100) NOT NULL,          -- e.g. 'Matthew Henry (Concise)'
    book_id INT NOT NULL,
    chapter INT NOT NULL,
    verse_start INT NOT NULL,
    verse_end INT NOT NULL,
    text MEDIUMTEXT NOT NULL,
    FOREIGN KEY (book_id) REFERENCES books(id) ON DELETE CASCADE,
    KEY idx_lookup (book_id, chapter, verse_start, verse_end)
) ENGINE=InnoDB;

-- Interlinear: one row per original-language word, in reading order.
CREATE TABLE IF NOT EXISTS interlinear_words (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    book_id INT NOT NULL,
    chapter INT NOT NULL,
    verse INT NOT NULL,
    word_order INT NOT NULL,          -- position within the verse, 1-based
    original_word VARCHAR(100) NOT NULL,   -- Greek or Hebrew text
    transliteration VARCHAR(150) NULL,
    strongs_number VARCHAR(10) NULL,       -- e.g. 'G3056', 'H0430'
    morphology VARCHAR(50) NULL,
    gloss VARCHAR(255) NULL,               -- short English meaning
    FOREIGN KEY (book_id) REFERENCES books(id) ON DELETE CASCADE,
    KEY idx_lookup (book_id, chapter, verse, word_order),
    KEY idx_strongs (strongs_number)
) ENGINE=InnoDB;

-- Accounts (for bookmarks and reading-plan progress)
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS bookmarks (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    book_id INT NOT NULL,
    chapter INT NOT NULL,
    verse INT NOT NULL,
    note VARCHAR(500) NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (book_id) REFERENCES books(id) ON DELETE CASCADE,
    UNIQUE KEY uniq_bookmark (user_id, book_id, chapter, verse)
) ENGINE=InnoDB;

-- Reading plans: a named plan made of ordered days, each day a list of readings.
CREATE TABLE IF NOT EXISTS reading_plans (
    id INT AUTO_INCREMENT PRIMARY KEY,
    slug VARCHAR(80) NOT NULL UNIQUE,
    name VARCHAR(150) NOT NULL,
    description VARCHAR(500) NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS reading_plan_days (
    id INT AUTO_INCREMENT PRIMARY KEY,
    plan_id INT NOT NULL,
    day_number INT NOT NULL,
    -- free-text reading list for the day, e.g. "Genesis 1-3; Psalm 1"
    reading_text VARCHAR(255) NOT NULL,
    FOREIGN KEY (plan_id) REFERENCES reading_plans(id) ON DELETE CASCADE,
    UNIQUE KEY uniq_plan_day (plan_id, day_number)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS user_reading_progress (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    plan_day_id INT NOT NULL,
    completed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (plan_day_id) REFERENCES reading_plan_days(id) ON DELETE CASCADE,
    UNIQUE KEY uniq_progress (user_id, plan_day_id)
) ENGINE=InnoDB;

INSERT INTO reading_plans (slug, name, description) VALUES
    ('through-the-bible-year', 'Through the Bible in a Year', 'One reading per day, roughly 3 chapters, covering the whole Bible in 365 days.')
ON DUPLICATE KEY UPDATE name = VALUES(name);

INSERT INTO translations (code, name, is_default) VALUES
    ('kjv', 'King James Version', 1),
    ('web', 'World English Bible', 0),
    ('asv', 'American Standard Version', 0)
ON DUPLICATE KEY UPDATE name = VALUES(name);