-- Import/Export Job Tracking
-- Track bulk import and export operations

CREATE TABLE IF NOT EXISTS import_jobs (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID,
  filename TEXT NOT NULL,
  file_type TEXT NOT NULL, -- 'csv', 'json'
  status TEXT DEFAULT 'pending', -- 'pending', 'processing', 'completed', 'failed'
  total_rows INTEGER,
  processed_rows INTEGER DEFAULT 0,
  success_count INTEGER DEFAULT 0,
  error_count INTEGER DEFAULT 0,
  errors JSONB DEFAULT '[]', -- Array of error details
  field_mapping JSONB DEFAULT '{}', -- Column mapping config
  options JSONB DEFAULT '{}', -- Import options (skip duplicates, etc.)
  created_at TIMESTAMPTZ DEFAULT NOW(),
  started_at TIMESTAMPTZ,
  completed_at TIMESTAMPTZ
);

CREATE TABLE IF NOT EXISTS export_jobs (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID,
  export_type TEXT NOT NULL, -- 'leads', 'communications', 'analytics', 'activity'
  filters JSONB DEFAULT '{}', -- Applied filters
  columns TEXT[] DEFAULT '{}', -- Columns to include
  format TEXT DEFAULT 'csv', -- 'csv', 'json'
  status TEXT DEFAULT 'pending', -- 'pending', 'processing', 'completed', 'failed'
  row_count INTEGER,
  file_url TEXT, -- Download URL (could be presigned)
  file_size_bytes INTEGER,
  error_message TEXT,
  expires_at TIMESTAMPTZ, -- When the download link expires
  created_at TIMESTAMPTZ DEFAULT NOW(),
  completed_at TIMESTAMPTZ
);

-- Indexes
CREATE INDEX IF NOT EXISTS idx_import_jobs_user ON import_jobs(user_id);
CREATE INDEX IF NOT EXISTS idx_import_jobs_status ON import_jobs(status);
CREATE INDEX IF NOT EXISTS idx_import_jobs_created ON import_jobs(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_export_jobs_user ON export_jobs(user_id);
CREATE INDEX IF NOT EXISTS idx_export_jobs_status ON export_jobs(status);
CREATE INDEX IF NOT EXISTS idx_export_jobs_created ON export_jobs(created_at DESC);

-- RLS policies
ALTER TABLE import_jobs ENABLE ROW LEVEL SECURITY;
ALTER TABLE export_jobs ENABLE ROW LEVEL SECURITY;

-- Users can only see their own jobs
CREATE POLICY "import_jobs_user_access"
  ON import_jobs FOR ALL TO authenticated
  USING (user_id = auth.uid() OR auth.uid() IN (SELECT user_id FROM user_xp WHERE role = 'admin'))
  WITH CHECK (user_id = auth.uid());

CREATE POLICY "import_jobs_service_all"
  ON import_jobs FOR ALL TO service_role
  USING (true) WITH CHECK (true);

CREATE POLICY "export_jobs_user_access"
  ON export_jobs FOR ALL TO authenticated
  USING (user_id = auth.uid() OR auth.uid() IN (SELECT user_id FROM user_xp WHERE role = 'admin'))
  WITH CHECK (user_id = auth.uid());

CREATE POLICY "export_jobs_service_all"
  ON export_jobs FOR ALL TO service_role
  USING (true) WITH CHECK (true);

-- Function to clean up old export jobs (run via cron)
CREATE OR REPLACE FUNCTION cleanup_old_export_jobs()
RETURNS void AS $$
BEGIN
  -- Delete completed exports older than 7 days
  DELETE FROM export_jobs
  WHERE status = 'completed'
    AND completed_at < NOW() - INTERVAL '7 days';

  -- Delete failed exports older than 30 days
  DELETE FROM export_jobs
  WHERE status = 'failed'
    AND created_at < NOW() - INTERVAL '30 days';

  -- Delete old import jobs (completed older than 30 days)
  DELETE FROM import_jobs
  WHERE status IN ('completed', 'failed')
    AND created_at < NOW() - INTERVAL '30 days';
END;
$$ LANGUAGE plpgsql;
