-- Admin Logs Table for System Activity Tracking
CREATE TABLE IF NOT EXISTS admin_logs (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  admin_id UUID REFERENCES auth.users(id) ON DELETE SET NULL,
  action TEXT NOT NULL,
  entity_type TEXT NOT NULL,
  entity_id TEXT,
  details JSONB DEFAULT '{}'::jsonb,
  ip_address TEXT,
  user_agent TEXT,
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX idx_admin_logs_admin_id ON admin_logs(admin_id);
CREATE INDEX idx_admin_logs_created_at ON admin_logs(created_at DESC);
CREATE INDEX idx_admin_logs_action ON admin_logs(action);
CREATE INDEX idx_admin_logs_entity_type ON admin_logs(entity_type);

-- Admin Analytics Table for Metrics Storage
CREATE TABLE IF NOT EXISTS admin_analytics (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  metric_name TEXT NOT NULL,
  metric_value NUMERIC NOT NULL,
  metric_type TEXT NOT NULL, -- 'count', 'revenue', 'percentage', etc.
  period TEXT NOT NULL, -- 'daily', 'weekly', 'monthly'
  period_start TIMESTAMPTZ NOT NULL,
  period_end TIMESTAMPTZ NOT NULL,
  metadata JSONB DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX idx_admin_analytics_metric_name ON admin_analytics(metric_name);
CREATE INDEX idx_admin_analytics_period ON admin_analytics(period, period_start DESC);

-- Admin Images Table for Image Management
CREATE TABLE IF NOT EXISTS admin_images (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  uploaded_by UUID REFERENCES auth.users(id) ON DELETE SET NULL,
  title TEXT NOT NULL,
  description TEXT,
  file_path TEXT NOT NULL,
  file_size INTEGER,
  mime_type TEXT,
  width INTEGER,
  height INTEGER,
  generation_method TEXT, -- 'upload', 'ai_generated'
  generation_prompt TEXT,
  tags TEXT[] DEFAULT ARRAY[]::TEXT[],
  metadata JSONB DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX idx_admin_images_uploaded_by ON admin_images(uploaded_by);
CREATE INDEX idx_admin_images_created_at ON admin_images(created_at DESC);
CREATE INDEX idx_admin_images_tags ON admin_images USING GIN(tags);
CREATE INDEX idx_admin_images_generation_method ON admin_images(generation_method);

-- Admin Search History Table
CREATE TABLE IF NOT EXISTS admin_search_history (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  admin_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
  search_type TEXT NOT NULL, -- 'ai_search', 'smart_search'
  query TEXT NOT NULL,
  results_count INTEGER DEFAULT 0,
  response_time_ms INTEGER,
  metadata JSONB DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX idx_admin_search_history_admin_id ON admin_search_history(admin_id);
CREATE INDEX idx_admin_search_history_created_at ON admin_search_history(created_at DESC);
CREATE INDEX idx_admin_search_history_search_type ON admin_search_history(search_type);

-- RLS Policies (Admin Only Access)
ALTER TABLE admin_logs ENABLE ROW LEVEL SECURITY;
ALTER TABLE admin_analytics ENABLE ROW LEVEL SECURITY;
ALTER TABLE admin_images ENABLE ROW LEVEL SECURITY;
ALTER TABLE admin_search_history ENABLE ROW LEVEL SECURITY;

-- Admin Logs Policies
CREATE POLICY "Admins can view all logs"
  ON admin_logs FOR SELECT
  TO authenticated
  USING (
    EXISTS (
      SELECT 1 FROM profiles
      WHERE profiles.id = auth.uid()
      AND profiles.role = 'admin'
    )
  );

CREATE POLICY "Admins can insert logs"
  ON admin_logs FOR INSERT
  TO authenticated
  WITH CHECK (
    EXISTS (
      SELECT 1 FROM profiles
      WHERE profiles.id = auth.uid()
      AND profiles.role = 'admin'
    )
  );

-- Admin Analytics Policies
CREATE POLICY "Admins can view analytics"
  ON admin_analytics FOR SELECT
  TO authenticated
  USING (
    EXISTS (
      SELECT 1 FROM profiles
      WHERE profiles.id = auth.uid()
      AND profiles.role = 'admin'
    )
  );

CREATE POLICY "Admins can manage analytics"
  ON admin_analytics FOR ALL
  TO authenticated
  USING (
    EXISTS (
      SELECT 1 FROM profiles
      WHERE profiles.id = auth.uid()
      AND profiles.role = 'admin'
    )
  );

-- Admin Images Policies
CREATE POLICY "Admins can view all images"
  ON admin_images FOR SELECT
  TO authenticated
  USING (
    EXISTS (
      SELECT 1 FROM profiles
      WHERE profiles.id = auth.uid()
      AND profiles.role = 'admin'
    )
  );

CREATE POLICY "Admins can manage images"
  ON admin_images FOR ALL
  TO authenticated
  USING (
    EXISTS (
      SELECT 1 FROM profiles
      WHERE profiles.id = auth.uid()
      AND profiles.role = 'admin'
    )
  );

-- Admin Search History Policies
CREATE POLICY "Admins can view their search history"
  ON admin_search_history FOR SELECT
  TO authenticated
  USING (
    admin_id = auth.uid() AND
    EXISTS (
      SELECT 1 FROM profiles
      WHERE profiles.id = auth.uid()
      AND profiles.role = 'admin'
    )
  );

CREATE POLICY "Admins can insert search history"
  ON admin_search_history FOR INSERT
  TO authenticated
  WITH CHECK (
    admin_id = auth.uid() AND
    EXISTS (
      SELECT 1 FROM profiles
      WHERE profiles.id = auth.uid()
      AND profiles.role = 'admin'
    )
  );

-- Helper Functions
CREATE OR REPLACE FUNCTION log_admin_action(
  p_action TEXT,
  p_entity_type TEXT,
  p_entity_id TEXT DEFAULT NULL,
  p_details JSONB DEFAULT '{}'::jsonb
)
RETURNS UUID
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
  v_log_id UUID;
BEGIN
  INSERT INTO admin_logs (admin_id, action, entity_type, entity_id, details)
  VALUES (auth.uid(), p_action, p_entity_type, p_entity_id, p_details)
  RETURNING id INTO v_log_id;
  
  RETURN v_log_id;
END;
$$;

-- Function to get admin dashboard stats
CREATE OR REPLACE FUNCTION get_admin_dashboard_stats()
RETURNS JSONB
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
  v_stats JSONB;
BEGIN
  -- Check if user is admin
  IF NOT EXISTS (
    SELECT 1 FROM profiles
    WHERE id = auth.uid() AND role = 'admin'
  ) THEN
    RAISE EXCEPTION 'Unauthorized';
  END IF;

  SELECT jsonb_build_object(
    'total_users', (SELECT COUNT(*) FROM profiles),
    'total_trips', (SELECT COUNT(*) FROM trips),
    'total_places', (SELECT COUNT(*) FROM places),
    'total_leads', (SELECT COUNT(*) FROM provider_leads),
    'total_providers', (SELECT COUNT(*) FROM provider_profiles),
    'active_providers', (SELECT COUNT(*) FROM provider_profiles WHERE is_active = true),
    'total_revenue', (SELECT COALESCE(SUM(amount), 0) FROM provider_transactions WHERE type = 'lead_purchase'),
    'recent_activity_count', (SELECT COUNT(*) FROM admin_logs WHERE created_at > now() - interval '24 hours'),
    'pending_leads', (SELECT COUNT(*) FROM provider_leads WHERE status = 'available'),
    'sold_leads', (SELECT COUNT(*) FROM provider_leads WHERE status = 'sold')
  ) INTO v_stats;

  RETURN v_stats;
END;
$$;