-- Hattingh Hub initial schema
-- Target: MySQL 8 / MariaDB 10.5+

CREATE TABLE IF NOT EXISTS hh_schema_migrations (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    migration VARCHAR(190) NOT NULL,
    applied_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY migration (migration)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hh_households (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(160) NOT NULL,
    currency_code CHAR(3) NOT NULL DEFAULT 'ZAR',
    timezone VARCHAR(80) NOT NULL DEFAULT 'Africa/Johannesburg',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hh_members (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    household_id BIGINT UNSIGNED NOT NULL,
    display_name VARCHAR(160) NOT NULL,
    relationship VARCHAR(80) NULL DEFAULT NULL,
    date_of_birth DATE NULL DEFAULT NULL,
    email VARCHAR(190) NULL DEFAULT NULL,
    phone VARCHAR(60) NULL DEFAULT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (id),
    KEY household_id (household_id),
    KEY display_name (display_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hh_users (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    household_id BIGINT UNSIGNED NOT NULL,
    member_id BIGINT UNSIGNED NULL DEFAULT NULL,
    name VARCHAR(160) NOT NULL,
    email VARCHAR(190) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    role VARCHAR(40) NOT NULL DEFAULT 'adult',
    status VARCHAR(30) NOT NULL DEFAULT 'active',
    last_login_at DATETIME NULL DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY email (email),
    KEY household_id (household_id),
    KEY member_id (member_id),
    KEY role_status (role, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hh_settings (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    household_id BIGINT UNSIGNED NULL DEFAULT NULL,
    setting_key VARCHAR(120) NOT NULL,
    setting_value LONGTEXT NULL DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY household_setting (household_id, setting_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hh_audit_log (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    household_id BIGINT UNSIGNED NULL DEFAULT NULL,
    member_id BIGINT UNSIGNED NULL DEFAULT NULL,
    module VARCHAR(80) NOT NULL,
    action VARCHAR(120) NOT NULL,
    entity_type VARCHAR(120) NULL DEFAULT NULL,
    entity_id BIGINT UNSIGNED NULL DEFAULT NULL,
    ip_address VARCHAR(64) NULL DEFAULT NULL,
    user_agent VARCHAR(255) NULL DEFAULT NULL,
    metadata JSON NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY household_module (household_id, module),
    KEY entity_lookup (entity_type, entity_id),
    KEY created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hh_files (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    household_id BIGINT UNSIGNED NOT NULL,
    uploaded_by_member_id BIGINT UNSIGNED NULL DEFAULT NULL,
    original_name VARCHAR(255) NOT NULL,
    storage_name VARCHAR(255) NOT NULL,
    storage_disk VARCHAR(40) NOT NULL DEFAULT 'local',
    mime_type VARCHAR(120) NOT NULL,
    extension VARCHAR(20) NOT NULL,
    file_size BIGINT UNSIGNED NOT NULL DEFAULT 0,
    sha256_hash CHAR(64) NOT NULL,
    is_private TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted_at DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (id),
    KEY household_id (household_id),
    KEY sha256_hash (sha256_hash),
    KEY uploaded_by_member_id (uploaded_by_member_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hh_file_links (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    file_id BIGINT UNSIGNED NOT NULL,
    module VARCHAR(80) NOT NULL,
    entity_type VARCHAR(120) NOT NULL,
    entity_id BIGINT UNSIGNED NOT NULL,
    label VARCHAR(160) NULL DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY file_id (file_id),
    KEY entity_lookup (module, entity_type, entity_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hh_tags (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    household_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(80) NOT NULL,
    color VARCHAR(20) NULL DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY household_tag (household_id, name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hh_reminders (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    household_id BIGINT UNSIGNED NOT NULL,
    member_id BIGINT UNSIGNED NULL DEFAULT NULL,
    module VARCHAR(80) NOT NULL,
    entity_type VARCHAR(120) NULL DEFAULT NULL,
    entity_id BIGINT UNSIGNED NULL DEFAULT NULL,
    title VARCHAR(190) NOT NULL,
    notes TEXT NULL,
    due_at DATETIME NOT NULL,
    status VARCHAR(30) NOT NULL DEFAULT 'open',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    completed_at DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (id),
    KEY household_status_due (household_id, status, due_at),
    KEY entity_lookup (module, entity_type, entity_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Finance

CREATE TABLE IF NOT EXISTS hh_finance_accounts (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    household_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(160) NOT NULL,
    institution VARCHAR(160) NULL DEFAULT NULL,
    account_type VARCHAR(60) NOT NULL DEFAULT 'bank',
    currency_code CHAR(3) NOT NULL DEFAULT 'ZAR',
    opening_balance DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (id),
    KEY household_id (household_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hh_budget_months (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    household_id BIGINT UNSIGNED NOT NULL,
    year SMALLINT UNSIGNED NOT NULL,
    month TINYINT UNSIGNED NOT NULL,
    label VARCHAR(80) NOT NULL,
    status VARCHAR(30) NOT NULL DEFAULT 'open',
    notes TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY household_year_month (household_id, year, month),
    KEY status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hh_budget_categories (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    household_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(140) NOT NULL,
    category_type VARCHAR(30) NOT NULL DEFAULT 'expense',
    sort_order SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY household_category_type_name (household_id, category_type, name),
    KEY household_type (household_id, category_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hh_income_sources (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    household_id BIGINT UNSIGNED NOT NULL,
    member_id BIGINT UNSIGNED NULL DEFAULT NULL,
    name VARCHAR(160) NOT NULL,
    default_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    sort_order SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY household_id (household_id),
    KEY member_id (member_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hh_income_entries (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    budget_month_id BIGINT UNSIGNED NOT NULL,
    income_source_id BIGINT UNSIGNED NULL DEFAULT NULL,
    member_id BIGINT UNSIGNED NULL DEFAULT NULL,
    source_name VARCHAR(160) NOT NULL,
    description VARCHAR(255) NULL DEFAULT NULL,
    amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    received_date DATE NULL DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (id),
    KEY budget_month_id (budget_month_id),
    KEY income_source_id (income_source_id),
    KEY received_date (received_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hh_budget_items (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    budget_month_id BIGINT UNSIGNED NOT NULL,
    category_id BIGINT UNSIGNED NULL DEFAULT NULL,
    member_id BIGINT UNSIGNED NULL DEFAULT NULL,
    item_name VARCHAR(190) NOT NULL,
    projected_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    paid_amount DECIMAL(12,2) NULL DEFAULT NULL,
    due_date DATE NULL DEFAULT NULL,
    paid_date DATE NULL DEFAULT NULL,
    status VARCHAR(30) NOT NULL DEFAULT 'planned',
    sort_order SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    notes TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (id),
    KEY budget_month_id (budget_month_id),
    KEY category_id (category_id),
    KEY status_due (status, due_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hh_expense_transactions (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    household_id BIGINT UNSIGNED NOT NULL,
    account_id BIGINT UNSIGNED NULL DEFAULT NULL,
    budget_item_id BIGINT UNSIGNED NULL DEFAULT NULL,
    category_id BIGINT UNSIGNED NULL DEFAULT NULL,
    member_id BIGINT UNSIGNED NULL DEFAULT NULL,
    transaction_date DATE NOT NULL,
    description VARCHAR(255) NOT NULL,
    merchant VARCHAR(190) NULL DEFAULT NULL,
    amount DECIMAL(12,2) NOT NULL,
    payment_method VARCHAR(80) NULL DEFAULT NULL,
    reference VARCHAR(190) NULL DEFAULT NULL,
    notes TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (id),
    KEY household_date (household_id, transaction_date),
    KEY account_id (account_id),
    KEY budget_item_id (budget_item_id),
    KEY category_id (category_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hh_recurring_items (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    household_id BIGINT UNSIGNED NOT NULL,
    category_id BIGINT UNSIGNED NULL DEFAULT NULL,
    member_id BIGINT UNSIGNED NULL DEFAULT NULL,
    name VARCHAR(190) NOT NULL,
    amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    frequency VARCHAR(40) NOT NULL DEFAULT 'monthly',
    due_day TINYINT UNSIGNED NULL DEFAULT NULL,
    start_date DATE NULL DEFAULT NULL,
    end_date DATE NULL DEFAULT NULL,
    status VARCHAR(30) NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (id),
    KEY household_status (household_id, status),
    KEY category_id (category_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hh_bank_imports (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    household_id BIGINT UNSIGNED NOT NULL,
    account_id BIGINT UNSIGNED NULL DEFAULT NULL,
    file_id BIGINT UNSIGNED NULL DEFAULT NULL,
    import_name VARCHAR(190) NOT NULL,
    source_bank VARCHAR(120) NULL DEFAULT NULL,
    row_count INT UNSIGNED NOT NULL DEFAULT 0,
    imported_by_member_id BIGINT UNSIGNED NULL DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY household_id (household_id),
    KEY account_id (account_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hh_bank_transactions (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    bank_import_id BIGINT UNSIGNED NOT NULL,
    account_id BIGINT UNSIGNED NULL DEFAULT NULL,
    transaction_date DATE NOT NULL,
    description VARCHAR(255) NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    balance DECIMAL(12,2) NULL DEFAULT NULL,
    reference VARCHAR(190) NULL DEFAULT NULL,
    raw_row JSON NULL,
    matched_transaction_id BIGINT UNSIGNED NULL DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY bank_import_id (bank_import_id),
    KEY account_date (account_id, transaction_date),
    KEY matched_transaction_id (matched_transaction_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Groceries

CREATE TABLE IF NOT EXISTS hh_grocery_stores (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    household_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(160) NOT NULL,
    slug VARCHAR(100) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY household_slug (household_id, slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hh_grocery_catalogue (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    household_id BIGINT UNSIGNED NOT NULL,
    store_id BIGINT UNSIGNED NULL DEFAULT NULL,
    name VARCHAR(190) NOT NULL,
    category VARCHAR(120) NOT NULL DEFAULT 'General',
    unit VARCHAR(60) NULL DEFAULT NULL,
    price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    notes VARCHAR(255) NULL DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (id),
    KEY household_category (household_id, category),
    KEY store_id (store_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hh_grocery_templates (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    household_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(190) NOT NULL,
    frequency VARCHAR(40) NOT NULL DEFAULT 'weekly',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (id),
    KEY household_id (household_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hh_grocery_template_items (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    template_id BIGINT UNSIGNED NOT NULL,
    catalogue_item_id BIGINT UNSIGNED NULL DEFAULT NULL,
    name VARCHAR(190) NOT NULL,
    store_id BIGINT UNSIGNED NULL DEFAULT NULL,
    category VARCHAR(120) NOT NULL DEFAULT 'General',
    quantity DECIMAL(8,2) NOT NULL DEFAULT 1.00,
    unit VARCHAR(60) NULL DEFAULT NULL,
    price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    sort_order SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    notes VARCHAR(255) NULL DEFAULT NULL,
    PRIMARY KEY (id),
    KEY template_id (template_id),
    KEY catalogue_item_id (catalogue_item_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hh_shopping_lists (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    household_id BIGINT UNSIGNED NOT NULL,
    budget_month_id BIGINT UNSIGNED NULL DEFAULT NULL,
    store_id BIGINT UNSIGNED NULL DEFAULT NULL,
    name VARCHAR(190) NOT NULL,
    list_type VARCHAR(40) NOT NULL DEFAULT 'weekly',
    budget_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    status VARCHAR(30) NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (id),
    KEY household_status (household_id, status),
    KEY budget_month_id (budget_month_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hh_shopping_items (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    shopping_list_id BIGINT UNSIGNED NOT NULL,
    catalogue_item_id BIGINT UNSIGNED NULL DEFAULT NULL,
    name VARCHAR(190) NOT NULL,
    quantity DECIMAL(8,2) NOT NULL DEFAULT 1.00,
    unit VARCHAR(60) NULL DEFAULT NULL,
    price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    is_purchased TINYINT(1) NOT NULL DEFAULT 0,
    sort_order SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    notes VARCHAR(255) NULL DEFAULT NULL,
    PRIMARY KEY (id),
    KEY shopping_list_id (shopping_list_id),
    KEY catalogue_item_id (catalogue_item_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Health

CREATE TABLE IF NOT EXISTS hh_health_weight_logs (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    member_id BIGINT UNSIGNED NOT NULL,
    log_date DATE NOT NULL,
    weight_kg DECIMAL(5,2) NULL DEFAULT NULL,
    body_fat_pct DECIMAL(4,1) NULL DEFAULT NULL,
    waist_cm DECIMAL(5,1) NULL DEFAULT NULL,
    hips_cm DECIMAL(5,1) NULL DEFAULT NULL,
    chest_cm DECIMAL(5,1) NULL DEFAULT NULL,
    arms_cm DECIMAL(5,1) NULL DEFAULT NULL,
    thighs_cm DECIMAL(5,1) NULL DEFAULT NULL,
    neck_cm DECIMAL(5,1) NULL DEFAULT NULL,
    notes TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY member_log_date (member_id, log_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hh_health_vitals_logs (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    member_id BIGINT UNSIGNED NOT NULL,
    log_date DATE NOT NULL,
    bp_systolic SMALLINT UNSIGNED NULL DEFAULT NULL,
    bp_diastolic SMALLINT UNSIGNED NULL DEFAULT NULL,
    heart_rate SMALLINT UNSIGNED NULL DEFAULT NULL,
    blood_glucose DECIMAL(5,2) NULL DEFAULT NULL,
    cholesterol DECIMAL(5,2) NULL DEFAULT NULL,
    haemoglobin DECIMAL(5,2) NULL DEFAULT NULL,
    uric_acid DECIMAL(5,2) NULL DEFAULT NULL,
    notes TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY member_log_date (member_id, log_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hh_health_exercises (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    household_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(160) NOT NULL,
    category VARCHAR(80) NULL DEFAULT NULL,
    muscle_group VARCHAR(120) NULL DEFAULT NULL,
    equipment VARCHAR(120) NULL DEFAULT NULL,
    description TEXT NULL,
    video_url VARCHAR(500) NULL DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY household_category (household_id, category)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hh_health_workout_plans (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    household_id BIGINT UNSIGNED NOT NULL,
    member_id BIGINT UNSIGNED NULL DEFAULT NULL,
    name VARCHAR(160) NOT NULL,
    description TEXT NULL,
    duration_weeks TINYINT UNSIGNED NOT NULL DEFAULT 4,
    status VARCHAR(30) NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (id),
    KEY household_status (household_id, status),
    KEY member_id (member_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hh_health_workout_plan_days (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    plan_id BIGINT UNSIGNED NOT NULL,
    day_of_week TINYINT UNSIGNED NOT NULL,
    day_label VARCHAR(80) NULL DEFAULT NULL,
    is_rest_day TINYINT(1) NOT NULL DEFAULT 0,
    sort_order SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    PRIMARY KEY (id),
    KEY plan_id (plan_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hh_health_workout_plan_exercises (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    plan_day_id BIGINT UNSIGNED NOT NULL,
    exercise_id BIGINT UNSIGNED NOT NULL,
    sets TINYINT UNSIGNED NULL DEFAULT NULL,
    reps VARCHAR(40) NULL DEFAULT NULL,
    weight_kg DECIMAL(6,2) NULL DEFAULT NULL,
    duration_mins SMALLINT UNSIGNED NULL DEFAULT NULL,
    rest_secs SMALLINT UNSIGNED NULL DEFAULT NULL,
    sort_order SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    notes TEXT NULL,
    PRIMARY KEY (id),
    KEY plan_day_id (plan_day_id),
    KEY exercise_id (exercise_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hh_health_workout_logs (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    member_id BIGINT UNSIGNED NOT NULL,
    plan_id BIGINT UNSIGNED NULL DEFAULT NULL,
    log_date DATE NOT NULL,
    duration_mins SMALLINT UNSIGNED NULL DEFAULT NULL,
    effort_rating TINYINT UNSIGNED NULL DEFAULT NULL,
    notes TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY member_log_date (member_id, log_date),
    KEY plan_id (plan_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hh_health_workout_log_sets (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    workout_log_id BIGINT UNSIGNED NOT NULL,
    exercise_id BIGINT UNSIGNED NOT NULL,
    set_number TINYINT UNSIGNED NOT NULL DEFAULT 1,
    reps SMALLINT UNSIGNED NULL DEFAULT NULL,
    weight_kg DECIMAL(6,2) NULL DEFAULT NULL,
    duration_mins SMALLINT UNSIGNED NULL DEFAULT NULL,
    notes TEXT NULL,
    PRIMARY KEY (id),
    KEY workout_log_id (workout_log_id),
    KEY exercise_id (exercise_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hh_health_foods (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    household_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(190) NOT NULL,
    category VARCHAR(120) NULL DEFAULT NULL,
    calories_per_100g DECIMAL(6,2) NULL DEFAULT NULL,
    protein_per_100g DECIMAL(5,2) NULL DEFAULT NULL,
    carbs_per_100g DECIMAL(5,2) NULL DEFAULT NULL,
    fat_per_100g DECIMAL(5,2) NULL DEFAULT NULL,
    fibre_per_100g DECIMAL(5,2) NULL DEFAULT NULL,
    sodium_per_100g DECIMAL(6,2) NULL DEFAULT NULL,
    food_status VARCHAR(30) NOT NULL DEFAULT 'allowed',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY household_category (household_id, category)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hh_health_meal_plans (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    household_id BIGINT UNSIGNED NOT NULL,
    member_id BIGINT UNSIGNED NULL DEFAULT NULL,
    name VARCHAR(160) NOT NULL,
    description TEXT NULL,
    calorie_target SMALLINT UNSIGNED NULL DEFAULT NULL,
    protein_target_g SMALLINT UNSIGNED NULL DEFAULT NULL,
    carbs_target_g SMALLINT UNSIGNED NULL DEFAULT NULL,
    fat_target_g SMALLINT UNSIGNED NULL DEFAULT NULL,
    is_template TINYINT(1) NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (id),
    KEY household_id (household_id),
    KEY member_id (member_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hh_health_meal_plan_items (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    meal_plan_id BIGINT UNSIGNED NOT NULL,
    food_id BIGINT UNSIGNED NOT NULL,
    day_of_week TINYINT UNSIGNED NULL DEFAULT NULL,
    meal_type VARCHAR(40) NOT NULL,
    quantity_g DECIMAL(7,1) NOT NULL DEFAULT 100.0,
    notes TEXT NULL,
    PRIMARY KEY (id),
    KEY meal_plan_id (meal_plan_id),
    KEY food_id (food_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hh_health_food_logs (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    member_id BIGINT UNSIGNED NOT NULL,
    food_id BIGINT UNSIGNED NOT NULL,
    log_date DATE NOT NULL,
    meal_type VARCHAR(40) NOT NULL,
    quantity_g DECIMAL(7,1) NOT NULL DEFAULT 100.0,
    notes TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY member_log_date (member_id, log_date),
    KEY food_id (food_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Games

CREATE TABLE IF NOT EXISTS hh_game_platforms (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    household_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(120) NOT NULL,
    slug VARCHAR(80) NOT NULL,
    sort_order SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    PRIMARY KEY (id),
    UNIQUE KEY household_slug (household_id, slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hh_game_series (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    household_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(190) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY household_series (household_id, name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hh_games (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    household_id BIGINT UNSIGNED NOT NULL,
    platform_id BIGINT UNSIGNED NOT NULL,
    series_id BIGINT UNSIGNED NULL DEFAULT NULL,
    title VARCHAR(255) NOT NULL,
    ownership_type VARCHAR(40) NOT NULL DEFAULT 'digital',
    completion_status VARCHAR(40) NULL DEFAULT NULL,
    rating TINYINT UNSIGNED NULL DEFAULT NULL,
    priority_order INT UNSIGNED NOT NULL DEFAULT 0,
    cover_art_url VARCHAR(500) NULL DEFAULT NULL,
    description TEXT NULL,
    metacritic SMALLINT UNSIGNED NULL DEFAULT NULL,
    release_date DATE NULL DEFAULT NULL,
    developer VARCHAR(255) NULL DEFAULT NULL,
    publisher VARCHAR(255) NULL DEFAULT NULL,
    notes TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (id),
    KEY household_platform (household_id, platform_id),
    KEY series_id (series_id),
    KEY completion_status (completion_status),
    KEY priority_order (priority_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hh_game_play_sessions (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    game_id BIGINT UNSIGNED NOT NULL,
    member_id BIGINT UNSIGNED NULL DEFAULT NULL,
    played_on DATE NOT NULL,
    minutes_played SMALLINT UNSIGNED NULL DEFAULT NULL,
    notes TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY game_id (game_id),
    KEY member_played_on (member_id, played_on)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Home Keeper

CREATE TABLE IF NOT EXISTS hh_workers (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    household_id BIGINT UNSIGNED NOT NULL,
    display_name VARCHAR(160) NOT NULL,
    id_number VARCHAR(80) NULL DEFAULT NULL,
    phone VARCHAR(60) NULL DEFAULT NULL,
    email VARCHAR(190) NULL DEFAULT NULL,
    daily_rate DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    status VARCHAR(30) NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (id),
    KEY household_status (household_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hh_worker_months (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    worker_id BIGINT UNSIGNED NOT NULL,
    year SMALLINT UNSIGNED NOT NULL,
    month TINYINT UNSIGNED NOT NULL,
    status VARCHAR(30) NOT NULL DEFAULT 'open',
    daily_rate DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    total_days TINYINT UNSIGNED NOT NULL DEFAULT 0,
    total_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    closed_at DATETIME NULL DEFAULT NULL,
    notes TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY worker_year_month (worker_id, year, month),
    KEY status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hh_worker_work_days (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    worker_month_id BIGINT UNSIGNED NOT NULL,
    work_date DATE NOT NULL,
    day_type VARCHAR(40) NOT NULL DEFAULT 'regular',
    amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    notes VARCHAR(255) NULL DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY worker_month_date (worker_month_id, work_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hh_worker_payments (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    worker_month_id BIGINT UNSIGNED NOT NULL,
    expense_transaction_id BIGINT UNSIGNED NULL DEFAULT NULL,
    paid_date DATE NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    payment_method VARCHAR(100) NULL DEFAULT NULL,
    payment_reference VARCHAR(190) NULL DEFAULT NULL,
    notes TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY worker_month_id (worker_month_id),
    KEY expense_transaction_id (expense_transaction_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Documents

CREATE TABLE IF NOT EXISTS hh_document_categories (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    household_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(160) NOT NULL,
    slug VARCHAR(100) NOT NULL,
    module VARCHAR(80) NULL DEFAULT NULL,
    sort_order SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY household_slug (household_id, slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hh_documents (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    household_id BIGINT UNSIGNED NOT NULL,
    category_id BIGINT UNSIGNED NULL DEFAULT NULL,
    file_id BIGINT UNSIGNED NOT NULL,
    owner_member_id BIGINT UNSIGNED NULL DEFAULT NULL,
    title VARCHAR(190) NOT NULL,
    holder_name VARCHAR(190) NULL DEFAULT NULL,
    reference_number_encrypted LONGTEXT NULL,
    provider VARCHAR(190) NULL DEFAULT NULL,
    effective_date DATE NULL DEFAULT NULL,
    expiry_date DATE NULL DEFAULT NULL,
    monthly_amount_encrypted LONGTEXT NULL,
    notes TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL,
    deleted_at DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (id),
    KEY household_category (household_id, category_id),
    KEY file_id (file_id),
    KEY expiry_date (expiry_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hh_document_field_definitions (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    household_id BIGINT UNSIGNED NOT NULL,
    category_id BIGINT UNSIGNED NULL DEFAULT NULL,
    field_key VARCHAR(100) NOT NULL,
    label VARCHAR(160) NOT NULL,
    field_type VARCHAR(40) NOT NULL DEFAULT 'text',
    is_encrypted TINYINT(1) NOT NULL DEFAULT 0,
    sort_order SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY household_category_field (household_id, category_id, field_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hh_document_field_values (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    document_id BIGINT UNSIGNED NOT NULL,
    field_definition_id BIGINT UNSIGNED NOT NULL,
    field_value LONGTEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY document_field (document_id, field_definition_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hh_document_access_log (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    document_id BIGINT UNSIGNED NOT NULL,
    member_id BIGINT UNSIGNED NULL DEFAULT NULL,
    action VARCHAR(80) NOT NULL DEFAULT 'view',
    ip_address VARCHAR(64) NULL DEFAULT NULL,
    user_agent VARCHAR(255) NULL DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY document_id (document_id),
    KEY created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO hh_schema_migrations (migration) VALUES ('2026_05_11_000001_initial_schema');