CREATE TABLE IF NOT EXISTS money_goals (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  goal_name VARCHAR(255) NOT NULL,
  target_amount DECIMAL(18,2) NOT NULL DEFAULT 0,
  current_amount DECIMAL(18,2) NOT NULL DEFAULT 0,
  status VARCHAR(50) NOT NULL DEFAULT 'active',
  due_date DATE NULL,
  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;

CREATE TABLE IF NOT EXISTS money_monthly_stories (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  month_key CHAR(7) NOT NULL,
  story_text LONGTEXT NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_month_key (month_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO money_goals (goal_name, target_amount, current_amount, status, due_date)
VALUES
('Build emergency fund', 50000, 12000, 'active', DATE_ADD(CURDATE(), INTERVAL 6 MONTH)),
('Reduce unplanned spending', 10000, 3500, 'active', DATE_ADD(CURDATE(), INTERVAL 2 MONTH))
ON DUPLICATE KEY UPDATE goal_name = goal_name;
