DROP TABLE IF EXISTS daily_intake;
DROP TABLE IF EXISTS supplements;
DROP TABLE IF EXISTS users;

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(100) NOT NULL UNIQUE,
  password VARCHAR(255) NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE supplements (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  brand VARCHAR(255),
  form VARCHAR(100),
  dose_amount DECIMAL(10,2),
  dose_unit VARCHAR(20),
  schedule_time VARCHAR(100),
  purpose VARCHAR(255),
  notes TEXT,
  tags TEXT,
  status ENUM('Active','Paused','Stopped') DEFAULT 'Active',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE daily_intake (
  id INT AUTO_INCREMENT PRIMARY KEY,
  intake_date DATE NOT NULL,
  supplement_id INT NOT NULL,
  taken TINYINT(1) DEFAULT 0,
  FOREIGN KEY (supplement_id) REFERENCES supplements(id) ON DELETE CASCADE
) ENGINE=InnoDB;