CREATE DATABASE IF NOT EXISTS saving_app CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE saving_app;
CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(100) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS wallets (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  name VARCHAR(150) NOT NULL,
  goal_amount INT NOT NULL,
  days INT NOT NULL,
  max_daily INT NOT NULL,
  unit INT NOT NULL DEFAULT 50,
  upi_id VARCHAR(150) DEFAULT NULL,
  telegram_chat_id VARCHAR(100) DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS wallet_plan (
  id INT AUTO_INCREMENT PRIMARY KEY,
  wallet_id INT NOT NULL,
  day_no INT NOT NULL,
  amount INT NOT NULL,
  paid TINYINT(1) DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (wallet_id) REFERENCES wallets(id) ON DELETE CASCADE,
  UNIQUE(wallet_id, day_no)
);
