CREATE TABLE budget_months (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  year INT NOT NULL,
  slug VARCHAR(32) NOT NULL,
  month_number TINYINT UNSIGNED NOT NULL,
  short_name VARCHAR(32) NOT NULL,
  range_label VARCHAR(32) NOT NULL,
  UNIQUE KEY uniq_budget_months_year_slug (year, slug),
  UNIQUE KEY uniq_budget_months_year_month (year, month_number)
);

CREATE TABLE budget_categories (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  year INT NULL,
  title VARCHAR(128) NOT NULL,
  import_sheet_name VARCHAR(128) NULL,
  import_needs_review_default BOOLEAN NOT NULL DEFAULT FALSE,
  hex_code CHAR(7) NOT NULL,
  includes_notes BOOLEAN NOT NULL DEFAULT FALSE,
  full_height BOOLEAN NOT NULL DEFAULT FALSE,
  display_order TINYINT UNSIGNED NOT NULL,
  UNIQUE KEY uniq_budget_categories_year_title (year, title)
);

CREATE TABLE monthly_category_budgets (
  month_id BIGINT UNSIGNED NOT NULL,
  category_id BIGINT UNSIGNED NOT NULL,
  budget_amount DECIMAL(12, 2) NOT NULL DEFAULT 0,
  PRIMARY KEY (month_id, category_id),
  CONSTRAINT fk_monthly_category_budgets_month
    FOREIGN KEY (month_id) REFERENCES budget_months(id)
    ON DELETE CASCADE,
  CONSTRAINT fk_monthly_category_budgets_category
    FOREIGN KEY (category_id) REFERENCES budget_categories(id)
    ON DELETE CASCADE
);

CREATE TABLE transaction_groups (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  year INT NOT NULL,
  entry_date DATE NOT NULL,
  company VARCHAR(255) NOT NULL,
  original_amount DECIMAL(12, 2) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_transaction_groups_year_date (year, entry_date)
);

CREATE TABLE monthly_entries (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  month_id BIGINT UNSIGNED NOT NULL,
  category_id BIGINT UNSIGNED NOT NULL,
  entry_date DATE NOT NULL,
  company VARCHAR(255) NOT NULL,
  description VARCHAR(255) NULL,
  amount DECIMAL(12, 2) NOT NULL,
  note TEXT NULL,
  needs_review BOOLEAN NOT NULL DEFAULT FALSE,
  transaction_group_id BIGINT UNSIGNED NULL,
  original_entry_kind VARCHAR(16) NULL,
  original_entry_id BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_monthly_entries_month_category (month_id, category_id),
  INDEX idx_monthly_entries_transaction_group (transaction_group_id),
  INDEX idx_monthly_entries_original_entry (original_entry_kind, original_entry_id),
  CONSTRAINT fk_monthly_entries_month
    FOREIGN KEY (month_id) REFERENCES budget_months(id)
    ON DELETE CASCADE,
  CONSTRAINT fk_monthly_entries_category
    FOREIGN KEY (category_id) REFERENCES budget_categories(id)
    ON DELETE CASCADE
);

CREATE TABLE income_people (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(64) NOT NULL,
  display_order TINYINT UNSIGNED NOT NULL,
  UNIQUE KEY uniq_income_people_name (name)
);

CREATE TABLE income_entries (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  month_id BIGINT UNSIGNED NOT NULL,
  person_id BIGINT UNSIGNED NOT NULL,
  entry_date DATE NOT NULL,
  amount DECIMAL(12, 2) NOT NULL,
  note TEXT NOT NULL,
  needs_review BOOLEAN NOT NULL DEFAULT FALSE,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_income_entries_month_person (month_id, person_id),
  CONSTRAINT fk_income_entries_month
    FOREIGN KEY (month_id) REFERENCES budget_months(id)
    ON DELETE CASCADE,
  CONSTRAINT fk_income_entries_person
    FOREIGN KEY (person_id) REFERENCES income_people(id)
    ON DELETE CASCADE
);

CREATE TABLE allocated_fund_categories (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  year INT NULL,
  title VARCHAR(128) NOT NULL,
  import_sheet_name VARCHAR(128) NULL,
  import_needs_review_default BOOLEAN NOT NULL DEFAULT FALSE,
  hex_code CHAR(7) NOT NULL,
  display_order TINYINT UNSIGNED NOT NULL,
  UNIQUE KEY uniq_allocated_fund_categories_year_title (year, title)
);

CREATE TABLE allocated_fund_settings (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  year INT NOT NULL,
  category_id BIGINT UNSIGNED NOT NULL,
  carryover DECIMAL(12, 2) NOT NULL DEFAULT 0,
  explained TEXT NULL,
  UNIQUE KEY uniq_allocated_fund_settings_year_category (year, category_id),
  CONSTRAINT fk_allocated_fund_settings_category
    FOREIGN KEY (category_id) REFERENCES allocated_fund_categories(id)
    ON DELETE CASCADE
);

CREATE TABLE allocated_fund_entries (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  year INT NOT NULL,
  category_id BIGINT UNSIGNED NOT NULL,
  entry_date DATE NOT NULL,
  company VARCHAR(255) NOT NULL,
  description VARCHAR(255) NULL,
  amount DECIMAL(12, 2) NOT NULL,
  exclude_from_total_spend BOOLEAN NOT NULL DEFAULT FALSE,
  needs_review BOOLEAN NOT NULL DEFAULT FALSE,
  transaction_group_id BIGINT UNSIGNED NULL,
  original_entry_kind VARCHAR(16) NULL,
  original_entry_id BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_allocated_fund_entries_year_category (year, category_id),
  INDEX idx_allocated_fund_entries_transaction_group (transaction_group_id),
  INDEX idx_allocated_fund_entries_original_entry (original_entry_kind, original_entry_id),
  CONSTRAINT fk_allocated_fund_entries_category
    FOREIGN KEY (category_id) REFERENCES allocated_fund_categories(id)
    ON DELETE CASCADE
);

CREATE TABLE overview_categories (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  year INT NULL,
  title VARCHAR(160) NOT NULL,
  entry_type VARCHAR(16) NOT NULL,
  classification VARCHAR(32) NOT NULL,
  display_order SMALLINT UNSIGNED NOT NULL,
  UNIQUE KEY uniq_overview_categories_year_title (year, title)
);

CREATE TABLE overview_category_settings (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  year INT NOT NULL,
  category_id BIGINT UNSIGNED NOT NULL,
  interest_rate DECIMAL(7, 2) NOT NULL DEFAULT 0,
  minimum_payment DECIMAL(12, 2) NOT NULL DEFAULT 0,
  show_growth BOOLEAN NOT NULL DEFAULT TRUE,
  UNIQUE KEY uniq_overview_category_settings_year_category (year, category_id),
  CONSTRAINT fk_overview_category_settings_category
    FOREIGN KEY (category_id) REFERENCES overview_categories(id)
    ON DELETE CASCADE
);

CREATE TABLE overview_monthly_values (
  year INT NOT NULL,
  category_id BIGINT UNSIGNED NOT NULL,
  month_index TINYINT UNSIGNED NOT NULL,
  amount DECIMAL(12, 2) NOT NULL DEFAULT 0,
  PRIMARY KEY (year, category_id, month_index),
  CONSTRAINT fk_overview_monthly_values_category
    FOREIGN KEY (category_id) REFERENCES overview_categories(id)
    ON DELETE CASCADE
);

CREATE TABLE app_admin_users (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(64) NOT NULL,
  password_salt CHAR(32) NOT NULL,
  password_hash CHAR(128) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_app_admin_users_username (username)
);
