-- Users table
CREATE TABLE IF NOT EXISTS users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  first_name TEXT NOT NULL,
  last_name TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE,
  mobile TEXT,
  address TEXT,
  lat DECIMAL,
  lng DECIMAL,
  collection_day TEXT,
  notify_email BOOLEAN DEFAULT false,
  notify_sms BOOLEAN DEFAULT false,
  reminder_slot TEXT DEFAULT 'morning' CHECK (reminder_slot IN ('morning', 'afternoon', 'evening')),
  password_hash TEXT NOT NULL DEFAULT '',
  email_verified BOOLEAN DEFAULT false,
  verification_token UUID DEFAULT gen_random_uuid(),
  verification_expires TIMESTAMPTZ DEFAULT (now() + interval '24 hours'),
  created_at TIMESTAMPTZ DEFAULT now()
);

-- Notifications log table
CREATE TABLE IF NOT EXISTS notifications (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  collection_date DATE NOT NULL,
  channel TEXT NOT NULL CHECK (channel IN ('email', 'sms')),
  status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'sent', 'failed')),
  sent_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT now()
);

-- Index for reminder queries (find users by collection day + reminder slot)
CREATE INDEX IF NOT EXISTS idx_users_reminders ON users (collection_day, reminder_slot) WHERE notify_email = true OR notify_sms = true;

-- Index for notification lookups
CREATE INDEX IF NOT EXISTS idx_notifications_user ON notifications (user_id, collection_date);