-- Reconstructed base schema for local development.
--
-- This repo ships no schema.sql/init.sql — scripts/*.sql are all incremental
-- ALTER/CREATE migrations that assume these core tables already exist in
-- production. This file recreates that assumed starting state (reverse
-- engineered from every raw-SQL query in app/Models, app/Controllers and
-- app/Services) so docker/mysql/init/001_*.sql onward (copies of scripts/*.sql,
-- in dependency order) can apply cleanly on top, ending at the current schema.
--
-- Not hand-applied in production — see CLAUDE.md's "Database" section for the
-- real (manual, no-framework) migration process. This file is local-dev-only.

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ── Accounts ────────────────────────────────────────────────────────────────

CREATE TABLE users (
    id                          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name                        VARCHAR(255) NOT NULL,
    email                       VARCHAR(255) NOT NULL,
    password                    VARCHAR(255) NOT NULL,
    role                        ENUM('admin','teacher','adviser','manager','parent','student','subadmin') NOT NULL,
    iin                         VARCHAR(12) NULL,
    remember_token              VARCHAR(64) NULL,
    remember_expires            DATETIME NULL,
    premium_until               DATE NULL,
    notification_period_weeks  TINYINT UNSIGNED NULL,
    created_at                  DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at                  DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_users_email (email),
    UNIQUE KEY uniq_users_iin (iin),
    KEY idx_users_role (role)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE teachers (
    user_id      BIGINT UNSIGNED NOT NULL PRIMARY KEY,
    price_value  DECIMAL(10,2) NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── Dictionaries (managed generically by admin/indexes.php) ─────────────────

CREATE TABLE classes (
    id   INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE languages (
    id   INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE learning_types (
    id            INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name          VARCHAR(100) NOT NULL,
    abbreviation  VARCHAR(20) NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE disciplines (
    id            INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name          VARCHAR(100) NOT NULL,
    abbreviation  VARCHAR(20) NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Only dictionary table that already had sort_order before
-- scripts/add_sort_order_to_dictionaries.sql (see that script's comment).
CREATE TABLE student_statuses (
    id          INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(50) NOT NULL,
    color       VARCHAR(20) NOT NULL DEFAULT 'secondary',
    sort_order  INT NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE classrooms (
    id               INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name             VARCHAR(100) NOT NULL,
    link             VARCHAR(255) NULL,
    comment          VARCHAR(255) NULL,
    rent_per_lesson  DECIMAL(10,2) NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Manual (app-assigned) primary keys — see DictionaryController::handleAdd().
CREATE TABLE comment_categories (
    id   INT NOT NULL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE comments (
    id           INT NOT NULL PRIMARY KEY,
    category_id  INT NOT NULL,
    text         VARCHAR(500) NOT NULL,
    KEY idx_comments_category_id (category_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── Students & guardians ─────────────────────────────────────────────────────

-- Note: `discount_percent` is added later by 018_add_discount_percent_to_students.sql.
CREATE TABLE students (
    id                     INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    first_name             VARCHAR(100) NOT NULL,
    last_name              VARCHAR(100) NOT NULL,
    middle_name            VARCHAR(100) NULL,
    gender                 TINYINT NOT NULL DEFAULT 0,
    phone                  VARCHAR(30) NULL,
    iin                    VARCHAR(12) NULL,
    goal                   VARCHAR(500) NULL,
    agreement_number       VARCHAR(50) NULL,
    agent_first_name       VARCHAR(100) NULL,
    agent_last_name        VARCHAR(100) NULL,
    agent_phone_number     VARCHAR(30) NULL,
    learning_type_id       INT NULL,
    class_id               INT NULL,
    language_id            INT NULL,
    status_id              INT NULL DEFAULT 1,
    general_tariff_id      INT NULL,
    individual_tariff_id   INT NULL,
    user_id                BIGINT UNSIGNED NULL,
    parent_id              BIGINT UNSIGNED NULL,
    manager_user_id        BIGINT UNSIGNED NULL,
    adviser_id             BIGINT UNSIGNED NULL,
    created_at             DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at             DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_students_iin (iin),
    UNIQUE KEY uniq_students_user_id (user_id),
    KEY idx_students_class_id (class_id),
    KEY idx_students_language_id (language_id),
    KEY idx_students_learning_type_id (learning_type_id),
    KEY idx_students_status_id (status_id),
    KEY idx_students_parent_id (parent_id),
    KEY idx_students_adviser_id (adviser_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE frozen_days (
    id          INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    student_id  INT NOT NULL,
    date_from   DATE NOT NULL,
    date_to     DATE NOT NULL,
    reason      VARCHAR(500) NULL,
    KEY idx_frozen_days_student_id (student_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE student_tags (
    id    INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name  VARCHAR(100) NOT NULL,
    color VARCHAR(20) NOT NULL DEFAULT 'primary',
    UNIQUE KEY uniq_student_tags_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE holidays (
    id          INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    date        DATE NOT NULL,
    description VARCHAR(1000) NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE student_tag_assignments (
    student_id  INT NOT NULL,
    tag_id      INT NOT NULL,
    PRIMARY KEY (student_id, tag_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Referenced by Student::getForAdviserList() / StudentProgress::disciplineNames()
-- but no write path was found anywhere in the app — kept for query
-- compatibility, expected to stay empty in most local setups.
CREATE TABLE student_disciplines (
    student_id     INT NOT NULL,
    discipline_id  INT NOT NULL,
    PRIMARY KEY (student_id, discipline_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Referenced by Student::getForAdviserList() (SUM(lb.lessons_total)) but no
-- write path was found — same as student_disciplines above.
CREATE TABLE lesson_balances (
    student_id     INT NOT NULL PRIMARY KEY,
    lessons_total  INT NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── Groups & schedule ─────────────────────────────────────────────────────────

CREATE TABLE study_groups (
    id                     INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    teacher_id             BIGINT UNSIGNED NULL,
    type                   VARCHAR(20) NOT NULL DEFAULT 'Group',
    group_name             VARCHAR(255) NOT NULL,
    discipline_id          INT NULL,
    language_id            INT NULL,
    learning_type_id       INT NULL,
    class_id               INT NULL,
    is_active              TINYINT(1) NOT NULL DEFAULT 1,
    maturity               VARCHAR(50) NULL,
    study_units_in_range   VARCHAR(50) NULL,
    description            VARCHAR(500) NULL,
    created_at             DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at             DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_study_groups_teacher_id (teacher_id),
    KEY idx_study_groups_is_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Snapshot table written by Group::archive() when a group is deleted.
CREATE TABLE archived_study_groups (
    id                     INT NOT NULL PRIMARY KEY,
    teacher_id             BIGINT UNSIGNED NULL,
    type                   VARCHAR(20) NULL,
    group_name             VARCHAR(255) NULL,
    maturity               VARCHAR(50) NULL,
    study_units_in_range   VARCHAR(50) NULL,
    description            VARCHAR(500) NULL,
    created_at             DATETIME NULL,
    updated_at             DATETIME NULL,
    discipline_id          INT NULL,
    class_id               INT NULL,
    learning_type_id       INT NULL,
    language_id            INT NULL,
    archived_at            DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE student_groups (
    id          INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    student_id  INT NOT NULL,
    group_id    INT NOT NULL,
    joined_at   DATE NOT NULL,
    end_date    DATE NULL,
    weekdays    INT NOT NULL DEFAULT 0,
    is_active   TINYINT(1) NOT NULL DEFAULT 1,
    KEY idx_student_groups_student_id (student_id),
    KEY idx_student_groups_group_id (group_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE schedule_items (
    id            INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    group_id      INT NOT NULL,
    begin_date    DATE NOT NULL,
    end_date      DATE NULL,
    weekdays      INT NOT NULL DEFAULT 0,
    begin_time    TIME NOT NULL,
    end_time      TIME NOT NULL,
    classroom_id  INT NULL,
    KEY idx_schedule_items_group_id (group_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── Tariffs ───────────────────────────────────────────────────────────────────

CREATE TABLE tariffs (
    id             INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name           VARCHAR(255) NOT NULL,
    category       VARCHAR(20) NOT NULL DEFAULT 'group',
    price          DECIMAL(10,2) NOT NULL DEFAULT 0,
    lessons_count  INT NOT NULL DEFAULT 0,
    duration_days  INT NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE group_tariffs (
    id          INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    group_id    INT NOT NULL,
    tariff_id   INT NOT NULL,
    start_date  DATE NOT NULL,
    end_date    DATE NOT NULL,
    KEY idx_group_tariffs_group_id (group_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE student_tariffs (
    id                  INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    student_id          INT NOT NULL,
    group_id            INT NOT NULL,
    tariff_id           INT NOT NULL,
    start_date          DATE NOT NULL,
    end_date            DATE NOT NULL,
    lessons_remaining   INT NOT NULL DEFAULT 0,
    KEY idx_student_tariffs_student_id (student_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── Lessons & attendance ──────────────────────────────────────────────────────

-- Note: `auto_marked` is added later by 015_add_auto_marked_to_lessons.sql.
CREATE TABLE lessons (
    id                 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ed_unit_id         INT NOT NULL,
    teacher_id         BIGINT UNSIGNED NOT NULL,
    lesson_date        DATE NOT NULL,
    begin_time         TIME NOT NULL,
    end_time           TIME NOT NULL,
    students_present   INT NOT NULL DEFAULT 0,
    all_students       INT NOT NULL DEFAULT 0,
    payment            DECIMAL(6,2) NOT NULL DEFAULT 0,
    KEY idx_lessons_ed_unit_date (ed_unit_id, lesson_date),
    KEY idx_lessons_teacher_date (teacher_id, lesson_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE student_lessons (
    id                INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    lesson_id         INT NOT NULL,
    student_id        INT NOT NULL,
    present           TINYINT(1) NOT NULL DEFAULT 0,
    status            VARCHAR(20) NOT NULL DEFAULT 'attended',
    kr_score          TINYINT NULL,
    dz_score          TINYINT NULL,
    dz_not_assigned   TINYINT(1) NOT NULL DEFAULT 0,
    lateness          VARCHAR(30) NULL,
    KEY idx_student_lessons_lesson_id (lesson_id),
    KEY idx_student_lessons_student_id (student_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE student_lesson_comments (
    id                  INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    student_lesson_id   INT NOT NULL,
    comment_id          INT NULL,
    custom_text         VARCHAR(500) NULL,
    created_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_student_lesson_comments_sl_id (student_lesson_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE weekly_tests (
    id          INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    lesson_id   INT NOT NULL,
    teacher_id  BIGINT UNSIGNED NOT NULL,
    max_score   INT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE weekly_test_results (
    id          INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    test_id     INT NOT NULL,
    student_id  INT NOT NULL,
    score       INT NULL,
    UNIQUE KEY uniq_weekly_test_results (test_id, student_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── Billing ───────────────────────────────────────────────────────────────────

CREATE TABLE payment_statuses (
    id    INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name  VARCHAR(50) NOT NULL,
    color VARCHAR(20) NOT NULL DEFAULT 'secondary'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE payments (
    id            INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    student_id    INT NOT NULL,
    amount        DECIMAL(10,2) NOT NULL,
    description   VARCHAR(255) NULL,
    payment_date  DATE NOT NULL,
    status_id     INT NULL,
    created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_payments_student_id (student_id),
    KEY idx_payments_payment_date (payment_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE charges (
    id            INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    student_id    INT NOT NULL,
    group_id      INT NULL,
    tariff_id     INT NULL,
    amount        DECIMAL(10,2) NOT NULL,
    charged_date  DATE NOT NULL,
    KEY idx_charges_student_id (student_id),
    KEY idx_charges_group_id (group_id),
    KEY idx_charges_charged_date (charged_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Note: `category` is added later by 013_add_category_to_expenses.sql.
CREATE TABLE expenses (
    id            INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    expense_date  DATE NOT NULL,
    amount        DECIMAL(10,2) NOT NULL,
    title         VARCHAR(255) NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── Feedback & adviser workflow ───────────────────────────────────────────────

CREATE TABLE feedbacks (
    id          INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    teacher_id  BIGINT UNSIGNED NOT NULL,
    student_id  INT NOT NULL,
    group_id    INT NULL,
    type        VARCHAR(50) NOT NULL,
    message     VARCHAR(1000) NOT NULL,
    is_read     TINYINT(1) NOT NULL DEFAULT 0,
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_feedbacks_student_id (student_id),
    KEY idx_feedbacks_is_read (is_read)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE adviser_tasks (
    id                  INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    student_id          INT NOT NULL,
    lesson_id           INT NULL,
    reason              VARCHAR(50) NOT NULL,
    streak_count        INT NOT NULL DEFAULT 0,
    is_repeat           TINYINT(1) NOT NULL DEFAULT 0,
    status              VARCHAR(20) NOT NULL DEFAULT 'open',
    resolution_action   VARCHAR(50) NULL,
    resolution_note     VARCHAR(500) NULL,
    resolved_by         BIGINT UNSIGNED NULL,
    resolved_at         DATETIME NULL,
    created_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_adviser_tasks_student_status (student_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── Gamification (hero/coins/XP/achievements) ────────────────────────────────

CREATE TABLE hero_levels (
    level_number  INT NOT NULL PRIMARY KEY,
    name          VARCHAR(100) NOT NULL,
    xp_required   INT NOT NULL,
    coin_reward   INT NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE heroes (
    id                       INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    student_id               INT NOT NULL,
    level_number             INT NOT NULL DEFAULT 1,
    xp_total                 INT NOT NULL DEFAULT 0,
    coins_balance            INT NOT NULL DEFAULT 0,
    avatar                   VARCHAR(16) NULL,
    active_since             DATE NULL,
    total_lessons            INT NOT NULL DEFAULT 0,
    total_attendance_days    INT NOT NULL DEFAULT 0,
    attendance_streak        INT NOT NULL DEFAULT 0,
    dz_streak                INT NOT NULL DEFAULT 0,
    UNIQUE KEY uniq_heroes_student_id (student_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE gamification_coins_ledger (
    id          INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    student_id  INT NOT NULL,
    lesson_id   INT NULL,
    amount      INT NOT NULL,
    reason      VARCHAR(30) NOT NULL,
    description VARCHAR(255) NULL,
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_gamification_coins_ledger_student_id (student_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE gamification_xp_ledger (
    id          INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    student_id  INT NOT NULL,
    lesson_id   INT NULL,
    amount      INT NOT NULL,
    reason      VARCHAR(30) NOT NULL,
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_gamification_xp_ledger_student_id (student_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE achievements (
    id                INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name              VARCHAR(255) NOT NULL,
    icon              VARCHAR(16) NOT NULL,
    description       VARCHAR(500) NULL,
    condition_type    VARCHAR(30) NOT NULL,
    condition_value   INT NOT NULL,
    coin_reward       INT NOT NULL DEFAULT 0,
    active            TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE student_achievements (
    id              INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    student_id      INT NOT NULL,
    achievement_id  INT NOT NULL,
    progress        INT NOT NULL DEFAULT 0,
    completed_at    DATETIME NULL,
    claimed_at      DATETIME NULL,
    UNIQUE KEY uniq_student_achievements (student_id, achievement_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;
