-- Roommate Expense Tracker — Database Schema
-- Import this via phpMyAdmin (cPanel) into your MySQL database

CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  username VARCHAR(50) NOT NULL UNIQUE,
  email VARCHAR(150) DEFAULT NULL,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('admin','member') NOT NULL DEFAULT 'member',
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  avatar_color VARCHAR(7) NOT NULL DEFAULT '#5E5CE6',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS reminders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  from_user INT NOT NULL,
  to_user INT NOT NULL,
  amount DECIMAL(10,2) DEFAULT NULL,
  message VARCHAR(255) NOT NULL,
  is_read BOOLEAN NOT NULL DEFAULT FALSE,
  email_sent BOOLEAN NOT NULL DEFAULT FALSE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (from_user) REFERENCES users(id),
  FOREIGN KEY (to_user) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL UNIQUE,
  icon VARCHAR(10) NOT NULL DEFAULT '🏷️',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS expenses (
  id INT AUTO_INCREMENT PRIMARY KEY,
  paid_by INT NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  category_id INT NOT NULL,
  description VARCHAR(255) DEFAULT '',
  is_shared BOOLEAN NOT NULL DEFAULT TRUE,
  expense_date DATE NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (paid_by) REFERENCES users(id),
  FOREIGN KEY (category_id) REFERENCES categories(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS expense_splits (
  id INT AUTO_INCREMENT PRIMARY KEY,
  expense_id INT NOT NULL,
  user_id INT NOT NULL,
  share_amount DECIMAL(10,2) NOT NULL,
  FOREIGN KEY (expense_id) REFERENCES expenses(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS settlements (
  id INT AUTO_INCREMENT PRIMARY KEY,
  paid_by INT NOT NULL,
  paid_to INT NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  note VARCHAR(255) DEFAULT '',
  status ENUM('confirmed','pending','declined') NOT NULL DEFAULT 'confirmed',
  settled_at DATE NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  confirmed_at TIMESTAMP NULL DEFAULT NULL,
  FOREIGN KEY (paid_by) REFERENCES users(id),
  FOREIGN KEY (paid_to) REFERENCES users(id)
) ENGINE=InnoDB;

-- Starter categories
INSERT INTO categories (name, icon) VALUES
  ('Food', '🍛'),
  ('Bill', '💡'),
  ('Shopping', '🛍️'),
  ('Rickshaw/Transport', '🛺'),
  ('Groceries', '🧺'),
  ('Other', '📦');

-- ============================================
-- MIGRATION: if you already imported the app before this update,
-- run ONLY these lines instead of the full schema above:
--   ALTER TABLE users ADD COLUMN email VARCHAR(150) DEFAULT NULL;
--   CREATE TABLE reminders (...) -- see previous migration note in git history
--   ALTER TABLE settlements ADD COLUMN status ENUM('confirmed','pending','declined') NOT NULL DEFAULT 'confirmed';
--   ALTER TABLE settlements ADD COLUMN confirmed_at TIMESTAMP NULL DEFAULT NULL;
-- ============================================

-- NOTE: The admin user is created automatically by install.php (not here).
-- If you're setting this up manually instead of using install.php, create
-- your admin manually with a bcrypt password hash.