-- VerseLive Database Schema
-- MySQL 8+
-- Charset chosen for full Unicode (accented translations, future languages)

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ---------------------------------------------------------------
-- CHURCHES (tenant table)
-- Present now so every other table can carry a nullable church_id
-- from day one. Phase 1 runs everything under church_id = 1
-- (a "Default Church"). The Super Admin phase later activates
-- real multi-tenancy without any schema migration.
-- ---------------------------------------------------------------
CREATE TABLE churches (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name            VARCHAR(150) NOT NULL,
    slug            VARCHAR(150) NOT NULL UNIQUE,
    status          ENUM('active','suspended') NOT NULL DEFAULT 'active',
    plan            ENUM('free','basic','standard','premium','enterprise') NOT NULL DEFAULT 'free',
    max_users       INT UNSIGNED NOT NULL DEFAULT 5,
    max_admins      INT UNSIGNED NOT NULL DEFAULT 1,
    storage_limit_mb INT UNSIGNED NOT NULL DEFAULT 100,
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------
-- BOOKS
-- ---------------------------------------------------------------
CREATE TABLE books (
    id              SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name            VARCHAR(50) NOT NULL,
    abbreviation    VARCHAR(10) NOT NULL,
    testament       ENUM('OT','NT') NOT NULL,
    order_no        SMALLINT UNSIGNED NOT NULL,
    UNIQUE KEY uq_books_order (order_no),
    KEY idx_books_abbreviation (abbreviation)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------
-- TRANSLATIONS
-- ---------------------------------------------------------------
CREATE TABLE translations (
    id              SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name            VARCHAR(100) NOT NULL,
    abbreviation    VARCHAR(10) NOT NULL UNIQUE,
    language        VARCHAR(50) NOT NULL DEFAULT 'English',
    is_public_domain TINYINT(1) NOT NULL DEFAULT 1,
    is_active       TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------
-- VERSES
-- verse_text_normalized: lowercase, punctuation-stripped copy used
-- for fast LIKE-based phrase search and fuzzy fallback matching.
-- FULLTEXT index powers relevance-ranked natural language search.
-- ---------------------------------------------------------------
CREATE TABLE verses (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    book_id             SMALLINT UNSIGNED NOT NULL,
    chapter             SMALLINT UNSIGNED NOT NULL,
    verse               SMALLINT UNSIGNED NOT NULL,
    translation_id      SMALLINT UNSIGNED NOT NULL,
    verse_text          TEXT NOT NULL,
    verse_text_normalized TEXT NOT NULL,
    popularity          INT UNSIGNED NOT NULL DEFAULT 0,
    CONSTRAINT fk_verses_book FOREIGN KEY (book_id) REFERENCES books(id) ON DELETE CASCADE,
    CONSTRAINT fk_verses_translation FOREIGN KEY (translation_id) REFERENCES translations(id) ON DELETE CASCADE,
    UNIQUE KEY uq_verse_ref (book_id, chapter, verse, translation_id),
    KEY idx_verse_lookup (book_id, chapter, verse),
    FULLTEXT KEY ft_verse_text (verse_text)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------
-- VERSE WORDS (dictionary used for fuzzy / misspelling correction)
-- Built once from the corpus; lets us SOUNDEX-match a mistyped
-- query word against real Bible vocabulary quickly.
-- ---------------------------------------------------------------
CREATE TABLE verse_words (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    word        VARCHAR(50) NOT NULL,
    soundex_code VARCHAR(10) NOT NULL,
    frequency   INT UNSIGNED NOT NULL DEFAULT 1,
    UNIQUE KEY uq_word (word),
    KEY idx_soundex (soundex_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------
-- SEARCH HISTORY
-- ---------------------------------------------------------------
CREATE TABLE search_history (
    id                  BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    church_id           INT UNSIGNED NULL,
    keyword             VARCHAR(255) NOT NULL,
    detected_reference  VARCHAR(100) NULL,
    ip_address          VARCHAR(45) NULL,
    created_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_search_created (created_at),
    KEY idx_search_church (church_id),
    CONSTRAINT fk_search_church FOREIGN KEY (church_id) REFERENCES churches(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------
-- USERS
-- ---------------------------------------------------------------
CREATE TABLE users (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    church_id       INT UNSIGNED NULL,
    fullname        VARCHAR(150) NOT NULL,
    email           VARCHAR(150) NOT NULL UNIQUE,
    password        VARCHAR(255) NOT NULL,
    role            ENUM('super_admin','church_admin','operator','viewer') NOT NULL DEFAULT 'viewer',
    status          ENUM('active','suspended') NOT NULL DEFAULT 'active',
    last_login_at   DATETIME NULL,
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_users_church (church_id),
    CONSTRAINT fk_users_church FOREIGN KEY (church_id) REFERENCES churches(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------
-- SETTINGS (key/value, scoped per church; church_id NULL = global)
-- ---------------------------------------------------------------
CREATE TABLE settings (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    church_id       INT UNSIGNED NULL,
    setting_key     VARCHAR(100) NOT NULL,
    setting_value   TEXT NULL,
    UNIQUE KEY uq_setting (church_id, setting_key),
    CONSTRAINT fk_settings_church FOREIGN KEY (church_id) REFERENCES churches(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------
-- FAVORITES / SERMON HISTORY (foundations for later phases)
-- ---------------------------------------------------------------
CREATE TABLE favorites (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id     INT UNSIGNED NOT NULL,
    verse_id    INT UNSIGNED NOT NULL,
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_favorite (user_id, verse_id),
    CONSTRAINT fk_fav_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_fav_verse FOREIGN KEY (verse_id) REFERENCES verses(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE sermon_sessions (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    church_id   INT UNSIGNED NULL,
    title       VARCHAR(150) NULL,
    started_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ended_at    DATETIME NULL,
    CONSTRAINT fk_session_church FOREIGN KEY (church_id) REFERENCES churches(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE sermon_verses (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    session_id  INT UNSIGNED NOT NULL,
    verse_id    INT UNSIGNED NOT NULL,
    detected_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_sv_session FOREIGN KEY (session_id) REFERENCES sermon_sessions(id) ON DELETE CASCADE,
    CONSTRAINT fk_sv_verse FOREIGN KEY (verse_id) REFERENCES verses(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------
-- AUDIT LOGS (foundation for Super Admin phase)
-- ---------------------------------------------------------------
CREATE TABLE audit_logs (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id     INT UNSIGNED NULL,
    church_id   INT UNSIGNED NULL,
    action      VARCHAR(100) NOT NULL,
    details     TEXT NULL,
    ip_address  VARCHAR(45) NULL,
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_audit_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;
